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

what are the difference between temp table and table variable in sql server

what are the difference between temp table and table variable in sql server?

• Declaration 

• Temp table is create by # sign with create keyword
• example: create table #TMPTABLE(id int, FName varchar(50),LName varchar(50))
• Table variable is declare by @ sign with 
• example: declare @TableVar table (id int,name varchar(50))

• Scope of use

• Temp table can be use by multiple user
• Table variable can use by current user only

• Use of DDL command

• Temp table allow use of all DDL commands on it.
• Table variable do not allow DDL command on it

• Index

• Temp table allow to create alter and drop table index
• Table variable do not allow to create index but it only allow to create cluster index only.

• Transaction

• Temp table allows to begin rollback and commit transaction
• Any Transaction command is not possible with table variable.

• Storage

• Temp table is physically created in tempdb
• Table variable is create in memory

Example Temp table

create table #TMPTABLE(id int, FName varchar(50),LName varchar(50))

insert into #TMPTABLE values (1,'jayendra','jayendra')

select * from #TMPTABLE

Example Table variable

declare @TableVar table (id int,fname varchar(50),lname varchar(50))

insert into @TableVar values (2,'jayendra','jayendra')

select * from @TableVar

varchar VS nvarchar

What are the difference between VARCHAR AND NVARCHAR in sql server?

• Defination

• VARCHAR is non-unicode character type with variable length
• NVARCHAR is unicode character type with variable length

• Size

• VARCHAR occupies 1 byte per character.
• for example: DECLARE @NAME VARCHAR(10)='JAYENDRA'
• occupies 8 bytes
• NVARCHAR occupies 2 byte per character.
• for example: DECLARE @NAME NVARCHAR(10)='JAYENDRA'
• occupies 16 bytes

• Usage

• use VARCHAR type when your column have only non-unicode character data only like any character which has ascii value 0 to 255
• use NVARCHAR type when you table column contains unicode character data like any other language character ex. gujarati or hindi

• Storage size

• Occupies number of bytes equal to the number of Characters entered and 2 bytes extra for defining offset.
• Occupies number of bytes equal to the number of Characters entered * 2 (twice of character) and 2 bytes extra for defining offset.

Cursor

Cursor

• When user executes any query, block of memory will be provided in database.
• This block of memory is provided for its internal processing and its private to database engine.
• It is called cursor.
• Microsoft SQL Server statements produce a complete result set, but there are times when the results are best processed one row at a time. Opening a cursor on a result set allows processing the result set one row at a time. You can assign a cursor to a variable or parameter with a cursor data type
• When user defines a block of memory to execute a query through programming it is said to be explicit cursor.
• To define explicit cursor, user have to follow below steps.
1. Declare cursor
2. Open cursor
3. Fatch cursor
4. Close Cursor
5. Deallocate Cursor

Example

DECLARE @fname varchar(50)
DECLARE cur_emp CURSOR
static FOR
SELECT fname from emp
OPEN cur_emp
IF @@CURSOR_ROWS > 0
BEGIN
FETCH NEXT FROM cur_emp INTO @fname
WHILE @@Fetch_status = 0
BEGIN
PRINT ' Name : '+@fname
FETCH NEXT FROM cur_emp INTO @fname
END
END
CLOSE cur_emp
DEALLOCATE cur_emp

Types of Cursors

• Static Cursors - A static cursor populates the result set at the time of cursor creation and query result is cached for the lifetime of the cursor.
• Dynamic Cursors - A dynamic cursor allows you to see the data updation, deletion and insertion in the data source while the cursor is open. Hence a dynamic cursor is sensitive to any changes to the data source and supports update, delete operations.

sql join

Joins

To get output from multiple tables we can use joins.
Using join, we can manipulate on two tables using single query.
Join can be applied if both tables contains same data type and width for column.

There are three types of joins

1. Inner
2. Outer (left, right)
3. Cross

Inner join

It is also called equi join.
It compares two columns from two tables using = operator.

Syntax:
select <columnName1>,<columnName2> from
<tableName1>Inner join<tableName2> on
<tableName1>.<columnName1> = <tableName2.>.<columnName2>;

Example:
select member_master.Name, member_master.Age, employee_master.salary
from member_master
inner join employee_master on
member_master.ID = employee_master.ID;

Outer Join

Outer join can be better illustrated using its types.

1. Left outer join

left outer join will take all the rows from left side table.
If there will be more rows in right side table, it will be truncated and if there will be less rows, null will be added as data.

syntax:
select <columnName1>,<columnName2>
from <tableName1> left join <tableName2> on
<tableName1>.<columnName> = <tableName2>.<columnName>;

2. Right Outer Join

right outer join is like left join but will take all rows from right side table.

Syntax:
select <columnName1>,<columnName2>
from <tableName1>right join <tableName2> on
<tableName1>.<columnName1> = <tableName2>.<columnName2>;

Cross Join

When we apply cross join, it will take first row from left side table and put it with each right side rows.
For example, table1 having 5 rows and table2 having 3rows
then first row from table1 will be placed with 3 rows of table2
and then 2nd row will be placed with 3 rows again and so on.

SQL Server Views with Example

SQL Server Views

Once table is created and data is populated we may need to hide some data from some users.
This leads to data security concept.
For this, we can create views.
View is sub set of database table, it means it is type of table but containing required part of data only.
It is virtual table.

Creating view:

Syntax:
create view <viewName> as select <columnName1>,<columnName2> from <tableName>;
Example:
create view Members as select Name,Age,Gender,Address from Employee_Master;

To select data from view, one can write as,
select <columnName1>,<columnName2> from <viewName>;

To destroy a view, one can write as,
drop view <viewName>;

SQL Indexer

Indexer

Indexer is also same as index in  sql server table

Indexers are of two type

1. Simple
2. Composite

An indexer created on single column of table is simple indexer where as indexer created on more
than one column is called composite indexer.

Simple Indexer
Syntax:
create index <indexName> on <tableName>(<columnName>);
Example:
create index Emp_Id_idx on Employee_Master(EmpId);

Composite index
Syntax:
create index <indexName> on <tableName> (<columnName1>,<columnName2>);
Example:
create index index11 on Employee_Master(Age,Salary);

Get all child or sub employee with its Level by using CTE

Get all child employee with its Level by using CTE

CTE stand for Common Table Expression

CTE use for get recursive data

here we will learn how get all child employee recursively and also get its employment level

Here i have usermaster table with structure Like

Column_name       Type

UserID                   int

UserName              nvarchar(100)

Password               nvarchar(400)

FirstName              nvarchar(40)

LastName              nvarchar(40)

Surname                nvarchar(100)

Mobile                   nvarchar(32)

Email                    nvarchar(100)

ParentUserID         int

-------------------------
Example :
-------------------------
;with cte(UserID,FirstName,ParentUserID,[Level])
as(
select UserID,FirstName,ParentUserID,0 as [Level] from usermaster where UserID = 1
union all
select u.UserID,u.FirstName,u.ParentUserID, c.[Level]+1 as [Level] from UserMaster u inner join cte c on c.UserID=u.ParentUserID
)
select * from cte
---------------------------
output
---------------------------

UserID  FirstName           ParentUserID    Level

1              Jayendra              0                              0

2              aaa                       1                             1

3              bbb                      1                             1

4              ccc                       1                             1

13           chaa                      1                             1

14           ccc                        1                             1

16           jay                         3                            2

12           chaa                      2                             2

15           chaa                      2                             2

print 1 to 100 by CTE

CTE

How to print 1 to 100 in sql by using CTE?

CTE stand for Common Table Expression

CTE use for get recursive data

here we will learn how to print 1 to 100 in single select statement.

-------------------------
Example :
-------------------------
;with cte (no)
as(
select 1 as no
union all
select no+1 from cte where no < 100
) select no from cte
---------------------------
output
---------------------------
no
1
2
3
4
5
6
7
.
.
.

100

use of Distinct

Distinct

• Sometimes from data table, we may get redundant data.
• To avoid redundant output, distinct keyword is used.

Example:

select distinct CustomerName from Customers 

--get distinct name 

select distinct CustomerName,Country from Customers

--get distinct name from every country event its repeat in other country

Pattern Matching by like

Pattern Matching

To compare one string with another ‘like’ is used.
For this, like will use wildcard characters that are

•  % for matching string of any length
•  _ for matching of single characters

Example

select * from customers where CustomerName like 'j%'
-- search records startig with j

select * from customers where CustomerName like '%a'
-- search records ending with a

select * from customers where CustomerName like '%ye%'
-- search records which contain ye any where

select * from customers where CustomerName like '_a%'
-- search records which has second character a

Keys in sql server - key Constrains

• To avoid duplication, we need to define constrains on data.
• For example, all students will have unique roll number.
• Here are primary key, foreign key, unique key etc.

Primary Key

• Primary key is column inside table that can be used to uniquely identify a record.
• For example, we can get record of particular employee based
• on his employee number.
• We can define primary key at the time of table creation.
• Syntax: create table <tableName> (<columnName><datatype>(<size>)Primary key));
• Example :  CREATE TABLE Employee (Emp_Id int PRIMARY KEY IDENTITY(1,1), LastName varchar(255) NOT NULL, FirstName varchar(255), LastName varchar(255), Add varchar(255), City varchar(255)
• Here Emp_Id field is primary key & auto incremented
• which has been specified by Identity(1,1)

• There are some attributes of primary key.
• It will not allow duplicate values.
• It will not allow null values.
• Only one primary key is allowed per table.
• It’s main purpose is record uniqueness.

Foreign Key

• When one column is primary key in one table and being referred in second table it is called foreign key in second table.
• For example, Emp_Id of employee will be referred in personal information table, leave table, salary table and so on..
• Syntax: <columnName><datatypa>(<size>) references <tableName> [(<columnName>)]
• Example: 
• create table Salary_Master(ID int primary key, Emp_ID numeric(5,0) references Employee_Master, basic numeric(5,0) date datetime);
• This will refer Emp_Id from Employee_Master table and inside Employee_Master it is primary key.
• Attributes of foreign key are…
• Parent that is being referred must have primary key.
• Parent record can be deleted if there is no record in child.
• Child may have duplicate value from parent but parent can not have.
• Record can not be inserted in child if corresponding record does not exist in parent table.

Unique Key

• When column inside the table is defined as unique key, this key can be used to uniquely identify a record. 
• It is similar to primary key but difference is it allows null inside column where as primary key will not allow null value.
• A table can have more than one unique key but it is not possible with primary key.

Types of SQL Commands

SQL command are divided in to 4 parts

1. DDL
2. DML
3. DQL
4. TCL

1. DDL 

• DDL stands for Data definition language.
• It is use to define structure of database and table.
• user can create change and delete structure of table.

List of DDL commands are

1. create
• Create command is use to create structure of table.
• Syntax: create table <table_name> (<columnName1> <datatype>(<size>));
• Example: crate table User_Master(Name varchar(50),Age numeric(18,0), Address1 varchar(500));
2. alter
• after create structure changes or modification is done by alter command
• alter command is use to add new column, delete existing column and increase size of column
• Add new column
• Syntax: alter table <tableName> add(<NewColumnName1> <dataType>(<Size>),<NewColumnName2><datatype>(<size>)..);
• Example: alter table User_Master add Address2 varchar(400);
• this command will add new column in  existing table User_Master
• Delete column
• Syntax: alter table <tableName> drop column<columnName>;
• Example: alter table User_Master drop column Address2;
• This will delete column Address2 from table User_Master
• Modify column size
• Syntax: alter table <tableName> alter (<columnName> <DataType> <newSize>));
• Example: alter table User_Master alter Address2 varchar(500);
3. rename
• this command is used to rename the existing table.
• Syntax: exec sp_rename <OldtableName>,<newTableName>
• Example: exec sp_rename User_Master ,Member_Master
• This will change name of User_Master to Member_Master
4. truncate
• Truncate will empty table completely. 
• It will drop table first and then will recreate structure of table.
• Syntax : truncate table <tableName>;
• Example: truncate table Member_Master;
• This will delete and recreate table again.
5. drop
• This command will discard whole table.
• Syntax : drop table <tableName>;
• Example : drop table Member_Master;
6. describe
• This command will describe the structure of table
• Syntax : exec sp_help tablename
• Example : exec sp_help Member_Master;
• result is in format of column name, datatype and size

2. DML

DML is data manipulation language.
It is used to manipulate data inside table.

list of DML Commands are:

1. Insert
• This command is used to insert records inside table.
• Syntax: insert into <tableName>(columnName1,columnName2,..column Name...n) values (value1,value2,..valuen);
• Example: insert into Member_Master(FName,LNane,Age,Address) values (‘Name1’,‘Name2’,28,’Address_here’);
2. Update
• This command is used to modify data inside table.
• Syntax: update <tableName> set <columnName1>=value1,<columnName2>=value2..<columnName n=value n;
• Example:update Member_Master set Name=‘new name’;
• Importnt : Condition is specified by where clause
• Example : update Member_Master set Name=‘new name’ where id = 55
• it will only update record which has id 55
3. Delete
• This command is used to delete records from table.
• Syntax: delete from <tableName>;
• Example: delete from Member_Master;
• This will delete all records from Member_Master, 
• for delete particular record we need to specify where clause as like update command.
• Example : delete from Member_Master where id = 55
• it will only delete rcord which has id 55

3. DQL

DQL stands for Data Query Language.

List of DQL commands are

1. Select
• use to get data from table
• Syntax : select <columnName1>,<columnName2>,..<ColumnName n> from <tableName>;
• This will select all columns from table.
• Example : select Name,Age,Gender,Address from Person_Master;
• for get specific record add where clause who have age > 15 then query will be,
• select * from Member_Master where Age > 15;

4. TCL

TCL stands for Transaction control language

List of TCL commands are

1. Commit
• Once any transaction has been performed successfully
• commit will save changes to the database.
• Commit is saving of result of all actions or queries.
• Syntax : commit;
• Example : 

                    begin tran
                    delete from User_Master where id =111
                    Commit

• Than after execute rollback command it will not show deleted record
1. Rollback
• Once any transaction has been done, to undo that transaction, rollback can be used.
• Rollback can undo only last transaction.
• Syntax : rollback;
• Example: 

                    begin tran
                    delete from Member_Masterwhere id =104
                    select * from Student_Master
                    rollback tran

                    select * from Student_Master

• When we execute above code it will not show deleted record but after rollback execution record will display
• after commit you can not rollback transaction.
• every transaction is started with begin tran
1. Save point and Rollback to save point
• Sometimes, during execution of transactions, we need to check whether some conditions are met after transactions or not, if conditions are met, we want to commit changes but If condition doesn’t met we need to rollback to any previous transactions.
• For this type of requirement, we can use rollback to save point.
• In this, we are defining one save point.
• Example : 

                    begin tran

                    save tran trans1

                    delete from ....

                    delete from ....

                    delete from ....

                    save tran trans2

                    delete from ....

                    delete from ....

• now we can rollback on any saved transaction like.
• rollback tran trans2
• rollback tran trans1