Best SQL Server Interview Questions And Answers

Q #1) Which TCP/IP port does SQL Server run on?

Ans. By default SQL Server runs on port 1433.

 

Q #2) What is the difference between clustered and non-clustered index?

Ans. A clustered index is an index that rearranges the table in the order of the index itself. Its leaf nodes contain data pages. A table can have only one clustered index.

 

A non-clustered index is an index that does not re-arranges the table in the order of the index itself. Its leaf nodes contain index rows instead of data pages. A table can have many non-clustered indexes.

 

Q #3) List the different index configurations possible for a table?

Ans. A table can have one of the following index configurations:

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

 

Q #4) What is the recovery model? List the types of recovery models available in SQL Server?

Ans. The recovery model tells SQL Server what data should be kept in the transaction log file and for how long. A database can have only one recovery model.

It also tells SQL server which backup is possible in a particular recovery model selected. There are three types of recovery model:

• Full
• Simple
• Bulk-Logged

Q #5) What are the different backups available in SQL Server?

Ans. Different possible backups are:

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

Q #6) What is a Full Backup?

Ans. A full backup is the most common type of backup in SQL Server. This is the complete backup of the database. It also contains part of the transaction log so it can be recovered.

Q #7) What is OLTP?

Ans. OLTP means Online transaction processing which follows rules of data normalization to ensure data integrity. Using these rules complex information is broken down into a most simple structure.

Q #8) What is RDBMS?

Ans. RDBMS or Relational Data Base Management Systems are database management systems that maintain data in the form of tables. We can create relationships between the tables. An RDBMS can recombine the data items from different files, providing powerful tools for data usage.

Q #9) What are the properties of the Relational tables?

Ans. Relational tables have six properties:

• Values are atomic.
• Column values are of the same kind.
• Each row is unique.
• The sequence of columns is insignificant.
• The sequence of rows is insignificant.
• Each column must have a unique name.

Q #10) What's the difference between a primary key and a unique key?

Ans. The differences between the primary key and a unique key are:

• The primary key is a column whose values uniquely identify every row in a table. Primary key values can never be reused. They create a clustered index on the column and cannot be null.
• A Unique key is a column whose values also uniquely identify every row in a table but they create a non-clustered index by default and it allows one NULL only.

Q #11) When is the UPDATE_STATISTICS command used?

Ans. As the name implies UPDATE_STATISTICS command updated the statistics used by the index to make the search easier.

Q #12) What is the difference between a HAVING CLAUSE and a WHERE CLAUSE?

Ans. The differences between HAVING CLAUSE and WHERE CLAUSE is:

• Both specify a search condition but Having clause is used only with the SELECT statement and typically used with GROUP BY clause.
• If GROUP BY clause is not used then Having behaved like WHERE clause only.

Q #13) What is Mirroring?

Ans. Mirroring is a high availability solution. It is designed to maintain a hot standby server which is consistent with the primary server in terms of a transaction. Transaction Log records are sent directly from the principal server to a secondary server which keeps a secondary server up to date with the principal server.

Q #14) What are the advantages of the Mirroring?

Ans. Advantages of Mirroring are:

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

Q #15) What is Log Shipping?

Ans. Log shipping is nothing but the automation of backup and restores of a database from one server to another standalone standby server. This is one of the disaster recovery solutions. If one server fails for some reason we will have the same data available on the standby server.

Q #16) What are the advantages of Log shipping?

Ans. Advantages of Log Shipping:

• Easy to set up.
• The secondary database can be used as a read-only purpose.
• Multiple secondary standby servers are possible
• Low maintenance.

Q #17) Can we take the full database backup in Log shipping?

Ans. Yes, we can take the full database backup. It won’t affect the log shipping.

Q #18) What is an execution plan?

Ans. An execution plan is a graphical or textual way of showing how the SQL server breaks down a query to get the required result. It helps a user to determine why queries are taking more time to execute and based on the investigation user can update their queries for the maximum result.

In Query Analyzer is an option called “Show Execution Plan” (located on the Query drop-down menu). If this option is turned on it will display a query execution plan in a separate window when a query is run again.

Q #19) What is the Stored Procedure?

Ans. A stored procedure is a set of SQL queries that can take input and send back output. And when the procedure is modified, all clients automatically get the new version. Stored procedures reduce network traffic and improve performance. Stored procedures can be used to help ensure the integrity of the database.

Q #20) List the advantages of using Stored Procedures?

Ans. Advantages of using Stored procedures are:

• Stored procedure boosts application performance.
• Stored procedure execution plans can be reused as they cached in SQL Server's memory which reduces server overhead.
• Stored procedures can be reused.
• Stored procedures can encapsulate logic. You can change the stored procedure code without affecting clients.
• Stored procedures provide better security for your data.

Q #21) What is identity in SQL?

Ans. An identity column in the SQL automatically generates numeric values. We can be defined as a start and increment value of the identity column. Identity columns do not need to be indexed.

Q #22) What are the common performance issues in SQL Server?

Ans. Following are the common performance issues:

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

Q #23) List the various tools available for performance tuning?

Ans. There are various tools available for performance tuning:

• Dynamic Management Views
• SQL Server Profiler
• Server Side Traces
• Windows Performance monitor.
• Query Plans
• Tuning advisor

Q #24) What is a performance monitor?

Ans. Windows performance monitor is a tool to capture metrics for the entire server. We can use this tool for capturing events of the SQL server also.
Some useful counters are – Disks, Memory, Processors, Network, etc.

Q #25) What are 3 ways to get a count of the number of records in a table?

Ans. SELECT * FROM table_Name
SELECT COUNT(*) FROM table_Name
SELECT rows FROM indexes WHERE id = OBJECT_ID(tableName) AND indid< 2

Q #26) Can we rename a column in the output of the SQL query?

Ans. Yes by using the following syntax we can do this.

SELECT column_name AS new_name FROM table_name;

Q #27) What is the difference between a Local and a Global temporary table?

Ans. If defined inside a compound statement a local temporary table exists only for the duration of that statement but a global temporary table exists permanently in the database but its rows disappear when the connection is closed.

Q #28) What is the SQL Profiler?

Ans. SQL Profiler provides a graphical representation of events in an instance of SQL Server for monitoring and investment purpose. We can capture and save the data for further analysis. We can put filters as well to captures the specific data we want.

Q #29) What do you mean by authentication modes in SQL Server?

Ans. There are two authentication modes in SQL Server.

• Windows mode
• Mixed Mode – SQL and Windows.

Q #30) How can we check the SQL Server version?

Ans. By running the following command:

SELECT @@Version

Q #31) Is it possible to call a stored procedure within a stored procedure?

Ans. Yes, we can call a stored procedure within a stored procedure. It is called the recursion property of the SQL server and these types of stored procedures are called nested stored procedures.

Q #32) What is the SQL Server Agent?

Ans. SQL Server agent allows us to schedule the jobs and scripts. It helps in implementing the day to day DBA tasks by automatically executing them on a scheduled basis.

Q #33) What is the PRIMARY KEY?

Ans. The primary key is a column whose values uniquely identify every row in a table. Primary key values can never be reused.

Q #34) What is a UNIQUE KEY constraint?

Ans. A UNIQUE constraint enforces the uniqueness of the values in a set of columns, so no duplicate values are entered. The unique key constraints are used to enforce entity integrity as the primary key constraints.

Q #35) What is FOREIGN KEY

Ans. When a one table’s primary key field is added to related tables to create the common field which relates the two tables, it called a foreign key in other tables.

Foreign Key constraints enforce referential integrity.

Q #36) What is a CHECK Constraint?

Ans. A CHECK constraint is used to limit the values or type of data that can be stored in a column. They are used to enforce domain integrity.

Q #37) What are a Scheduled Jobs?

Ans. The scheduled job allows a user to run the scripts or SQL commands automatically on a scheduled basis. The user can determine the order in which commands need to execute and the best time to run the job to avoid the load on the system.

Q #38) What is a heap?

Ans. A heap is a table that does not contain any clustered index or non-clustered index.

Q #39) What is BCP?

Ans. BCP or Bulk Copy is a tool by which we can copy a large amount of data to tables and views. BCP does not copy the structures the same as source to destination. BULK INSERT command helps to import a data file into a database table or view in a user-specified format.

Q #40) What is Normalization?

Ans. The process of table design to minimize the data redundancy is called normalization. We need to divide a database into two or more tables and define relationships between them. Normalization usually involves dividing a database into two or more tables and defining relationships between the tables.

Q #41) List the different normalization forms?

Ans. Different normalization forms are:

1NF (Eliminate Repeating Groups): Make a separate table for each set of related attributes, and give each table a primary key. Each field contains at most one value from its attribute domain.

2NF (Eliminate Redundant Data): If an attribute depends on only part of a multi-valued key, remove it to a separate table.

3NF (Eliminate Columns Not Dependent On Key): If attributes do not contribute to a description of the key, remove them to a separate table. All attributes must be directly dependent on the primary key.

BCNF (Boyce-Codd Normal Form): If there are non-trivial dependencies between candidate key attributes, separate them into distinct tables.

4NF (Isolate Independent Multiple Relationships): No table may contain two or more 1:n or n:m relationships that are not directly related.

5NF (Isolate Semantically Related Multiple Relationships): There may be practical constraints on information that justifies separating logically related many-to-many relationships.

ONF (Optimal Normal Form): A model limited to only simple (elemental) facts, as expressed in Object Role Model notation.

DKNF (Domain-Key Normal Form): A model free from all modification is said to be in DKNF.

Q #42) What is De-normalization?

Ans. De-normalization is the process of adding redundant data to a database to enhance the performance of it. It is a technique to move from higher to lower normal forms of database modeling to speed up database access.

Q #43) What is a Trigger and types of a trigger?

Ans. The trigger allows us to execute a batch of SQL code when a tabled event occurs (Insert, update or delete command executed against a specific table). Triggers are stored in and managed by DBMS. It can also execute a stored procedure.

3 types of triggers that are available in the SQL Server are as follows:

• DML Triggers: DML or Data Manipulation Language triggers are invoked whenever any of the DML commands like INSERT, DELETE or UPDATE happens on the table or the view.
• DDL Triggers: DDL or Data Definition Language triggers are invoked whenever any0 changes occur in the definition of any of the database objects instead of actual data. These are very helpful to control the production and development of database environments.
• Logon Triggers: These are very special triggers that fire in case of the logon event of the SQL Server. This is fired before the setup of a user session in the SQL Server.

Q #44) What is the Subquery?

Ans. A Subquery is a subset of select statements whose return values are used in filtering conditions of the main query. It can occur in a SELECT clause, FROM clause and WHERE clause. It nested inside a SELECT, INSERT, UPDATE, or DELETE statement or inside another subquery.

Types of Sub-query:

• Single-row sub-query: where the subquery returns only one row
• Multiple-row sub-query: where the subquery returns multiple rows, and
• Multiple column sub-query: where the sub-query returns multiple columns

Q #45) What is a Linked Server?

Ans. Linked Servers is a concept by which we can connect another SQL server to a Group and query both the SQL Servers database using T-SQL Statements sp_addlinkedsrvloginisssed to add link server.

Q #46) What is Collation?

Ans. Collation refers to a set of rules that determine how data is sorted and compared. Character data is sorted using rules that define the correct character sequence, with options for specifying case-sensitivity, accent marks, kana character types, and character width.

Q #47) What is View?

Ans. A view is a virtual table that contains data from one or more tables. Views restrict data access of the table by selecting only required values and make complex queries easy.

Rows updated or deleted in the view are updated or deleted in the table the view was created with. It should also be noted that as data in the original table changes, so does data in the view, as views are the way to look at part of the original table. The results of using a view are not permanently stored in the database

Q #48) Where SQL server usernames and passwords are stored in a SQL server?

Ans. They get stored in System Catalog Views sys.server_principals and sys.sql_logins.

Q #49) What are the properties of a transaction?

Ans. Generally, these properties are referred to as ACID properties. They are:

• Atomicity
• Consistency
• Isolation
• Durability

Q #50) Define UNION, UNION ALL, MINUS, INTERSECT?

Ans. UNION – returns all distinct rows selected by either query.

UNION ALL – returns all rows selected by either query, including all duplicates.

MINUS – returns all distinct rows selected by the first query but not by the second.

INTERSECT – returns all distinct rows selected by both queries.

Q #51) What is SQL Server used for?

Ans. SQL Server is one of the very popular Relational Database Management Systems. This is a product from Microsoft to store and manage the information in the database.

Q #52) Which language is supported by SQL Server?

Ans. SQL Server is based upon the implementation of the SQL also known as Structured Query Language to work with the data inside the database.

Q #53) Which is the latest version of SQL Server and when it is released?

Ans. SQL Server 2017 is the latest version of SQL Server that is available in the market and Microsoft launched this on 2 October 2017 with the support of the Linux O/S.

Q #54) What are the various editions of SQL Server 2017 that are available in the market?

Ans. SQL Server 2017 is available in 4 editions. These are as follows:

• Enterprise: This supports leading the high performance for the Tier 1 database along with the capability of supporting business intelligence and workloads of advanced analytics.
• Standard: This supports mid-tier applications to achieve fast performance. This can be easily upgraded to an enterprise edition also without having any changes in the coding part.
• Express: This is available for free and supports the building of web and mobile applications up to 10 GB in the size.
• Developer: This supports building, testing and demonstrating applications in a non-production.

Q #55) What are functions in the SQL Server?

Ans. Functions are the sequence of the statements which accept inputs, process the inputs to perform some specific task and then provide the outputs. Functions should have some meaningful name but these should not start with a special character such as %,#,@, etc.

Q #56) What is a User-Defined function in the SQL Server and what is its advantage?

Ans. User-Defined Function is a function that can be written as per the needs of the user by implementing your logic. The biggest advantage of this function is that the user is not limited to pre-defined functions and can simplify the complex code of pre-defined function by writing a simple code as per the needs.

This returns Scalar value or a table.

Q #57) Explain the creation and execution of a user-defined function in the SQL Server?

Ans.  A User-Defined function can be created in the following way:

Create Function fun1(@num int)

returns table

as

return select * from employee where empid=@num

This function can be executed as follows:

select * from fun1(12)

So, in the above case, a function with the name of ‘fun1’ is created to fetch employee details of an employee having empid=12.

 Q #58) What are the Pre-Defined functions in the SQL Server?

Ans. These are Built-In functions of the SQL Server like String functions which are provided by SQL Server like ASCII, CHAR, LEFT, etc. string functions.

Q #59) Why are Views required in the SQL Server or any other database?

Ans.  Views are very beneficial because of the following reasons:

• Views are required to hide the complexity that is involved in the database schema and also to customize the data for a particular set of users.
• Views provide a mechanism to control access to particular rows and columns.
• These help in aggregating the data to improve the performance of the database.

Q #60) What is TCL in SQL Server?

Ans. TCL is Transaction Control Language Commands which are used to manage the transactions in the SQL Server.

Q #61) Which TCL Commands are available on the SQL Server?

Ans.  There are 3 TCL Commands in the SQL Server. These are as follows:

• Commit: This command is used to save the transaction permanently in the database.
• Rollback: This is used to roll back the changes that are done i.e. to restore the database in the last committed state.
• Save Tran: This is used for saving the transaction to provide the convenience that the transaction can be rolled back to the point wherever required.

Q #62) What are the 2 types of classifications of constraints in the SQL Server?

Ans. Constraints are classified into the following 2 types in the SQL Server:

• Column Types Constraints: These constraints are applied to the columns of a table in the SQL Server. The definition of these can be given at the time of the creation of a table in the database.
• Table Types Constraints: These constraints are applied on a table and these are defines after the creation of a table is completed. Alter command is used to apply the table type constraint.

Q #63) How is table type constraint applied to a table?

Ans. Table Type Constraint is applied in the following way:

Alter Table Name of the Constraint

Alter Table Constraint_1

Q #64) What are the different types of Columns Types Constraints in the SQL Server?

Ans. SQL Server provides 6 types of Constraints. These are as follows:

SQL Server provides 6 types of Constraints. These are as follows:

1. Not Null Constraint: This puts a constraint that the value of a column
   cannot be null.
2. Check Constraint: This puts a constraint by checking some particular
   condition before inserting data in the table.
3. Default Constraint: This constraint provides some default value that can
   be inserted in the column if no value is specified for that column.
4. Unique Constraint: This puts a constraint that each row of a particular
   column must have a unique value. More than one unique constraint can
   be applied to a single table.
5. Primary Key Constraint: This puts a constraint to have a primary key in the
   table to identify each row of a table uniquely. This cannot be null or
   duplicate data.
6. Foreign Key Constraint: This puts a constraint that the foreign key should
   be there. A Primary key in one table is the foreign key of another table.
   Foreign Key is used to create a relation between 2 or more tables.

Q #65) What command is used to delete a table from the database in the SQL Server and how?

Ans. Delete Command is used to delete any table from the database in the SQL Server. Following is the way to use this command:

Delete Name of the table

Example: If the name of a table is “employee” then delete command to delete this table can be written as Delete employee.

Q #66) Why is replication required on the SQL Server?

Ans. Replication is the mechanism that is used to synchronize the data among the multiple servers with the help of a replica set.

This is mainly used to increase the capacity of the reading and to provide an option to its users to select among various servers to perform the read/write operations.

Q #67) What command is used to create a database in the SQL Server and how?

Ans. CREATEDATABASE Command is used to create any database in the SQL Server. Following is the way to use this command:

CREATEDATABASE Name of the Database

Example: If the name of a database is “employee” then create command to create this database that can be written as CREATEDATABASE employee.

Q #68) What function does a database engine serve in the SQL Server?

Ans. Database Engine is a type of service in the SQL Server which starts as soon as the Operating System starts. This may run by default depending upon the settings in the O/S.

Q #69) What are the advantages of having an index on the SQL Server?

Ans. The index has the following advantages:

• Index supports the mechanism of having faster data retrieval from the database.
• This forms a data structure in a way that helps in minimizing data comparisons.
• This improves the performance of the retrieval of the data from the database.

 SQL Server database developer interview questions and answers

What are DMVs?

 

Dynamic management views (DMVs) and functions return server state information that can be used to monitor the health of a server instance, diagnose problems, and tune performance; that is, they let you see what is going on inside SQL Server. They were introduced in SQL Server 2005 as an alternative to system tables. One example is viewing operating system wait statistics via this query:

SELECT * FROM sys.dm_os_wait_stats;

Another example is examining current sessions, much like the sp_who2 command:

SELECT * FROM sys.dm_exec_sessions;

What are temp tables? What is the difference between global and local temp tables?

 

Temporary tables are temporary storage structures. You may use temporary tables as buckets to store data that you will manipulate before arriving at a final format. The hash (#) character is used to declare a temporary table as it is prepended to the table name. A single hash (#) specifies a local temporary table.

CREATE TABLE #tempLocal ( nameid int, fname varchar(50), lname varchar(50) )

Local temporary tables are available to the current connection for the user, so they disappear when the user disconnects.

Global temporary tables may be created with double hashes (##). These are available to all users via all connections, and they are deleted only when all connections are closed.

CREATE TABLE ##tempGlobal ( nameid int, fname varchar(50), lname varchar(50) )

Once created, these tables are used just like permanent tables; they should be deleted when you are finished with them. Within SQL Server, temporary tables are stored in the Temporary Tables folder of the tempdb database.

How are transactions used?

 

Transactions allow you to group SQL commands into a single unit. The transaction begins with a certain task and ends when all tasks within it are complete. The transaction completes successfully only if all commands within it complete successfully. The whole thing fails if one command fails. The BEGIN TRANSACTION, ROLLBACK TRANSACTION, and COMMIT TRANSACTION statements are used to work with transactions. A group of tasks starts with the begin statement. If any problems occur, the rollback command is executed to abort. If everything goes well, all commands are permanently executed via the commit statement.

What is the difference between a clustered and a nonclustered index?

 

A clustered index affects the way the rows of data in a table are stored on disk. When a clustered index is used, rows are stored in sequential order according to the index column value; for this reason, a table can contain only one clustered index, which is usually used on the primary index value.

A nonclustered index does not affect the way data is physically stored; it creates a new object for the index and stores the column(s) designated for indexing with a pointer back to the row containing the indexed values.

You can think of a clustered index as a dictionary in alphabetical order, and a nonclustered index as a book's index.

What are DBCC commands?

 

Basically, the Database Consistency Checker (DBCC) provides a set of commands (many of which are undocumented) to maintain databases -- maintenance, validation, and status checks. The syntax is DBCC followed by the command name. Here are three examples:

DBCC CHECKALLOC -- Check disk allocation consistency.

DBCC OPENTRAN -- Display information about recent transactions.

DBCC HELP -- Display Help for DBCC commands.

What is the difference between truncate and delete?

 

Truncate is a quick way to empty a table. It removes everything without logging each row. Truncate will fail if there are foreign key relationships on the table. Conversely, the delete command removes rows from a table, while logging each deletion and triggering any delete triggers that may be present.

What does the NOLOCK query hint do?

 

Table hints allow you to override the default behavior of the query optimizer for statements. They are specified in the FROM clause of the statement. While overriding the query optimizer is not always suggested, it can be useful when many users or processes are touching data. The NOLOCK query hint is a good example because it allows you to read data regardless of who else is working with the data; that is, it allows a dirty read of data -- you read data no matter if other users are manipulating it. A hint like NOLOCK increases concurrency with large data stores.

SELECT * FROM table_name (NOLOCK)

Microsoft advises against using NOLOCK, as it is being replaced by the READUNCOMMITTED query hint. There are lots more query hints with plenty of information online.

 

What is a CTE?

 

A common table expression (CTE) is a temporary named result set that can be used within other statements like SELECT, INSERT, UPDATE, and DELETE. It is not stored as an object and its lifetime is limited to the query. It is defined using the WITH statement as the following example shows:

WITH ExampleCTE (id, fname, lname)AS

(

SELECT id, firstname, lastname FROM table

)

SELECT * FROM ExampleCTE 

A CTE can be used in place of a view in some instances.

What is a view? What is the WITH CHECK OPTION clause for a view?

 

A view is a virtual table that consists of fields from one or more real tables. Views are often used to join multiple tables or to control access to the underlying tables.

The WITH CHECK OPTION for a view prevents data modifications (to the data) that do not confirm to the WHERE clause of the view definition. This allows data to be updated via the view, but only if it belongs in the view.

What is a query execution plan?

SQL Server has an optimizer that usually does a great job of optimizing code for the most effective execution. A query execution plan is the breakdown of how the optimizer will run (or ran) a query. There are several ways to view a query execution plan. This includes using the Show Execution Plan option within Query Analyzer; Display Estimated Execution Plan on the query dropdown menu; or use the SET SHOWPLAN_TEXT ON command before running a query and capturing the execution plan event in a SQL Server Profiler trace.

What does the SQL Server Agent Windows service do?

SQL Server Agent is a Windows service that handles scheduled tasks within the SQL Server environment (aka jobs). The jobs are stored/defined within SQL Server, and they contain one or more steps that define what happens when the job runs. These jobs may run on demand, as well as via a trigger or predefined schedule. This service is very important when determining why a certain job did not run as planned -- often it is as simple as the SQL Server Agent service not running.

What is the default port number for SQL Server?

If enabled, the default instance of Microsoft SQL Server listens on TCP port 1433. Named instances are configured for dynamic ports, so an available port is chosen when SQL Server starts. When connecting to a named instance through a firewall, configure the Database Engine to listen on a specific port, so that the appropriate port can be opened in the firewall.