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 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.

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);

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

What is database and it`s benefits

What is Database?

Database is convenient tool for manage large amount of data.

Benefits of Database

• Redundancy can be reduced.
• Inconsistency can be reduced.
• Data can be shared among single and multiple users by share same database with multiple user
• Standards can be made and can be followed
• Common security mechanism can be set and followed by every user of database.

Types of command are here
Keys in sql server - key Constrains
pattern matching by like
use of distinct
print 1 to 10 by cte
get all child or sub employee with its level
sql indexer
sql server views with example
sql-join
cursor
varchar vs nvarchar
what are difference between temp table and table variable
what is stored procedure
what is function in sql
What are the difference between Stored procedure and function in sql server
how to compare null not null and blank
Use of Sub Query
rank denserank rownumber function in sql server
what is use of nolock?
set identity insert on off