MS SQL Server Online Test

🎓 Top 15 Udemy Courses (80-90% Discount): My Udemy Courses - Ramesh Fadatare — All my Udemy courses are real-time and project oriented courses.

▶️ Subscribe to My YouTube Channel (178K+ subscribers): Java Guides on YouTube

▶️ For AI, ChatGPT, Web, Tech, and Generative AI, subscribe to another channel: Ramesh Fadatare on YouTube

Welcome to the MS-SQL Server Online Test! We will present 25 MCQs (Multiple-Choice Questions) to test your knowledge of the MS-SQL Server database.

You can select the correct answer for each question and submit the test. You will get your online test score after finishing the complete test.

1. What is the primary function of the SQL Server TRANSACTION statement?

BEGIN TRANSACTION;
UPDATE Accounts SET balance = balance - 100 WHERE account_id = 1234;
COMMIT;

a) To start a new database

b) To execute a series of SQL operations as a single unit of work

c) To delete data from the database

d) To create a new table

2. What does the following SQL Server query do?

SELECT TOP 5 * FROM Employees ORDER BY Salary DESC;

a) Selects all employees with the top 5 salaries

b) Selects the first 5 entries from the Employees table

c) Deletes the top 5 salaries from the Employees table

d) Updates the top 5 salaries

3. How do you handle errors in SQL Server using a TRY-CATCH block?

BEGIN TRY
-- Code that might cause an error goes here
INSERT INTO Products (ProductID, ProductName) VALUES (1, 'New Product')
END TRY
BEGIN CATCH
-- Code to handle the error goes here
SELECT ERROR_MESSAGE() AS ErrorMessage;
END CATCH

a) The given query

b) The TRY block executes error-free code, the CATCH block catches and resolves errors

c) The TRY block stores data, and the CATCH block deletes data

d) Both blocks perform the same operations

4. What is the purpose of the FULL OUTER JOIN keyword in SQL Server?

SELECT a.EmployeeName, b.DepartmentName FROM Employees a FULL OUTER JOIN Departments b ON a.DepartmentID = b.DepartmentID;

a) To return only the rows from both tables where the join condition is met

b) To return all rows from both tables, with matching rows from both sides where available

c) To delete entries from both tables that do not match

d) To update matching entries in both tables

5. How do you create a stored procedure in SQL Server?

CREATE PROCEDURE GetEmployeeInfo
AS
SELECT * FROM Employees;
GO

a) The given query

b) CREATE PROCEDURE GetEmployeeInfo: SELECT * FROM Employees;

c) DEFINE PROCEDURE GetEmployeeInfo AS SELECT * FROM Employees;

d) EXECUTE PROCEDURE GetEmployeeInfo AS SELECT * FROM Employees;

6. What does the SQL Server OUTPUT clause do?

DELETE FROM Customers
OUTPUT DELETED.*
WHERE CustomerID = 10;

a) It returns the deleted customer records as output

b) It outputs the total count of deleted records

c) It saves deleted records into another table

d) It prevents deletion if the OUTPUT condition fails

7. What is the SQL Server function to get the current date and time?

SELECT GETDATE();

a) The given query

b) SELECT SYSDATE();

c) SELECT CURRENT_TIMESTAMP();

d) SELECT NOW();

8. What does the following SQL Server query compute?

SELECT DATEDIFF(year, '2000-01-01', '2020-12-31');

a) The difference in months between the two dates

b) The difference in days between the two dates

c) The difference in years between the two dates

d) The difference in hours between the two dates

9. How do you add a default constraint to a column in SQL Server?

ALTER TABLE Orders
ADD CONSTRAINT DF_ShipDate DEFAULT GETDATE() FOR ShipDate;

a) The given query

b) ALTER TABLE Orders SET DEFAULT GETDATE() FOR ShipDate;

c) MODIFY TABLE Orders ADD DEFAULT GETDATE() FOR ShipDate;

d) UPDATE TABLE Orders SET DEFAULT GETDATE() FOR ShipDate;

10. What is the use of the RAISERROR function in SQL Server?

RAISERROR ('Error message here', 16, 1);

a) To generate an informational message

b) To create a new error log

c) To stop execution and return an error message

d) To clear all existing errors

11. How do you retrieve the first non-null value from a list in SQL Server?

SELECT COALESCE(NULL, NULL, 'First non-null', 'Second non-null') AS Result;

a) The given query

b) SELECT FIRST_VALUE(NULL, NULL, 'First non-null', 'Second non-null') AS Result;

c) SELECT FIRST_NON_NULL(NULL, NULL, 'First non-null', 'Second non-null') AS Result;

d) SELECT NVL(NULL, NULL, 'First non-null', 'Second non-null') AS Result;

12. What is the function of the SQL Server PARTITION BY clause in a SELECT statement?

SELECT ROW_NUMBER() OVER (PARTITION BY DepartmentID ORDER BY Salary DESC) AS RowNum, Name, Salary FROM Employees;

a) It limits the number of rows returned

b) It groups the result set by the specified columns before applying the ORDER BY clause

c) It applies an aggregate function to each group

d) It orders the entire result set by DepartmentID

13. How do you enforce data integrity for the 'Email' column to ensure it contains a valid email format in SQL Server?

ALTER TABLE Users ADD CONSTRAINT chk_Email CHECK (Email LIKE '%_@__%.__%');

a) The given query

b) ALTER TABLE Users MODIFY Email CHECK (Email LIKE '%_@__%.__%');

c) UPDATE TABLE Users SET Email = '%_@__%.__%' WHERE Email NOT LIKE '%_@__%.__%';

d) INSERT INTO Users (Email) VALUES ('%_@__%.__%') WHERE NOT EXISTS (SELECT * FROM Users WHERE Email LIKE '%_@__%.__%');

14. What is the primary key purpose in an SQL Server database table?

ALTER TABLE Employees ADD CONSTRAINT PK_EmployeeID PRIMARY KEY (EmployeeID);

a) To create a relationship between two tables

b) To ensure that each row in the table has a unique identifier

c) To encrypt data

d) To provide a default value

15. How do you create a new index on the 'CustomerName' column of the 'Customers' table for better query performance in SQL Server?

CREATE INDEX idx_CustomerName ON Customers (CustomerName);

a) The given query

b) CREATE NEW INDEX idx_CustomerName FOR Customers (CustomerName);

c) INDEX NEW idx_CustomerName ON Customers (CustomerName);

d) ADD INDEX idx_CustomerName ON Customers (CustomerName);

16. What does the UNIQUE constraint do in SQL Server?

ALTER TABLE Employees ADD CONSTRAINT UC_Email UNIQUE (Email);

a) Prevents duplicate values in a column

b) Checks for NULL values in a column

c) Automatically generates values for a column

d) Converts all entries in a column to unique identifiers

17. What is the correct SQL Server syntax to rollback a transaction?

BEGIN TRANSACTION;
UPDATE Accounts SET Balance = Balance - 100 WHERE AccountID = 1;
IF @@ERROR <> 0
ROLLBACK TRANSACTION;
ELSE
COMMIT TRANSACTION;

a) The given query

b) BEGIN TRANSACTION; COMMIT IF @@ERROR = 0 ELSE ROLLBACK;

c) START; IF @@ERROR <> 0 THEN ROLLBACK; END;

d) TRANSACTION START; IF @@ERROR <> 0 ROLLBACK; ELSE COMMIT;

18. How can you increase the number of worker threads available to SQL Server?

EXEC sp_configure 'max worker threads', 500;
RECONFIGURE;

a) The given query

b) SET 'max worker threads' = 500;

c) UPDATE CONFIGURATION SET 'max worker threads' = 500;

d) MODIFY SYSTEM SET 'max worker threads' = 500;

19. What is the purpose of the sp_helpdb stored procedure in SQL Server?

EXEC sp_helpdb 'AdventureWorks';

a) It creates a backup of the 'AdventureWorks' database

b) It provides information about the 'AdventureWorks' database

c) It restores the 'AdventureWorks' database from a backup

d) It deletes the 'AdventureWorks' database

20. How do you update statistics for all indexes on the 'Orders' table in SQL Server?

UPDATE STATISTICS Orders;

a) The given query

b) REFRESH STATISTICS Orders;

c) RENEW STATISTICS Orders;

d) RECALCULATE STATISTICS Orders;

21. What is the function of the @@IDENTITY global variable in SQL Server?

INSERT INTO Products (ProductName) VALUES ('New Product');
SELECT @@IDENTITY;

a) It returns the ID of the currently logged-in user

b) It returns the last-used identity value in the session

c) It resets all identity values in the database to zero

d) It increments identity values in all tables

22. How do you create a composite primary key in SQL Server?

ALTER TABLE Orders ADD CONSTRAINT PK_OrderID_CustomerID PRIMARY KEY (OrderID, CustomerID);

a) The given query

b) CREATE PRIMARY KEY ON Orders (OrderID, CustomerID);

c) SET PRIMARY KEY (OrderID, CustomerID) ON Orders;

d) ADD PRIMARY KEY Orders (OrderID, CustomerID);

23. What is the purpose of the DBCC CHECKDB command in SQL Server?

DBCC CHECKDB ('AdventureWorks');

a) It checks the integrity of database objects in the specified database

b) It backs up the specified database

c) It restores the specified database from a backup

d) It deletes the specified database

24. How do you return the current SQL Server instance name?

SELECT @@SERVERNAME;

a) The given query

b) SELECT SERVERNAME();

c) GET SERVER_NAME;

d) FETCH SERVER_NAME;

25. What is the SQL Server data type used to store variable-length binary data up to 2 GB?

CREATE TABLE Documents (DocID INT, DocVarBinary VARBINARY(MAX));

a) The given query

b) VARBINARY(2048)

c) LONGVARBINARY

d) BINARY