Here are the top 50 commonly asked questions in SQL Server interviews. Whether you’re just starting your preparation or need a quick refresher, these questions and answers will boost your confidence for the interview. Ranging from basic to advanced, they cover a wide array of SQL Server concepts. Practice these questions for campus and company interviews, positions from entry to mid-level experience, and competitive examinations. It’s also important to practice them to strengthen your understanding of SQL Server.
SQL Server Interview Questions with Answers
1. What are the query languages used by Microsoft SQL Server?
SQL Server is a ‘relational database management system’ and it is the product of Microsoft developed for Enterprise environment. SQL Server uses two query languages which are as follows:
- Transact-SQL (T-SQL)
- ANSI-SQL
2. Describe different components in SQL Server Architecture?
The different components are as follows:
- Database Engine: It is the core service for storing, processing and securing data
- Analysis Services: It is an online analytical data engine used in decision support and BI solutions
- Reporting Services: It includes a complete set of tools to create, manage and deliver reports
- Integration Services: It is a platform for building enterprise-level data integration and data transformations solutions
3. Describe different editions of SQL Server?
Microsoft has got different editions of SQL Server targeting different users.
- Enterprise
- High end, all inclusive product
- Support for Always-on availability
- Advanced auditing
- Standard
- Support for basic BI and reporting features
- Does not support advanced features such as data warehousing
- BI
- Includes all standard and enterprise edition features
- No support for advanced tuning
- Express
- Includes new Local DB feature for lightweight deployments
- Free edition
4. What is the use of database engine in SQL Server?
Database engine is core service used for creating relational databases for OLTP or OLAP. It provides controlled access and rapid transaction processing to meet the requirements of the most demanding data consuming applications within your enterprise. It also provides rich support for sustaining high availability.
5. What is the purpose of SQL Server Agent in SQL Server?
SQL Server Agent is a Microsoft Windows Service that automates administrative tasks. It runs jobs, monitors SQL Server and process alerts. Jobs can be scheduled using SQL Server Agent. SQL Server Agent jobs can be created and scheduled under object explorer.
6. What are the different tools used for database development and maintenance?
Different tools used are as follows:
- SQL Server Management Studio
- SQL Server Configuration Manager
- SQL Server Data Tools
- SQL Profiler and DTA
7. Explain different system databases in SQL Server?
Different databases are as follows:
- master: records all system level information
- msdb: used by SQL Server agent to schedule jobs
- model: used as template for all databases created on instance of SQL Server.
- resource: read only database that contain system objects
- tempdb: is a workspace for holding temporary objects
8. Explain data storage in SQL Server?
SQL Server data is organized in to files. When a database is created, two types of files are created:
- Data files (.mdf) – It is the main repository for the database
- Log files (.ldf) – These logs are used for data recovery
9. Explain different types of instance in SQL Server?
SQL Server supports multiple instances of SQL Server concurrently running on same computer.
Default: It is solely identified by computer name.By default, components try to connect to this instance
Named: It is identified by instance name specified during installation of the instance
10. What are the different database objects in SQL Server?
Some of the Database objects are as follows:
- Scalar functions
- System tables
- System stored procedures
- User defined tables
- User defined data type
- Inline table functions
11. Explain different data types in SQL Server?
Different category of data types with example are as follows:
- Exact numerics: INT, MONEY
- Approximate numerics: FLOAT, REAL
- Date and time: DATETIME, SMALLDATETIME, DATE
- Character strings: CHAR, VARCHAR, TEXT
- Binary strings: BINARY, VAR BINARY, IMAGE
12. Explain the concept of variables in SQL Server?
There are two types of variables in SQL Server:
- Global
- Always prefixed with ‘@@’ sign.
- are predefined and maintained by system
- Local
- Must be prefixed with ‘@’ sign
- User defined
- Initialized to null when created
- Confined to batch
13. What are different foreign key constraints in SQL Server?
Different foreign key constraints are as follows:
- ON DELETE SET NULL: A foreign key with “set null on delete” means that if a record in the parent table is deleted, then the corresponding records in the child table will have the foreign key fields set to NULL
- ON DELETE CASCADE: A foreign key with cascade delete means that if a record in the parent table is deleted, then the corresponding records in the child table will automatically be deleted.
- ON DELETE SET DEFAULT: Specifies that if an attempt is made to delete a row with a key referenced by foreign keys in existing rows in other tables, all the values that make up the foreign key in the rows that are referenced are set to their default value.
14. What do you mean by bulk insert in SQL Server?
BULK INSERT is used to import data from a data file in to a table or view. The format of the imported data can be specified, based on how that data is stored in the file. Using the BULK INSERT statement we can insert bulk data into the database directly from a CSV file.
15. Do we have natural join in SQL Server?
Natural join is a type of equi-join which occurs implicitly by comparing all the same names columns in both tables. The join result have only one column for each pair of equally named columns. Natural join is not supported by current SQL Server version.
16. What is the purpose of TOP clause in SQL Server?
TOP clause limits the rows returned in a query result set to a specified number of rows or percentage of rows. When TOP is used in conjunction with the ORDER BY clause, the result set is limited to the first N number of ordered rows; otherwise, it returns the first N number of rows in an undefined order.
17. Why do we need common table expression in SQL Server?
A CTE can be used to:
- Create a recursive query
- Substitute for a view when the general use of a view is not required
- Enable grouping by a column that is derived from a scalar subselect
- Reference the resulting table multiple times in the same statement
18. What are the different ranking functions in SQL Server?
Ranking functions return a ranking value for each row in a partition. Transact-SQL provides the following ranking functions:
- RANK
- DENSE_RANK
- NTILE
- ROW_NUMBER
19. What are the different batch programming control flow statements?
Procedural features are included in T-SQL for easier processing of data.Control flow statements supported are as follows:
- IF ELSE
- While
- CASE
- Goto
20. What is user defined function in SQL Server and enlist it’ types?
User defined function is a T-SQL routine that returns a value. It can be invoked by query. It can be executed like stored procedure. Types of user defined functions are as follows:
- Scalar
- Inline table valued function
- Multi-statement table valued function
21. Explain usage of triggers in SQL Server?
A trigger is a special kind of stored procedure that automatically executes when an event occurs in the database server. DML triggers execute when a user tries to modify data through a data manipulation language (DML) event. DDL triggers execute in response to a variety of data definition language (DDL) events. SQL Server allows for creating multiple triggers for any specific statement.
22. Explain different types of trigger in SQL Server?
There are three type of Triggers
- DML Triggers
- Instead of Trigger: Instead of trigger are fired in place of the triggering action such as an insert, update, or delete
- After Trigger: After trigger execute following the triggering action, such as an insert, update, or delete.
- DDL Triggers: This type of trigger is fired against DDL statements like Drop Table, Create Table or Alter Table. DDL Triggers are always after Triggers
- Logon trigger: This type of trigger is fired against a LOGON event before a user session is established to the SQL Server
23. Explain exception handling in SQL Server?
TRY…CATCH implements error handling for Transact-SQL .A group of Transact-SQL statements can be enclosed in a TRY block. If an error occurs in the TRY block, control is passed to another group of statements that is enclosed in a CATCH block.
24. What are different transaction control statements?
The three different transaction control statements are:
- BEGIN: Alerts SQL Server that transaction is beginning
- Rollback: Undoes the changes to the named savepoint or the beginning of transaction
- Commit: End the transaction and saves changes to the database
25. Which resources can be locked by database engine?
The following are the resources that the database engine can lock:
- RID
- Key
- Page
- Event
- Table
- File
- Metadata
- Application
- Database
Advanced SQL Server Interview Questions with Answers
26. Explain advantages of multiple instances in SQL Server?
Advantages are as follows:
- A machine having multiple instance can support many organizations
- A single organization can host ten server application on a single machine
- One machine can be used for reporting, reproducing problems and bug fixing
27. What is the purpose of Output Clause in SQL Server?
Output clause returns information from each row affected by following statements:
- UPDATE
- DELETE
- INSERT
- MERGE
These results can be returned to the processing application to be used as confirmation message.
28. Explain spatial types in SQL Server?
The geography spatial data type, geography, is implemented as a .NET common language runtime (CLR) data type in SQL Server. This type represents data in a round-earth coordinate system. The SQL Server geography data type stores ellipsoidal (round-earth) data, such as GPS latitude and longitude coordinates.
The planar spatial data type, geometry, is implemented as a common language runtime (CLR) data type in SQL Server. This type represents data in a Euclidean (flat) coordinate system.
29. Explain advantages and drawbacks of FILESTREAM data type?
Advantages
- Taken care by SQL Server
- Reliable
- Storage capacity is limited to disk volumes
Drawbacks
- Database mirroring and snapshot are not supported
- Cannot be used as table valued parameter
- Supported isolation level is read committed
30. What is the difference between inline and multi-statement table valued function?
Inline Table-Valued Functions returns a resultset, as opposed to a single scalar value. Multi-statement Table-Valued Function also returns a resultset, like the Inline variety UDF, but with a much more powerful result. When an inline table-valued function is used as part of an outer query, the query processor expands the UDF definition and generates an execution plan that accesses the underlying objects, using the indexes on these objects. For a multi-statement table valued function, an execution plan is created for the function itself and stored in the execution plan cache.
31. What are the different statements allowed in a multi statement function?
Statements allowed in a multi statement function:
- Assignment
- Control of flow
- Declare statement defining data variables
- Select statement containing select list
- TABLE variables
- Calling Extended Procedures using Execute statement
32. What are the different types of stored procedures in SQL Server?
A stored procedure can take input parameters, return tabular or scalar results and messages to the client. Types are as follows:
- System
- Temporary
- Remote
- Extended
- User-defined
33. What are the special tables used with trigger?
DML trigger statements use two special tables: the deleted table and the inserted tables. SQL Server automatically creates and manages these tables. Functions performed by them are as follows:
- Extend referential integrity between tables.
- Insert or update data in base tables underlying a view.
- Test for errors and take action based on the error.
- Find the difference between the state of a table before and after a data modification and take actions based on that difference
34. Why do we need snapshot isolation in SQL Server?
The term “snapshot” reflects the fact that all queries in the transaction see the same version, or snapshot, of the database, based on the state of the database at the moment in time when the transaction begins. Snapshot isolation uses an optimistic concurrency model. If a snapshot transaction attempts to commit modifications to data that has changed since the transaction began, the transaction will roll back and an error will be raised. When the SNAPSHOT isolation level is enabled, each time a row is updated, the SQL Server Database Engine stores a copy of the original row in tempdb, and adds a transaction sequence number to the row.
35. Explain different types of lock mode in SQL Server?
- Shared (S): Used for read operations that do not change or update data, such as a SELECT statement.
- Update (U): Used on resources that can be updated.
- Exclusive (X): Used for data-modification operations, such as INSERT, UPDATE, or DELETE.
- Intent: Used to establish a lock hierarchy.
36. What are the key features of column store technology?
The key characteristics of SQL Server column store technology are as follows:
- Columnar data format
- Faster query results
- Key columns – There is no concept of key columns in a columnstore index
- Partitioning – Columnstore indexes works with table partitioning
- Record Size – The index key record size limitation of 900 bytes also does not apply to columnstore indexes
37. How to detect and reduce fragmentation in SQL Server?
Storing data non-contiguously on disk is known as fragmentation. sys.dm_db_index_physical_stats is used to detect fragmentation.
When an index is fragmented in a way that the fragmentation is affecting query performance, there are three choices for reducing fragmentation:
- Drop and re-create the clustered index. Re-creating a clustered index redistributes the data and results in full data pages.
- Use ALTER INDEX REORGANIZE
- Use ALTER INDEX REBUILD
38. Explain the different transaction modes supported by SQL Server?
Microsoft’s SQL Server provides support for transactions in three different modes of operation. They are as follows:
- Implicit: If a transaction is implicitly started, then the transaction will continue until it’s either fully committed or until the transaction is rolled back
- Explicit: Explicit mode is most commonly used in triggers, stored procedures and application programs
- Autocommit: In autocommit mode, each SQL statement is treated as a separate transaction
39. Explain concept of principals in SQL Server?
Principals are entities that can request SQL Server resources. Types of principals are as follows:
- Windows Level-It includes window domain and window local login
- SQL Server Level
- Database Level-It includes database role, user and application role.
40. Explain few server level roles in SQL Server?
SQL Server provides server-level roles to help you manage the permissions on a server
- sysadmin: Members of the sysadmin fixed server role can perform any activity in the server
- processadmin: Members of the processadmin fixed server role can end processes that are running in an instance of SQL Server
- diskadmin: The diskadmin fixed server role is used for managing disk files.
- dbcreator: Members of the dbcreator fixed server role can create, alter, drop, and restore any database.
41. What is the difference between contained and partially contained database?
A contained database is a database that is isolated from other databases and from the instance of SQL Server that hosts the database. A partially contained database is a contained database that can allow some features that cross the database boundary. SQL Server includes the ability to determine when the containment boundary is crossed.
42. Explain some of the best practices to be adopted while using SQL Server?
Some of best practices are as follows:
- Avoid using Nchar or nVarchar unless you need to store Unicode characters
- Use char for fixed length data
- Avoid using SQL variant data type
- Use varchar(max) instead of text
- Use @@Error or try..catch to handle the errors
43. What is CLR Integration?
The common language runtime (CLR) is the heart of the Microsoft .NET Framework and provides the execution environment for all .NET Framework code. Code that runs within the CLR is referred to as managed code. The CLR provides various functions and services required for program execution, including just-in-time (JIT) compilation, allocating and managing memory, enforcing type safety, exception handling, thread management, and security.
44. Explain two forms of APPLY operator?
The APPLY operator allows you to invoke a table-valued function for each row returned by an outer table expression of a query. Two forms of APPLY are as follows:
- CROSS APPLY: It returns only those rows in the outer table for which the table value function returns data.
- OUTER APPLY: It returns all rows from the outer table, whether or not the function returns data for a specific row
45. What is the difference between SET and SELECT statement in SQL Server?
Set is ANSI standard for assigning values to variables. Select can be used when assigning values to multiple variables. SET is slower than SELECT. SELECT is mainly to return data. SET is mainly to assign values to local variables.
46. What is the difference between PIVOT and UNPIVOT clause in SQL Server?
PIVOT rotates a table-valued expression by turning the unique values from one column in the expression into multiple columns in the output, and performs aggregations where they are required on any remaining column values that are wanted in the final output. UNPIVOT performs the opposite operation to PIVOT by rotating columns of a table-valued expression into column values.
47. What is the purpose of MERGE statement in SQL Server?
MERGE performs insert, update, or delete operations on a target table based on the results of a join with a source table. When used after MERGE, @@ROWCOUNT (Transact-SQL) returns the total number of rows inserted, updated, and deleted to the client. The MERGE statement requires a semicolon (;) as a statement terminator.
48. Explain Identity property in SQL Server?
Identity property creates an identity column in a table. This property is used with the CREATE TABLE and ALTER TABLE Transact-SQL statements. Only one identity column can be created per table. Identity columns can be used for generating key values.
49. What are SQL Server specific data types?
They are as follows:
- XML – Used for storing semi structured data
- Geospatial – This type represents data in a round-earth coordinate system
- HierarchyId – It is used to represent position in a hierarchy
- FILESTREAM – It is used to store unstructured data
50. What are different isolation levels for transactions?
The different isolation levels are as follows:
- Uncommitted Read: This lets a transaction read any data currently on a data page, whether or not that data has been committed
- Committed Read: This ensures that an operation will never read data another application has changed but not yet committed
- Repeatable Read: This isolation level adds to the properties of Committed Read by ensuring that if a transaction revisits data or if a query is reissued, the data will not have changed
- Serializable: This isolation level ensures that if a query is reissued, no data will have changed and no new rows will appear in the interim
Useful Resources:
