Below is top 100 SQL Server interview question and answers with examples for freshers and intermediate developers.
What is SQL?
SQL stands for Structured Query Language.
SQL is used to communicate with a database.
SQL is used to query against database and perform operations like insert, update and delete etc.
It is an ANSI standard.
What is SQL Server?
SQL Server is relational database management system (RDBMS) developed by Microsoft.
SQL Server supports ANSI SQL, the standard SQL language.
There are different RDBMS systems like Oracle Database, MySQL, and IBM DB2.
What is RDBMS?
RDBMS stands for Relational Database Management Systems.
All modern database management systems like SQL, MS SQL Server, IBM DB2, ORACLE, My-SQL and Microsoft Access are based on RDBMS.
It is called Relational Data Base Management System (RDBMS) because it is based on relational model introduced by E.F. Codd.
What are the DML, DDL, DCL and TCL statements?
DML: DML stands for Data Manipulation Language. DML is used to retrieve, store, modify, delete, insert and update data in database.
Ex. SELECT, UPDATE, INSERT, DELETE statements.
DDL: DDL stands for Data Definition Language. DDL is used to create and modify the structure of database objects.
Ex. CREATE, ALTER, DROP, TRUNCATE, COMMENT, RENAME statements.
DCL: DCL stands for Data Control Language. DCL is used to create roles, grant and revoke permissions, establish referential integrity etc.
Ex. GRANT, REVOKE statements.
TCL: TCL stands for Transactional Control Language. TCL is used to manage transactions within a database.
Ex. COMMIT, ROLLBACK, SAVEPOINT,SET TRANSACTION statements.
What is Temporary Table in SQL Server?
As the name suggests, temporary table is like normal table but stores a data for certain period of time. It is created in tempdb database. Temporary tables are automatically destroyed at the termination of the procedure or session is closed.
What are the 2 types of Temporary Tables in SQL Server?
- Local Temporary Tables
- Global Temporary Tables
What is the difference between Local and Global Temporary Tables?
Local Temporary Tables:
Prefixed with a single pound sign (#).
Local temporary tables are visible to that session of SQL Server which has created it.
Local temporary tables are automatically dropped, when the session that created the temporary tables is closed.
Global Temporary Tables:
Prefixed with two pound signs (##).
Global temporary tables are visible to all the SQL server sessions.
Global temporary tables are also automatically dropped, when the session that created the temporary tables is closed.
Can you create foreign key constraints on temporary tables?
No
Do you have to manually delete temporary tables?
No, temporary tables are automatically dropped, when the session that created the temporary tables is closed. But if you maintain a persistent connection or if connection pooling is enabled, then it is better to explicitly drop the temporary tables you have created.
However, it is generally considered a good coding practice to explicitly drop every temporary table you create.
In which database, the temporary tables get created?
TEMPDB database.
How to check whether temporary table exists or not?
if object_id('tempdb..#Employee') is not null begin print '#Employee exists' end else begin print '#Employee does not exists' end
What is difference between Stored Procedure and Function in SQL Server?
| Stored Procedure | Function |
|---|---|
| Stored procedure may or not return values. | Function must return a value. |
| You can use DML statement like Insert, Update, Delete etc. | You can only use Select statement. |
| Stored procedure can have input as well as output parameters. | Function can only have input parameter, but not output parameter. |
| try-catch block is supported. | try-catch block is not supported. |
| You can use Temporary table as well as Table variable. | You can only use Table variable, but not Temporary table. |
| Stored Procedure can not be called from select statement. | Function can be called from select statement. |
| Procedure can not be used with Join clause. | Function can be used with Join clause. |
| Transactions can be used inside stored procedure. | Transactions can not be used inside function. |
| Stored Procedure can execute Dynamic SQL. | Function can not Execute Dynamic SQL. |
| Stored Procedure can have upto 21000 input parameters. | Function can have upto 1023 input parameters |
What is a View in SQL Server?
You can think of a view either as a compiled sql query or a virtual table. As a view represents a virtual table, it does not physically store any data. When you query a view, you actually retrieve the data from the underlying base tables.
What are the advantages of using views? Or When do you usually use views?
1) Views can be used to implement row level and column level security.
2) Simplify the database schema to the users. You can create a view based on multiple tables which join columns from all these multiple tables so that they look like a single table.
3) Views can be used to present aggregated and summarized data.
Can you update views?
Yes, views can be updated. However, updating a view that is based on multiple tables, may not update the underlying tables correctly. To correctly update a view that is based on multiple tables you can make use INSTEAD OF triggers in SQL Server.What are indexed views? Or What are materialized views?
A view is a virtual table, it does not contain any physical data. A view is nothing more than compiled SQL query. Every time, we issue a select query against a view, we actually get the data from the underlying base tables and not from the view, as the view itself does not contain any data.
When you create an index on a view, the data gets physically stored in the view. So, when we issue a select query against an indexed view, the data is retrieved from the index without having to go to the underlying table, which will make the select statement to work slightly faster. However, the disadvantage is, INSERT, UPDATE and DELETE operations will become a little slow, because every time you insert or delete a row from the underlying table, the view index needs to be updated. Inshort, DML operations will have negative impact on performance.
Oracle refers to indexed views as materialized views.
Only the views created with schema binding, can have an Index. Simply adding WITH SCHEMABINDING to the end of the CREATE VIEW statement will accomplish this. However, the effect is that any changes to the underlying tables which will impact the view are not allowed. Since the indexed view is stored physically, any schema changes would impact the schema of the stored results set. Therefore, SQL Server requires that schema binding be used to prevent the view's schema (and therefore the underlying tables) from changing.
The first index for a view must be a UNIQUE CLUSTERED INDEX, after which, it's possible to create non-clustered indexes against the view.
Indexed Views are heavily used in data warehouses and reporting databases that are not highly transactional.
What are the limitations of a View?
- You cannot pass parameters to a view.
- Rules and Defaults cannot be associated with views.
- The ORDER BY clause is invalid in views unless TOP or FOR XML is also specified.
- Views cannot be based on temporary tables.
How can you perfrom DML operations on View?
We can perform DML operations like insert, update and delete. But for a successful implementation of a DML query we should use some conditions as shown below.
- View should not contain multiple tables.
- View should not contain set function.
- View should not use the Distinct keyword.
- View should not contain Group By, having clauses.
- View should not contain Sub query.
- View should not use Set Operators.
- All NOT NULL columns from the base table must be included in the view in order for the INSERT query to function.
What are the different types of Views?
There are two types of Views in SQL Server ie. User defined views and System defined views.
User-Defined Views: User defined views can be created as shown below.
------To create View-------- create view View_Name As select col1,col2,... from table_name where conditions ------To alter View-------- alter view View_Name As select col1,col2,... from table_name where conditions ------To drop View-------- drop view View_Name ------To rename View-------- EXEC sp_rename 'dbo.old_view_name', 'new_view_name'
System-Defined Views: SQL Server also contains various predefined databases like Tempdb, Master, temp. Each database has their own properties and responsibility. Master data is a template database for all other user-defined databases. A Master database contains many Predefine_View that work as templates for other databases and tables. Master databases contain nearly 230 predefined views.
Can we call view inside stored procedure?
Yes
Can we call view inside inside another views?
Yes
What is an Index in SQL Server?
Indexes are used in relational databases to quickly retrieve the data. They are similar to indexes at the end of the books whose purpose is to find a topic quickly.
- Data is internally stored in a SQL Server database in "pages" where the size of each page is 8KB.
- A continuous 8 pages is called an "Extent".
- When we create the table then one extent will be allocated for two tables and when that extent is computed it is filled with the data then another extent will be allocated and this extent may or may not be continuous to the first extent.
What are the different types of Index in SQL Server?
Below are the different types of Indexes available in SQL Server.
- Clustered Index
- Nonclustered Index
- Unique
- Full-text
- Spatial
- Filtered
- XML
- Columnstore
- Index with included columns
- Index on computed columns
What is a table scan? Or What is the impact of table scan on performance?
When a SQL Server has no index to use for searching, the result is similar to the reader who looks at every page in a book to find a word. The SQL engine needs to visit every row in a table. In database terminology we call this behavior a table scan, or just scan. A full table scan of a very large table can adversely affect the performance. Creating proper indexes will allow the database to quickly narrow in on the rows to satisfy the query, and avoid scanning every row in the table.
What is Clustered Index?
A clustered index determines the physical order of data in a table. A clustered index sorts and stores the data rows of the table or view in order based on the index key. This type of index is implemented as a B-tree structure that supports fast retrieval of the rows, based on their key values. For this reason, a table can have only one clustered index.
What is Nonclustered Index?
A nonclustered index is analogous to an index in a textbook. The data is stored in one place, the index in another place. The index will have pointers to the storage location of the data. Since, the nonclustered index is stored separately from the actual data, a table can have more than one non clustered index, just like how a book can have an index by Chapters at the beginning and another index by common terms at the end.
In the index itself, the data is stored in an ascending or descending order of the index key, which doesn't in any way influence the storage of data in the table.
Difference between Clustered and Nonclustered Index?
- Only one clustered index per table, where as you can have more than one non clustered index.
- Clustered index is faster than a non clustered index, because, the non-clustered index has to refer back to the table, if the selected column is not present in the index.
- Clustered index determines the storage order of rows in the table, and hence doesn't require additional disk space, but where as a Non Clustered index is stored seperately from the table, additional storage space is required.
What are the disadvantages of an Index?
Disk Space: Indexes are stored on the disk, and the amount of space required will depend on the size of the table, and the number and types of columns used in the index. Disk space is generally cheap enough to trade for application performance, particularly when a database serves a large number of users.
Insert, Update and Delete statements could be slow: Another downside to using an index is the performance implication on data modification statements. Any time a query modifies the data in a table (INSERT, UPDATE, or DELETE), the database needs to update all of the indexes where data has changed. Indexing can help the database during data modification statements by allowing the database to quickly locate the records to modify, however, providing too many indexes to update can actually hurt the performance of data modifications. This leads to a delicate balancing act when tuning the database for performance.
How many Clustered and Nonclustered Indexes can you have per table?
Clustered Index: Only one Clustered Index per table. A clustered index contains all of the data for a table in the index, sorted by the index key. Phone Book is an example for Clustered Index.
Nonclustered Index: You can have multiple Non Clustered Indexes per table. Index at the back of a book is an example for Non Clustered Index.
Which Index is faster, Clustered or Non Clustered Index?
Clustered Index is slightly faster than Non Clustered Index. This is because, when a Non Clustered Index is used there is an extra look up from the Non Clustered Index to the table, to fetch the actual rows.
When is it usually better to create a unique nonclustered index on the primary key column?
Sometimes it is better to use a unique nonclustered index on the primary key column, and place the clustered index on a column used by more queries. For example, if the majority of searches are for the price of a product instead of the primary key of a product, the clustered index could be more effective if used on the price field.
What is a Composite Index in SQL Server? or What is the advantage of using a Composite Index in SQL Server? or What is Covering Query?
A composite index is an index on two or more columns. Both clustered and nonclustered indexes can be composite indexes.
If all of the information for a query can be retrieved from an Index. A clustered index, if selected for use by the query optimizer, always covers a query, since it contains all of the data in a table.
By creating a composite indexes, we can have covering queries.
What is PIVOT Operator in SQL Server?
Pivot operator is used to turn unique values from one column, into multiple columns in the output, there by effectively rotating a table.
The syntax of PIVOT operator from MSDN is shown below:
SELECT <non-pivoted column>, [first pivoted column] AS <column name>, [second pivoted column] AS <column name>, ... [last pivoted column] AS <column name> FROM (<SELECT query that produces the data>) AS <alias for the source query> PIVOT ( <aggregation function>(<column being aggregated>) FOR [<column that contains the values that will become column headers>] IN ( [first pivoted column], [second pivoted column], ... [last pivoted column]) ) AS <alias for the pivot table> <optional ORDER BY clause>
What is UNPIVOT Operator in SQL Server?
UNPIVOT operation is a reverse process of PIVOT operation, but UNPIVOT is not the exact reverse of PIVOT. If PIVOT performs an aggregation and merges multiple rows into a single row in the output, then UNPIVOT can’t reproduce the original table-valued expression result because rows have been merged. So conclusion is that if PIVOT operation merges multiple row in a single row, then UNPIVOT operation can’t retrieve original table from the output of PIVOT operation. But if PIVOT operation doesn’t merge multiple row in a single row, then UNPIVOT operation can retrieve original table from the output of PIVOT operation.
How to create dynamic PIVOT query?
Dynamic pivot query can be created as shown below.
/*Declare Variable*/ DECLARE @Pivot_Column [nvarchar](max); DECLARE @Query [nvarchar](max); /*Select Pivot Column*/ SELECT @Pivot_Column= COALESCE(@Pivot_Column+',','')+ QUOTENAME(Year) FROM (SELECT DISTINCT [Year] FROM Employee)Tab /*Create Dynamic Query*/ SELECT @Query='SELECT Name, '+@Pivot_Column+'FROM (SELECT Name, [Year] , Sales FROM Employee )Tab1 PIVOT ( SUM(Sales) FOR [Year] IN ('+@Pivot_Column+')) AS Tab2 ORDER BY Tab2.Name' /*Execute Query*/ EXEC sp_executesql @Query
