TOP 15 SQL Interview Questions and Answers
Following are the top 15 SQL Interview Questions and Answers:
1. What are Indexes in SQL
Indexes are applied on table columns to improve the SELECT query results. We can add one or more Indexes on one or more columns of a table. Indexes creates lookup table which database search engine uses to search the results. Following is the way to create Index on table:
First, we will create a new Table with name as "Book":
Create Table Book
(
id int IDENTITY(1,1) PRIMARY KEY,
title nvarchar(200),
price float,
authorname nvarchar(200),
publishername nvarchar(500)
)
Now, we will create an Index on Book Table, following is the syntax of creating new Index on a table:
CREATE INDEX index_name on TABLE_NAME(Column_Name)
Now, Create UNIQUE Index named as book_title on Book table
CREATE UNIQUE INDEX book_title on Book(title)
Now, Create Non Unique Index named as book_author on Book table:
CREATE INDEX book_author on Book(authorname)
Following is the Schema of Book Table: It has created 3 Indexes on Book table, one is Clustered Index on "id" column which is our Primary Key in our table, and other 2 are non clustered index on title and authorname columns.
If we want to create Index on multiple columns, then following is the way to create it:
CREATE INDEX book_search on book(title,authorname)
Following is the syntax to use to DROP Index on book table:
DROP INDEX book_title on book
Clustered Index Vs Non Clustered Index:
Clustered Index | Non Clustered Index |
| Clustered Index sorts and store data rows in a table based on their key value, just like Dictionary | Non Clustered stores data at one location and have pointer stored at another location to point to the data. |
| it is faster than Non-Clustered Index | It is slower than Clustered Index |
| It requires less memory to execute the operation. | It requires more memory to execute the operation. |
| A table will have only one Clustered Index | A table can have multiple Non-Clustered Index |
| Clustered Index stores the data pages in leaf nodes of Index | Non Clustered Index never stores data pages in leaf nodes of Index |
2. What are Joins in SQL
3. What is View in SQL
4. What is CTE in SQL
5. What is Temp Table
6. What is Magic Tables
7. How to improve performance of SQL Stored Procedure
8. What is Deadlock in SQL
9. Transaction Scope in SQL SERVER
10. Scalar vs Table valued functions
11. Difference between Union and Union All
12. What are Constraints
13. Top Functions (COALESCE, Convert, CAST, CONCAT, LOWER, UPPER, MAX, AVG, STUFF)
14. What are the way to get 3rd Highest Salary in SQL
15. Write query to delete Duplicate rows from table