Sqlserver Interview questions


Total available count: 45
Subject - Databases
Subsubject - Sqlserver

What is difference between DELETE and TRUNCATE commands?

TRUNCATE:-
1. It is faster and uses fewer system and transaction log resources than DELETE
2. It removes the data by deallocating the data pages used to store the table's data, and only the page deallocations are recorded in the transaction log
3. It removes all rows from a table, but the table structure, its columns, constraints, indexes, and so on, remains. The counter used by an identity for new rows is reset to the seed for the column
4. It cannot be rolled back
5. It is a DDL (Data Definition Language) Command
6. It Resets the identity of the table
7. It removes the rows from a table based on the condition that we provide with a WHERE clause
 
DELETE:-
1. It removes rows one at a time and records an entry in the transaction log for each deleted row. If a programmer wants to retain the identity counter, use DELETE instead. If you want to remove the table definition and its data, use the DROP TABLE statement.
2. It can be used with or without a WHERE clause
3. It activates Triggers
4. It can be rolled back
5. It is a DML (Data Manipulation Language) Command
6. It does not reset the identity of the table
7. It will actually remove all the rows from a table and there will be no data in the table after we run the truncate command



Next 5 interview question(s)

1
What are the difference between clustered and a non-clustered index?
2
What is the difference between a primary key and a unique key?
3
What is NOT NULL Constraint?
4
What is Check Constraint?
5
What is Unique key constraint?