SQL Server MCQ - Multiple Choice Questions

Welcome to the Microsoft SQL Server Quiz for Beginners! This quiz consists of 30 multiple-choice questions designed to test your foundational knowledge of Microsoft SQL Server, one of the industry’s leading relational database management systems.

Whether you’re new to SQL Server or looking to refresh your basics, this quiz will challenge your understanding of key concepts such as database structure, queries, commands, and functions.

Each question is followed by an answer and a brief explanation to enhance your learning. So, are you ready to test your SQL Server chops? Let’s get started!

1. Which of the following is the default port for SQL Server?

A. 80

B. 8080

C. 3306

D. 1433

Answer:

D. 1433

Explanation:

The default port for SQL Server is 1433. However, it can be changed if needed.

2. Which programming language is used to develop MS SQL?

A. C

B. C++

C. Both A and B

D. Java

Answer:

C. Both A and B

Explanation:

Microsoft SQL Server is primarily developed in C and C++. While C was used for the earlier versions, later versions, and additional components have been developed using C++. Java is not the language used for the development of MS SQL Server.

3. The primary key of a table ensures:

A. Data Accuracy

B. Data consistency

C. Data redundancy

D. Data uniqueness

Answer:

D. Data uniqueness

Explanation:

The primary key ensures that each record in the table has a unique value and does not allow NULL values.

4. Which SQL statement is used to extract data from a database?

A. SELECT

B. GET

C. EXTRACT

D. PULL

Answer:

A. SELECT

Explanation:

The SELECT statement is used to fetch data from a database.

5. Which of the following is NOT a SQL Server data type?

A. varchar

B. integer

C. blob

D. datetime

Answer:

C. blob

Explanation:

blob is not a SQL Server data type. It is used in some other databases like MySQL.

6. In SQL Server, which command is used to delete a table?

A. REMOVE TABLE

B. DELETE TABLE

C. DROP TABLE

D. DESTROY TABLE

Answer:

C. DROP TABLE

Explanation:

The DROP TABLE command is used to delete an existing table in SQL Server.

7. What is the purpose of the GO command in SQL Server?

A. To create a loop

B. To execute the preceding batch of statements

C. To invoke a stored procedure

D. To initialize a variable

Answer:

B. To execute the preceding batch of statements

Explanation:

In SQL Server, the GO command is used to signal the end of a batch of Transact-SQL statements to the server.

8. Which SQL clause is used to filter the results of a query?

A. FILTER

B. WHERE

C. CHECK

D. LOCATE

Answer:

B. WHERE

Explanation:

The WHERE clause is used to filter the records based on a specified condition.

9. What is the maximum size of a VARCHAR(MAX) data type in SQL Server?

A. 8000 characters

B. 2^31-1 characters

C. Unlimited

D. 10000 characters

Answer:

B. 2^31-1 characters

Explanation:

The VARCHAR(MAX) data type can store a maximum of 2^31-1 characters.

10. In SQL Server, what does the IDENTITY property do?

A. Associates a unique identity with each user

B. Makes a column read-only

C. Automatically generates sequential numbers for a column

D. Encrypts data in a column

Answer:

C. Automatically generates sequential numbers for a column

Explanation:

The IDENTITY property in SQL Server is used to auto-generate sequential numbers for a column, often used for primary keys.

11. Which of the following SQL Server system databases is used to store system-level information for an instance?

A. master

B. model

C. msdb

D. tempdb

Answer:

A. master

Explanation:

The master database holds system-level information for a SQL Server instance, including instance-wide metadata and login information.

12. The CHAR and VARCHAR data types are different because:

A. CHAR is used for numbers, and VARCHAR is used for text

B. CHAR is a fixed length, and VARCHAR is a variable length

C. VARCHAR can store up to 8000 characters, but CHAR can store only 255

D. CHAR supports Unicode characters, but VARCHAR does not

Answer:

B. CHAR is a fixed length, and VARCHAR is a variable length

Explanation:

The primary difference between CHAR and VARCHAR in SQL Server is that CHAR is a fixed-length data type, whereas VARCHAR is variable-length.

13. Which SQL statement is used to create a database in SQL Server?

A. CREATE DATABASE

B. NEW DATABASE

C. SETUP DATABASE

D. ADD DATABASE

Answer:

A. CREATE DATABASE

Explanation:

The CREATE DATABASE statement is used to create a new database in SQL Server.

14. What does the @@ROWCOUNT function return in SQL Server?

A. The number of rows affected by the last query

B. The total number of rows in the current database

C. The number of rows in a specific table

D. The row number of the last inserted record

Answer:

A. The number of rows affected by the last query

Explanation:

The @@ROWCOUNT function returns the number of rows affected by the last executed statement in SQL Server.

15. Which of the following functions returns the current date and time in SQL Server?

A. NOW()

B. CURRENT_TIME()

C. GETTIME()

D. GETDATE()

Answer:

D. GETDATE()

Explanation:

The GETDATE() function returns the current date and time in SQL Server.

16. How can you prevent SQL injection in SQL Server?

A. By using the ESCAPE clause

B. By using dynamic SQL

C. By using stored procedures

D. By using direct user input in queries

Answer:

C. By using stored procedures

Explanation:

One of the most effective ways to prevent SQL injection is by using stored procedures and avoiding the concatenation of user input directly into SQL queries.

17. In SQL Server, which command is used to remove all data from a table without deleting the table itself?

A. REMOVE

B. DELETE

C. TRUNCATE

D. EMPTY

Answer:

C. TRUNCATE

Explanation:

The TRUNCATE command removes all rows from a table without logging individual row deletions and is typically faster than the DELETE command.

18. Which of the following commands is used to return only distinct (different) values?

A. UNIQUE

B. DISTINCT

C. DIFFER

D. SEPARATE

Answer:

B. DISTINCT

Explanation:

The DISTINCT keyword is used in conjunction with the SELECT statement to eliminate all the duplicate records and fetch only unique records.

19. Which of the following is an SQL Server system function to get the current user?

A. CURRENT_USER()

B. GET_USER()

C. USER_NAME()

D. FETCH_USER()

Answer:

C. USER_NAME()

Explanation:

USER_NAME() is a system function in SQL Server that returns the name of the current user.

20. SQL Server operates in which of the following authentication modes?

A. Windows Authentication Mode

B. Server Authentication Mode

C. Mixed Mode

D. Both A and C

Answer:

D. Both A and C

Explanation:

SQL Server operates in two authentication modes: Windows Authentication Mode and Mixed Mode (which allows both Windows Authentication and SQL Server Authentication).

21. Which clause can be used in SQL Server to group rows that have the same values in specified columns?

A. GROUP_BY

B. GROUP EACH

C. GROUPING

D. GROUP BY

Answer:

D. GROUP BY

Explanation:

The GROUP BY clause groups rows that have the same values in specified columns into summary rows, like “total quantity of items sold by a salesperson.”

22. Which SQL Server function returns the length of a string?

A. SIZE()

B. LENGTH()

C. CHAR_LENGTH()

D. LEN()

Answer:

D. LEN()

Explanation:

The LEN() function in SQL Server returns the length of a string.

23. The process of organizing data to reduce redundancy and improve data integrity is known as:

A. Normalization

B. Serialization

C. Minimization

D. Summarization

Answer:

A. Normalization

Explanation:

Normalization is the process of efficiently organizing data in a database, reducing redundancy and ensuring data integrity.

24. Which of the following is a valid aggregate function in SQL Server?

A. CONCATENATE()

B. TOTAL()

C. AVERAGE()

D. SUM()

Answer:

D. SUM()

Explanation:

The SUM() function is an aggregate function in SQL Server that returns the sum of all values in a particular column.

25. Which command is used to retrieve previous transactions and log events in SQL Server?

A. UNDO

B. REDO

C. BACKLOG

D. ROLLBACK

Answer:

D. ROLLBACK

Explanation:

The ROLLBACK command in SQL Server is used to undo transactions that have not been saved to the database.

26. If you want to find out the total number of tables in a specific database, which system table would you query?

A. sys.tables

B. sys.databasenames

C. sys.objects

D. sys.columns

Answer:

A. sys.tables

Explanation:

The sys.tables system table in SQL Server provides information about all the tables in a particular database.

27. Which command in SQL Server is used to make permanent changes done by the transaction?

A. COMMIT

B. SAVE

C. HOLD

D. RETAIN

Answer:

A. COMMIT

Explanation:

The COMMIT command in SQL Server is used to permanently save all changes made during the current transaction.

28. Which of the following statements is true about temporary tables in SQL Server?

A. Temporary tables can be backed up.

B. Temporary tables persist even after the server is restarted.

C. Temporary tables are stored in the tempdb database.

D. Temporary tables can be accessed by multiple users.

Answer:

C. Temporary tables are stored in the tempdb database.

Explanation:

Temporary tables in SQL Server are stored in the tempdb database and are automatically deleted when they are no longer used.

29. Which of the following is a built-in scalar function in SQL Server that returns the current database ID?

A. GETDBID()

B. CURRENT_DBID()

C. DB_ID()

D. FETCHDBID()

Answer:

C. DB_ID()

Explanation:

DB_ID() is a built-in scalar function in SQL Server that returns the database ID of the current database.

30. Which of the following is not a system database in SQL Server?

A. master

B. model

C. users

D. msdb

Answer:

C. users

Explanation:

“users” is not a system database in SQL Server. The other options listed are system databases.