Types of Views in Sql Server

There are 3 type of views in Sql Server:

1 - Standard View
2 - Indexed View
3 - Partitioned View

1 - Standard View

Standard View is a genral view that we know all about.

2 - Indexed View

An indexed view is a view that has been materialized. This means it has been computed & stored. You index a view by creating a unique clustered index on it. Indexed view improve the performance of the some type of queries. Indexed view work best for queries that aggregate many rows. They are not well suited for underlying data sets thar are frequently updated.

3 - Partitioned View

A view is a partitioned view that join horizontally partitioned data from a set of member tables across one or more servers.

- A view that join member tables on the same instance of Sql Server is called local partitioned view.

- A view that join data from tables across server is called distributed pertitioned view.

Regards
Manish

Concept of Transection Isolation level & ACID & Save Point

Coming Soon..............

Concept of Open Query & Covering Index & Index View

Open Query

Open Query is mechanism in Sql Server. Using the Open Query you can write any sql query on the Link Server Database object.

Link Server : It is a technique by which you can add any database server in your Sql Sever instance.

Ex:
---
Suppose you are working on a Sql Server(S1) which is in IST and you have a Sql Server(S2) which is in PST. And you need to write a Sql query in S1 server based on S2 server date, then how it can be possible?

It is possible by Link Server & Open Query.

First you need to add S2 server as a link server(LS_S2) in S1 and now you can get the date of S2 server as follow.

select S2Date from OpenQuery('LS_S2','select getdate() as S2Date')

Covering Index

Covering Index is not seperate kind of Index but it is a technique that is used to improve the performance. And you can say it is a non-clustered index which covers all the field given in your Select statement.

The use of Covering Index is best (and only should be used) in that case when you have a simple query and you need to run this query again & again because of Covering Index store the data at both level : heap & index page so Index Covering give the result fast.

Indexed View

If your view has the clustered index that means your view is a Indexed view.

Index view is a performance optomization tips.

Suppose you are not satisfied with your query response then you can create a view for that query and also you can create the index on that view as like regular table.
Indexed views consume disk space and involve some maintenance overhead (every time underlying tables change, the indexed view also has to change), but they usually provide a good boost in performance.

Regards
Manish

Performance tunning of Stored Procedure

After follow these steps mentions in this article

http://manish-sqlserver.blogspot.com/2011/02/tips-write-better-sql-code.html


You can optimize your SP as:

O - First make sure that the statistics on all tables in your query plan are up to date if not then first update the statistics of all the tables(Using update statistics command) which are used in SP. And look at the query plan first. It will show you the optimal current execution plan from the query engine's point of view. Find the most expensive part of the plan and start optimizng from here.

O - If you see the table scan in that part that means the table used in that part have not only indexes but also it dont have clustered index, so first create the clustered index on that table and run again the SP and check the SP is working fine or not according to time.

O - If you see the clustered index scan in that part that means the table used in that part have the indexes but the indexes are not using by the execution plan to search the data. In that case you need to replace the indexes position that means indexes are not created on the right columns. So when you create the indexes on right column then the index seek is performed to search the data in table.

So finally you need to create the indexes in such a way that the query execution plan always perform the Index seek to search the data in table.

Difference between * & *

[ Where & Having ]

1 - Where clause can be used with select, update and delete statement but having can be used only with select statement.

2 - Having can be used only with Group By clause but where clause is applied to each row before they are part of Group By function in query.

3 - Aggregate functions can be used only with having clause not with where clause.

[ Truncate & Delete ]

1 - Delete is DML command and Truncate is a DDL command.

2 - Where clause can be used with the Delete command but can not be used with Truncate command.

3 - Identity is reset in case of Truncate but does not reset in the case of Delete.

4 - Delete command maintain the log in row by row manner but Truncate maintain the only one log for the datapage.

5 - Truncate is faster than Delete because of delete maintain the log in row basis.

6 - Truncate will not return the number of row deleted but Delete return the number of row deleted.

[Note : Both Delete & Truncate can be rollbacked.]

[ SP & Function(UDF) ]

1 - UDF is just like the parameter view and can be used any where as views but SP can not be used any where.

2 - You can not implement exception handlind in UDF but in SP you can implement.

3 - You can not be used transection in UDF but you can be used transection in SP.

4 - You can not use SP within the UDF but you can use the UDF within SP.

5 - You can use only select command in UDF but in SP you can also use the insert, update, delete command.

6 - Function always return the value but in SP is not necessary.

[ Normalization & De-Normalization ]

Normalization concept say that you should remove the redundancy of data
to better organize the data in database that is take less space in storage
But De-Normalization concept say that you should maintain the redundent data for faster search means minimum relation between data because relationship slow your query performance.

[Note : Normalization is used in OLTP system(RDBMS) and De-Normalization is uesd in OLAP system (DWH). OLTP use the Normal Form from 1 > 2 > 3 but OLAP use the Normal Form from 3 > 2 > 1].


[ Clustered & Non-Clustered Index ]

Index are created on the table for faster searching the data when you have large amount of data in your table. And Data is stored in B-Tree (Balanced Tree) formate

1 - In case clustred index the data is physically sorted and in case of non-clustered the data is logically sorted.

2 - In case clustred index the leaf node of index has the actual data but in case of non-clustered the leaf node has the index of data.

3 - Clustered index is only one in a table but many non-clustered may be in a table.(Reasone is : the leaf node of clustered index is data page which is physically sorted and data page can only one so a table can have only one clusterd index and many non-clustered index because it has the reference of data and reference may be any number of count.)

[ varchar & nvarchar ]

1 - nvarchar datatype is used to store the unicode data (multilanguage data : japanies, chainees etc) and varchar is used to store non-unicode data.

2 - varchar use 1 byte per char and nvarchar use 2 byte per char.(nvarchar consume double space than varchar)

[ charindex & patindex ]

Both are used to find the starting pasition og the given string in expression.

1 - Wildcard charectors can not be used in charindex but you can use wildcard charectors in patindex.

2 - % sign is needed on the front & back of the pattern in case of patindex.

3 - You can not define any pattern in case of charindex but you can define the pattern in patindex.

[ Cast & Convert ]

The goal of both are same but you should use the Cast() because this ANSI-92 syntax and Convert is used when you need a specific style of your data.
So moslty Convert() is used in case of DataTime convertion where you need
a specific style of your DataTime.

[ Replce & Stuff ]

Replace function is used to replace the all occurance a string into expression by give new string.

Replace(Expression,StringTobeReplaced,NewStringToReplace)

EX : select Replace('MS SQL Server','S','M')
EX : select Replace('MS SQL Server','SQL','NewSQL')

If you want to replace the first occurance of a string, then Replace will not work, since it always replaces all the occurrences of the string. But Stuff would, since it only replaces the string it finds at the starting location we tell it for the number of chars we want it to replace.

stuff(Expression,StartLocation,TillLocation,NewString)

EX : select stuff('Hi Manish',4, 6,'Mukesh')


[ Inline Table Valued Function & Multi-Statement Table Valued Function ]

Both are User defined function and return a table.

1 - In Inline table valued function, we need not to define the return table defination but in Multivalued table valued function, you need to defined the return table defination.

2 - In Inline table valued function, You need not to write the Begin and End Statement but in Multivalued table valued function, you need to write.

3 - In Inline table valued function, you can write only one select statement nothing else but in Multivalued table valued function, you can write any business logic and any number of select statement.

[ Temp Table & Table Varible ]

1 - Temp table is initially is stored in TempDB but Table variable is first stored in memory and after a specifc size it is also stored in TempDB.

2 - Transection logs are not recorded for table variable but are mentained for Temp table.

3 - Any procedure with Temp table can not be precomplied while an execution plan of procedure with table variable can be complied in advanced.

4 - Table variable exist only in the same scope as variable.

5 - Table variable can not have the non-clustered index but Temp table can have.

6 - You can not create the constraint in the table varible while you can create in Temp table.

7 - You can not create the default value on table variable column while you can create on Temp table column.

[ Table Scan & Index Scan & Index Seek ]

When you pass a SQL query to SQL Server then an execution plan is created for the query and data is search by either Table Scan or Index Scan or Index Seek.

Table Scan is performed when your table does not have any index or Clustered index.

Index Scan is performed when your table have the indexes but index is not used to search the data.

Index Seek is performed when data is searched by the indexes.

Regards
Manish

Power of Merge Statement in Sql Sever 2008

Merge Statement

The SQL Server 2008 provide a new and very importent feature that is Merge Statement.

Using the Merge, you can perform many statement like (Insert, Update, Delete) in a single statement.

To implement Merge statement, the following WHEN clauses are required:

- WHEN MATCHED THEN [INSERT, UPDATE, DELETE]
- WHEN NOT MATCHED [BY TARGET] THEN [INSERT, UPDATE, DELETE]
- WHEN NOT MATCHED BY SOURCE THEN [INSERT, UPDATE, DELETE]

I am explaining a real time problem which is easily handeled by Merge Statment.

EX :
------

Suppose you have a Customer table in your databse like

declare @Customer table(CustID int,CustName varchar(10),PhoneNo int)

insert into @Customer(CustID,CustName,PhoneNo)
values(1,'C1',101),(2,'C2',102),(3,'C3',103),(4,'C4',104),(5,'C5',105)

select * from @Customer


And you get a new Customer Table is like this

declare @CustomerNew table(CustID int,CustName varchar(10),PhoneNo int)

insert into @CustomerNew(CustID,CustName,PhoneNo)
values(2,'C2',1002),(4,'C4',1004),(6,'C6',1006)

select * from @CustomerNew


Task - 1

And you are assign a task that In Customer table some of Customers phonenumber has been changed and some are new customers then you need to update customer phonenumber in your customer table and also insert new customer details in customer table from New Customer Table.

Then generally before MS SQL 2008, you need to follow 2 steps : 1 for Update and 1 for Insert. But using New Merge Statement you can do this in a single statemnet like :

;merge @Customer as T
using @CustomerNew as S
on (T.CustID = S.CustID)
when matched then
update set T.PhoneNo = S.PhoneNo
when not matched then
insert(CustID,CustName,PhoneNo) values(S.CustID,S.CustName,S.PhoneNo)

select * from @Customer


Task - 2

And you are assign an another task that In Customer table some of Customers phonenumber has been changed and some are new customers then you need to update customer phonenumber in your customer table and also insert new customer details in customer table from New Customer Table and delete those customers which are not in New Customer Table.

Then generally before MS SQL 2008, you need to follow 3 steps : 1 for Update and 1 for Insert and 1 for Delete. But using New Merge Statement you can do this in a single statemnet like :

;merge @Customer as T
using @CustomerNew as S
on (T.CustID = S.CustID)
when matched then
update set T.PhoneNo = S.PhoneNo
when not matched then
insert(CustID,CustName,PhoneNo) values(S.CustID,S.CustName,S.PhoneNo)
when not matched by source then
delete;

select * from @Customer

Regards
Manish

Implementation of Pivot & UnPivot

Pivot

[ Just Copy & Paste ]

declare @TBL table(Cust varchar(10),Product varchar(10),QTY int)

insert into @TBL(Cust,Product,QTY) values('C1','P1',5)
insert into @TBL(Cust,Product,QTY) values('C1','P2',2)
insert into @TBL(Cust,Product,QTY) values('C2','P1',1)
insert into @TBL(Cust,Product,QTY) values('C2','P2',10)

select * from @TBL

select Product,C1,C2
from
(
select Cust,Product,QTY from @TBL
) up
pivot (sum(QTY) for Cust in (C1,C2)) as Pvt
order by Product

UnPivot

[ Just Copy & Paste ]

declare @TBL table(Product varchar(10),C1QTY int,C2QTY int)

insert into @TBL(Product,C1QTY,C2QTY) values('P1',5,1)
insert into @TBL(Product,C1QTY,C2QTY) values('P2',2,10)

select * from @TBL

select Cust,Product,QTY
from
(
select Product,C1QTY,C2QTY from @TBL
) up
unpivot (QTY for Cust in (C1QTY,C2QTY)) as UnPvt
order by Cust

Use of Fill Factor in Index

Fill Factor

While createting the indexes on the table, you can set the fill factor for the index.(Default value of Fill factor is 0))

Fill factor is use to reserve the space on an index page to avoid the page spliting.

[Page Spliting : Page Spliting is internal machanism that server use to make the room for new element in a file. If an index page splits then system consume more disk I/O to read the index alloction map (IAM). And new pages are not created sequentially, so fill factor leave a little room
to grow.]

When creating an index, you can specify a Fill factor to leave the extra gap & reserve a percentage of free space on each leaf level page of the index for future expension.

The fill factor value is a % of 0 to 100 that specify how much to fill the data page after the index is created. A value of 100 means the page will be full & will take the least amount of storage space.

[Note : there is a diffrence betwwen the value of fill factor is 0 or 100. In case of 100 the index has some space but in the case of 0 no space.]

EX :
-------

If you set the value of fill factor is 80 meanse 20% space will be left to grow up the data.

TC of it :
-------------
- Fill factor value will be set when there are lot of changed(Insert, Delete, Update) in your table.

- If your table is read only or minimum change in data (in case of DWH where only historic data is stored) then you should not set the fill factor value. (i.e default value (0) is fine)

Regards
Manish

Tips @ Write a better SQL Code

Things to be remembered while writting a SQL code :

1 - Always use set nocount on.

2 - Avoid the use of Distinct keyword and use Group By.

3 - Avoide the use of Order By clause.

4 - Avoid the use of IN keyword use Exists or inner join.

5 - Avoid the use of NOT IN keyword use NOT Exists.

6 - Dont use the cursor use while loop with the help of identity column.

7 - Select only required field in query not use select *.

8 - In case of dynamic query always use sp_executesql instead of execute.

9 - Avoid use of temp table use table variable or CTE.

10 - Do not use the SP_ prefix with stored procedure name.

11 - Avoid any operation on the fields where possible. Some operations will prevent the use of index on this field even if it exist.
EX: where cast(dtfield as varchar(20)) = @DTval

Regards
Manish

Concurrency Problem in SQL Server

There are four concurrency problem in Sql Server.

1 - Lost Update
2 - Non-Repeatable Read
3 - Dirty Read
4 - Phantom Read

1 - Lost Update

This type of problem occure when two transections modify the same data at the same time and the transection that complete first is lost by the second transection.

2 - Non-Repeatable Read

This type of problem occure when a transection is able to read same row multiple times and get a diffrent value each time.

3 - Dirty Read

This type of problem is a special case of Non-Repeatable Read. This occure if you run a report and get the data which are not commited yet and can be rollbacked by a transection.

4 - Phantom Read

This type of problem occure when you have read a record in first read and when you edit this record then you get the error then the record is not exist. This problem is called Phantom Read.

Regards
Manish

Implementation of Ranking Function in Sql Server

Ranking Function :

Ranking functions are new feature in Sql Sever 2005. Ranking functions are functions which allow you to create sequentially numbers.

There are 4 Ranking functions in sql Server :

1 - Row_Number()
2 - Rank()
3 - Dense_rank()
4 - NTILE(n) (Here : n is any number to group the rows in recordset)


Implementation :

[ Just copy & Paste ]

declare @TBL table(EmployeeName Varchar(50),Gender char(1),Age int)

insert into @TBL(EmployeeName,Gender,Age) values('Manish Pathak','M',20)
insert into @TBL(EmployeeName,Gender,Age) values('Milan Kumar','M',15)
insert into @TBL(EmployeeName,Gender,Age) values('Kiran Sharma','F',18)
insert into @TBL(EmployeeName,Gender,Age) values('Vikas Yadav','M',18)
insert into @TBL(EmployeeName,Gender,Age) values('Parul Saxena','F',15)
insert into @TBL(EmployeeName,Gender,Age) values('Rahul Saxena','M',19)

-- 1 - Row_Number()

select SrNo = ROW_NUMBER() over (order by EmployeeName),EmployeeName,Gender,Age from @TBL

select SrNoByGender = ROW_NUMBER() over (partition by Gender order by EmployeeName),EmployeeName,Gender,Age from @TBL

-- 2 - Rank()

select SrNoRankByGender = rank() over (order by Gender),EmployeeName,Gender,Age from @TBL

select SrNoRankByAge = rank() over (partition by Age order by EmployeeName),EmployeeName,Gender,Age from @TBL

-- 3 - Dense_rank()

select SrNoRankByGender = dense_rank() over (order by Gender),EmployeeName,Gender,Age from @TBL

select SrNoRankByAge = dense_rank() over (partition by Age order by EmployeeName),EmployeeName,Gender,Age from @TBL

-- 4 - NTILE(n)

select EmployeeName,Gender,Age,AgeGroup = NTILE(3) over (order by Age) from @TBL

[Note : The difrence between Rank() & Dense_Rank() function is only that the Rank() function provide the gap between number based on RowCount and Dense_Rank() function does not provide the gap between number]


Regards
Manish

SQL Injection

Sql injection is an attack in which malicious code is inserted into string that are later passd to the Sql server for parsing & execution.

EX :
-------
var Shipcity = Request.Form("ShipCity")
var SQL = 'select * from Order where Shipcity =''"+Shipcity+"''
exec(SQL)

If the user is promted to enter a cityname(like Delhi) then the qry will be

[ select * from Order where Shipcity = 'Delhi' ]

assume that an expert user enter the cityname as
(Delhi';drop table order--)

In this case your qry will be:

[ select * from Order where Shipcity = 'Delhi';drop table order--]

Note : -- is comment in sql server

And when this query will be executed then it will first select the row based in passed CityName and then drop your table form the database.

This type of problem is called SQL Injection:

TC of it by :
---------------
1 - Remove all special chars from user input.
2 - Always use SP instead of direct QRY.
3 - Avoid to take the filter condition data in txtbox use dropdown as possible as.

Regards
Manish

How to break the FullName of a Employee into First & Last Name

[ Just Copy & Paste ]

declare @TBL table(EmployeeName varchar(100))

insert into @TBL(EmployeeName)
values('Manish Pathak'),('Raj Kumar'),('Rakesh Singh'),('Suresh Panday'),('Anil Kare')

select
EmployeeName as 'FullName',
SUBSTRING(EmployeeName,1,PATINDEX('% %',EmployeeName)-1) as 'FirstName',
SUBSTRING(EmployeeName,PATINDEX('% %',EmployeeName),LEN(EmployeeName)) as 'LastName' from @TBL

How to get last day of the given month & How to get the Month number of Month Name?

Trick - Get the first day of the month, then add 1 month to it, then subtract 1 day

[ Just Copy & Paste ]

declare @Month int,@Year int

declare @LastDayofMonth varchar(25)

set @Month = 2
set @Year = 2012

set @LastDayofMonth = dateadd(dd,-1,Dateadd(mm,1,cast(@Month as varchar(2))+'/1/'+cast(@Year as varchar(4))))

select CONVERT(datetime,@LastDayofMonth,106)

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

declare @MonthName varchar(25)

set @MonthName = 'September'
set @MonthName = 'Sep'

select month(CAST(@MonthName + '1 2001' as datetime))

Remove Duplicate Row without PK

[ Just Copy & Paste ]

declare @TBL table(EmpID int)

insert into @TBL(EmpID)
values(90020),(90020),(90021),(90022),(90023),(90022),(90025),(90022)

select * from @TBL order by EmpID

;with TBLCTE(EmpID,Ranking)
AS
(
select
EmpID,
Ranking = DENSE_RANK() over (PARTITION BY EmpID order by newID())
from @TBL
)
delete from TBLCTE where Ranking > 1

select * from @TBL order by EmpID

Employee - Manager hierarchy

[ Just Copy & Paste ]

declare @TBLEmployee table(EmpID int,EmpName varchar(10),MgrID int null)

insert into @TBLEmployee values(101,'Boss',null)
insert into @TBLEmployee values(1,'A',101)
insert into @TBLEmployee values(2,'B',3)
insert into @TBLEmployee values(3,'C',101)
insert into @TBLEmployee values(4,'D',2)

;WITH DirectReports (EmpID,EmpName, MgrID, MgrName)
AS
(
-- Anchor member definition
SELECT e.EmpID, e.EmpName, e.MgrID,null--,0 AS Level
FROM @TBLEmployee AS e
WHERE e.MgrID IS NULL
UNION ALL
-- Recursive member definition
SELECT e.EmpID, e.EmpName, e.MgrID,eM.EmpName--,Level + 1
FROM @TBLEmployee AS e
INNER JOIN @TBLEmployee AS eM
ON e.MgrID = eM.EmpID
)
select * from DirectReports

Paging in SQL Server (2008)

[ Just Copy & Paste ]


declare @customers table(custid int,cusname varchar(50))

insert into @customers(custid,cusname)
values(1,'C1'),(2,'C2'),(3,'C3'),(4,'C4'),(5,'C5'),(6,'C6'),(7,'C7'),(8,'C8'),(9,'C9'),(10,'C10')

declare
@PageSize int, @PageNumber int

set @PageSize = 5
--set @PageNumber = 1
set @PageNumber = 2

Declare @RowStart int
Declare @RowEnd int

if @PageNumber > 0
Begin

SET @PageNumber = @PageNumber -1

SET @RowStart = @PageSize * @PageNumber + 1
SET @RowEnd = @RowStart + @PageSize - 1

end

;with cust as
(
select custid, cusname, row_number() over (order by custid) RowNumber
from @customers
)
select custid, cusname from cust where RowNumber >= @RowStart and RowNumber <= @RowEnd