Top 20 SQL Server Interview Questions and Answers

1. What is SQL Server?

Answer: SQL Server is a relational database management system (RDBMS) developed by Microsoft. It is used to store and manage data, and it uses Structured Query Language (SQL) for managing and querying data.

2. What are the different types of joins in SQL Server?

Answer: SQL Server supports several types of joins to combine data from two or more tables:

• Inner Join: Returns records that have matching values in both tables.
• Left Join: Returns all records from the left table and matched records from the right table.
• Right Join: Returns all records from the right table and matched records from the left table.
• Full Outer Join: Returns all records when there is a match in either left or right table.
• Cross Join: Returns the Cartesian product of both tables (every combination of rows).

3. What is normalization in SQL Server?

Answer: Normalization is the process of organizing data in a database to avoid redundancy and dependency. The goal is to ensure that the database is free from insertion, update, and deletion anomalies. It involves dividing large tables into smaller, manageable tables and defining relationships between them.

4. What are indexes in SQL Server?

Answer: Indexes are database objects that speed up the retrieval of rows from a table. They can be created on one or more columns of a table. There are two main types of indexes in SQL Server:

• Clustered Index: Determines the physical order of data rows in the table.
• Non-Clustered Index: Creates a separate structure from the data table and contains pointers to the data.

5. What is a stored procedure in SQL Server?

Answer: A stored procedure is a precompiled collection of one or more SQL statements that can be executed as a single unit. Stored procedures are used to encapsulate logic and improve performance by reducing network traffic.

6. What is a trigger in SQL Server?

Answer: A trigger is a special type of stored procedure that automatically executes when an event such as an INSERT, UPDATE, or DELETE occurs on a specified table or view. Triggers can be used for enforcing business rules or for logging.

7. What is the difference between UNION and UNION ALL?

Answer: Both UNION and UNION ALL are used to combine results from two or more queries:

• UNION: Removes duplicate rows from the result set.
• UNION ALL: Includes all rows from the result set, including duplicates.

8. What is a primary key?

Answer: A primary key is a column (or combination of columns) that uniquely identifies each row in a table. It must contain unique values and cannot contain NULL values. Each table can have only one primary key.

9. What is a foreign key?

Answer: A foreign key is a column (or combination of columns) used to establish a relationship between two tables. It refers to the primary key of another table and ensures referential integrity between the two tables.

10. What is a view in SQL Server?

Answer: A view is a virtual table that is defined by a query. It can be used to simplify complex queries, aggregate data, or provide a security layer by restricting access to specific columns or rows.

11. What is a subquery?

Answer: A subquery is a query nested inside another query. It can be used to perform operations such as filtering or calculating values. Subqueries can be classified as correlated or non-correlated depending on whether they depend on the outer query.

12. What is a transaction in SQL Server?

Answer: A transaction is a sequence of one or more SQL operations that are executed as a single unit. Transactions ensure data consistency and integrity by adhering to the ACID properties: Atomicity, Consistency, Isolation, and Durability.

13. What are the ACID properties?

Answer: ACID stands for the four key properties of a transaction:

• Atomicity: Ensures that a transaction is fully completed or fully rolled back.
• Consistency: Ensures that a transaction brings the database from one valid state to another.
• Isolation: Ensures that concurrent transactions do not interfere with each other.
• Durability: Ensures that once a transaction is committed, its effects are permanent, even in the case of a system failure.

14. What is the difference between DELETE and TRUNCATE?

Answer: Both DELETE and TRUNCATE are used to remove data from a table, but:

• DELETE: Removes rows one by one and logs each row removal. It can be rolled back and can have conditions applied.
• TRUNCATE: Removes all rows from the table without logging individual row deletions. It cannot be rolled back and does not fire triggers.

15. What is an index scan and index seek?

Answer: An index scan is when SQL Server reads all the rows in an index to satisfy a query, while an index seek is when SQL Server uses the index to directly access the rows that satisfy the query's conditions.

16. What are the differences between a clustered index and a non-clustered index?

Answer: A clustered index determines the physical order of data rows, while a non-clustered index creates a separate structure that contains pointers to the data rows. A table can have only one clustered index but multiple non-clustered indexes.

17. What is a deadlock in SQL Server?

Answer: A deadlock occurs when two or more sessions are blocking each other and cannot proceed because each session is waiting for a resource held by another. SQL Server automatically detects and resolves deadlocks by terminating one of the transactions.

18. What is the purpose of the WITH (NOLOCK) hint?

Answer: The WITH (NOLOCK) hint allows SQL Server to read data without acquiring locks, which can improve performance. However, it can lead to dirty reads, where uncommitted changes may be read.

19. What are common SQL Server data types?

Answer: Common data types in SQL Server include:

• INT: Stores integer values.
• VARCHAR: Stores variable-length character strings.
• DATETIME: Stores date and time values.
• DECIMAL: Stores fixed-point numbers.
• BIT: Stores Boolean values (0 or 1).

20. What is a CTE (Common Table Expression)?

Answer: A CTE is a temporary result set defined within the execution scope of a SELECT, INSERT, UPDATE, or DELETE statement. CTEs are used to simplify complex queries and make the code more readable.

What is Normalization in SQL Server?

Normalization in SQL Server is the process of organizing data in a database to minimize data redundancy and improve data integrity. Normalization is achieved by breaking down large tables into smaller, more manageable tables that are related to each other through primary and foreign keys.

There are different levels of normalization, known as normal forms, that each have their own set of rules and criteria. The most commonly used normal forms are:

First Normal Form (1NF):

    This level requires that each table in the database has a primary key and that each column in the table contains only atomic values. However, the primary requirement for 1NF is that each row in the table is unique, which means that each table should have a primary key that identifies each row.

Second Normal Form (2NF):

    This level requires that each non-key column in the table is dependent on the entire primary key, and not just on a part of it. This means that if a table has a composite primary key, each non-key column must be dependent on both parts of the key, not just one part. The idea behind 2NF is to eliminate partial dependencies, where a non-key column depends only on part of the primary key.

Third Normal Form (3NF):

    This level requires that each non-key column in the table is dependent only on the primary key, and not on other non-key columns. This helps to eliminate data redundancy and improve data integrity by ensuring that each column in a table contains data that is related only to the primary key.

There are additional normal forms, such as Boyce-Codd Normal Form (BCNF) and Fourth Normal Form (4NF), that are less commonly used but may be appropriate for certain situations.

Normalization helps to prevent data anomalies, such as data duplication, inconsistent data, and update anomalies, which can lead to errors and inconsistencies in the database. Normalization also helps to improve database performance and scalability by reducing data redundancy and improving data retrieval and manipulation.

It's important to note that normalization is not always the best approach for every situation. Over-normalization can lead to complex database structures, slow queries, and difficulty in maintaining the data. Therefore, normalization should be used judiciously based on the specific requirements of the database and its intended usage.

How to improve the performance of SQL queries ?

There are several ways to improve the performance of SQL queries:

1. Indexing: 
• Indexing is the process of creating a data structure that allows for faster retrieval of data. By indexing frequently used columns, queries can run much faster. You should index columns that are frequently used in WHERE, JOIN, and ORDER BY clauses.
2. Use appropriate data types: 
• It's important to use appropriate data types for columns in your tables. Using the wrong data type can lead to slower queries. For example, if you're storing dates as strings instead of using the DATE data type, it can slow down queries that are filtering by dates.
3. Limit the amount of data returned: 
• When you're writing queries, try to limit the amount of data that is returned. You can do this by using the SELECT statement to only return the columns that you need, and by using the WHERE clause to filter out rows that you don't need.
4. Avoid using SELECT *: 
• Using SELECT * can slow down queries because it returns all columns in the table, even if you don't need them. Instead, you should specify the columns that you need.
5. Use JOINs wisely: 
• JOINs can be very powerful, but they can also be very slow if not used correctly. Try to use INNER JOINs instead of OUTER JOINs whenever possible, and make sure that you're joining on columns that are indexed.
6. Avoid subqueries: 
• Subqueries can be slow, so try to avoid them whenever possible. You can often rewrite a subquery as a JOIN or a correlated subquery to improve performance.
7. Optimize database design: 
• Good database design can improve query performance. Make sure that your tables are normalized and that you're using appropriate data types for columns.
8. Use stored procedures: 
• Stored procedures can be precompiled and stored in memory, which can improve performance. If you have frequently used queries, consider creating stored procedures for them.
9. Use database performance tools: 
• Most databases have performance tools that can help you identify slow queries and optimize them. Use these tools to identify queries that need to be optimized.
10. Keep your database software up to date: 
• Finally, make sure that you're using the latest version of your database software. Newer versions often have performance improvements that can help speed up queries.

By following these tips, you can help ensure that your SQL queries are as fast and efficient as possible. 

Query to generate Single XML from multiple tables in SQL Server

Here is the sample code to generate a single xml from multiple table query in Sql Server. In this example I am querying data from two tables.

Sample Query :

SELECT
    (SELECT * FROM dbo.TableA FOR XML PATH('TableA'), TYPE),
    (SELECT * FROM dbo.TableB FOR XML PATH('TableB'), TYPE)
FOR XML PATH(''), ROOT('root')

XML Output :

<root>
   <TableA>
	 <Column1>Value1</Column1>
    	 <Column2>Value2</Column2>
   </TableA>
   <TableB>
	 <Column1>Value1</Column1>
    	 <Column2>Value2</Column2>
   </TableB>
</root>

You can load this Xml into DataSet using below C# code.

DataSet ds = new DataSet();
ds.ReadXml("queryResult.xml");

Sample SQL script to delete messages in Service Broker Queue manually

SQL Script:

declare @ch uniqueidentifier
while(1=1)
begin
    select top 1 @ch = conversation_handle from [dbo].[YourQueueName]
    if (@@ROWCOUNT = 0)
    break
    end conversation @ch with cleanup
end

Add or ignore where conditions in SQL Server query based on a flag

Assume you want to select data from SQL Server based on a condition. But the condition should be considered only if another condition satisfies else it should return all data. How would you do that. Here is a sample program to achieve the solution.

declare @flagAll = 1;


Select *

from Students S

where @flagAll = 1

or S.Department = 'CSE'

In this example if the @flagAll is set to 1, then the first condition will be satisfied so the second condition will not be executed as it is OR condition, so all data will be returned. Else if @flagAll = 0 then the first condition will be failed, as it is OR condition the second condition S.Department = 'CSE' will be considered and all CSE department Student data will be returned.

If you have alternate solution, Kindly post as comment.

Encrypt database using Transparent Data Encryption (TDE) technology in SQL Server

The Transparent Data Encryption (TDE) encryption technology protects the data in the database by encrypting the underlying files of the database, and not the data itself. So not just the sensitive data but all data in the database will be encrypted. This prevents the data from being hacked.

Assume you are having a database SampleDatabase with credit card details. If you open the backup file in Notepad and search for any card number, you should be able to see the actual data in the backup file. Hence anyone having access to the backup file can read the actual data, without restoring it.

To protect the sensitive data from users who do not have appropriate permission, encrypt the data using TDE feature as given below:

Step 1:

Create a Master Key or Encryption Key in the master database.

Use master;

Create Master Key Encryption By Password = 'C0mplexP@ssw0rd!';

Step 2:

Create a certificate for use as the database encryption key (DEK) protector and is protected by the DMK.

Create Certificate Cert4TDE

With Subject = 'TDE Certificate';

Step 3:

Create a database encryption key (DEK) encrypted with the certificate created in previous step. The supported encryption algorithms are AES with 128-bit, 192-bit, or 256-bit keys or 3 Key Triple DES. The created Master Key and the Certificate will be stored in the master database in an encrypted format.

Use SampleDatabase

Create Database Encryption Key With Algorithm = AES_256 

Encryption By Server Certificate Cert4TDE

Step 4:

Encrypt the data using the Master Key created in previous step.

Alter Database SampleDatabase

Set Encryption ON

Step 5:

We can verify the encryption using the below database query.

SELECT db.name,
    db.is_encrypted,
    ddek.encryption_state,
    ddek.key_algorithm,
    ddek.key_length,
    ddek.percent_complete
FROM sys.databases db
LEFT OUTER JOIN sys.dm_database_encryption_keys ddek
ON db.database_id = ddek.database_id;

GO

From the result we can see the SampleDatabase database is encrypted.

name	is_encrypted	encryption_state	key_algorithm	key_length	percent_complete
SampleDatabase	1	3	AES	256	0

Step 6:

Repeat Step 3 to backup the database again (with encrypted data) and open the backup file in Notepad. Now search the same card number and see that the data in the backup file is encrypted and secured.

Create custom SQL exception with custom message in C# (CSharp)

Below is a sample code which is used to create a custom SQL exception with custom message. The parameter accepts the message to be thrown. It is mostly used in Unit testing scenario where you need to validate for a custom message.

Sample Code

private SqlException GetSqlException(string message)
{
    SqlErrorCollection collection = Construct();
    SqlError error = Construct(-2, (byte)2, (byte)3, "Server", message, "Prcedure", 100, (uint)1);

    typeof(SqlErrorCollection)
        .GetMethod("Add", BindingFlags.NonPublic | BindingFlags.Instance)
        .Invoke(collection, new object[] { error });

    var e = typeof(SqlException)
        .GetMethod("CreateException", BindingFlags.NonPublic | BindingFlags.Static, null, CallingConventions.ExplicitThis, new[] { typeof(SqlErrorCollection), typeof(string) }, new ParameterModifier[] { })
        .Invoke(null, new object[] { collection, "11.0.0" }) as SqlException;

    return e;
}

private T Construct(params object[] p)
{
    return (T)typeof(T).GetConstructors(BindingFlags.NonPublic | BindingFlags.Instance)[0].Invoke(p);
}

Sample SQL Server function to Strip HTML tags from a HTML string

If you want to remove the HTML tags from a HTML string and retrieve only plain text, the below SQL Server function can be used. It is just removing all the HTML tags by identifying '<' and '>'.

SQL Server Function :

create function [dbo].[StripHTML] 
(
 @HTMLText varchar(max)
)
returns varchar(max) 
as begin
 declare @Start int
    declare @end int
    declare @Length int
    set @Start = charindex('<',@HTMLText)
    set @end = charindex('>',@HTMLText,charindex('<',@HTMLText))
    set @Length = (@end - @Start) + 1
    while @Start > 0 and @end > 0 and @Length > 0
    begin
        set @HTMLText = stuff(@HTMLText,@Start,@Length,'')
        set @Start = charindex('<',@HTMLText)
        set @end = charindex('>',@HTMLText,charindex('<',@HTMLText))
        set @Length = (@end - @Start) + 1
    end
    return ltrim(rtrim(@HTMLText))
end

Sample Input:

select dbo.StripHTML('
<!DOCTYPE html><html><body><h1>My First Heading. </h1><p>My first paragraph.</p></body></html>
')

Output:

My First Heading. My first paragraph.

Sample SQL Server Function to Truncate a string

Below is the sample SQL Server function to Truncate a string to the given number of characters.

If the string is smaller than the given limit, it is returned as-is; otherwise, it is truncated to 3 characters less than the given limit, and '...' is placed at its end..

SQL Server Function :

create function [dbo].[TruncateString]
(
    @Str varchar(max),
    @MaxLength int
)
returns varchar(8000)
as begin
    if @MaxLength is null or @MaxLength < 3 set @MaxLength = 3
    if @MaxLength > 8000 set @MaxLength = 8000

return case
    when datalength(@Str) <= @MaxLength then @Str
    else left(@Str, @MaxLength - 3) + '...'
    end
end

Sample SQL Server Function to Trim a string

Below is a sample SQL Server function to Trim a string. It removes leading & trailing whitespace characters from a string.

This function differs from rtrim() and ltrim() in that it removes tab, newline, and carriage return characters in addition to spaces. Like ltrim() and rtrim(), if you pass null in, you get null back.

SQL Server Function :

create function [dbo].[TrimString]
(
    @Str varchar(max)
)
returns varchar(max)
as begin
    declare @First int = 1,
    @Last int = datalength(@Str)

    while ascii(substring(@Str, @First, 1)) in (32, 9, 10, 13)
    set @First += 1

    if @First > @Last begin
    -- the string is all whitespace (or empty)
    return ''
 end

    while ascii(substring(@Str, @Last, 1)) in (32, 9, 10, 13)
    set @Last -= 1

    return substring(@Str, @First, @Last - @First + 1)
end

Find which stored procedures taking more time to execute using SQL Server DMV Query

It is very important to optimize the SQL Server stored procedure query to improve the performance. Optimizing the query is not a one time task as it needs to be checked frequently based on the developers activity on database related changes. Adding one single column to an existing select query would leads to performance degradation. There are many ways to monitor and identify the query performance. One of which is DMV (Dynamic Management Views).

DMV is nothing but queries returning information about server state that is current at the time the query was run. The information which are returned are actually returned from the cache. Every time a query executes, its information will be stored in the cache.

Below is the DMV query to identify the stored procedure which uses most resources which taking more time to execute. It is also returning the Query Plan in the last column which is very useful to identify the query section and optimize it.

SELECT CASE WHEN database_id = 32767 then 'Resource' ELSE DB_NAME(database_id)END AS DBName
      ,OBJECT_SCHEMA_NAME(object_id,database_id) AS [SCHEMA_NAME]  
      ,OBJECT_NAME(object_id,database_id)AS [OBJECT_NAME]
      ,cached_time
      ,last_execution_time
      ,execution_count
      ,total_worker_time / execution_count AS AVG_CPU
      ,total_elapsed_time / execution_count AS AVG_ELAPSED
      ,total_logical_reads / execution_count AS AVG_LOGICAL_READS
      ,total_logical_writes / execution_count AS AVG_LOGICAL_WRITES
      ,total_physical_reads  / execution_count AS AVG_PHYSICAL_READS,
      queryplan.query_plan
FROM sys.dm_exec_procedure_stats querystatus
CROSS APPLY sys.dm_exec_query_plan(querystatus.plan_handle) queryplan
where DB_NAME(database_id) = 'DBNAME'
ORDER BY AVG_LOGICAL_READS DESC

You have to mention your database name in the where condition to return stored procedure information from that. As I said earlier, the last column shows the Query plan link. Clicking it would open the query plan of the corresponding stored procedure similar to the below diagram.

(Solved) Error: Changes to the state or options of database '' cannot be made at this time. The database is in single-user mode, and a user is currently connected to it.

Error:

Changes to the state or options of database '' cannot be made at this time. The database is in single-user mode, and a user is currently connected to it.

Solution:

ALTER DATABASE [dbname] set multi_user

SELECT request_session_id FROM sys.dm_tran_locks 
WHERE resource_database_id = DB_ID('[dbname]')

KILL [session_id]

(Solved) Error: Exclusive access could not be obtained because the database is in use

Error:

Exclusive access could not be obtained because the database is in use

Solution:

USE MASTER;
ALTER DATABASE [dbname] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

(Solved) SQL Server Error: The subscription(s) have been marked inactive and must be reinitialized. NoSync subscriptions will need to be dropped and recreated.

Solution:

1. Run the below query to check the status of the subscription

use distribution
go
select * From distribution..MSsubscriptions

2. Note down the publisher_id, publisher_db, publication_id, subscriber_id, subscriber_db if the status is 0

3. Then update the status by runing the below query, apply the where condition value properly that you have noted in the previous step.

use distribution
go
if exists (select * from distribution..MSsubscriptions where status = 0)
begin
    UPDATE distribution..MSsubscriptions
    SET STATUS = 2
    WHERE publisher_id = 0
    AND publisher_db = 'Publisher_Db'
    AND publication_id = 16
    AND subscriber_id = 0
    AND subscriber_db ='Subscriber_Db'
end
 else
begin
 print 'All the subscription is Active'
end

4. Check the replication monitor for any issues.

(Solved) SQL Server Error: This database is not enabled for publication.

To enable a database for replication,

1. On the Publication Databases page of the Publisher Properties [Publisher] - dialog box, select the Transactional and/or Merge check box for each database you want to replicate. 
2. Select Transactional to enable the database for snapshot replication. 
3. Click OK.

(Solved) SQL Server Error: Cannot drop the database because it is being used for replication.

Solution:

Drop the replication in the corresponding database using the below Stored Procedure query. Then delete the Database.

sp_removedbreplication 'YOUR_DATABASE_NAME'

How to search for a text in SQL Server Database using Stored procedure

Here is the stored procedure that will search and display the table name and column name of the text you are passing as a parameter to search from the Database.
SQL Query

CREATE PROC SearchTextFromDatabaseTables
(
    @SearchString nvarchar(100)
)
AS
BEGIN
    CREATE TABLE #Results (ColumnName nvarchar(370), ColumnValue nvarchar(3630))
    SET NOCOUNT ON
    DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @SearchStr2 nvarchar(110)
    SET  @TableName = ''
    SET @SearchStr2 = QUOTENAME('%' + @SearchString + '%','''')
    WHILE @TableName IS NOT NULL
    BEGIN
        SET @ColumnName = ''
        SET @TableName =
        (
            SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))
            FROM     INFORMATION_SCHEMA.TABLES
            WHERE         TABLE_TYPE = 'BASE TABLE'
                AND    QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName
                AND    OBJECTPROPERTY(
                        OBJECT_ID(
                            QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)
                             ), 'IsMSShipped'
                               ) = 0
        )
        WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL)
        BEGIN
            SET @ColumnName =
            (
                SELECT MIN(QUOTENAME(COLUMN_NAME))
                FROM     INFORMATION_SCHEMA.COLUMNS
                WHERE         TABLE_SCHEMA    = PARSENAME(@TableName, 2)
                    AND    TABLE_NAME    = PARSENAME(@TableName, 1)
                    AND    DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar', 'int', 'decimal')
                    AND    QUOTENAME(COLUMN_NAME) > @ColumnName
            )
            IF @ColumnName IS NOT NULL
            BEGIN

                INSERT INTO #Results
                EXEC
                (
                    'SELECT ''' + @TableName + '.' + @ColumnName + ''', LEFT(' + @ColumnName + ', 3630)
                    FROM ' + @TableName + ' (NOLOCK) ' +
                    ' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2
                )
            END
        END  
    END
    SELECT ColumnName, ColumnValue FROM #Results
END

Execute the Procedure using the below query and pass the text to be searched in it.

exec SearchTextFromDatabaseTables 'TEXT TO BE SEARCHED'

and you will get the result as below

    ColumnName                                    ColumnValue
1  [dbo].[TABLENAME].[COLUMNNAME]                SEARCHED TEXT STRING

How to call and pass parameter to a MS SQL Server Stored procedure

Refer the below code to call and pass parameter to a MS SQL Server Stored procedure. Replace the YOUR CONNECTION STRING text with the connection string of your database.

 Here UpdateIssueStatus_SP is the Stored Procedure name.

SqlParameter param;  
SqlCommand cmd;  
string connStr = "YOUR CONNECTION STRING";  

public void UpdateIssueStatus(int issueNo, char status)  
{  
    try  
    {  
        SqlConnection conn = new SqlConnection(connStr);  
        conn.Open();  
        cmd = new SqlCommand("UpdateIssueStatus_SP", conn);  
        cmd.CommandType = System.Data.CommandType.StoredProcedure;  
   
        param = new SqlParameter("@IssueNo", SqlDbType.Int);  
        param.Direction = ParameterDirection.Input;  
        param.Value = issueNo;  
        cmd.Parameters.Add(param);  
   
        param = new SqlParameter("@ApprovalStatus", SqlDbType.Char);  
        param.Direction = ParameterDirection.Input;  
        param.Value = status;  
        cmd.Parameters.Add(param);  
   
        cmd.ExecuteNonQuery();  
        conn.Close();  
    }  
    catch (Exception ex)  
    {  
    }  
}  

How to check whether a database exist or not in MS SQL Server

Using a simple SQL query we can check whether a database exist or not. Refer the below query.
Query:

SELECT database_id FROM sys.databases WHERE Name = 'DATABASE NAME'  

If the output is greater then 0 then the Database is exist else it's not exist

Replace the DATABASE NAME with the database name you going to check