Unlock hundreds more features
Save your Quiz to the Dashboard
View and Export Results
Use AI to Create Quizzes and Analyse Results

OR
Sign inSign in with Facebook
Sign inSign in with Google
Quizzes > Computers & Technology > Programming & Software

Prove Your MS SQL Server Proficiency - Take the Quiz!

Think you can ace this MS SQL quiz? Test your database skills now!

Difficulty: Moderate
2-5mins
Learning OutcomesCheat Sheet
Paper art style laptop with SQL symbols database icons magnifying glass charts on golden yellow background quiz invitation

This free SQL proficiency test helps you check your MS SQL skills in T‑SQL, performance tuning, and database design. Get instant feedback as you go, spot gaps before an exam or interview, and follow up with our companion quiz or SQL programming practice to keep learning.

What does the following T-SQL statement do? SELECT * FROM Employees;
Updates all records in the Employees table.
Creates a copy of the Employees table.
Retrieves all columns and rows from the Employees table.
Deletes all data from the Employees table.
The SELECT * syntax returns every column for every row in the specified table. It does not modify data or structure. This is the simplest way to retrieve entire table contents.
Which T-SQL clause limits the number of returned rows to the first 10 records?
LIMIT 10;
FETCH FIRST 10 ROWS;
SELECT TOP 10;
ROWCOUNT 10;
In SQL Server, the TOP keyword restricts the result set to a specified number of rows. Using SELECT TOP 10 will return only the first ten rows. OFFSET/FETCH is ANSI SQL but TOP is the native SQL Server method.
Which keyword is used to sort the results of a query in T-SQL?
ORDER BY
GROUP BY
SORT
FILTER BY
ORDER BY is the standard T-SQL clause for sorting query results. You can specify ASC for ascending or DESC for descending order. Other clauses like GROUP BY perform grouping, not sorting.
Which data type stores variable-length Unicode character data in SQL Server?
CHAR
VARCHAR
NVARCHAR
TEXT
NVARCHAR stores variable-length Unicode data, supporting international character sets. VARCHAR stores non-Unicode data only. TEXT is deprecated and should be avoided.
What clause filters rows based on a specified condition in a SELECT statement?
ON
GROUP BY
HAVING
WHERE
The WHERE clause filters rows before any grouping or aggregation. HAVING filters after aggregation is applied. ON is used in JOIN conditions.
Which function returns the current date and time in SQL Server?
NOW()
GETDATE()
SYSDATE()
CURRENT_DATE()
GETDATE() returns the current system date and time in SQL Server. CURRENT_TIMESTAMP is a non-T-SQL equivalent. Functions like NOW() or SYSDATE() belong to other database platforms.
How can you identify duplicate rows in a table based on one or more columns?
Using WHERE COUNT(*) > 1
Using GROUP BY with HAVING COUNT(*) > 1
Using DISTINCT and checking for duplicates
Using ORDER BY and checking manually
GROUP BY groups rows by specified columns and HAVING COUNT(*) > 1 filters groups with more than one row. This technique efficiently identifies duplicates. DISTINCT removes duplicates rather than finding them.
What is the purpose of creating an index on a column?
To prevent NULL values in the column
To enforce uniqueness only
To improve data retrieval performance
To store large binary objects more efficiently
Indexes create a data structure that speeds up the retrieval of rows based on column values. While unique indexes enforce uniqueness, that is a secondary feature. They do not handle storage of BLOBs.
Which join type returns all rows from the left table and matching rows from the right table?
INNER JOIN
FULL OUTER JOIN
RIGHT OUTER JOIN
LEFT OUTER JOIN
A LEFT OUTER JOIN returns all rows from the left table plus matched rows from the right. Unmatched right-side rows will produce NULLs. INNER JOIN only returns matches.
Which transaction isolation level in SQL Server prevents dirty reads?
READ UNCOMMITTED
SNAPSHOT
READ COMMITTED
REPEATABLE READ
READ COMMITTED is the default isolation level in SQL Server and prevents dirty reads by acquiring shared locks on data. READ UNCOMMITTED allows dirty reads. Higher levels like REPEATABLE READ also prevent dirty reads but the minimum level is READ COMMITTED.
How do you add a new column named 'BirthDate' of type DATE to an existing table 'Persons'?
ALTER TABLE Persons ADD COLUMN BirthDate DATE;
MODIFY TABLE Persons ADD BirthDate DATE;
UPDATE TABLE Persons ADD BirthDate DATE;
ALTER TABLE Persons ADD BirthDate DATE;
In SQL Server, ALTER TABLE ... ADD ColumnName DataType is the correct syntax. The COLUMN keyword is not used in SQL Server. MODIFY and UPDATE TABLE are not valid DDL in SQL Server.
Which T-SQL function concatenates two or more strings?
JOIN
MERGE
UNION
CONCAT
CONCAT is the built-in T-SQL function to merge multiple strings into one. MERGE is used for DML operations, UNION for combining result sets, and JOIN for combining tables.
What is a covering index in SQL Server?
An index that automatically updates statistics
An index on every column of a table
An index that covers partitioning of a table
An index that includes all columns needed to satisfy a query without touching the base table
A covering index contains all columns referenced by a query in its key or included columns, eliminating lookups. This reduces I/O and speeds up query execution. It differs from a non-covering index that requires additional page reads.
Which practice can help reduce deadlocks in SQL Server?
Using NOLOCK hint everywhere
Avoiding indexes on frequently used tables
Accessing resources in the same order in all transactions
Using long-running transactions
Consistent resource access order prevents circular locking dependencies that cause deadlocks. Long transactions and NOLOCK hints can worsen concurrency issues. Proper indexing and transaction design is key.
Which dynamic management view provides details about missing indexes?
sys.indexes
sys.dm_db_missing_index_details
sys.dm_db_index_physical_stats
sys.dm_exec_query_stats
sys.dm_db_missing_index_details returns information on potential indexes that could improve query performance. It is one of several DMVs for index tuning. sys.indexes shows existing indexes only.
What does the WITH (NOLOCK) table hint do?
It prevents any locks from being placed on the table
It enforces a schema lock on the table
It allows reading uncommitted data, enabling dirty reads
It acquires an exclusive lock on the table
The NOLOCK hint permits reading data without acquiring shared locks, allowing dirty reads. It can reduce blocking at the risk of inconsistent data. It does not prevent all locks nor enforce schema locks.
What does the 'fill factor' setting control for an index?
The percentage of page space to leave free when creating or rebuilding an index
The maximum number of columns in the index
The order of pages in memory
The compression level of the index pages
Fill factor determines how full to pack index pages during creation or rebuild. Leaving free space reduces page splits and fragmentation as data grows. It's configured per index.
How can you optimize large DELETE operations to minimize log usage and locking?
Disable the transaction log temporarily
Delete all rows in a single transaction
Create a clustered index on the table before deleting
Use batch deletes with a specified TOP value or limit
Batching deletes in smaller transactions reduces lock durations and transaction log usage. Using TOP or loops helps process manageable chunks. Deleting en masse can lead to long locks and bloated logs.
What benefit does a columnstore index provide in SQL Server?
It improves row-by-row OLTP performance primarily
It eliminates the need for any other indexes
It automatically balances partition sizes
It compresses data and accelerates analytical query performance
Columnstore indexes store data by column, enabling high compression and faster scan performance for analytical workloads. They are not optimized for row-by-row OLTP operations. They complement, not replace, other index types.
0
{"name":"What does the following T-SQL statement do? SELECT * FROM Employees;", "url":"https://www.quiz-maker.com/QPREVIEW","txt":"What does the following T-SQL statement do? SELECT * FROM Employees;, Which T-SQL clause limits the number of returned rows to the first 10 records?, Which keyword is used to sort the results of a query in T-SQL?","img":"https://www.quiz-maker.com/3012/images/ogquiz.png"}

Study Outcomes

  1. Analyze T-SQL Queries -

    Break down complex T-SQL statements to understand their logic and optimize your query-writing skills during the T-SQL test.

  2. Apply Performance Tuning Techniques -

    Implement indexing strategies and execution plan analysis to improve query efficiency as featured in our MS SQL quiz.

  3. Design Efficient Database Schemas -

    Use normalization principles and best practices to model relational structures effectively in the database proficiency quiz scenarios.

  4. Troubleshoot and Debug SQL Scripts -

    Identify and resolve common errors and bottlenecks in SQL Server scripts, sharpening your problem-solving skills through the SQL proficiency test challenges.

  5. Assess Overall SQL Server Proficiency -

    Evaluate your strengths and pinpoint improvement areas across T-SQL, performance tuning, and design to prepare for the SQL Server exam.

Cheat Sheet

  1. Advanced T-SQL Queries and JOIN Strategies -

    Boost your sql proficiency test score by mastering INNER JOIN, LEFT JOIN, RIGHT JOIN, and CROSS JOIN to combine tables efficiently. For example, use SELECT e.Name, d.DepartmentName FROM Employees e INNER JOIN Departments d ON e.DeptID = d.ID WHERE d.Location = 'HQ'; Official Microsoft Docs outline how join order and statistics impact performance.

  2. Indexing and Query Execution Plans -

    Understanding indexes is crucial for the MS SQL quiz performance tuning section; clustered vs. nonclustered indexes directly affect seek vs. scan operations. Use SET STATISTICS IO ON to compare I/O costs and refer to Microsoft's Performance Tuning Guide for execution plan analysis. A simple mnemonic: "Seek First, Scan Last" helps remember optimal strategies.

  3. Database Normalization Principles -

    The database proficiency quiz often tests your grasp of 1NF, 2NF, and 3NF rules to reduce redundancy and maintain data integrity. Remember the "AIM" mnemonic: Attribute atomicity, Identify keys, Maintain dependencies. Stanford's Database Course materials and ACM publications emphasize normalization's role in scalable design.

  4. Stored Procedures, User-Defined Functions, and Error Handling -

    For the T-SQL test, know how to encapsulate logic in stored procedures with CREATE PROC and modularize calculations in scalar or table-valued functions. Always include TRY…CATCH blocks to log errors, as recommended in Microsoft's best practices. Practical experience debugging via SQL Server Management Studio bolsters your confidence.

  5. Transaction Management and Isolation Levels -

    Transaction isolation knowledge is key for the SQL Server exam scenario questions; understand READ COMMITTED, REPEATABLE READ, and SNAPSHOT levels to prevent dirty reads or phantom rows. Test locking behavior using SET TRANSACTION ISOLATION LEVEL commands and consult official documentation on blocking vs. deadlocking. Remember: "CCC - Commit, Consistency, Concurrency" when handling transactions.

Make a Quiz (FREE) Copy & Edit This Quiz Create a Quiz with AI

Programming & Software Quizzes

Powered by: Quiz Maker