SQL Server Interview Questions

We have categorized SQL Server Interview Questions - 2023 (Updated) into 3 levels they are:

• Freshers
• Experienced
• Advanced

Below mentioned are the Top Frequently asked SQL Server Interview Questions and Answers that will help you to prepare for the SQL Server interview. Let's have a look at them.

Top 10 Frequently Asked SQL Server Interview Questions

1. What do you mean by RDBMS?
2. Can you differentiate between SQL and MYSQL?
3. What is a JOIN, and mention its Types?
4. What are Triggers, and mention their types in the SQL server?
5. What is SQL Server Profiler?
6. What are the various SQL Database Functions?
7. Can you mention the different types of Queries in SQL Servers?
8. What are SQL Server Integration Services and their functions?
9. Compare Local Variables and Global Variables?
10. What is the difference between OLAP and OLTP?

SQL Server Interview Questions and Answers for Freshers

1. What do you mean by database?

Ans: A database is a structured form of data storage where data can be retrieved and managed efficiently. It is the collection of tables where the data is stored in an organized way. Tables consist of rows and columns in which rows are also known as records or tuples, and columns are known as attributes. Bank Management Database and University Management Database are a few examples of databases.

If you want to enrich your career and become a professional in SQL Server, then enroll in "SQL Server Online Training" - This course will help you to achieve excellence in this domain.

2. What is a Relational Database?

Ans: A relational database is the collection of data that have an established relationship with each other. It consists of rows and columns. Each row in a database is a record, and columns have the attributes of data. Each row in a table can be identified with a unique ID known as a primary key. And rows in a table can be related to other tables using foreign keys.

3. What do you mean by RDBMS?

Ans: RDBMS is nothing but – Relational DataBase Management System. It is the software that allows storing, managing, querying, and retrieving data from a relational database. And RDBMS interacts with users and the database; it can also carry out administrative tasks such as – managing data storage, accessing data, and assessing database performance.

4. Can you differentiate between SQL and MYSQL?

Ans:

SQL	MySQL
It is the licensed product of Microsoft	It is an open-source platform managed by Oracle Corporation
Knowns as Structured Query Language – It is a query programming language	It is the Relational Database Management System
It uses SQL queries to perform tasks in RDBMS	It has no separate querying language. Uses SQL to perform tasks in RDBMS
Data is protected safely by not allowing third parties to intrude on the SQL servers.	Unlike SQL, it is an open-source platform, so data security wouldn't be as expected.
SQL doesn’t support any connectors	MySQL supports connectors such as the workbench tool to build databases.

5. What do you mean by Relationships between Tables and mention their Types?

Ans: Relationships between tables describe how a row in a table is related to a row or rows of another table in a database.

There are three types of relationships, as mentioned below:

1. One to one: When a row in a table has only one related row in another table
2. One to many: When a row in a table has multiple related rows in another table
3. Many to many: When a row has multiple related rows in another table and vice-versa.

6. What is an Entity in a Database?

Ans: Entities are nothing but objects that are stored in the master data services models. They are real-world objects that can be distinguishable from other objects. Rows in an entity represent members of the master data, and columns represent attributes of the master data. Generally, entities group the same kind of data. For example, a list of employees of a company.

7. What do you mean by Attributes and mention their Types?

Ans: Attributes are the objects that are included in master data service entities. Also, attribute values are used to describe the members of the entity.

There are three types of attributes, as mentioned below:

• Free-form attributes
• Domain-based attributes
• File attributes

Related Article: SQL Server Tutorial for Beginner

8. What is SQL Server, and mention its core components?

Ans: SQL Server is an RDBMS developed by Microsoft. It has two core components – database engine and SQLOS. The database engine has a relation engine that supports processing queries, and managing database files, pages, indexes, etc. And SQLOS is nothing but a SQL Operating system. SQLOS provides operating systems such as memory and I/O management. It also performs the operations like exception handling and synchronization services.

Related Article: SQL Server Architecture

9. What is a transaction in SQL Server and mention its modes?

Ans: A transaction represents a single task. Once a transaction is over, modifications made in the data will be committed and stored in a database permanently. If an error occurs in the transaction, then the data changes will be canceled immediately.

The following are the modes of transactions:

• Autocommit transactions
• Explicit transactions
• Implicit transactions
• Batch-scoped transactions

10. What is a Transaction Log, and why is it important?

Ans: Transaction log records all transactions and related database modifications of every transaction. To be precise, it records the beginning of a transaction, the changes during the transaction, and the last COMMIT or ROLLBACK of the transaction. The transaction log is one of the vital components in database management since it is used to retrieve the database to a consistent state if there is a system failure.

11. What are ACID properties, and what do they mean?

Ans: The ACID properties are nothing but Atomicity, Consistency, Isolation, and Durability. Generally, all transactions must follow ACID properties.

• Atomicity: It ensures that a complete transaction must take place in a single execution. Suppose there is only a subset of operations during a transaction, then there could be a compromise in the aim of the transaction. But, atomicity eliminates this possibility.
• Consistency: It ensures that a transaction takes place with absolute data consistency before and after the transaction. Simply put, data consistency shouldn’t be compromised during transactions.
• Isolation: It ensures that each transaction takes place in complete isolation from other transactions. It means that each transaction must run as if it is the only transaction that is happening in the system.
• Durability: It makes sure that every transaction must be recoverable when required. Once a transaction is committed, it means that all the data updates have been made in the database then, and they can be retrieved at any time by users.

12. What is a Stored Procedure?

Ans: It is a function that consists of a group of statements, which can be stored and used repeatedly. Stored procedures can be called and executed as and when required. Stored procedures are stored as ‘Named object’ in the SQL server database. The result set of the stored procedure depends on the values of the parameters.

13. What is the use of an Extended Stored Procedure?

Ans: Generally, stored procedures are the blocks of codes that can be accessed by simple application calls. At the same time, extended stored procedures help expand the functionality of SQL servers through external functions written in C or C++ languages. As a result, it allows returning result sets and parameters to the server from different data sources.

14. What is a Recursive Stored Procedure?

Ans: A stored procedure turns into a recursive stored procedure, including a CALL statement in the procedure body. Here, the CALL statements can be nested. In fact, there is no limit for recursions, but stored procedures nesting is limited by 15 times. Moreover, there are two forms of recursive stored procedures – mutual recursion and chain recursion.

15. What are Database Normalisation and Denormalisation?

Ans: Database normalization is the process of restructuring a relational database to reduce data redundancy and improve data integrity. On the other hand, denormalization is a reverse engineering process that helps increase the read performance of a database. And it is achieved by either adding copies of data or grouping data. Hence, data can be read in a shorter time.

16. What is a JOIN, and mention its Types?

Ans: JOIN is a logical operation used to retrieve data from two or more two tables. It can only be accomplished when there is a logical relationship between two tables. Here, data from one table is used to select rows in another table.

There are five types of logical JOIN operations as mentioned below:

• INNER JOIN
• LEFT (OUTER) JOIN
• RIGHT (OUTER) JOIN
• FULL (OUTER) JOIN
• CROSS JOIN

Related Article: Different Types Of  SQL Server Joins

17. What is Subquery in SQL Server?

Ans: It is known as subquery when a query is nested inside the statements such as SELECT, INSERT, UPDATE, or DELETE. Besides, a nested query inside a subquery is also known as a sub-query. Further, a subquery is also known as an inner query or an inner select. Similarly, the statement that has a subquery is known as an outer query or an outer select.

18. What are the Types of Subquery in SQL Server?

Ans: The following are the subquery types:

• Subqueries with table aliases
• Subqueries with IN and NOTIN
• Subqueries in UPDATE, DELETE and INSERT statements
• Subqueries with comparison statements
• Comparison operators modified by ANY, SOME or ALL
• Subqueries with EXISTS and NOT EXISTS

19. How can you differentiate between a Primary Key and a Unique Key?

Ans: The primary key identifies each record in a table. It should have unique values but shouldn’t have NULL values. At the same time, the unique key ensures that all the values in a column are different. Simply put, the unique key avoids duplication of data in a column except for NULL Values. Moreover, a table will have only one primary key, but it may have many unique keys.

20. How can you relate a Foreign Key and a Primary Key?

Ans: A foreign key is a field (s) in a table that links the primary key with another table. A foreign key is mainly used to prevent the loss of a link between two tables. The table that has a primary key is known as the parent table, and a table that has a foreign key is known as the child table. A Foreign key can link with a unique key of another table as it links with a primary key.

21. What are Defaults in the SQL Server?

Ans: Default is the value specified for a column in a database table. This value will be duplicated in all the new records of the column unless there is no other value specified. In this regard, SQL server management studio is the tool used to specify a default value. Know that we can create defaults only for a current database, and the default value can be truncated when it exceeds the size of the column.

22. What are Cursors, and mention their types?

Ans: Cursors are known to be the extensions to result in sets that are the group of rows returned for a statement. They help retrieve one or more blocks of rows from the current position. Similarly, they can support data modifications for the rows in the current position in the result set.

There are four cursor types, as you can find below:

• Forward only
• Static
• Keyset
• Dynamic

23. What are Triggers, and mention their types in the SQL server?

Ans: Triggers are the special stored procedures. When there is an event in the SQL server, triggers will run automatically. There are three types of triggers: LOGON, DDL, and DML.

• LOGON Triggers: They are fired when a user establishes a LOGON event.
• DDL Triggers: They are fired when there is a Data Definition Language (DDL) event.
• DML Triggers: They are fired when there is a modification in data due to Data Manipulation Language (DML).

24. When can you use Triggers in the SQL Server?

Ans:

• When there is a need for an audit trail of activity in a database
• Deriving additional data that is not available within a database
• When enforcing referential integrity
• To implement a business rule

Related Article: JP Morgan Interview Questions

25. What is Referential Integrity?

Ans: Referential integrity aims at keeping SQL databases consistent. It is achieved using a group of rules that enforces relationships among data in tables. Generally, referential integrity is enforced with the support of foreign key constraints. Besides, it can be enforced with the help of check constraints with user-defined functions and triggers.

26. What do you mean by ER Diagram?

Ans: The ER diagram is known as the Entity-Relationship diagram. This diagram shows the visual representation of the structure of tables in databases and their logical relationships. ER Diagram displays table structures with column names and the associated data types, primary and foreign keys used, and relationships between the tables.

Related Article: Learn SQL Server Constraints with Examples

27. What do you mean by CTE?

Ans: CTE is represented as Common Table Expression. It specifies the temporary named result set. This result set is obtained by executing simple queries. CTE can be referred to in SELECT, INSERT, UPDATE, DELETE and MERGE statements. Moreover, CTE can also be used in VIEW statements. There are two types of CTE – recursive and non-recursive.

28. What is a Sparse Column?

Ans: Sparse columns are nothing but ordinary table columns that provide optimized storage for NULL values. They reduce space requirements for NULL values by about 20 to 40 percent. Sparse columns can be used with filtered indexes and column sets. Sparse columns are defined by CREATE TABLE and ALTER TABLE statements.

29. What do you mean by Shared, Exclusive, and Updated locks?

Ans:

• Shared locks: It allows a page or rows only for reading. It restricts modifications of data by concurrent transactions.
• Exclusive locks: It allows exclusive transactions to modify a page or row using DML statements such as INSERT, UPDATE, and DELETE.
• Updated locks: It is used to avoid deadlocks. You can place a shared lock over a resource that already has an updated lock.

30. What is SQL Server Profiler?

Ans: It is a graphical user interface used for monitoring an instance of the database engine. It creates and manages traces and analyses the trace results when there is a demand. Generally, trace files record events, which can be replayed while diagnosing issues such as slow-running queries, finding the root cause of problems, etc.

31. What do you mean by Check Constraints?

Ans: Check constraints help to limit values to ensure their domain integrity. For instance, you can use logical operators that will only return either TRUE or FALSE. They can be used to create a check constraint to limit upper and lower values within a range. Also, you can apply multiple check constraints to a single column; similarly, you can apply a single constraint to multiple columns.

32. What is an SQL Server Agent?

Ans: SQL server agent is a Microsoft Windows service that runs jobs on-demand, on schedule, or in response to an event. Generally, jobs are known as scheduled administrative tasks, and the SQL server agent executes the jobs in the SQL server. Jobs may contain one or more steps, and each step will have a task. For example, if there is a problem in a backup, the SQL server will record this event and notify the user.

33. What is COALESCE in SQL server and mention its few properties?

Ans: It is an expression that evaluates arguments in a list and returns only the first value that is not NULL.

For example, consider the following statement

 SELECT COALESCE (NULL, 14, 15);

Now, the COALESCE expression returns the first value 14, which comes first after a NULL value.

Properties of COALESCE expression:

• The data type must be the same
• It can be a syntactic shortcut for the case expression

34. What is BULK COPY in SQL server?

Ans: BULK COPY allows a large amount of data transfer in and out of SQL tables or views. In addition, it allows data transfer between an SQL server and an Operating System data file such as ASCII.

BULK COPY in SQL server can be done in the following four modes:

• Native mode data file: Bulk copy from a table or view into a table or view in the same format.
• Character mode data file: Bulk copy from a table or view into another table in a different format
• Bulk copying from a data file into a table or view
• Loading data into program variables initially and then bulk copying into a table or view.

35. What do you mean by Collation?

Ans: Collation refers to the pre-defined rules to sort and compare data, and it also defines bit patterns that represent characters in a database. The rules help determine how the data should be stored, accessed, and compared. In addition, collation also provides case sensitivity and accent sensitivity for a dataset. SQL servers can store objects that have different collations in a single database.

36. What is the use of the UPDATE_STATISTICS command?

Ans: SQL Server updates query optimization statistics regularly in a database table or indexed view. In this regard, the SQL server's query optimizer performs this function by default. UPDATE_STATISTICS is the command that allows updating query statistics frequently in database tables. As a result, it improves query plans and query performance as well.

37. What is a Filtered Index?

Ans: A filtered index is nothing but a non-clustered index with an optimized disk-based restore. It uses a filter predicate to select a portion of rows in a table. A filtered index is created when a column has fewer relevant values for queries. Hence, it helps to improve query performance, reduce storage costs, and index maintenance.

38. How can you select data from an SQL table?

Ans: The SELECT statement helps to select data from databases.

The below example shows the retrieval of all the data from a table using the SELECT statement.

SELECT *
FROM DimEmployee
ORDER BY LastName;

Suppose you need to select a portion of a table, then the WHERE clause must be used along with the SELECT statement to retrieve the required data. The below example shows the meaning of the WHERE clause in a SELECT statement.

SELECT FirstName, LastName, StartDate AS FirstDay
FROM DimEmployee
WHERE EndDate IS NOT NULL
AND MaritalStatus='M'
ORDER BY LastName;

39. A table variable or a temporary table: which one is faster?

Ans: A table variable functions faster than a temporary table because table variables are stored in memory, whereas temporary tables are stored in a disk. Moreover, if a table variable’s size exceeds the size of memory, then its speed decreases.

40. What do you mean by Scheduled Tasks in SQL server?

Ans: To update the backups and statistics, databases must be maintained regularly. It can be achieved using scheduled jobs. In this way, the SQL server agent supports scheduling tasks in the SQL server to perform maintenance at regular intervals. In addition, the SQL server agent helps to view and modify the scheduled tasks.

Related Article: How to Learn Error Handling in SQL Server

41. What is the use of the SIGN function?

Ans: The SIGN function returns the sign of a number. The syntax for this function is shown as follows:

SIGN (numeric_expression)

This function returns values based on the following conditions:

When numeric_expression > 0; returns positive (+1)

When numeric_expression = 0; returns zero (0)

When numeric_expression < 0; returns negative (-1)

42. What is a TABLESAMPLE?

Ans: This is the SQL statement that allows extracting random samples from a table using FROM statements. When users don’t require the entire dataset of a table, this statement can be applied to extract only the necessary samples. Note that the extracted rows won't be in any order, and sampling can be performed based on the percentage of rows.

43. What is SQL injection?

Ans: It is a malicious attack that would be sent targeting an SQL server instance. It is usually inserted through strings and will be passed into the SQL server for execution. TO OVERCOME THIS ATTACK, all SQL statements need to be verified for SQL injection vulnerabilities before their execution. Otherwise, the SQL server will execute the statements as usual, which will, in turn, cause harm to resources.

44. What is Database Mirroring, and mention its benefits?

Ans: Database mirroring allows keeping two copies of a single database in two different locations. The database is copied into different server instances of the SQL server database engine. It is applicable for the databases which adapt the full recovery model.

The benefits of database mirroring are as follows:

• It increases the availability of the database through high-safety mode with automatic failover
• It increases data protection irrespective of the mode –whether high-performance mode or operating mode
• It increases the availability of the production database during upgrades, which will reduce downtime significantly.

SQL Server Interview Questions and Answers for Experienced

45. What are the various SQL Database Functions?

Ans:

Aggregate functions	They work on a group of values and return a single value.
Analytic functions	They work with a set of groups and can return multiple rows for each group.
Ranking functions	They return the ranking value for each row.
Rowset functions	They return a value that can be used as the table reference.
Scalar functions	They work on a single value and return a single value as well.

46. What is the difference between Stored Procedures and Functions?

Ans: In stored procedures, codes are usually compiled, and these compiled codes are executed when the program calls them. But in functions, on the contrary, codes are compiled and executed every time they are called.

Also, there must be a return while executing functions, whereas it is optional while executing stored functions. Furthermore, functions can be called from stored procedures, but stored procedures cannot be called from functions.

47. Can you mention the different types of Queries in SQL Servers?

Ans:

• Select Query: This query creates the SQL SELECT statement. It retrieves data from tables or views.
• Insert results: This query creates the SQL INSERT INTO….SELECT statement. It allows copying rows from one table to another and copying rows within a table.
• Insert Values: This query creates the SQL INSERT INTO….VALUES statement. It creates new rows in tables and inserts values into them.
• Update Query: This query creates SQL UPDATE…SET statement. It allows updating values in multiple rows.
• Delete Query: This query creates the SQL delete statement. It helps to remove rows from a table.
• Make table query: This query creates the SQL SELECT…INTO statement. It creates a new table and rows in it.

48. What is the use of Database Engine in the SQL Server?

Ans: The database engine can store, process, and secure data. It creates database objects such as stored procedures, views, and triggers; and executes the objects. It processes queries, provides controlled access, and optimizes transactions in a database. A SQL server can support up to 50 database engine instances in a single computer.

49. How can you differentiate between UNION and UNION ALL?

Ans: UNION is the operator that combines two queries into a single result set using select statements. Note that it functions based on specified conditions.

Syntax: query1 UNION query2

UNION ALL is the operator that combines two queries into a single result set, extracting all the rows from the tables without any conditions.

Syntax: query1 UNION ALL query2

50. Brief the different types of JOINS?

Ans:

• INNER JOIN: This command helps to return records that are common to both the tables
• LEFT JOIN: This command helps to return values that are common to each other along with the complete records of the left table
• RIGHT JOIN: This command helps to return values that are common to each other along with the complete records of the right table
• FULL JOIN: This command helps return all the tables' records when there is a match between the two.
• CROSS JOIN: It is known as Cartesian Join. This command returns all combinations of each row from the tables. There is no specific condition for joining two tables.

51. What are the four types of physical JOIN operations?

Ans:

• Nested loop joins: They are applied when one join input is small, and the other join input is large because nested loop joins require minimum I/O and few comparisons.
• Merge joins: When two inputs are large and of the same size and sorted out based on the join column, then merge join will provide a fast join operation.
• Hash joins: They efficiently process large, non-indexed, and unsorted inputs. They are also helpful in getting intermediate results in complex queries.
• Adaptive joins: This join helps to decide when to switch to a Nested loop based on a threshold value

52. What is the difference between JOIN and UNION in SQL server?

Ans: JOIN is the operator that combines data from many tables based on specific conditions, which creates new columns. In contrast, UNION combines data from many tables using SELECT statements, creating new rows.

The number of columns retrieved from the tables needn't be the same in JOIN statements, whereas the number of columns retrieved from the tables must be the same in UNION statements.

53. What do you mean by Clustered Indexes?

Ans: The clustered index describes how data is stored in a table, and the table should have a key value. Know that there can be only one clustered index for a table. When there is a clustered index in a table, then it is known as the clustered table. When there is no clustered index in a table, then data is stored in tables unstructured.

54. How can you use the SCOPE_IDENTITY function in the SQL server?

Ans: This function returns the last identity value inserted into an identity column within the same scope. Here, the scope is nothing but a module, which will have a stored procedure, trigger, batch, and function. If two statements exist in the same stored procedure or batch or function, then it means that they are in the same scope.

The syntax for this function is provided as SCOPE_IDENTITY ( )

55. What is the use of WITH TIES?

Ans: WITH TIES allows adding one or more rows along with the rows limited by the TOP or similar statements. It works only when you use it alongside the ORDER BY expression.

The syntax is given as shown below:

[
     TOP (expression) [PERCENT]
     [WITH TIES]
]

56. How can Deadlocks in the SQL server be resolved?

Ans: Deadlock occurs in a SQL server when two processes lock a single resource simultaneously and wait for the other process to unlock the resource. Generally, the SQL engine notices this type of incident and ends one of the processes voluntarily, which helps to release the lock. Thus, the SQL engine allows one process to complete successfully while stopping another process simultaneously.

Related Article: What is PolyBase in SQL Server

57. How can you compare the Local and Global Temporary tables?

Ans: Local temporary tables are visible only to the table creators when connected with an SQL instance. The tables will be deleted once the user disconnects the SQL instance.

On the other hand, global temporary tables are visible to any user. These tables are deleted only when any user referencing these tables gets disconnected from the SQL instance.

58. How can you distinguish between SUBSTR and CHARINDEX?

Ans: Given the starting position and length details, SUBSTR helps extract a substring from the specified string.

The syntax for this function is given by:

SUBSTRING (expression, starting_position, length)

On the contrary, the CHARINDEX function helps identify a substring's position from the specified string.

The syntax for this function is given by:

CHARINDEX (substring, input_string)

59. When to execute COMMIT and ROLLBACK commands?

Ans:

COMMIT: statement is executed to save the changes made on the current transaction; after that, the transaction becomes permanent.

ROLLBACK: statement is executed to delete the changes made on the current transaction after the last COMMIT.

60. How can you distinguish between GETDATE and SYSDATETIME functions?

Ans: The GETDATE function returns the date and time of a location. In contrast, the SYSDATETIME function returns the date and time with the precision of 7 digits after the decimal point.

You can understand this from the following examples.

SELECT SYSDATETIME( ) AS CURRENT_DATE_TIME;
CURRENT_DATE_TIME
2015-1016 12:37:06 . 6151778

61. How to delete a table using SQL Server Management Studio?

Ans:

• Select the table that you need to delete in Object Explorer
• Right-click on the table, then choose delete from the shortcut menu
• Confirm deleting the table by clicking ‘Yes’ in the prompt box

62. What is SSMA in SQL server?

Ans: SSMA is known as Microsoft SQL Server Migration Assistant. It is an automation tool that helps migrate from Microsoft Access Database to SQL Server or Azure SQL Database or Azure SQL Database Managed Instance. It also supports DB2, MySQL, Oracle, and SAP ASE migration to SQL Server.

63. What do you mean by Data Quality Services (DQS)?

Ans: DQS is nothing but a knowledge-driven data quality platform that supports carrying out data quality tasks such as correction, enrichment, standardization, and de-duplication of data. In addition, DQS analyses the integrity of data using profiling that is integrated with data-quality tasks.DQS consists of two components: Data Quality Server and a Data Quality Client.

64. What are SQL Server Integration Services and their functions?

Ans: It is the platform for enterprise-level data integration and data transformation services. It includes copying and downloading files, loading data warehouses, managing SQL Server objects, and cleansing and mining data to solve complex business problems quickly. Integration services consist of a group of built-in tasks, graphical tools, and an SSIS catalog database.

65. Compare the difference between Clustered Index and the Non-clustered Index?

Ans:

Clustered Index	Non-clustered Index
It describes the order in which data is stored in tables physically.	It doesn’t sort tables physically inside a table but creates a logical order for stored data.
Each table will have only one clustered index.	There could be many non-clustered indexes for a table.
Less storage is required since they only sort tables.	A non-clustered index is stored in one location, and data is stored in another. So, large storage is required.
Supports faster operations than the non-clustered index.	Decreases the speed of performance due to extra lookup setup.

66. Differentiate: Derived attribute, Derived persistent attribute, and Computed attribute?

Ans:

• Derived Attribute: It is the attribute that obtains values from other columns.
• Derived Persistent Attribute: It is the stored derived attribute.
• Computed Attributes: It is the attribute that is computed from internal system values

67. What are the different levels of normalization, and explain them shortly?

Ans:

• First Normal Form (1 NF): It avoids data duplication in a table. It creates a specific table for the related data and uses the primary key to identify the data.
• Second Normal Form (2 NF): It creates separate tables for the group of data that belongs to multiple records. The tables are linked with foreign keys.
• Third Normal Form (3 NF): It eliminates the fields that are not related to keys
• Boyce Codd normal form /Fourth Normal form (BCNF or 4NF): It should be in the form of the 3 NF. Also, there shouldn’t be any multi-valued dependencies.

68. What is the difference between DELETE and TRUNCATE commands?

Ans: The DELETE command removes a row (s) from a table based on given conditions, whereas the TRUNCATE command removes the entire rows from a table. Also, changes have to be manually COMMITTED after executing the DELETE command, whereas changes are COMMITTED automatically after the execution of the TRUNCATE command.

69. Compare Local Variables and Global Variables?

Ans:

Global Variables	Local Variables
Global variables can be declared outside of all functions, and any function of the program can access them.	Local variables are declared inside the function, and they can be called only by that function.
Global variables exist until the execution of the entire program.	Local variable exists until the execution of that specific function.
Global variables are stored in fixed memory and not cleaned up automatically.	Local variables are stored in stack memory and cleaned up automatically.

70. What is the difference between OLAP and OLTP?

Ans: OLAP is known as Online Analytical Processing. It consists of the tools used for data analysis for making business decisions. It works on historical data and provides insights into multiple database systems. For example, NETFLIX and Spotify generate insights from past data.

On the other side, OLTP is the Online Transaction Processing and works on the operational data. It manages ACID properties during transactions. It performs faster than OLAP, so it is used to perform day-to-day activities such as online ticket booking, messaging, etc.

71. What is the FLOOR function in the SQL server?

Ans: This function allows returning the largest integer value, which is less than or equal to the specified value.

The syntax for this function is provided as:

FLOOR ( numeric_expression )

For example, refer to the following statement about using the FLOOR function.

SELECT FLOOR (22.35) AS FloorValue

This statement will return a value less than 22.35 or the same.

72. What is the use of SQL server locks, and what resources can be locked by server locks?

Ans: If an exclusive lock locks a row in a table during a specific transaction, that row cannot be accessed by other transactions. So, others can view or modify that row only when the lock is released. Also, locks reduce concurrency in transactions, so locks must be minimized to improve transaction performance.

Note that the resources such as AllocUnit, application, database, extent, file, key, metadata, etc., can be locked using lock functions.

73. What is the use of the SET NOCOUNT function?

Ans: This function helps to stop the message that indicates how many rows are being affected while executing a T-SQL statement or stored procedure.

The syntax for the function is given as:

SET NOCOUNT { ON | OFF } 

If you set this function ON, then no count is returned in the result set; on the other hand, if you set this function OFF, then count is returned.

74. What do you mean by Magic Tables in SQL server?

Ans: Magic tables are virtual tables that exist in two types – INSERTED AND DELETED. They hold the information of the newly INSERTED and DELETED rows. The INSERTED table will have the newly inserted rows on top of it. The DELETED tables will have the recently deleted rows on top of it on similar tracks. Magic tables are stored in tempDB.

75. How can you prevent SQL injection vulnerabilities?

Ans: We can prevent SQL injection vulnerabilities in the following ways:

• Using Type-Safe SQL parameters
• Using parameterized input with stored procedures
• Filtering inputs
• Reviewing codes
• Wrapping parameters

76. What do you mean by the recovery model in SQL Server and its types?

Ans: The recovery model is the property that controls the transaction log maintenance in a database. It tracks the logging of transactions and decides about the necessary backup for the transactions and the availability of the restoration options. There are three types of recovery models Simple, Full, and Bulk-logged.

• Simple Recovery Model: There is no log backup, and it eliminates the need to manage the transaction log space.
• Full Recovery Model: This model requires log backups. It doesn’t allow any work to be lost for any reason.
• Bulk-logged Model: This model requires log backups. It allows high-performance bulk-copy operations.

77. What are the different types of backups used in SQL servers?

Ans:

• Copy-only backup: It is a special-use backup that is independent of the regular backups
• Data backup: It is a backup of data either entirely or partially
• Database backup: It is a backup of a complete database
• Differential backup: It is a record of only the changes made in a database after a database backup event
• Full backup: It is a backup of full data with the necessary transaction log for restoration
• Log backup: It is a backup of all transaction logs
• File backup: It is a backup of files in a database
• Partial backup: It is a backup of files from a specific filegroup such as primary, every read/write, and optionally specified read-only files.

78. How can you use HAVING and WHERE clauses in a single query?

Ans: Generally, the WHERE Clause acts on individual rows, whereas the HAVING clause acts on groups. A SQL query can be constructed using the HAVING clause and WHERE clause. In that situation, WHERE Clause acts first based on the given conditions and groups rows in a table. Then, the HAVING clause acts on the groups and creates a result set only including the groups based on the given conditions.

79. What are User-Defined Functions, and why do we have to use them?

Ans: User-defined functions (UDFs) are routines that accept parameters, perform complex functions, and return a value or the result set. There are three types of UDFs – user-defined scalar functions, table-valued functions, and system functions.

We can use UDFs for the following reasons:

• They can be created, stored, and called at any number of times.
• They allow faster execution because UDFs don’t need to be reparsed or reoptimized.
• They minimize network traffic as it reduces the number of rows sent to the client.

Department:

Department_No	Department_Name
101	ECE
102	EEE
103	CSE
104	IT
105	MECH

Employee Details:

Employee_No	Emp_Name	Address	Age	Department_No	Salary
1	Kamal	Hyderabad	23	101	25000
2	Vijay	Bangalore	22	102	24000
3	Amar	Chennai	21	103	23000
4	Umar	Chennai	24	104	22000
5	Sunil	Hyderabad	25	103	26000

80. Write a query to display employee details working in the EEE department?

Ans: SELECT employee.employee_name, employee.address, employee.salary, employee.age,

FROM Department D

INNER JOIN Employees E

ON department.D_no=employee.D_no WHERE department.D_name=’EEE’

81. Write a query to display employee details with their department names?

Ans: SELECT employee.employee_no, employee.employee_name, employee.address, employee.salary, employee.age, department.department_name

FROM department D

INNER JOIN employee E

ON department.D_no=employee.D_no

82. Write a query to display employee details along with department_name and their age between 21 to 25?

Ans: SELECT emp.e_no, emp.e_name, emp.address, emp.salary, emp.age, dept.d_name

FROM Department D

INNER JOIN employee E

ON dept.D_no=emp.D_no

WHERE E.age between 21 and 25

83. Write a query to display the employee details whose salary is above 23000 and age is above 22 and working in the CSE department?

Ans: SELECT emp.e_no, emp.e_name, emp.address, emp.salary, emp.age, dept.d_name

FROM Department D

INNER JOIN employee E

ON dept.D_no=emp.D_no

WHERE E.age>22 and E.salary>23000 and dept.D_name=’CSE’ 

SQL Server Interview Questions and Answers for Advanced

84. How important is Database Design in SQL servers?

Ans: The performance of SQL servers depends on the factors such as proper data population in tables, a good relationship between tables, and elimination of data redundancy. Therefore, when building a new system or modifying an existing system, the design must be correctly designed to yield consistent performance.

85. Can you mention the advantages of Stored Procedures over Dynamic SQL?

Ans:

• A stored procedure is cached in server memory, so it is faster than dynamic SQL
• Stored procedures keep ‘business logic’ separate from ‘database logic’. So, if any error occurs in the business logic, you have to change the application codes only. Similarly, if there is an issue with the database logic, you have to change the stored procedures only.
• The stored procedure creates low network traffic, whereas dynamic SQL creates high network traffic.
• Stored procedures with static SQL can detect errors before they run, whereas stored procedures with dynamic SQL cannot detect errors before they run.

86. How can you hold the Stored Procedure scripts in the SQL server?

Ans: We can store the stored procedure scripts in a server table known as Sys.SQL_Modules. Also, Sys. procedures table is used to store the name of the stored procedures.

87. What are the advantages of using stored procedures in an SQL server?

Ans:

• Stored procedures provide faster performance since they are compiled and stored in executable form.
• A stored procedure is nothing but a piece of code that can be used many times, increasing productivity.
• It can be stored in a database server instead of on a client machine. As a result, it increases the speed of query execution.
• It can be used in many applications after the successful compilation

88. How can you hide an instance of the SQL Server database engine?

Ans: You can hide the SQL server database engine using the SQL Server configuration manager. The following two steps can accomplish it:

1. Expand SQL server network configuration, right-click protocols and then select properties.
2. Open the Flags tab, select ‘YES’ in the HideInstance box, and click OK to close the dialogue box.

89. What are the different third-party tools used in SQL servers?

Ans: The following are the various third-party tools used in SQL servers:

•  Red Gate SQL Compare
• SSW SQL Deploy
• SSW SQL Server Auditor
•  Instant SQL formatter
• Apex SQL Doc
• LECCO SQL Expert
• Speed Ferret
• XPSMTP

90. What do you mean by SSRS in SQL Server?

Ans: SSRS is known as SQL Server Reporting Services, which provides a group of on-premises tools and services. SSRS allows creating, deploying, and managing mobile and paginated reports.

• Paginated Reports: They produce up-to-date reports with the support of updated tools and new features.
• Mobile Reports: This type has a format that adapts to different devices.
• Web Portal: Paginated reports, excel workbooks, KPIs, and mobile reports can be organized and viewed through the web portal. You can view the web portal using any modern browser.

91. How can you alter a table schema in an SQL server?

Ans: The following syntax is used to transfer ‘securable’ between schemas.

ALTER SCHEMA schema_name
      TRANSFER [ <entity_type> : : ] securable_name
[ ; ]

<entity_type> : : =
        {
        object | Type | XML Schema Collection
        }

Schema_name: It is the name of the schema where the securable is to be moved.

Entity_type: It represents the class of the entity.

Securable _name: It is the one-part or two-part name of the securable.

92. How does Intelligent Query Processing improve the performance of SQL Server workloads?

Ans: The intelligent query processing (IQP) family has many features that improve the performance of workloads with minimum implementation effort. Here, the various IQP features are adaptive joins, batch mode on rowstore, approximate QP, etc. You can automatically apply IQP to all server workloads by enabling compatibility levels for databases.

93. What do you mean by in-memory OLTP database?

Ans: In-memory OLTP (Online Transaction Processing) is a database technology that supports optimizing the performance of various processes such as transaction processing, data ingestion, transient data scenarios, and data load. It provides low latency, higher throughput, and faster response time.

94. What is the use of isolation level in SQL transactions?

Ans: Isolation is the property of SQL transactions, which is used to isolate a SQL transaction from other transactions – as a result, it ensures data integrity in the transactions. The isolation feature helps lock a row during a transaction so that other transactions cannot access that row. There are five isolation levels in SQL transactions that support maintaining data concurrency in databases.

95. What are the benefits of Partially Contained Databases?

Ans:

• Partially contained databases can retain crucial information while making data transactions. So data can be accessed from databases even if there is missing data during transactions.
• It will be useful in ‘failover’ times when users use the 'Always On' availability groups.
• It helps developers detect ‘instance-level impacts’ and ‘instance-level concerns' on databases.

96. Compare Triggers with Event Notifications?

Ans:

Triggers	Event Notifications
Triggers respond to both DML and DLL events	Event notifications respond to DDL events and a subset of SQL trace events
They run T-SQL or CLR codes	They don’t run any codes
They are processed synchronously within the scope of transactions	They are processed asynchronously, not within the scope of transactions
They must be processed on local servers	They can be processed on remote servers

 97. What is FILESTREAM, and when can you use it?

Ans: FILESTREAM allows SQL Server-based applications to store unstructured data such as images and documents. It helps maintain transactional consistency between unstructured data and the corresponding structured data. We can use FILESTREAM only when the following conditions are true:

• Objects have more than 1 MB file size
• Faster read access
• Applications have a middle tier for application logic

98. What is the Columnstore Index, and why should you use it?

Ans: A Columnstore index is a method of storing, retrieving, and managing data using a columnar data format. Columnstore helps achieve query performance up to 10 times greater than the traditional row-oriented storage.

Columnstore index is used for the following reasons as follows:

• They provide high compression rates
•  I/O Bottlenecks are minimized or removed
• Memory footprint is reduced
• Batch execution improves query performance

99. How to improve query performance in SQL server?

Ans: We can improve the query performance in the following ways:

• Defining the transaction requirements precisely
• Choosing the right query execution plan
• Reducing table sizes and simplifying joins
• Using ‘SELECT fields FROM’ instead of ‘SELECT*FROM’
• Using EXIST() instead of COUNT()
• Creating SQL query indexes
• Avoiding running queries in loops

100. How can we improve the performance of poor-performing SQL queries?

Ans: We can improve the performance of poor-performing SQL queries in the following ways:

• Using indexes properly
• Creating primary and foreign keys
• Not making table scans
• Avoiding using cursors
• Using partitioned views

101. What do you mean by Graph Database?

Ans: We can create a graph using node or edge tables for a database. Here, node tables are the collection of similar types of nodes. Similarly, edge tables are a collection of similar types of edges. Note that node tables can be created based on a logical graph and under any schema.

102. How to use Service Broker in SQL server?

Ans: SQL Server Service Broker is a message delivery framework that supports effective messaging and queuing in the SQL server database engine. Developers can use Service Broker to distribute workloads across various databases without programming efforts. Service Broker reduces workloads by managing communication paths and improves performance at the end.

103. What is the use of views in SQL server, and mention its types?

Ans: The view is the virtual database table created by selecting a few rows and columns from other tables. Rows and columns are selected through SQL query, which creates a view as a result. And it helps to organize data by simplifying complex queries, restricting access to data, and summarising data from many tables.

There are two types of views. They are given as follows:

• System-defined views
• User-defined views

104. What is a Logon Trigger, and when do they get triggered?

Ans: When there is a logon event, Logon triggers initiate stored procedures. It occurs when a user session is created with an instance of the SQL Server. Logon triggers fire only when the authentication stage of logging is over. If the authentication phase fails, logon triggers won't fire stored procedures.

105. Compare Extended Stored Procedures and CLR Integration?

Ans:

Extended Stored Procedure	CLR Integration
They support the functionalities that cannot work with T-SQL stored procedures.	CLR provides managed code with services such as cross-language integration, object lifetime management, code access security, and debugging and profiling support.
Developers need to write server-side logic that is complex in a way	Provides an alternative method to writing codes simply. Logic is expressed in the form of table-valued functions
It compromises the integrity of the SQL server process	It doesn’t compromise the integrity of the SQL server process
It supports all the versions of the SQL server	It doesn’t support older versions of the SQL server
Codes can be written in C/C++ programming languages	Codes can be written in .NET programming languages

106. What is SQL Latch Contention, and how can it be avoided?      

Ans: Latch contention occurs when many threads try to acquire incompatible latches at the same time. It will happen when latches are in the same in-memory structure. SQL engine decides when to use latches automatically and hence ensures memory consistency. When there is a latch contention, the SQL server tends the incompatible latch requests to wait in the queue until outstanding latch requests are completed.

107. What is Spinlock Contention, and how to resolve it?

Ans: Spinlock can protect access to data structures. Familiar data structures are typically accessed by many threads concurrently, which in turn causes spinlock contention issues. Also, spinlock contention creates CPU overhead.

The spinlock contention can be diagnosed by the tools such as performance meter, SQL server extended events, and memory dumps. To resolve the spinlock contention issues, the root cause must be identified first. A shorter code path will resolve contention issues significantly. In addition, the best practices such as Fully Qualified Names, Paremterised Queries, and LOCK_HASH contention.

108. What are the two execution modes in the Database Engine in the SQL server, and what is the difference between the two modes?

Ans: The database engine in the SQL server can execute T-SQL statements in the following two modes:

• Row-mode execution
• Batch-mode execution

Row-mode Execution: This mode of execution is applicable when data is stored in row format. In this mode, when a query is executed, rows are read one by one with respect to columns specified in the table schema. Then, the SQL server forms the result set from the data of columns using the statements such as SELECT, JOIN, etc.

Batch-mode Execution: Multiple modes are executed together as a batch in this mode. It operates on compressed data. It offers better parallelism, faster performance, and increases memory throughput.

109. What are Data Cleansing and Data Matching in SQL server?

Ans:

• Data Cleansing: This Data Quality Service (DQS) process identifies the incorrect or invalid data in the SQL database and then corrects it; cleansing is carried out in two steps such as computer-assisted and interactive. The computer-assisted method uses the knowledge of the DQS knowledge base to process the data and suggests corrections automatically. In the interactive method, the data steward approves, rejects, and modifies the changes required to correct the incorrect or incomplete data.
• Data Matching: This DQS process supports reducing data duplication and increases data accuracy. Data matching is carried out in two ways, such as creating a matching policy in the knowledge base and performing a deduplication process. The matching rules help to identify how the records are matching with each other and the degree of similarity.

110. How can you ensure DQS security in the SQL server?

Ans: DQS security infrastructure is designed based on SQL server security infrastructure. Here, the Data administrator provides access to DQS resources based on the defined DQS roles. There are four DQS roles to manage the security of DQS services. They are known as the database administrator, DQS administrator, DQS KB Editor, and DQS KB Operator. A set of permissions are assigned with each DQS role so that they can access the resources based on the given permissions.

111. What is the use of Windows PowerShell Snap-ins?

Ans: Windows PowerShell snap-in is a .NET framework assembly with Windows PowerShell providers and cmdlets. The PowerShell has a group of snap-ins as default; however, snap-ins can be added with them to increase the power of Windows PowerShell. Once the snap-ins are added to the PowerShell, they can be used by the current session itself. If the added snap-ins are required for future sessions, they must be saved in the Windows PowerShell profile.

112. Mention a few subquery restrictions in the SQL server?

Ans:

• The ntext, image, and text data types cannot be used in the select list of subqueries.
• If there is a column name in the WHERE clause of an outer query, then it should be join-compatible with the column in the subquery select list.
• The DISTINCT keyword cannot be used with subqueries that include GROUP BY
• A view created by using a subquery cannot be updated
• The COMPUTE and INTO clauses cannot be specified

113. What is Polybase, and why do you have to use it?

Ans: Polybase is the data virtualisation feature of the SQL server. This feature supports connecting with external sources without installing client connection software. So, we can query data from SQL Server, Oracle, Teradata, MongoDB, Hadoop clusters, and Cosmos DB using T-SQL. Polybase allows data to stay in its original location and format. So, it reduces the need for ETL in data movement.

114. What is an SSIS designer in SQL Server, and why do we use it?

Ans: It is a graphical tool used to manage integration service packages in SQL servers. It allows for creating and maintaining the integration of service packages.

We can use SSIS designer to accomplish the following tasks:

• Constructing the dataflow and control flow in a package
• Adding event handlers to the packages
• Viewing package content and viewing expression progress

115. Compare Package-level Connection Managers and Project-level Connection Managers?

Ans: A connection manager is available for all the packages in the project when it is created at the project level. Similarly, a connection manager will be available only to the specific package when created at the package level.

116. Differentiate EXCEPT and INTERSECT commands?

Ans: These commands are used to return distinct rows by comparing the results of two separate queries.

EXCEPT: operator allows returning distinct rows from the left input query only.

INTERCEPT: operator allows returning distinct rows from both left and right input queries.

The syntax for these commands is given as follows:

{ <query_specification> | ( <query_expression> ) }
{ EXCEPT | INTERSECT }
{ <query_specification> | ( <query_expression> ) }

117. Explain the Pattern Matching in the SQL server?

Ans: SQL server uses LIKE operator to identify whether a character string matches a specified pattern. A pattern may consist of regular characters as well as wildcard characters. So, pattern matching is performed using wildcard characters and string comparison characters. However, pattern matching using wildcard characters is more flexible than string comparison operators.

The syntax for the LIKE operator for pattern matching is given as below:

match_expression [ NOT ] LIKE pattern [ ESCAPE escape_character ]

118. What are the various types of collation sensitivity, and brief them?

Ans:

• Case Sensitivity: It distinguishes uppercase and lowercase letters
• Accent Sensitivity: It distinguishes between accented and unaccented characters
• Kana Sensitivity: It distinguishes the two types of Japanese characters hiragana and katakana
• Width Sensitivity: It distinguishes the full-width and half-width characters
• Variation-selector Sensitivity: It distinguishes the variation selectors in Japanese collations.

119. What is the difference between ORDER BY expression and clustered index?

Ans: ORDER BY expression sorts the result set of a query as per the condition given in the statement. It can sort the result set either in ascending order or descending order. And, it can sort the result set based on attributes given in the statement such as country, company, etc. At the same time, clustered index sorts data physically only in a specific way in the table. So, every table will have one clustered index only.

120. How can you improve the performance of stored procedures in the SQL server?

Ans:

• Using SET NOCOUNT ON messages, information messages can be prevented from the result set. It will reduce network traffic and increase operational performance.
• When fully qualified procedure name, the SQL server finds the compiled plan quickly, which in turn increases the performance
• Specifying stored procedures as sp_procedurename must be avoided because the SQL server will search the master database first if it finds ‘sp’ in the procedure name. It would decrease the performance and yield error results at times.
• Transactions need to be shorter so that deadlocks and blocking can be prevented.

Conclusion

We have covered most of the frequently asked SQL Server interview questions in this blog. We hope this blog might have been interesting to read and understand the core concepts of SQL servers. You know it – Hardwork Never Fails. Lastly, ALL THE BEST for your interview.