What is use of NOLOCK ?

Use of NOLOCK

• NOLOCK is also known and dirty reading or blind reading in SQL Server.
• User can read uncommitted data without any wait by using NOLOCK.
• NOLOCK ignore the any LOCK given to transaction by any SQL user.

Example : 

Code :

BEGIN TRANSACTION 

UPDATE C 

SET C.ContactName='' 

FROM Customers C 

WHERE C.CustomerId = 2

-------------------------------------------------

select * from Customers WHERE CustomerId = 2

-----------------------------------------------

select * from Customers WITH (NOLOCK) WHERE CustomerId = 2

Rank DenseRank RowNumber function in sql server

Ranking function in SQL server

RANK()

• RANK() function returns number in sequence and it returns the same number when we have same value in column and skip (with gaps) the value from sequence like (1, 2, 2, 4, 4, 6, 7, 8)

DENSE_RANK()

• DENSE_RANK() function returns number in sequence and it returns the same number when we have same value in column and does not skip (no gaps) any number from sequence like(1, 2, 2, 3, 3, 4, 5, 6)

ROW_NUMBER() 

• ROW_NUMBER() function generates the unique number in sequence
•  based on specified order by condition on particular column for each rows without any gap and duplication. (1, 2, 3, 4, 5, 6, 7, 8)

Example:

SELECT 

RANK() OVER(ORDER BY Age) AS Rank_number,

DENSE_RANK() OVER(ORDER BY Age) AS Dense_Rank_number,  

ROW_NUMBER() OVER(ORDER BY Age) AS Rownumber_number,

* FROM Customers 

Use of Partition by

• Partition by divides result and then ranking function are apply

Example:

SELECT   

RANK() OVER(PARTITION BY Country ORDER BY Age) AS Rank_number,

DENSE_RANK() OVER(PARTITION BY Country ORDER BY Age) AS Dense_Rank_number,

ROW_NUMBER() OVER(PARTITION BY Country ORDER BY Age) AS Rownumber_number,

* FROM Customers 

Use of sub query

Sub query

• Sub query is also known as Inner Query, Nested Query or Query with in Query.
• Result of sub query is assign to main query as where clause condition..
• Sub query is use with any DML and DQL Sql commands.
• Sub query is enclosed with in parentheses .
• In use of sub query select statement only one column selection is allow, multiple column selection are allow in main query.
• Between operator can not use with sub query but it can use with in sub query.

Example

SELECT * FROM dbo.Orders 

WHERE CustomerId IN (

SELECT CustomerId FROM Customers WHERE Age >= 18

)

Disadvantage 

• As part of query optimization we should use JOIN instead of sub query
• As in use of sub query there are multiple select statement so it will affect performance.

How to compare NULL, NOT NULL and Blank in SQL

Compare NULL in SQL

• IS keyword is use to compare NULL in SQL
• Here in this example we will get all the record which has value NULL in Zip column
• SELECT * FROM dbo.UserMaster WHERE Zip IS NULL

Compare NOT NULL in SQL

• IS and NOT keyword is use to compare NOT NULL in sql
• Here in this example we will get all the record which does not value NULL in Zip column
• SELECT * FROM dbo.UserMaster WHERE Zip IS NOT NULL

Compare Blank value in SQL

• = operator is use to compare blank value
• Here in this example we will get all the record which has value Blank in Zip column
• SELECT * FROM dbo.UserMaster WHERE Mobile = ''

What are the difference between Stored procedure and function in sql server

Difference between Stored procedure and function

• Return Value

• Function must return any value
• Procedure can return single value or n number of rows or null

• Parameter Type

• Function has input parameter only
• Stored procedure can have input or output or both parameter

• Call

• Function can call from stored procedure
• Stored procedure can not call from function

• Use of DML

• Function can not use any DML statement
• Stored procedure can use all DML statement

• Exception handling

• Exception handling is not possible with function
• Stored procedure allow to use Exception handling

• Use of Transaction

• Function can not use any command of transaction control like begin, commit, rollback
• Transaction can be begin, commit and rollback in procedure.

• Compile

• Function compiles every time when its call
• Stored procedure compiles only once when it created

What is Function in SQL

What is function?

• function is a database object of sql server
• function is a collection of sql statement.
• function has only input parameter (not specify output as in procedure)
• function only return single value
• any DML statement are not allow in function 
• function can call from another function and also call from stored procedure.
• exception handling is not possible with function

Types of function in sql server

• In SQL there are two type of function are available

1. Scalar Function
• Scalar function are return only single value as result of function
• function can return any type of data as result 
2. Table value function 
• table value function can return data in table format as result of function

Example of Scalar function

Create function fnGetEmpAge

(

@EmplId INT

)

returns INT

As

Begin 

return (Select Age from TblUserMaster where EmplId=EmplId);

end 

Example of Table value function 

Create function GetAllUser()

returns Table

As

return (Select FName, LName, Age from TblUserMaster) 

End

What is Stored Procedure

What is stored procedure

• They are logically grouped SQL or Pl/SQL commands that performs some task.
• When stored procedure is create or alter it compiled and saved in database
• Compiled code is execute when stored procedure is call
• Stored procedure contains input and output parameter
• Stored procedure can contain DML and DQL statement.
• Transaction can be handled in stored procedure

Syntax for create procedure

CREATE PROCEDURE [dbo].[CreateNewPost]
-- Add the parameters for the stored procedure here
<Parameter name >, <Data type>,
<Parameter name >, <Data type> {input, output}
AS
BEGIN
<Queries>
Exception
<exception part>
End;

Example of insert record with output parameter

CREATE PROCEDURE [dbo].[InsertData]
@Title nvarchar(400),
@UserId int,
@Output nvarchar(100) output
AS
BEGIN
SET NOCOUNT ON;

INSERT INTO Post
(
Title,
UserId,
)
VALUES
(
@Title,
@UserId,
)
set @Output = @@IDENTITY

END