SQL SERVER INTERVIEW QUESTIONS

Normalization is a database design technique to remove
redundant data.
Denormalization is a database design technique to improve search
performance. we merge tables.Duplicate data is present in
denormalization.
OLTP:Online Transaction Processing.:Follow Normalization:insert,delete.
OLAP:Online Analytical Processing.:Follow Denormalization:Select
OLTp Normalization avoids redundancy and we follow normalization desing(1st,2nd and 3rd Normal)
OLAP Denormalization improve search performance and we follow denormalization design.

1st Normal form: A table is in first normal form when the columns
have Atomic values. It should not have repeating groups.
2nd Normal form: First Normal form should be satisfied.All non-key columns should be fully dependent on the Primary Key.
3rd Normal:-All 1st and 2nd normal form should be satisfied.No
transient depedency should be present.

A:Atomic
P:Partial Dependency
T:Transient

Duplicates are not allowed in primary or Unique key.
2 N->null:Unique can have NULLS,but primary key can not have NULLS
N->number:many unique keys but only One Primary Key.
Primary key:
Unique Key:

Differentiate b/w Char vs Varchar?
Char is fixed lenght.
Varchar has variable lenght.
If you want to just store english characters then use Char,For multilingual language(Non-English) use NChar.
Char 1 Character =1 byte
NChar 1 Character=2 bytes.

what is the use of index?
index is used to help in sqlserver for search performance or
indexes increases search performance.
Search becomes faster beacause of Balance tree structure.Internally
it creates Node and Leaf nodes to reach to the data quick.

What are the two types of Indexes?
Clustered Index and Non-Clustered Indexes
Clustred Index:leaf node will point towards the actual data.
Non-Clustered Indexes:The point node

In clustered and non-clustered indexes they have a leaf nodes.
In Clustered indexes leaf node will point to actual data.While in case of non-clustered index leaf node takes help of clustred index

That’why you can have only one clustered index and many non-clustred indexes.

Difference between function vs Stored Procedures
Function: function return Computed Values ,But will not make any permanent changes
to the enviroment. Only Selects aloowed,insert/update/deletes not allowed.
Should have one input with a return value.Mostly Scalar value, Table valued functions. Can be called from select/where/call from other stored procedure.

Strored Procedure:
Mini Batch Program.Can change the enviroment . Insert, Updates and Deletes allowed.
ZERO input parameter is ok and can have single or multiple uotputs.
Stored procedures can be executed from Select/Where or from other functions.

Stored procedures can not be executed from Select/Where or from other functions.Can have single or multiple outputs.

Function can be called from select/where/call from other procedures.
Mostly Scalar value, Table valued functions.

What are triggers and Why do you need it?
Triggers are logics which can be executed when events like insert,update,delete etc happens.

What are types of triggers?
One is the After trigger and One is instead of trigger.

What is need of Identity?
Identity helps to define auto-incremented column.

Explains transactions and how to implement it?
Transaction helps to implement series of activity treat as one single unit.Either everything is successful and or everything rollbacks.
Transactions treats series of activity as one single unit.
Either everything is successful and or everything rollbacks.

What are inner joins?
Inner join selects matching records from both tables.

Explain Left join?
All data from left table selected and only matching records from right table.

Explain Right join?
All data from right table selected and only matching records from the left table.

Explain Cross joins?
Cross join is cartesian.Every record of one table is joined with other table records.