Commonly asked database Q&A

What is the difference between a “where” clause and a “having” clause?
“Where” is a kind of restriction statement. You use where clause to restrict all the data from DB.Where clause is using before result retrieving. But Having clause is using after retrieving the data.Having clause is a kind of filtering command.

What is bit data type and what’s the information that can be stored inside a bit column?
Bit data type is used to store Boolean information like 1 or 0 (true or false). Until SQL Server 6.5 bit data type could hold either a 1 or 0 and there was no support for NULL. But from SQL Server 7.0 onwards, bit data type can represent a third state, which is NULL.

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 is de-normalization and when would you go for it?
As the name indicates, de-normalization is the reverse process of normalization. It is the controlled introduction of redundancy in to the database design. It helps improve the query performance as the number of joins could be reduced.

What is a “constraint”?
A constraint allows you to apply simple referential integrity checks to a table. There are four primary types of constraints that are currently supported by SQL Server:
PRIMARY/UNIQUE – enforces uniqueness of a particular table column.
DEFAULT – specifies a default value for a column in case an insert operation does not provide one.
FOREIGN KEY – validates that every value in a column exists in a column of another table.
NOT NULL – constraint which does not allow values in the specific column to be null. And also, it is the only constraint which is not a table level constraint.

What’s the difference between a primary key and a unique key?
Both primary key and unique 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 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 “functional dependency”? How does it relate to database table design?
Functional dependency relates to how one object depends upon the other in the database. for example, procedure/function sp2 may be called by procedure sp1. Then we say that sp1 has functional dependency on sp2.

Why can a “group by” or “order by” clause expensive to process?
Processing of “group by” or “order by” clause often requires creation of Temporary tables to process the results of the query, which depending of the result set can be very expensive.

Source - GeekyFry