SQL SERVER Interview Questions-part1
- What
is denormalization and when would you go for it?
As the name indicates, denormalization
is the reverse process of normalization. It's the controlled introduction
of redundancy in to the database design. It helps improve the query
performance as the number of joins could be reduced.
- How
do you implement one-to-one, one-to-many and many-to-many relationships
while designing tables?
One-to-One relationship can be
implemented as a single table and rarely as two tables with primary and
foreign key relationships. One-to-Many relationships are implemented by
splitting the data into two tables with primary key and foreign key
relationships. Many-to-Many relationships are implemented using a junction
table with the keys from both the tables forming the composite primary key
of the junction table.
- 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
nonclustered index by default. Another major difference is that, primary
key doesn't allow NULLs, but unique key allows one NULL only.
- What
are user defined datatypes and when you should go for them?
User defined datatypes let you extend
the base SQL Server datatypes by providing a descriptive name, and format
to the database. Take for example, in your database, there is a column
called ZIP_Code which appears in many tables. In all these tables it
should be varchar(6). In this case you could create a user defined
datatype called ZIP_Code_Type of varchar(6) and use it across all your
tables.
- What
is bit datatype and what's the information that can be stored inside a bit
column?
Bit datatype is used to store boolean
information like 1 or 0 (true or false). Untill SQL Server 6.5 bit
datatype could hold either a 1 or 0 and there was no support for NULL. But
from SQL Server 7.0 onwards, bit datatype can represent a third state,
which is NULL.
- Define
candidate key, alternate key, composite key.
A candidate key is one that can
identify each row of a table uniquely. Generally a candidate key becomes
the primary key of the table. If the table has more than one candidate
key, one of them will become the primary key, and the rest are called alternate
keys. A key formed by combining at least two or more columns is called
composite key.
- What
are defaults? Is there a column to which a default can't be bound?
A default is a value that will be used
by a column, if no value is supplied to that column while inserting data.
IDENTITY columns and timestamp columns can't have defaults bound to them.
See CREATE DEFUALT in books online.
- 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. These are the properties of
a transaction. For more information and explanation of these properties,
see SQL Server books online or any RDBMS fundamentals text book.
- Explain
different isolation levels An isolation level determines the degree of
isolation of data between concurrent transactions.
The default SQL Server isolation level
is Read Committed. Here are the other isolation levels (in the ascending
order of isolation): Read Uncommitted, Read Committed, Repeatable Read,
Serializable.
- How
does .NET and SQL SERVER thread is work?
There are two types of threading
pre-emptive and Non-preemptive but Sql Server support Non-preemptive and
.NET thread model is different. Because Sql have to handle thread in
different way for SQLCLR this different thread are known as Tasking of
Threads . In this thread there is a switch between SQLCLR and SQL SERVER
threads .SQL SERVER uses blocking points for transition to happen between
SQLCLR and SQL SERVER threads.
