Important Differences in SQL Server

Important Difference in SQL Server:

Difference between Primary Key and Unique key
Sr.No.	Primary Key	Unique key
1	It cannot have null values	It can have nulls
2	In a table there will be only one primary key.	In a table we can create multiple unique key.
3	By Default, primary key creates clustered index.	By default, It creates non-clustered index.

 

Difference between Delete and Truncate
Sr.No.	Delete	Truncate
1	Deleted data can be rolled back.	Truncated data cannot be rolled back.
2	Delete is slower than Truncate.	It is faster than delete.
3	It can have where clause.	It cannot have where condition.
4	Delete operation is logged on row wise.	It is on page wise.
5	It does not reset the identity value.	It resets.
6	It is DML statement	It is DDL statement.

 

Difference between Clustered Index and Non- Clustered Index
Sr.No.	Clustered Index	Non- Clustered Index
1	In clustered index, leaf node points to the physical data/data pages.	In non-clustered index, leaf node contains to row index and then row index points to actual physical data/data pages.
2	A table can have only one clustered index.	There can be more than one non-clustered index on a table.

 

Difference between Instead of and After Trigger
Sr.No.	Instead of Trigger	After Trigger
1.	It fire before the DML operations occur, first inserted and deleted get flourished and then trigger fires	This trigger occurs after when an insert, update and delete operation

 

Difference between Stored Procedure and Function
Sr.No.	Stored Procedure	Function
1	It is stored in parsed and pre compiled format in database.	It is compiled and executed at run time.
2	It can have zero or more values as returned values.	It can return only one value.
3	It can call function.	It cannot call stored procedure.
4	It can have input and output parameter.	It can have only input parameter.