MS SQL Server Interview Questions – Database fundamentals, SQL queries, and server administration must all be thoroughly understood in order to prepare for MS SQL Server interviews. Frequently discussed subjects include stored procedures, backup/restore plans, indexing, and performance tuning. Anticipate inquiries about error management, real-world situations, and sophisticated features like replication and Always On Availability Groups. The secret to success is proving you have real-world experience with optimization and debugging.
Table of Contents
Security and Administration-related MS SQL Server Interview Questions
A few Security and Administration MS SQL Server Interview Questions with answers are given below for your interview preparation:
Q: What are the SQL Server roles?
Ans: Server roles such as db_owner, db_datareader, and db_datawriter allow us to handle database rights correctly.
Q. What is Transparent Data Encryption (TDE)?
Ans: TDE encrypts the data at rest to prevent unauthorized access.
Q: What are the different forms of authentication in SQL Server?
Ans: Windows and SQL Server authentication.
Q: How does one secure a database?
Ans: Use encryption, manage permissions, deploy fixes, and adhere to the concept of least privilege.
Q: What is the sysadmin’s role?
Ans: The sysadmin position has full access to all SQL Server functions.
Q: How do you audit SQL Server activity?
Ans: Use SQL Server Auditing or Extended Events.
Q. What is SQL injection?
Ans: A security flaw in which attackers run harmful SQL code.
Q: How do you avoid SQL injections?
Ans: Utilize parameterized queries and stored procedures.
Q: What is the purpose of the DENY statement?
Ans: It explicitly denies a user or role access to a resource.
Q. What is row-level security?
Ans: A feature that restricts access to rows in a table based on the user’s identity.
Data Manipulation and Advanced Queries-related MS SQL Server Interview Questions
A few Data Manipulation and Advanced Queries related to MS SQL Server Interview Questions with answers are given below for your interview preparation:
Q: Create a query that deletes duplicate rows from a table.
Ans: We can use the below query to fetch duplicate records & delete them from the table:
Syntax:-
WITH DeleteDupliRecordCTE AS (
SELECT *, ROW_NUMBER() OVER (PARTITION BY ColumnName1, ColumnName2 ORDER BY ID) AS RN
FROM UserTableName
)
DELETE FROM DeleteDupliRecordCTE WHERE RN > 1;Example:-
Use TestDB;
-- Step 1: Script to create the Items table
CREATE TABLE Items (
ItemID INT IDENTITY(1,1) PRIMARY KEY,
ItemName NVARCHAR(100),
ItemCategory NVARCHAR(50),
ItemPrice DECIMAL(10, 2)
);
-- Step 2: Insert 20 records into the Items table
INSERT INTO Items (ItemName, ItemCategory, ItemPrice)
VALUES
('Laptop', 'Electronics', 45000.00),
('Smartphone', 'Electronics', 25000.00),
('Tablet', 'Electronics', 18000.00),
('Washing Machine', 'Appliances', 30000.00),
('Refrigerator', 'Appliances', 50000.00),
('Laptop', 'Electronics', 45000.00), -- Duplicate
('Microwave', 'Appliances', 15000.00),
('Chair', 'Furniture', 5000.00),
('Table', 'Furniture', 7000.00),
('Desk', 'Furniture', 12000.00),
('Laptop', 'Electronics', 45000.00), -- Duplicate
('Bed', 'Furniture', 20000.00),
('Laptop', 'Electronics', 45000.00), -- Duplicate
('Smartphone', 'Electronics', 25000.00), -- Duplicate
('Tablet', 'Electronics', 18000.00), -- Duplicate
('Sofa', 'Furniture', 25000.00),
('Bookshelf', 'Furniture', 10000.00),
('Air Conditioner', 'Appliances', 40000.00),
('Chair', 'Furniture', 5000.00), -- Duplicate
('Heater', 'Appliances', 8000.00),
('Blender', 'Appliances', 3000.00),
('Washing Machine', 'Appliances', 30000.00),-- Duplicate
('Refrigerator', 'Appliances', 50000.00),-- Duplicate
('Smart TV', 'Electronics', 60000.00),
('Headphones', 'Electronics', 2000.00);
--Step 3: T-SQL Query to Find Duplicate Records in Item Table
SELECT ItemName, ItemCategory, ItemPrice, COUNT(*)
FROM Items
GROUP BY ItemName, ItemCategory, ItemPrice
HAVING COUNT(*) > 1;
--Step 4: T-SQL Query to Delete Duplicate Records from Item Table
WITH ItemCTE AS (
SELECT
ItemID,
ROW_NUMBER() OVER (PARTITION BY ItemName, ItemCategory, ItemPrice ORDER BY ItemID) AS RowNumber
FROM Items
)
DELETE FROM ItemCTE
WHERE RowNumber > 1;
Q: What’s the distinction between UNION and UNION ALL?
Ans: UNION removes duplicates, whereas UNION ALL contains duplicates.
Q: Please explain how CASE works in SQL Server.
Ans: CASE statement allows the placement of conditional logic in SQL queries.
SELECT ItemName,
(CASE WHEN ItemPrice > 12000 THEN 'High Value Item' ELSE 'Low Value Item' END) AS ItemCategory
FROM Items;
Q: What is the JSON support in SQL Server?
Ans: SQL Server has functions that can process JSON data, such as FOR JSON.
Q: How can we use Merge Statement?
Ans: We can use the below query
MERGE INTO MergeTable AS MergeTable
USING SourceUserTable AS SourceUserTable
ON MergeTable.ID = SourceUserTable.ID
WHEN MATCHED THEN UPDATE SET MergeTable.Column = SourceUserTable.Column
WHEN NOT MATCHED THEN INSERT (ColumnName1, ColumnName2) VALUES (SourceUserTable.ColumnName1, SourceUserTable.ColumnName2);Q: What exactly is windowing in SQL Server?
Ans: Windowing functions such as ROW_NUMBER(), RANK(), and NTILE() work with a set of rows.
Advanced Queries and T-SQL-related MS SQL Server Interview Questions
A few Advanced Queries and T-SQL-related MS SQL Server Interview Questions with answers are given below for your interview preparation:
Q: What is the definition of a derived table in SQL Server?
Ans: A derived table is a subquery that is included in the FROM clause.
SELECT Convert(varchar(10), ItemID) +'-'+ ItemName, ItemPrice
FROM (SELECT ItemID, ItemName, ItemPrice FROM Items) AS DerivedTable;
Q: Describe the differences between RANK() and DENSE_RANK().
Ans: RANK() gives each row a distinct rank, omitting duplicate ranks. Without skipping, DENSE_RANK() assigns sequential rankings.
Q: What does a SQL Server lateral join mean?
Ans: Although SQL Server lacks an explicit LATERAL keyword, it may accomplish the same thing with CROSS APPLY or OUTER APPLY.
Q: Use a window function to write a query that retrieves the cumulative salary.
Ans: Use the below query
SELECT EmpID, EmpSalary,
SUM(EmpSalary) OVER (ORDER BY EmpID) AS CumulativeEmpSalary
FROM Emp;Q: What does a SQL Server NULL-safe equal operator mean?
Ans: Because SQL Server lacks a NULL-safe equal operator, IS NULL or IS NOT NULL are used to handle NULL comparisons.
Q: What benefits can table variables offer over temporary tables?
Ans: Table variables have less overhead because they are stored in memory, but they do not permit rollbacks from transaction logs.
Q: What distinguishes IN from EXISTS, please?
Ans: Whereas IN tests for particular values, EXISTS checks for the existence of rows and is quicker for correlated subqueries.
Q: Use TOP to write a query to obtain the third-highest income.
Ans: We can use the below query to find 3rd highest salary:
SELECT TOP 1 EmpSalary
FROM (SELECT DISTINCT TOP 3 EmpSalary FROM Emp ORDER BY EmpSalary DESC) AS Temp
ORDER BY EmpSalary;Q: What is meant by a self-join?
Ans: When a table is joined with itself, it’s called a self-join.
SELECT e1.EmpID, e2.ManagerID
FROM Emp e1
INNER JOIN Emp e2 ON e1.ManagerID = e2.EmpID;Data Types and Functions-related MS SQL Server Interview Questions
A few Data Types and Functions-related MS SQL Server Interview Questions with answers are given below for your interview preparation:
Q: What distinguishes SYSDATETIME() from GETDATE()?
Ans: Whereas SYSDATETIME() gives more accurate date-time information, GETDATE() returns the current date and time with less accuracy.
Q: In SQL Server, how do you change the kind of data?
Ans: Make use of CONVERT or CAST.
SELECT CAST(ColumnName AS INT), CONVERT(VARCHAR, GETDATE(), 101);Q: What distinguishes NTILE() from ROW_NUMBER()?
Ans: While NTILE() divides rows into designated buckets, ROW_NUMBER() gives each row a unique number.
Q: What is the process for creating a random number in SQL Server?
Ans: We can use the below query to generate a random number
SELECT RAND() AS RandomNumber;Q: What is a VARCHAR(MAX) column’s maximum size?
Ans: A maximum of 2^31-1 bytes (2 GB) is allowed.
SQL Server Architecture-related MS SQL Server Interview Questions
A few SQL Server Architecture-related Questions with answers are given below for your interview preparation:
Q: What distinguishes an instance from a database?
Ans: An instance is the SQL Server engine that oversees several databases, whereas a database is a collection of data.
Q: Describe a transaction log and explain its significance.
Ans: For the sake of recovery and rollback, the transaction log keeps track of every database transaction.
Q: What does a SQL Server fill factor mean?
Ans: The percentage of an index page that is left empty for future expansion is known as the fill factor.
Q: What are filegroups in SQL Server?
Ans: Database files are logically contained within file groups, which are used to control data storage.
Q: What distinguishes DBCC SHRINKDATABASE from DBCC SHRINKFILE?
Ans: Whereas DBCC SHRINKDATABASE shrinks the size of every file in a database, DBCC SHRINKFILE targets a single file.
High Availability and Disaster Recovery-related MS SQL Server Interview Questions
A few High Availability and Disaster Recovery-related Questions with answers are given below for your interview preparation:
Q: What distinguishes Always On from failover clustering?
Ans: Always On offers high availability at the database level, whereas failover clustering provides high availability at the server level.
Q: Describe the database mirroring idea.
Ans: To guarantee high availability, database mirroring keeps two copies of the database (primary and mirror).
Q: In mirroring, what is a witness server?
Ans: By keeping an eye on the mirroring session, the witness server makes automated failover possible.
Q: What does a SQL Server checkpoint mean?
Ans: A checkpoint copies every dirty page from the buffer cache to disk in order to reduce recovery time.
Q: What distinguishes a transaction log backup from a differential backup?
Ans: A transaction log backup captures all logged transactions since the last transaction log backup, whereas a differential backup contains changes since the previous full backup.
Dynamic Management Views-related MS SQL Server Interview Questions
A few Dynamic Management Views-related Questions with answers are given below for your interview preparation:
Q: What function do DMVs provide in SQL Server?
Ans: DMVs offer data on the performance, diagnostics, and state of servers.
Q: To check for active sessions, write a query.
Ans: We can use the below query
SELECT * FROM sys.dm_exec_sessions;Q: How can missing indexes be checked?
Ans: We can use the below query
SELECT *
FROM sys.dm_db_missing_index_details;Q: What DMV is best for obtaining statistics on query execution?
Ans: We can use the below query
SELECT *
FROM sys.dm_exec_query_stats;Q: How can the wait statistics in SQL Server be checked?
Ans: We can use the below query
SELECT *
FROM sys.dm_os_wait_stats;Miscellaneous MS SQL Server Interview Questions
A few Dynamic Management Views-related Questions with answers are given below for your interview preparation:
Q: What function do DMVs provide in SQL Server?
Ans: DMVs offer data on the performance, diagnostics, and state of servers.
Q: To check for active sessions, write a query.
Ans: We can use the below query
SELECT * FROM sys.dm_exec_sessions;Q: How can missing indexes be checked?
Ans: We can use the below query
SELECT *
FROM sys.dm_db_missing_index_details;Q: What DMV is best for obtaining statistics on query execution?
Ans: We can use the below query
SELECT *
FROM sys.dm_exec_query_stats;Q: How can the wait statistics in SQL Server be checked?
Ans: We can use the below query
SELECT *
FROM sys.dm_os_wait_stats;Q: What distinguishes OLTP systems from OLAP systems?
Ans: Whereas OLAP systems are built for analytical queries and reporting, OLTP systems manage transactional data.
Q: What makes DROP and TRUNCATE different from one another?
Ans: Whereas TRUNCATE merely removes the data, DROP removes both the data and the table structure.
Q: What does a SQL Server histogram mean?
Ans: One statistical tool for showing the distribution of data in a column is a histogram.
Q: How may a SQL Server deadlock be analyzed?
Ans: Make use of SQL Profiler, Extended Events, or trace flags such as -T1222.
Q: What do SQL Server extended stored procedures mean?
Ans: Extended stored procedures allow SQL Server to call external programs or functions using DLLs.
Review the articles below
Top 100 SQL Server Interview Questions Part-1
DBCC FREEPROCCACHE: A powerful command
Extended Events in SQL Server: A Deep Dive
Understand Deadlocks in SQL Server
SQL Server Pivot: Top 5 Concepts
A Powerful Merge Statement in SQL Server
Dynamic Data Masking in SQL Server
DBCC SQLPerf (LogSpace): Top 15 Usage
A Powerful SQL Server Developer Edition
Unveiling the Power of SQL Server CharIndex
SQL Server Convert: An Important Function
SQL Server Configuration Manager