Database Overview: Q & A
What's the difference between a primary key and a unique key?
Both primary key and unique key enforce uniqueness of the column on which they are defined. But by default primary key creates a clustered index on the column, where are unique key creates a non-clustered index by default. Another major difference is that, primary key does not allow NULLs, but unique key allows one NULL only.
What is a transaction and what are ACID properties?
A transaction is a logical unit of work in which, all the steps must be performed or none. ACID stands for Atomicity, Consistency, Isolation, Durability are the properties of a transaction.
What type of Index will get created after executing the below statement?
CREATE INDEX myIndex ON myTable (myColumn)
It is Non-clustered index. Important thing to note: By default a clustered index gets created on the primary key, unless specified otherwise.
What's the difference between DELETE TABLE and TRUNCATE TABLE commands?
DELETE TABLE is a logged operation, so the deletion of each row gets logged in the transaction log, which makes it slow. TRUNCATE TABLE also deletes all the rows in a table, but it will not log the deletion of each row, instead it logs the de-allocation of the data pages of the table, which makes it faster. Of course, TRUNCATE TABLE can be rolled back.
What are constraints? Explain different types of constraints.
Constraints enable the RDBMS enforce the integrity of the database automatically, without needing you to create triggers, rule or defaults.
Types of constraints: NOT NULL, CHECK, UNIQUE, PRIMARY KEY, FOREIGN KEY