SQL Server interview questions & Answers Set 4

What is De-normalization and when do you do it?

As the name indicates, de-normalization 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.

 

Difference between Function and Stored Procedure?

1. Function must returned a value, but stored procedure is not necessarily returned value

2. A function can be used to calculations, but stored procedure used to solve a business logic

What is a NOLOCK?

Advantage:
Applying nolock in select statement will increase concurrency and performance in fetching.

Disadvantage:
it doesn’t consider the transaction scenario, even if the transaction is under process not yet committed or rollback it will fetch the current record e.g.

begin tran
insert into tablename
values('1','asdf')

select * from tablename with (nolock)

-- it will display the table with inserted record but the transaction is not completed, it can be rollback
so it became dirty read.