MS SQL Server is one of the most popular relational database management systems, offering several database management systems. The structure of this type of DBMS allows users to identify and access data with another piece of data in the database. Therefore, knowing MS SQL Server opens up the possibility of becoming a Database Administrator. Your landing on this MS SQL Server Interview Questions article suggests you already know these facts.

Therefore in this article, I have compiled a list of 50 commonly asked SQL Server interview questions to help you excel in your interview round.

Learn to create complex database courses and build reports by working on real-life projects with the best SQL tutorials.

SQL Server Interview Questions

1. What is SQL?

Answer: SQL, also known as Sequel, is a structured English query language that is intended to manipulate and retrieve data in IBM Database Management System (System R). The SQL definition of the language defines how a database object manipulates data. Several revisions for SQL have been defined, including data types, isolation levels, logical operator syntax, data definition language, data manipulation syntax, and language definitions for procedures and functions.

2. What is PL/SQL?

Answer: PL/SQL refers to Procedural Language extensions to SQL (Structured Query Language). Developed by Oracle, it aims to overcome the disadvantages of SQL for easier development and handling of critical applications in a general manner.

3. What do you understand by SQL Server Agent?

Answer: The SQL Server Agent is a Windows service that schedules and executes jobs. The job here consists of one or more steps, with each step containing a task. In this way, the Server Agent uses SQL Server to store job information and run a job on schedule.

4. How to create a database in SQL Server?

Answer: A database is an organized collection of data. It contains schemas, tables, procedures, code functions, and other objects. Several query languages are used to access and handle data. A table is an object that stores data in a tabular (columns and rows) form in SQL Server.

Using the CREATE DATABASE SQL command, you can create a new database.

Example: CREATE DATABASE Student

Syntax: CREATE DATABASE DatabaseName

Alternatively, you can create databases in SQL Server Management Studio. Follow the wizard steps by right-clicking on Databases and selecting New Database.

5. What is RDBMS?

Answer: RDBMS (Relational Database Management Systems) is a program that allows us to create, delete, and update a relational database. Typically, relational databases are structured in a hierarchy of rows and columns and are configured to store and retrieve data. Most of the major DBMS like SQL, MySQL, ORACLE is based on the principles of relational DBMS.

6. What is a database table?

Answer: The database table is a table that holds records arranged by rows and columns. A permanent table is created in the database you specify and remains there permanently until it is deleted.

Syntax:

Create table TableName (ID INT, NAME VARCHAR(30) )   
Drop syntax: drop table TableName  
Select Syntax: Select * from TableName

7. How do you create a table in SQL?

Answer: SQL provides an organized way to create tables:

Syntax:

Create table TableName (columnName1 datatype, columnName2 datatype)

8. Which TCP/IP port does SQL Server run on?

Answer: SQL Server runs on port 1433 by default.

9. How to delete a table in SQL Server?

Answer: You can delete a record from the database table and delete an existing table using the following method:

Syntax: To delete all table records of a table:

• Delete TableName
• DELETE info

10. What is the difference between clustered and non-clustered indexes?

Answer:

11. List the different index configurations possible for a table?

Answer: Tables can have one of the following index configurations:

• No indexes
• A clustered index
• A clustered index and many non-clustered indexes
• A non-clustered index
• Many non-clustered indexes

12. What are the different backups available in SQL Server?

Answer: The different types of backups available in SQL Server are:

• Full backup
• Differential Backup
• Transactional Log Backup
• Copy Only Backup
• File and Filegroup backup

13. What are the relationships in the SQL Server database?

Answer: A relationship is created when a column in one table is linked to a column in another table. Relationships fall into three categories:

• One-to-One Relationship
• Many-to-One Relationship
• Many-to-Many Relationship

14. What is the recovery model? List the types of recovery models available in SQL Server?

Answer: SQL Server's recovery model specifies what data should be kept in the transaction log file and for how long. A database can only have one recovery model. In addition, it tells SQL servers which backups are possible based on the selected recovery model. Three types of recovery models exist:

• Full
• Simple
• Bulk-Logged

15. What is OLTP?

Answer: OLTP stands for Online Transaction Processing. It follows the rules of data normalization to ensure data integrity. Complex information is broken down into a simple structure by using these rules.

16. What are the properties of the Relational tables?

Answer: A relational table has six properties:

1. Atomic value
2. Column values are of the same kind.
3. Every row is unique.
4. Column sequence is not significant.
5. The sequence of rows is not important.
6. It is necessary to name each column uniquely.

17. When is the UPDATE_STATISTICS command used?

Answer: As the name implies, the UPDATE_STATISTICS command is used to update the statistics used by the index to make searching easier.

18. What is Mirroring?

Answer: Mirroring is a dependable solution. In terms of transactions, it is designed to maintain a hot standby server that is consistent with the primary server. Records from the Transaction Log are sent directly from the principal server to a secondary server, which keeps the secondary server up-to-date with the primary server.

19. What advantages does Mirroring offer?

Answer: Mirroring has the following advantages:

• It features an automatic failover mechanism.
• It is more efficient and robust than Log shipping.
• The secondary server is synced with the primary server in near real-time.

20. What is Log Shipping? What are its advantages?

Answer: By log shipping, we mean automating the process of backing up and restoring databases from one server to another standalone standby server. It is among the disaster recovery solutions. When one server fails, the standby server will have the same data.

The advantages of Log Shipping are:

• Easy to set up.
• Low maintenance
• It is possible to have multiple secondary standby servers.
• If you want to use the secondary database for read-only purposes, you can do so.

21. What is the usage of the SIGN function?

Answer: The SIGN function determines whether a mentioned number is zero, positive, or negative. Therefore, it will either return 0, +1, or -1.

Syntax: SIGN(number)

Example:

SIGN (0)  returns 0
SIGN (21)  returns 1
SIGN (-21)  returns -1

22. What is identity in SQL?

Answer: In SQL, an identity column automatically generates numeric values. It is possible to define we as a start and increment value for the identity column. It is not necessary to index identity columns.

23. What are the common performance issues with SQL Server?

Answer: Performance issues in SQL Server include the following:

• Blocking
• Deadlocks
• Fragmentation
• I/O bottlenecks
• Missing and unused indexes
• Poor Query plans

24. What is an execution plan?

Answer: The execution plan shows how the SQL server breaks down a query to get a result based on a graphical or textual representation. It allows the user to determine why queries are taking longer to execute and, based on the investigation users, can update queries to achieve maximum results.

Query Analyzer has a feature called "Show Execution Plan" (located on the Query drop-down menu). When this option is enabled, a query execution plan will appear in a separate window when the query is run again.

25. What are three ways to get a count of the number of records in a table?

Answer: The three ways to get a count of the number of records in a table:

SELECT * FROM table_Name;

SELECT COUNT(*) FROM table_Name;

SELECT rows FROM indexes WHERE id = OBJECT_ID(tableName) AND indid< 2;

26. What is the SQL Profiler?

Answer: The SQL Profiler shows graphical representations of events in an instance of SQL Server for monitoring and investment purposes. For further analysis, we can save and capture the data. Moreover, we can filter the data to capture only the information relevant to us.

27. Is it possible to call a stored procedure within a stored procedure?

Answer: Yes, we can call a stored procedure within a stored procedure. In SQL Server, this is known as nested stored procedures.

28. What is a stored procedure?

Answer: A stored procedure is a set of T-SQL statements. Stored Procedures are a set of one or more statements that are pre-compiled and stored together in a database. As a result of precompilation, they reduce network load. Using the "Create proc" statement, we can create a Stored Procedure.

It is useful to use stored procedures for various reasons. They reduce network load and execution time because they are precompiled. In terms of security, Stored Procedures serve a vital function. Therefore, we can prevent SQL injection by using stored procedures.

Example:

CREATE PROCEDURE spEmployee   
AS   
BEGIN   

SELECT EmployeeId, Name, Gender, DepartmentName   
FROM tblEmployees   
INNER JOIN tblDepartments   
ON tblEmployees.EmployeeDepartmentId = tblDepartments.DepartmentId   
END

29. What is a trigger in SQL Server?

Answer: It is a database object similar to a stored procedure, or we can say it is a kind of stored procedure that fires when an event occurs in a database. It is a database object attached to a table that is executed automatically. Triggers cannot be explicitly called. Triggers provide data integrity by allowing access to and checking data before and after modifications using DDL and DML queries.

There are two types of Triggers:

1. DDL Trigger - Triggers for these events respond to DDL (Data Definition Language) command events that start with Create, Alter, and Drop like Create_table, Create_view, drop_table, Drop_view, and Alter_table.
2. DML trigger - Triggers for these events respond to DML (Data Manipulation Language) command events that start with Insert, Update and Delete like insert_table, Update_view, and Delete_table.

30. What is replication in SQL Server?

Answer: Syncing data across multiple servers is known as Replication. A replica set performs replication. It maintains the same data set. Multiple copies of the data are stored on different database servers for high availability and redundancy.

As replication removes dependencies from one server, it protects databases from the loss of a single server. Using replication, you can recover from hardware failures and service interruptions. Additionally, replication is used to increase the read capacity.

For read and write operations, replication allows the client to select a different server. In replication, data is replicated across different data centers to increase locality and availability for distributed applications.

31. What is SQL Server Profiler?

Answer: The Microsoft SQL Server Profiler is an interface for creating and managing traces. Additionally, it analyzes and replays trace results. Trace files are used for storing events that will be later analyzed or replayed for debugging purposes. Among the activities you can perform with SQL Server Profiler are:

• Identifying the root cause of the problem.
• Monitoring SQL Server's performance as it handles the workload.
• Diagnosing a slow query.
• Additionally, it helps correlate performance counters to debug issues more efficiently.
• • Capturing a series of SQL statements causing problems, replicating the issue on a test server, and debugging the issue.

32. Mention the different types of replication in SQL Server.

Answer: There are three types of replications in SQL Server:

1. Merge Replication: This replication gathers data from various sources into one centralized database and works in server-to-client systems.

2. Transactional Replication: This type of replication is used in server-to-server environments to distribute data from publisher to subscriber.

3. Snapshot Replication: This replication reproduces data exactly as it appears at a specific moment and is suitable for replicating data that does not change frequently.

33. What are the differences between MS SQL Server & Oracle?

Answer:

34. What are trace flags and mention a few common trace flags used with SQL Server?

Answer: These flags are used to modify server behavior or set server characteristics. The following are a few common trace flags used with SQL Server:

• 1204, 1205, 1222 – These flags contain information about deadlocks.
• 174 – This trace flag increases the SQL Server Database Engine plan cache bucket count from 40,009 to 160,001 on 64-bit systems.
• 1118 – This flag forces uniform extent allocations instead of mixed page allocations – (SQL 2005 and 2008) To reduces TempDB contention.
• 652 – This trace flag disables page prefetching scans.
• 2566 – Runs DBCC CHECKDB without data purity checks, unless the DATA_PURITY option is specified.

35. When do you think a developer should use SQL Server-based cursors?

Answer: SQL server-based cursors are used when you want to work on a record at any time, rather than taking the entire table as a whole. However, cursors should not be used when there is a large amount of data since they impact performance. In some cases, it may not be possible to avoid cursors, so you can use a temporary table to reduce the number of records to process and then create the cursor from there.

36. What do you understand by User-Defined function in the SQL Server and explain the steps to create and execute a user-defined function in the SQL Server?

Answer: User-defined functions are functions written based on the needs of the user by using logic. A user is not limited to predefined functions in these kinds of functions, and the complex code of predefined functions can be simplified by writing simple code. The function returns either a table or a scalar value.

Here is an example of how to create a user-defined function:

CREATE FUNCTION samplefunc(@num INT)
RETURNS TABLE
AS
RETURN SELECT * FROM customers WHERE CustId=@num

Refer to the following command to execute the above-created function:

SELECT * FROM samplefunc(10)

37. What are joins in SQL, and what are the different types of joins?

Answer: You can use a JOIN clause to combine rows from two or more tables based on a related column between them. It is used to merge two tables or retrieve data from them. SQL has four joins, namely:

• Inner Join
• Right Join
• Left Join
• Full Join

38. How can you ensure that the database and SQL Server-based application perform well?

Answer: A developer must consider the type of information stored, the volume of data, and the data that will be accessed.

For example, when upgrading an existing system, you should analyze the existing data, data volumes, and the method of accessing the data to identify areas for improvement for design.

39. What part does database design play a role in the performance of an SQL Server-based application?

Answer: The performance of SQL Server-based applications depends on the physical and logical design. We have to ensure that the correct data is entered into the proper tables, that the data items have the appropriate relationships between them and that data redundancy is kept to a minimum. Additionally, I would suggest that while designing a database, make sure that it is an iterative process that achieves all system goals and that it is constantly observed. After the database design is set, it is challenging to change it to meet requirements. It is only possible to add new relationships and data items.

40. What is the meaning of Not Null in SQL?

Answer: Constraints are rules that determine what type of data goes into the database tables. There are six types of constraints for SQL servers:

• Primary Key Constraint
• Foreign Key Constraint
• Not Null Constraint
• Unique constraint
• Default Constraint
• Check Constraint

Not Null in SQL is useful to stop storing the null entries in the specified columns. For example, we will mark the student name column as the not null column. In this way, we can always have some entries in the student name column of the student table without NULL.

41. What is the command DBCC CHECKDB used for?

Answer: DBCC CHECKDB checks the physical and logical integrity of all the objects in the mentioned database. To accomplish this, it performs the following operations:

• Runs DBCC CHECKALLOC on the mentioned database.
• On every table and view in the database, the DBCC CHECKTABLE command is performed.
• Runs DBCC CHECKCATALOG on the database.
• Afterward, it validates the contents of every indexed view in the mentioned database.
• Furthermore, it validates link-level consistency between file system directories and table metadata while storing varbinary(max) data in the file system using FILESTREAM.
• Lastly, it validates the Service Broker data in the database.

Therefore, you just have to execute the DBCC CHECKDB command, and automatically, DBCC CHECKALLOC, DBCC CHECKTABLE, or DBCC CHECKCATALOG commands get executed.

You should also know that DBCC is supported for databases that have memory-optimized tables, but no repair options. It implies that you should regularly back up databases and test those backups.

42. What do you understand by CHECK constraint in SQL Server?

Answer: CHECK constraints in SQL Server limit the type or value of data stored in a column. Using the CHECK constraint on a single column, you can then apply specific values for that column.

CREATE TABLE Customer (  
    Cust_ID int NOT NULL,  
    FirstName varchar(255),  
    Age int,  
    City varchar(255),  
    CONSTRAINT CHK_Customer CHECK (Age>20 AND City= 'Hyderabad')  
);  

43. Explain the usage of FLOOR function in SQL Server.

Answer: To round up a non-integer value to the previous least integer value, we use the FLOOR function. After rounding the digits, this function returns a unique value.

Syntax: FLOOR(expression)

Example: FLOOR(7.3)

44. What do you understand by COALESCE in SQL Server?

Answer: The function returns the first non-null expression within an argument. The COALESCE command returns a non-null value from more than one column in arguments. For example:

SELECT COALESCE(CustID, CustName, Amount) from Customers;

45. Can you explain how long are locks retained within the REPEATABLE_READ and SERIALIZABLE isolation levels, during a read operation with row-level locking?

Answer: With REPEATABLE_READ and SERIALIZABLE isolation levels, locks are held throughout the transaction. However, if you consider READ_COMMITTED, then locks are held for isolation level.

46. What is the purpose of the UPDATE STATISTICS and SCOPE_IDENTITY() function?

Answer: UPDATE _STATISTICS updates information used by indexes, such as distributing key values for one or more statistic groups in the mentioned indexed view or table. Meanwhile, SCOPE_IDENTITY creates identity values for tables in the current execution scope.

47. What are the options which must be set to allow the usage of optimistic models?

Answer: The READ_COMMITED_SNAPSHOT option and the ALLOW_SNAPSHOT_ISOLATION option are required to allow the usage of optimistic models.

• The READ_COMMITTED_SNAPSHOT option is used to read a committed optimistic model.
• ALLOW_SNAPSHOT_ISOLATION option specifies snapshot isolation level.

48. What do you understand by the PHYSICAL_ONLY option in DBCC CHECKDB?

Answer: These three points will describe the PHYSICAL_ONLY option in DBCC CHECKDB:

• The PHYSICAL_ONLY option limits the check to the physical structure of the headers, pages, and allocation consistency of the database.
• The PHYSICAL_ONLY check ensures the physical consistency of the database with a small overhead.
• In addition, the PHYSICAL_ONLY option reduces the run-time for DBCC CHECKDB on large databases. Therefore, it is generally recommended for frequent use on production systems.

49. Can you name a few encryption mechanisms in the SQL server?

Answer: In SQL Server, there are a few encryption mechanisms to encrypt data in the database:

• Asymmetric Keys
• Certificates
• Symmetric Keys
• Transparent Data Encryption
• Transact SQL functions

50. What do you understand by Hotfixes and Patches in SQL Server?

Answer: Hotfixes are single, aggregate software packages utilized to live systems. It includes one or more files that address problems with a software product. On the other hand, Patches are programs installed on the machines to correct any problems on the system and ensure its security. Therefore, hotfixes are a type of patch offered by Microsoft SQL Server to address specific issues.

---

If you have made it this far, then certainly you are willing to learn more about SQL. Here are some more resources related to the SQL that we think will be useful to you.

• Top 50 Frequently Asked PL/SQL Interview Questions
• 50 Best DBMS Interview Questions For Database Developers
• 10 Best MySQL Courses & Tutorials - Learn MySQL Online