SQL Server Interview Questions -- Part 2

11. By using group by, check for duplicate names in test table
Ans: select [name],count(*) from test
group by [name]
having count(*) > 1

12. What is a database transaction ?
Ans: A database transaction is the smallest unit of work in a database.

13. What is are properties of database transaction ?
Ans: A database transaction always comply with ACID properties -- Atomicity, Consistency, Isolation and Durability.

14. What are the different Isolation levels ?
Ans: Read uncommitted, Read committed, Repeatable Read and Serializable.

15. What is differnce between DELETE and TRUNCATE ?
Ans: DELETE is a logged operation. So, each row deleted is logged in the transaction log.
TRUNCATE is not a logged operatio. So, each row is not entered in transaction log.
The logging makes the DELETE slower than TRUNCATE.

16. Name a few datatypes in SQL Server 2008
Ans: Date, Time, Datatime2, Geometry, hierarchyid...

17. What is datetime2 ?
Ans: It is the extension of current datetime where the date range can be from Jan 1, 0001 AD to dec 31, 9999 AD.
Also, the time range can be from 00:00:00 to 23:59:59.9999999

18. What is a stored procedure ?
Ans: Stored Procedure is a set of transact SQL statements that are pre compiled and stored in database.

19. What is a trigger ?
Ans: Trigger is a special kind of stored procedure, that is run automatically by the database, depending on the transaction it is registered for like insert, update and/or delete.

20. What is a database index ?
Ans: Database index is same as the index in a book. It helps to find the required row faster.