SQL Server interview question Part 1

1. Ques: What is normalization? Explain different levels of normalization? What are benefit of normalization?
Answer:
Normalization is process in RDBMS  by which dividing a database into two or more tables and defining relationships between the tables. Normalization avoid data redundancy. This should eliminates unnecessary duplication and provides a rapid search. The objective of Normalization is to isolate data so that additions, deletions, and modifications of a field can be made in just one table.

Database normalization process divide by following form:
First Normal Form (1NF)
Second Normal Form (2NF)
Third Normal Form (3NF)
Boyce-Codd Normal Form (BCNF)
4th Normal Form (4NF)
5th Normal Form (5NF)(PJNF)

Benefit of normalization:
Data integrity -Eliminate data redundancy and data inconsistency.
Improve performance.
Allows for consistent storage and efficient access of data.
Query optimization.
Faster index creation and sorting (because the tables have fewer columns).


2. Ques: What is Denormalization? 
Answer:
The process of adding redundant data to get complex join, in order to optimize database performance.
Denormalization is reverse process of normalization. it avoid multiple join into table,and speeding up database read performance.


3. Ques: What are the way you can Optimization Query in SQL database.
Answer:

• Try to use constraints instead of triggers.
• Use table variables instead of temporary tables.
• Use  EXISTS operator instead of IN operator.
• Try to use UNION ALL statement instead of UNION.
• Try to avoid using the DISTINCT clause.
• Try to avoid the HAVING clause.
• Avoid * from SELECT statement and use columns which are necessary.
• Avoid unnecessary complicated joins into table.
• Avoid more number of triggers on the table.
• Include SET NOCOUNT ON statement on top of store procedure statement.
• Use the select statements with TOP keyword
• Table should have primary key.
• Table should have minimum of one clustered index.
• Table should have appropriate amount of non-clustered index.
• Try to avoid prefix "sp_" with stored procedure.
• Use TRY-CATCH block for handling errors in query statements.
• Try to keep small Transaction.
• Try to avoid nvarchar datatype and use varchar. varchar takes less memory as compare nvarchar.

4. Ques: What are differences in @@IDENTITY , SCOPE_IDENTITY, IDENT_CURRENT ?
Answer:
@@IDENTITY -It returns the last identity value generated for any table in the current session, any scopes.
SCOPE_IDENTITY -It returns the last identity value generated for any table in the current session and the current scope.
IDENT_CURRENT -It returns the last identity value generated for a specific table in any session and any scope.

5. Ques: What are the different authentications in sql server ?
Answer:
 SQL  server have 2 different authentication

• Windows Authentication 
• SQL Authentication 

Windows authentication uses the windows credentials to authenticate the user and takes it to the server
SQL authentication uses SQL user name and password to authenticate the user and its secure as well 

5. Ques: What is a trigger in SQL Server? What are the advantages and disadvantages of using SQL Triggers ?
Answer:
A trigger is a special kind of stored procedure that automatically executes when an event occurs in the database server. DML triggers execute when a user tries to modify data through a DML event. DML events are INSERT, UPDATE, or DELETE statements on a table or view.

Advantages of Using SQL Triggers :

• It can catch the errors in business logic at the database level. 
• It provides an alternative way to run scheduled tasks. 
• It is very much useful when we use it to audit the change the data in a database table. 
• Using SQL trigger,we don’t have to wait to run the scheduled tasks. we can handle those tasks before or after changes being made to database tables. 
• It provides an alternative way to check integrity.

Disadvantages of using SQL Triggers 

• Trigger can provide extended validation and it cannot be replaced with all the validations. 
• Simple validations can be done at the application level itself . 
• Triggers executes invisibly from client-application which connects to the database server.So it is difficult to figure out what happens at the database layer. 
• Triggers runs on every update made to the table therefore it adds more load to the database and cause the system to run slow.

6. Ques: Explain Primary key, Alternate key, Candidate Key and Composite Key in Sql Server?

Answer:

Primary key: 

Primary key constraint uniquely identifies each record in a database table. Primary keys must contain UNIQUE values. A primary key column cannot contain NULL values.There is only one primary key per table.when we create primary key to any table then a clustered index is automatically created to that column.

Alternate key:

If table have more than one candidate key, then after choosing primary key from those candidate key, rest of candidate keys are known as an alternate key of that table. 

Candidate key:

A column, or set of columns that can uniquely identify a row in a table.

Composite key:

A key that is composed of more than one column.A composite Key can be either Primary or Unique Key.