SQL Server Interview Question & Answer Vol-1

No.of Qsns.-10 / Vol-1

1.    What are the differences between Clustered and Non-Clustered Indexes in SQL Server?
Ans.

Clustered Index	Nonclustered Index
i. Clustered Index stores data rows in a sorted order(ascending) based on key values.	i. The nonclustered index will not store data in any sorted order. It stores and sorts of data separately from the data table.ie on the index page.
ii. In a table, we can have one clustered index.	ii. We can have one or multiple Nonclustered indexes in a table max(999).
iii. It requires less memory.	iii. It requires more memory.
iv. It is faster	iv. It is slower.

2.    What are the differences between Stored Procedure and Stored Function?

Ans.

Stored Procedure	Stored Function
i. Stored Procedures may return value.	i. Stored Function must return value.
ii. Stored Procedures have both input and output parameters.	ii. Stored functions have only input parameters.
iii. A stored procedure can be defined with or without parameter.	iii. A stored procedure can not be defined with parameters.
iv. In a stored procedure, we can perform Insert, Update, Delete, and Select operations.	iv. In a stored procedure, we can only perform Select operations.
v. To call Stored Procedure we use the "Execute" command.	v. To call Stored Procedure we use the "Select" command.
vi. We can call the Stored Function in a Procedure.	vi. We can not call the Stored Procedure in a Function.
vii. Here we can handle the exception.	vii. Here we can handle the exception.

3.    What are the advantages of using Stored Procedure?

Ans.

 i.   Stored Procedure is a pre-compiled block of code, so when we call a Stored Procedure it performs the task without compiling unnecessary again and again. So it will reduce the burden on the database.

ii.   Application Performance will improve and the user will get a quick response.

iii.  We don't need to write the same query again again.we can call the procedure many times. We can call this modular programming i.e. Once create, stored, and call again and again.

iv.  The stored procedure provides security to the data.

4.    How to get the count of records in a database table?

Ans.

Select Count(*) From table_Name;

5.   What is the difference between a Primary Key and a Unique Key in SQL Server?

Ans.

Primary Key	Unique Key
i. Primary key does not allow Null values.	i. Unique key allows Null values.
ii. A table can have only one primary key.	ii. A table can have multiple unique keys.
iii. Primary key is by default a clustered index.	iii. Unique key is by default a nonclustered index.

6.    Differences between Delete and Truncate in SQL Server.

Ans.

Delete	Truncate
i. Delete is a Data Manipulation Language.	i. Truncate is a Data Definition Language.
ii. We use Delete command to delete a specific row of a table.	ii. We can not delete a specific row of a table, instead, we can delete all rows at a time.
iii. Delete through Delete command is a temporary data deletion. So we can rollback the.	iii. If we delete through Truncate then it is permanent data deletion. So we can not roll back the data again.
iv. It is slow.	iv. It is fast.
v.The identity values of a column will not reset.	v. The identity values of a column will reset.
vi. It supports where condition.	vi. It won't support where condition.

7.   What is the difference between a UNION and a JOIN?

Ans.

Union	Join
i. It is an Operator used in the SQL server.	i. It is a SQL Clause.
ii. Union combines two table data into new rows(vertically).	ii. Join combines two table data into new columns(horizontally).
iii. It combines the results of multiple queries.	iii. It fetches data from two or more tables using a query.
iv. It will fetch all the records from both tables.	iv. It will fetch the records which are common to both tables.
v. The selected column(s) must have the same data type.	v. Only the matching columns should have the same data type.
vi. We should match the number of columns from both queries and all the columns should be in order to match their data types.	vi. It is irrespective of the number of columns per table and order of columns data type matching is not required.

8.   What is ORDER BY and how is it different than clustered index?

Ans.

Order  By is a SQL Server clause, It shorts/arranges the records in Ascending or Descending order. By default, it is ascending. To arrange in descending we have to write a "desc" keyword.

The clustered index is used to speed up the querying process. so it sorts the data based on key-value by default. Its purpose is to enhance performance. 

But Order by clause is used for sorting purposes. Using this we can order any column value. Though we sort any other column of a table that has a clustered index, it will first scan for the cluster row. If no Clustered Index is present on the table it will look up the whole table and will take more time. To faster the speed we can use the nonclustered index on that particular column also.

9.   What is a Join? Explain Different Types of Joins.

Ans.

Join is a SQL Query technique for retrieving the data from one or more than one table at a time. 

We have different types of joining concepts, those are:-

• Inner Join
• Left Outer Join
• Right Outer Join
• Full Outer Join
• Cross Join
• Natural Join
• Self Join
• Equi Join
• Non-Equi Join

10. What is Trigger and explain the types of triggers in SQL?

Ans.

A trigger is a special type of procedure that automatically invokes when an event occurs. It restricts a database table and any operation. It can not have any parameters.

It is of 2 types:-

1.DML Trigger:- It invokes when any user tries to do data manipulations(Insert, Update, Delete ) /modification on data.

2.DDL Trigger:-It restricts DDL operations(Create, Alter, Drop, etc...) on a specific Database on SQL Server.

 go to Vol-2