Maximum Capacity Specification For SQL Server

Following tables specify the maximum sizes and numbers of various objects defined in Microsoft SQL Server Denali, 2008 and 2005 components.

SQL Server Database Engine object	SQL Server Denali (32-Bit)	SQL Server Denali (64-Bit)	SQL Server 2005 (32-Bit)	SQL Server 2005 (64-Bit)	SQL Server 2008 (32-Bit)	SQL Server 2008 (64-Bit)
Batch size1	65,536 * Network Packet Size	65,536 * Network Packet Size	65,536 * Network Packet Size	65,536 * Network Packet Size	65,536 * Network Packet Size	65,536 * Network Packet Size
Bytes per short string column	8,000	8,000	8,000	8,000	8,000	8,000
Bytes per GROUP BY, ORDER BY	8,060	8,060	8,060	8,060	8,060	8,060
Bytes per index key2	900	900	900	900	900	900
Bytes per foreign key	900	900	900	900	900	900
Bytes per primary key	900	900	900	900	900	900
Bytes per row8	8,060	8,060	8,060	8,060	8,060	8,060
Bytes in source text of a stored procedure	Lesser of batch size or 250 MB	Lesser of batch size or 250 MB			Lesser of batch size or 250 MB	Lesser of batch size or 250 MB
Bytes per varchar(max),varbinary(max), xml, text, orimage column	2^31-1	2^31-1	2^31-1	2^31-1	2^31-1	2^31-1
Characters per ntext ornvarchar(max) column	2^30-1	2^30-1	2^30-1	2^30-1	2^30-1	2^30-1
Clustered indexes per table	1	1	1	1	1	1
Columns in GROUP BY, ORDER BY	Limited only by number of bytes	Limited only by number of bytes	Limited only by number of bytes	Limited only by number of bytes	Limited only by number of bytes	Limited only by number of bytes
Columns or expressions in a GROUP BY WITH CUBE or WITH ROLLUP statement	10	10	10	10	10	10
Columns per index key7	16	16	16	16	16	16
Columns per foreign key	16	16	16	16	16	16
Columns per primary key	16	16	16	16	16	16
Columns per nonwide table	1,024	1,024	1,024	1,024	1,024	1,024
Columns per wide table	30,000	30,000	4,096	4,096	30,000	30,000
Columns per SELECT statement	4,096	4,096	1,024	1,024	4,096	4,096
Columns per INSERT statement	4096	4096	Maximum value of configured connections	Maximum value of configured connections	4096	4096
Connections per client	Maximum value of configured connections	Maximum value of configured connections	524,258 terabytes	524,258 terabytes	Maximum value of configured connections	Maximum value of configured connections
Database size	524,272 terabytes	524,272 terabytes	32,767	32,767	524,272 terabytes	524,272 terabytes
Databases per instance of SQL Server	32,767	32,767	32,767	32,767	32,767	32,767
Filegroups per database	32,767	32,767	32,767	32,767	32,767	32,767
Files per database	32,767	32,767			32,767	32,767
File size (data)	16 terabytes	16 terabytes	16 terabytes	16 terabytes	16 terabytes	16 terabytes
File size (log)	2 terabytes	2 terabytes	2 terabytes	2 terabytes	2 terabytes	2 terabytes
Foreign key table references per table4	253	253	253	253	253	253
Identifier length (in characters)	128	128	128	128	128	128
Instances per computer	50 instances on a stand-alone server for all SQL Server editions.	50 instances on a stand-alone server.	50 instances on a stand-alone server for all SQL Server 2005 editions except for Workgroup Edition. Workgroup Edition supports a maximum of 16 instances.	50 instances on a stand-alone server.	50 instances on a stand-alone server for all SQL Server editions except for Workgroup. Workgroup supports a maximum of 16 instances per computer.	50 instances on a stand-alone server.
			SQL Server 2005 supports 25 instances on a failover cluster.	25 instances on a failover cluster.	SQL Server supports 25 instances on a failover cluster.	25 instances on a failover cluster.
Length of a string containing SQL statements (batch size)1	65,536 * Network packet size	65,536 * Network packet size	65,536 * Network packet size	65,536 * Network packet size	65,536 * Network packet size	65,536 * Network packet size
Locks per connection	Maximum locks per server	Maximum locks per server	Maximum locks per server	Maximum locks per server	Maximum locks per server	Maximum locks per server
Locks per instance of SQL Server5	Up to 2,147,483,647	Limited only by memory	Up to 2,147,483,647	Limited only by memory	Up to 2,147,483,647	Limited only by memory
Nested stored procedure levels6	32	32	32	32	32	32
Nested subqueries	32	32	32	32	32	32
Nested trigger levels	32	32	32	32	32	32
Nonclustered indexes per table	999	999	249	249	999	999
Number of distinct expressions in the GROUP BY clause when any of the following are present: CUBE, ROLLUP, GROUPING SETS, WITH CUBE, WITH ROLLUP	32	32			32	32
Number of grouping sets generated by operators in the GROUP BY clause	4,096	4,096			4,096	4,096
Parameters per stored procedure	2,100	2,100	2,100	2,100	2,100	2,100
Parameters per user-defined function	2,100	2,100	2,100	2,100	2,100	2,100
REFERENCES per table	253	253	253	253	253	253
Rows per table	Limited by available storage	Limited by available storage	Limited by available storage	Limited by available storage	Limited by available storage	Limited by available storage
Tables per database3	Limited by number of objects in a database	Limited by number of objects in a database	Limited by number of objects in a database	Limited by number of objects in a database	Limited by number of objects in a database	Limited by number of objects in a database
Partitions per partitioned table or index	15,000	15,000	1,000	1,000	1,000	1,000
Statistics on non-indexed columns	30,000	30,000	2,000	2,000	30,000	30,000
Tables per SELECT statement	Limited only by available resources	Limited only by available resources	256	256	Limited only by available resources	Limited only by available resources
Triggers per table3	Limited by number of objects in a database	Limited by number of objects in a database	Limited by number of objects in a database	Limited by number of objects in a database	Limited by number of objects in a database	Limited by number of objects in a database
Columns per UPDATE statement (Wide Tables)	4096	4096	249 nonclustered and 1 clustered	249 nonclustered and 1 clustered	4096	4096
User connections	32,767	32,767	32,767	32,767	32,767	32,767
XML indexes	249	249	249	249	249	249

New Feature and Enhancement In SQL Server 2012 (Code Named Denali)

SQL Server 2011 (Code Named Denali) CTP 3 has been launched and Can be downloaded from following link. Denali has come with many exciting features for DBAs, BI Developers and SQL Developers. I have found lots of new functions which were most awaited. There are certain function which are easy to use and will make SQL Developers life very easy.

I have explored some of the feature and found them really useful from development point of view.

Download : Microsoft Download Center

• Paging With SQL Server 2011 (Code Named Denali) Using Offset Clause.
• CONCAT() New Function In SQL Server 2011 (Code Named Denali)
• Date Time Format With New FORMAT() function In SQL Server 2011 (Code Named Denali)
• Formatting Numeric And Currency With FORMAT() Along With CULTURE
• Analytic Function In SQL Server 2011 (Code Named Denali)
• CHOOSE() Function In SQL Server 2011 (Code Named Denali)
• New Inline function IIF In SQL Server 2011 (Code Named Denali)

Analytic Function LEAD LAG In SQL Server, (Reading Next And Previous Row In SQL Server 2011 (“Code Named Denali”)

 

SQL Server 2011 Comes with 8 new function which are grouped under Analytic Function Group. Here Will We Discuss about only two functions listed below. These function must be used with Over and Partition By Clause

1. LEAD()
2. LAG()

Create Following Table With Script Provided Below

CREATE TABLE [dbo].[Records](
[Id] [int] IDENTITY(1,1) NOT NULL,[EmpName] [varchar](20) NULL,
    [AccountNo] [varchar](12) NULL,
    [IsActive] [tinyint] NULL,
    [Salary] [decimal](22, 2) NULL,
    [Department] [varchar](50) NULL
)
GO
INSERT [dbo].[Records] ([EmpName], [AccountNo], [IsActive], [Salary], [Department]) 
VALUES 
('Kuldeep Bisht', N'012398760987', 1, CAST(7680.00 AS Decimal(22, 2)), N'IT'),
('Vishal Verma', N'987026749876', 3, CAST(6700.00 AS Decimal(22, 2)), N'BPO'),
('Rakesh Kumar', N'876894326754', 1, CAST(4500.00 AS Decimal(22, 2)), N'TESTING'),
('Sandeep Verma', N'786798564329', 1, CAST(9800.00 AS Decimal(22, 2)), N'IT'),
('Revinder Singh', N'999867543987', 1, CAST(7850.00 AS Decimal(22, 2)), N'TESTING'),
('Seema Singh', N'876549879898', 2, CAST(4560.00 AS Decimal(22, 2)), N'BPO'),
('Ashutosh', N'785640987567', 1, CAST(7680.00 AS Decimal(22, 2)), N'IT'),
('Devesh Gupta', N'776623458123', 2, CAST(6600.00 AS Decimal(22, 2)), N'TESTING'),
('Satinder Negi', N'768098723456', 1, CAST(3500.00 AS Decimal(22, 2)), N'BPO'),
('Meenakshi', N'009812346785', 1, CAST(9800.00 AS Decimal(22, 2)), N'BPO'),
('Manoj Pandey', N'767689900145', 3, CAST(7800.00 AS Decimal(22, 2)), N'IT'),
('Sanjay Rana', N'980765430974', 1, CAST(8800.00 AS Decimal(22, 2)), N'SALE')
Go

Lets Explore Analytic Functions

LEAD()

Syntax :

LEAD(scalar_expression [,offset] [,default])     OVER ( [ partition_by_clause ] order_by_clause )

LEAD Function : It is used to get result from next row in current row. You can define the row number to read from current row. 

Offset : Row Number To Read From Current Row. Default Value is 1 i.e. Next Row.

Default : if Next Row Is not Found then Return Default Value Else Null Is Returned

Select Department,EmpName,Salary,
LEAD(Salary) Over (Partition By Department Order By Salary Desc) As NextHighest,
LEAD(Salary,2) Over (Partition By Department Order By Salary Desc) As Second_Next_Highest
From Records

As you can see in above result of lead function each row shows value from its next row. don’t you think its very exciting without using self join or any other complex query we are able to get next row values. Its really simple and easy to use.

In above query you saw that each row reads value from next row In (Next_Highest) Column and where next row doesn’t exists against a department it show null values. you can handle and change this default behavior of LEAD() function. by using offset  and default parameter of LEAD() function.

LAG()

LAG(scalar_expression [,offset] [,default])     OVER ( [ partition_by_clause ] order_by_clause )

LAG Function : It is used to get result from Previous row in current row. You can define the row number to read from current row. 

Offset : Row Number To Read From Current Row. Default Value is 1 i.e. Previous Row.

Default : if Previous Row Is not Found then Return Default Value Else Null Is Returned.

Select Department,EmpName,Salary,
LAG(Salary) Over (Partition By Department Order By Salary ) As Previous_Highest,
LAG(Salary,2) Over (Partition By Department Order By Salary) As Second_Previous_Highest
From Records

Output ;

So Now Its Very Easy To Get Or Check Values From Previous and Next Rows With Ease and Little Effort.

Formatting Numeric Value, Currency And DateTime With Culture In SQL Server 2012 (“Code Named Denali”)

 

Format() is most awaiting function in SQL Server 2011 (“Denali”) most of the developers faces lots of problem in formatting datetime, numeric value and currency. But with the introduction of FORMAT() Function formatting has become more and more easy. FORMAT() function is simple and easy to use and a single function to format numeric, data time and currency format. The best part is FORMAT() function is culture aware.

Lets Explore It

To Generate Following CodeNumber. Series

00001,00002,00004….So On

We Use Replicate Function

Select REPLICATE('0',5-Len('1'))+'1' AS Data

Now With FORMAT() Function

Select FORMAT(1,'00000') As Data

Decimal Format

Showing different decimal format Is very easy with Format() function

Select 1 Id,'.0' as FormtCode, FORMAT(20.5678,'.0') As Data
Union All
Select 2 Id,'.00' as FormtCode,FORMAT(20.5678,'.00') As Data
Union All
Select 3 Id,'.000' as FormtCode,FORMAT(20.5678,'.000') As Data
Union All
Select 4 Id,'.0000' as FormtCode,FORMAT(20.5678,'.0000') As Data
Union All
Select 5 Id,'.00000' as FormtCode,FORMAT(20.5678,'.00000') As Data
Order By Id

Output :

 

Currency Format :

Select 'c0' as FormatCode, Format(177,'c0') as Format
Union All
Select 'c1' as FormatCode,Format(177,'c1') as Format
Union All
Select 'c2' as FormatCode,Format(177,'c2') as Format
Union All
Select 'c3' as FormatCode,Format(177,'c3') as Format

Output:

Currency Format With Culture :

Select 'en-us' as FormatCode, Format(1609,'c','en-us') as Format
Union All
Select 'en-in' as FormatCode,Format(1609,'c','en-in') as Format
Union All
Select 'hi-in' as FormatCode,Format(1609,'c','hi-in') as Format
Union All
Select 'en-gb' as FormatCode,Format(1609,'c','en-gb') as Format
Union All
Select 'ja-jp' as FormatCode,Format(1609,'c','ja-jp') as Format
Union All
Select 'de-de' as FormatCode,Format(1609,'c','de-de') as Format
Union All
Select 'fr-fr' as FormatCode,Format(1609,'c','it-it') as Format

Output:

 

Using Culture With Data Time

Select 1 Id,'English (en)' As FormatCode,FORMAT(Getdate(),'dddd, dd MMMM yyy','en') AS Format
Union
Select 2 Id,'Franch (fr)' As FormatCode,FORMAT(Getdate(),'dddd, dd MMMM yyy','fr') AS Format
Union
Select 3 Id,'Hindi (hi)' As FormatCode,FORMAT(Getdate(),'dddd, dd MMMM yyy','hi') AS Format
Union
Select 4 Id,'Japenese (ja)' As FormatCode,FORMAT(Getdate(),'dddd, dd MMMM yyy','ja') AS Format
Union
Select 5 Id,'Urdu (ur)' As FormatCode,FORMAT(Getdate(),'dddd, dd MMMM yyy','ur') AS Format
Union
Select 6 Id,'Gujrati (gu) ' As FormatCode,FORMAT(Getdate(),'dddd, dd MMMM yyy','gu') AS Format
Union
Select 7 Id,'Bangla (bn) ' As FormatCode,FORMAT(Getdate(),'dddd, dd MMMM yyy','bn') AS Format
Union
Select 8 Id,'Malyalam (ml) ' As FormatCode,FORMAT(Getdate(),'dddd, dd MMMM yyy','ml') AS Format
Union 
Select 9 Id,'Kannada (kn) ' As FormatCode,FORMAT(Getdate(),'dddd, dd MMMM yyy','kn') AS Format
Union All
Select 10 Id,'Punjabi (pa)' As FormatCode,FORMAT(Getdate(),'dddd, dd MMMM yyy','pa') AS Format

Output :

So Format() Function is very helpful and provides easy access to different format without putting much effort and writing complex logic.

Date Time Formatting With Format() Function In SQL Server 2012 (“Code Named Denali”)

 

Format:  Is the New Function Introduced with SQL Server Denali. It Converts Values into specified format. This function provides more flexibility and ease of getting different format. Here we will see how you can desired format of date and time.

Lets Explore FORMAT() for Date And Time.

Day Format (d)

Select 1 as Id,'d' as FormatCode,FORMAT(GETDATE(),'d') as Format
Union All
Select 2 as Id,'dd' as FormatCode,FORMAT(GETDATE(),'dd') as Format
Union All
Select 3 as Id,'ddd' as FormatCode,FORMAT(GETDATE(),'ddd') as Format
Union All
Select 4 as Id,'dddd' as FormatCode,FORMAT(GETDATE(),'dddd') as Format
Order By Id

 

Month Format(m)

Select 1 as Id,'M' as FormatCode,FORMAT(GETDATE(),'M') as Format
Union All
Select 2 as Id,'MM' as FormatCode,FORMAT(GETDATE(),'MM') as Format
Union All
Select 3 as Id,'MMM' as FormatCode,FORMAT(GETDATE(),'MMM') as Format
Union All
Select 4 as Id,'MMMM' as FormatCode,FORMAT(GETDATE(),'MMMM') as Format
Order By Id

 

Year Format (y)

Select 1 as Id,'y' as FormatCode,FORMAT(GETDATE(),'y') as Format
Union All
Select 2 as Id,'yy' as FormatCode,FORMAT(GETDATE(),'yy') as Format
Union All
Select 3 as Id,'yyy' as FormatCode,FORMAT(GETDATE(),'yyy') as Format
Union All
Select 4 as Id,'yyyy' as FormatCode,FORMAT(GETDATE(),'yyyy') as Format
Order By Id

Now With Above Different Format Combination You Can Achieve Different Date Format, Some Examples Are Given Below

Select 0 Id,'dd-MM-yyy' as FormatCode , FORMAT(GETDATE(),'dd-MM-yyy') As Format
Union All
Select 1 Id,'dd MMM' as FormatCode , FORMAT(GETDATE(),'dd MMM') As Format
Union All
Select 2 Id,'dd.MM.yy' as FormatCode,FORMAT(GETDATE(),'dd.MM.yy') As Format
Union All
Select 3 Id,'dd/MM/yyy' as FormatCode, FORMAT(GETDATE(),'dd/MM/yyy') As Format
Union All
Select 4 Id,'dd MMM yy (ddd)' as FormatCode,FORMAT(GETDATE(),'dd MMM yy (ddd)') As Format
Union All
Select 5 Id,'dd MMMM yyyy (dddd)' as FormatCode,FORMAT(GETDATE(),'dd MMMM yyyy (dddd)') As Format
Union All
Select 6 Id,'MMMM yyyy' as FormatCode,FORMAT(GETDATE(),'MMMM yyyy')
Order By Id

Time Part(Hour, Minute, Second and MiliSecond)

Select 1 Id,GETDATE() DateValue,'Hour' FormatCode,FORMAT(GETDATE(),'hh') as Format
Union All
Select 1 Id,GETDATE() DateValue,'Minute' FormatCode,FORMAT(GETDATE(),'mm') as Format
Union All
Select 1 Id,GETDATE() DateValue,'Second' FormatCode,FORMAT(GETDATE(),'ss') as Format
Union All
Select 5 Id,GETDATE() DateValue,'MiliSecond' FormatCode,FORMAT(GETDATE(),'fff') As Format

Examples of Time Formats

Select 1 as Id,'hh:mm:ss tt' as FormatCode,FORMAT(GETDATE(),'hh:mm:ss tt') As Format
Union
Select 2 as Id,'hh:mm tt' as FormatCode,FORMAT(GETDATE(),'hh:mm tt') As Format
Union 
Select 3 as Id,'hh:mm' as FormatCode,FORMAT(GETDATE(),'hh:mm') As Format
Union
Select 4 as Id,'hh ''Hours'' mm ''Mintues'' ss ''Seconds''' as FormatCode,
FORMAT(GETDATE(),'hh ''Hours'' mm ''Mintues'' ss ''Seconds''') As Format
Order By Id

I Am Really happy with the introduction of FORMAT() function in SQL Server Denali as It saves lot of effort in writing sql to get desired format.

CHOOSE() Function In SQL Server 2012 (Code Named Denali)

Choose is the new function introduced in SQL Server 2011. This function helps in finding value from a the comma separated list based on position.

You Might have come across scenario where you have used case statement of find value of based on some condition.

Support based in index value you have to find week day name then probably case statement is appropriate for it.

Declare @WeekIndex Tinyint
Set @WeekIndex=6
Select Case @WeekIndex 
When 1 then 'Monday'
When 2 then 'Tuesday'
When 3 then 'Wednesday'
When 4 then 'Thursday'
When 5 then 'Friday'
When 6 then 'Saturday'
When 7 then 'Sunday'
End As Value

Output :

Value
---------
Saturday

 

Lets Use similar thing with CHOOSE() Function.

Select CHOOSE(6,'Monday','Tuesday','Wednesday','Thursday','Friday','Saturday','Sunday')  As Value

Output :

Value
------------
Saturday

 

Lets Look at following Query Which fetches employee records with status, I have used Case Statement and CHOOSE() function to find Status Of Employee.

Select *,
Case IsActive 
When 1 Then 'Working' 
When 2 Then 'Resigned' 
When 3 Then 'InActive' 
End As Status_With_Case,
CHOOSE(IsActive,'Working','Resigned','IsActive') As Status_With_Choose
from Records

Output :

So you can see ease of using Choose function, however things can be achieved by Case statement also, but the benefit is CHOOSE() is very simple to implement and reduces complexity.

IIF Function In SQL Server 2012 (Code Named Denali)

A New Function IIF (Short For Inline Function) Added to SQL Server 2012. Its proivdes easiest method and alternate of writing case statements and Makes your query more Clear and readable.

Syntax :

IIF ( boolean_expression, true_value, false_value )

Take A look at following example.

This Typical A  Case Statement.

Declare @Str Varchar(30)
Set @Str='A'
Select  (Case When @Str='A' Then 'Its A' Else 'Its Not A' End) As Result

Output :

Result
---------
Its a

 

Now Take A Look How IIF can be used for above statement.

Declare @Str Varchar(30)
Set @Str='A'
Select IIF(@Str='A','Its A','Its Not A') As Result

Output :

Result
---------
Its A

Conditional Order by Clause

Create Table With Following Script

Create Table Records(
Id Int Identity(1,1),
EmpName Varchar(20),
AccountNo Varchar(12)
)
Go
 
Insert Into Records Values
('Kuldeep Bisht','012398760987'),('Vishal Verma','987026749876'),
('Rakesh Kumar','876894326754'),('Saneep Verma','786798564329'),
('Revinder Singh','999867543987'),('Seema Singh','876549879898'),
('Ashutosh','785640987567'),('Devesh Gupta','776623458123'),
('Satinder Negi','768098723456'),('Meenakshi','009812346785'),
('Manoj Pandey','767689900145'),('Sanjay Rana','980765430974')
Go

 

Now Let’s Write a Query With Conditional Order By. However Conditional Order by can also be implemented by Case Statement.

Declare @SortColumn Varchar(30)
Set @SortColumn='EmpName'
Select * from Records 
Order by IIF(@SortColumn='EmpName',EmpName,AccountNo)

Output :

So in all functionality is same as in case statement but IIF is more clear and provides readable code specially when you have nested IIF.

CONCAT() In SQL Server 2011 ("Code Named Denali")

SQL Server 2011 has come with new function CONCAT() with return string after concatenating 2 or more values.

Syntax :

CONCAT ( values1,Value2,Value3….Upto 254 Values)

CONCAT takes Minimum 2 input values and 254 Maximum input Values, In Simple word you can concatenate at least 2 values and at most 254 values.

Let’s Explore :

Select CONCAT('Microsoft','SQL','Server','2011') As Value

Output :

Value
----------------------
MicrosoftSQLServer2011

Concatenate Integer Type With String

Select CONCAT('SQL Server',2011) As Value

Output :

Value
----------------------
SQL Server2011

Concatenate Integer, Date And String

Select CONCAT(GETDATE(),' Is Date Of Year', 2011) As Value

Output :

Value
--------------------------------------------------------------------
Sep 24 2011 11:42AM Is Date Of Year2011

Concatenate Null Value With Any Data type

Select CONCAT('ADDED', NULL)

Output :

Value
-----
ADDED

Concatenate Integer With Integer

Select CONCAT(1,1,1,1) As Value

Output :

Value
------------------------------------------------
1111

So The Beauty Of CONCAT() function is that you need not to convert any value explicitly into string to get result everything is automatically handled by CONCAT() Function. however CONCAT Function Doesn’t Concatenate XML datatype. you need to convert xml data type to varchar explicitly then only you can concatenate xml data type.

Declare @XML1 XML
Declare @XML2 XML
Set @XML1='<table><data>1</data></table>'
Set @XML2='<table><data>1</data></table>'
Select Concat(@XML1,@XML1)

Output :

Msg 257, Level 16, State 3, Line 5
Implicit conversion from data type xml to varchar is not allowed. Use the CONVERT function to run this query.

Now Try Following

Declare @XML1 XML
Declare @XML2 XML
Set @XML1='<table><data>1</data></table>'
Set @XML2='<table><data>1</data></table>'
Select Concat(Convert(Varchar(100),@XML1),Convert(Varchar(100),@XML2))

Output :

Value
----------------------------------------------------------
<table><data>1</data></table><table><data>1</data></table>

Paging In SQL Server 2011 (Code Named “Denali”) , Order By Offset In SQL Server 2011, Limiting Rows In SQL Server

SQL Server 2011 (Code Name Denali) CTP (Community Technology Preview) Has been launched with exciting features and lots enhancement for SQL Developers, BI Developers and DBAs.

You Can Download It From MSDN Download Center

Let’s Start Exploring Some Exciting Features and Enhancement For SQL Developers.

Custom paging always been a challenge for developers. We generally achieve this using some logic in your web page or using RowNumber Function SQL Server or Using TempTable.

Now Paging has been come more easy with SQL Server 2011 Order By Offset Clause Lets See How ?

Create Table and Data With Following Script

Create Table Records(
Id Int Identity(1,1),
EmpName Varchar(20),
AccountNo Varchar(12)
)
Go

Insert Into Records Values
('Kuldeep Bisht','012398760987'),('Vishal Verma','987026749876'),
('Rakesh Kumar','876894326754'),('Saneep Verma','786798564329'),
('Revinder Singh','999867543987'),('Seema Singh','876549879898'),('Ashutosh','785640987567'),('Devesh Gupta','776623458123'),
('Satinder Negi','768098723456'),('Meenakshi','009812346785'),
('Manoj Pandey','767689900145'),('Sanjay Rana','980765430974')
Go

Select * from Records Order by Id
Go

Output :

Now Lets Use Order By With Offset Clause. When You Specify Order By Clause With Offset Then Number Of Rows Specified With Offset are Ignored and rest of result is returned.

Select * from Records 
Order by Id 
Offset 5 Rows

Output :

So You Can See Number of Rows Specified With Offset Has Been Skipped.

Now Suppose You Want To Limit Number Rows After Offset.

Select * from Records 
Order by Id 
Offset 5 Rows 
Fetch Next 5 Rows Only

Now Let’s See How We Can Use Stored Procedure To Return Page Wise Data.

Create Procedure Usp_GetPageWiseData
(
@PageNumber Int,
@RecordPerPage Int
)
AS
Begin
Select * From Records
Order By Id
Offset ((@PageNumber-1)*@RecordPerPage) Rows
Fetch Next @RecordPerPage Rows Only
End
Go

Above Stored Procedure Has Two Parameter

PageNumber : Record Page Number You Want To Retrieve

RecordPerPage : No Of Record To Be Returned In a Page.

Disaster Recovery In SQL Server

Disaster recovery :  It is the process of recovering data when any disaster occurs. It is very important to plan disaster recovery of information as information is very important asset for any organization. Disaster Can be man-made or Natural disaster like Fire or it can also be technical disaster like Disk Failure or system failure.

How to Plan Disaster Recovery ?

Before Planning Disaster Recovery You Must Know How Much “Data Loss Is Accepted By An Organisation”.

Let’s Say Organization Says 15 Min Data Loss Is Accepted.

You Database Planning Can Be

• Full Back In the Morning Or At Less Production Hour (When Your Database Is Not Being Accessed By Many Users).
• Take Transaction Log Backup Every After 15 Min As Max Data Loss Is 15 Min.
• Take Differential Backup Every After 1 Hour.

So Since Above Involves Log Backup. We Must Have Database recovery model either “FULL” Or “BULK LOGGED”.

Full Recovery Model Is Preferred As Point In Time Recovery Is not Guaranteed In BULK LOGGED Recovery Model.

Differential Backup Contains All the Transaction Since Last Full Backup Was Taken.

Now According To Above Planning We Have Following Backup Log History and we Encounter A Database Crash At 5:38 AM.

 

Time	Backup Type
12:00AM	Full Database Backup
12:15AM	Transaction Log Backup
12:30AM	Transaction Log Backup
12:45AM	Transaction Log Backup
1:00AM	Transaction Log Backup , Differential Backup
1:15AM	Transaction Log Backup
1:30AM	Transaction Log Backup
1:45AM	Transaction Log Backup
2:00AM	Transaction Log Backup, Differential Backup
2:15AM	Transaction Log Backup
2:30AM	Transaction Log Backup
2:45AM	Transaction Log Backup
3:00AM	Transaction Log Backup, Differential Backup
3:15AM	Transaction Log Backup
3:30AM	Transaction Log Backup
3:45AM	Transaction Log Backup
4:00AM	Transaction Log Backup, Differential Backup
4:15AM	Transaction Log Backup
4:30AM	Transaction Log Backup
4:45AM	Transaction Log Backup
5:00AM	Transaction Log Backup, Differential Backup
5:15AM	Transaction Log Backup
5:30AM	Transaction Log Backup
5:38AM	Database Failure (Database Crash)

 

Now You Are Left With Above Backup Files and A Crashed Database.

Crash Can Be :

• Disk Failure (Database Files Cannot Be Recovered)
• Data File Has been Corrupted.
• Log File Has Been Corrupted.
• And Many More…

Let’s Say Disk Failure Occurred And Database Files Cannot Be Recovered.

Now With Above Database Backup Files We Have To Do Fastest Recovery

Steps :

1. Restore Full Database Backup Taken At 12:00 AM With No-Recovery(No Recovery Specifies That There Are More Transaction Log Backup To Be Restored).
2. Restore Last Differential Backup Taken At 5:00 AM With No-Recovery (Since Each Differential Backup Contains Transaction Since Last Full Backup).
3. Restore Transaction Log Backup Taken At 5:15 AM With No-Recovery
4. Restore Transaction Log Backup Taken At 5:30 AM With Recovery.

So In Above Scenario Total Database Loss Is 8 Minute.

This 8 Minute database loss can also be minimized. Support You Database has been crashed but Log file is available.

Then You Can Take “Tail Log Backup From Log File” Tail Log Back Is Same as Transaction Log Backup But The Different Is Your Tail Log Backup Will Not Have Meta Data Information as Data File Is not Available.

So, If You Have Tail Log Backup Then You Can Must Apply

Steps :

1. Restore Full Database Backup Taken At 12:00 AM With No-Recovery(No Recovery Specifies That There Are More Transaction Log Backup To Be Restored).
2. Restore Last Differential Backup Taken At 5:00 AM With No-Recovery (Since Each Differential Backup Contains Transaction Since Last Full Backup).
3. Restore Transaction Log Backup Taken At 5:15 AM With No-Recovery
4. Restore Transaction Log Backup Taken At 5:30 AM With No-Recovery.
5. Restore Tail Log Backup Taken After Database Crash With Recovery.
   

So In This Way Data Loss can be minimized By Taking Tail Log Backup.

Database Recovery Model In SQL Server

Recovery Models Are Used To Manage Database Log File For Disaster Recovery. There Are Three Types Of Recovery Model Offered By SQL Server

• Simple
• Full
• Bulk Logged

Every Recovery Model Has Advantage And Disadvantages Over Each Other. Let’s See What They Are? And How They Affect Log File ?.

Simple Recovery Model:

• Its Very Simple as name suggests.
• Log is not maintained in this recovery model only small amount of information is stored in log file.
• Log is truncated on every CheckPoint.
• Transaction Log Backup cannot be taken in this recovery model.
• Point in time recovery is not possible.
• Database Mirroring and Log Shipping is not Possible With This Recovery Model.

Full Recovery Model:

• It Is Default Recovery Model In SQL Server. If You Create any database default recovery model of the database will be Full.
• Until You Take Full Backup of database It acts As Special Recovery Model Called(Pseudo_Simple Recovery Model).
• Full , Differential And Transaction Log Backup Is Possible in this recovery Model and With these combination of Backup you can design your disaster recovery.
• Once You take full backup of database, SQL Server starts preserving transaction log of database.
• The log files grows until you take Transaction log backup of database. if you don’t take regular transaction log backup, your transaction log will keep on growing and will grow until it runs out of disk space. It this case your database may be marked as suspect.
• Point In Time Recovery Is Possible.

Bulk Logged Recovery Model :

• Bulk Logged Recovery Model is similar as Full Recovery Model except it has a special feature called minimal logging.
• Logging of Bulk Operations (Bulk Operation Can Be Re-Building Index, BCP Command) is different then full Recovery Model.
• For Example If You Insert 20000 Rows From CSV file into a database Table then Under Bulk Logged Recovery Model Not Every Rows Is Logged in Transaction Log Rather a Bulk Operation is Logged in Transaction Log.
• It is recommended that Bulk Logged Recovery Model should be used only when you need to perform bulk operation on database.
• Mirroring Is Not Supported With Bulk Logged Recovery Model.
• Point In Time Recovery Is Not Guaranteed.

Conclusion :

• When You Don’t Need Transaction Log Or Point in time recovery is not required Or in other words data loss is accepted then Use Simple Recovery Model
• When You Set This Option For A Database
• Exec Sp_dboption 'DatabaseName','trunc. log on chkpt.','True' 
• It it as good as using a Simple Recovery Model.
• When Your Database is in Full Recovery Model and You need to perform Some Bulk Operation on Database Then
• Switch Database Recovery Model From “FULL” To “BULK LOGGED”
• Perform Bulk Operation
• Switch Database Recovery Model From “BULK LOGGED” To “FULL”

 

How To See  Or Change Recovery Model Of Database ?

Run Following Query To See Recovery Model Of All Databases.

Select name As DatabaseName,recovery_model_desc 
from Sys.databases
Go

Change The Recovery Model Of A Database

Alter Database DatabaseName Set Recovery Full

Alter Database DatabaseName Set Recovery Bulk_Logged

Alter Database DatabaseName Set Recovery Simple

You Can Do This with SQL Server Management Studio  (SSMS)

Select Database From Object Explorer In SSMS Right Click and Choose Property

CTE - Common Table Expression In Sql Server

Applies To SQL Server 2005+

CTE (Common Table Expression) is very useful construct in sql server and very exciting feature added to sql server. CTE is temporary result set which is not available as data in a table or view. prior to CTE there are various scenario where we used complex join, temp Table and cursor.  Here we will how see CTE is helpful over temptable or Cursor.

Syntax Of CTE

;With <CTEName> (Optional ColumnList)
As
(
Select * From TableName
)
Select * From CTE

Let’s Do Some Task With Help Of CTE

Taks 1 : List All The Dates Of Current Month

With CTE
As
(
    Select GETDATE()-DAY(Getdate())+1 As DateList
    Union All
    Select DateList+1 From CTE 
    Where DateList<DateAdd(m,1,GETDATE())-DAY(Getdate())
)
Select CONVERT(Date,DateList) As DateList from CTE

Output : This is recursive use of CTE

Task 2 : Delete Duplicate Records From The Table

Create Table With Following Script

Create Table Orders(OrderNumber Varchar(20),OrderPrice Decimal(20,2))
Go
Insert Into Orders Values
('TR0001',56.70),('TR0002',65.00),('TR0003',35.50),
('TR0002',65.00),('TR0001',56.70),('TR0001',56.70),
('TR0004',36.30),('TR0005',90.20),('TR0003',35.50),
('TR0004',36.30),('TR0006',90.20),('TR0007',35.50)
Go
Select * From Orders 

So as you can see we have many duplicate OrderNumbers.

Now Let’s See how can we delelete Duplicate records using CTE.

;With DeleteDuplicate
As
(
    Select *,ROW_NUMBER() Over (Partition By OrderNumber,OrderPrice 
Order By OrderNumber) As RowNo
    From Orders
)
Select * From DeleteDuplicate

As You can see the out of above query, Row No Is Given To Every OrderNumber, Each Duplicate OrderNumber Has RowNo in Series. Now Next Is to Delete The Duplicate Rows Found. I can simply say delete All Rows Where RowNo>1. Let’s See How ?

Delete the Data Now

;With DeleteDuplicate
As
(
    Select *,ROW_NUMBER() Over (Partition By OrderNumber,OrderPrice Order By OrderNumber) As RowNo
    From Orders
)
Delete From DeleteDuplicate Where RowNo>1
 

Now lets see what we have in table now

Select * From Orders 
Order By OrderNumber

Task 3 : Month Wise Sale Or Data

Create Table With Following Script

Create Table Sale(SaleId  Int Identity(1,1),SaleDate DateTime,BillAmount Decimal(22,2))
Go
Insert Into Sale Values
('2011-01-07',3457.00),('2011-01-12',876.00),('2011-01-27',2346.00),
('2011-04-01',987.00),('2011-04-13',564.00),('2011-04-23',7865.00),
('2011-08-09',11092.00),('2011-08-12',1243.00),('2011-08-20',6657.00),
('2011-09-04',456.00),('2011-09-16',876.00),('2011-09-21',8876.00)
Go

To Achieve Above Task We Generally Write following Query

Select DateName(Month,SaleDate) MonthName,SUM(BillAmount) As TotalSaleAmount
From Sale
Group By Month(SaleDate),DateName(Month,SaleDate)
Order By Month(SaleDate)

as you can see the output below shows only those month where sale happened

MonthName	TotalSaleAmount
April	9416.00
August	18992.00
January	6679.00
September	10208.00

What if the requirement is to get all the month name irrespective of sale.

Let see use of CTE in this scenario

;With CTE
As
(
    Select DATENAME(Month,'1900-01-01') As MonthName,
    DateAdd(M,1,'1900-01-01') As MonthDate,1 As MonthValue
    Union All
    Select DATENAME(Month,MonthDate) As MonthName,
    DATEADD(M,1,MonthDate) As MonthDate,MonthValue+1 as MonthValue
    From CTE Where Year(MonthDate)<=1900
)
Select MonthName,Isnull(SUM(BillAmount),0) As TotalSale
From CTE
Left Join Sale On Cte.MonthValue=Month(SaleDate)
Group By MonthValue,MonthName
Order By MonthValue

Output :

MonthName	TotalSaleAmount
January	6679.00
February	0.00
March	0.00
April	9416.00
May	0.00
June	0.00
July	0.00
August	18992.00
September	10208.00
October	0.00
November	0.00
December	0.00

You can see above the desired result with the help of CTE.

Hope From Above Examples You Might have got Information and Functionality Of CTE, And Now You Can Utilize CTE in Different Scenario According to Requirement.

Find Active Or Long Running Transaction Using DMV

Applies To SQL Server 2005+

DMV : Dynamic Management Views, Provide information about current state of Sql Server. You can use DMV to diagnose or troubleshoot various activity. DMV’s are majorly used to determine performance bottlenecks.

You Can Find All The DMVs Using Following Query

SELECT name, type, type_desc 
FROM sys.system_objects 
WHERE name LIKE 'dm[_]%' ORDER BY name

Description of each DMV can be found at msdn site.

Here We Will See DMVs which can be help us in finding out active transactions.

• sys.dm_tran_session_transactions
• Provides correlation information for associated transactions and sessions.
• sys.dm_exec_sessions
• Provides information about all active user connections and internal tasks
• sys.dm_tran_active_transactions
• Provides transactions for the instance of SQL Server.

Create Table And Populate Data Using Following Script For Performing Tests

Create Database Test_Transaction
Go
Use Test_Transaction
Create Table Test1(Id int Identity(1,1),UserData Varchar(30))
Go
Insert Into User1 Default Values
Go 50

Run Following Command In Query Window

Begin Transaction
Update Test1 Set UserData='Classic' Where Id=1

Note: That Transaction Is Not Completed As No Commit Or Rollback Transaction Has Been Issued.

Now Take New Query Window And Run Following Query To See Is There Any Active Transaction

Select B.session_id,A.transaction_id,C.transaction_begin_time,
DATEDIFF(Second,C.transaction_begin_time,getdate()) TimeTaken_In_Seconds,B.HOST_NAME,B.program_name,B.login_name,
b.login_time as UserLoginTime 
from sys.dm_tran_session_transactions A
Join sys.dm_exec_sessions B On A.session_id=B.session_id 
Join sys.dm_tran_active_transactions C On A.transaction_id =C.transaction_id

Output :

session_id	transaction_id	transaction_begin_time	TimeTaken_In_Seconds	HOST_NAME	program_name	login_name	UserLoginTime
53	1944398	2011-09-17 11:27:42.777	58	STIPLGGNL008	Microsoft SQL Server Management Studio - Query	sa	2011-09-17 11:26:00.783

Please See Following Screen as Above table Is Difficult to Fit in.

The Most Important Is Time Taken By Query Which can be Found In TimeTaken_In_Seconds. If You Wish To See Time In Minutes Then You Can Modify Above Query and Write Minutes In-Place of Second

How To Know Which Query is Running From Above Result ???

You Can Take Session_Id From Above Result And Run Following Query

DBCC InputBuffer(53)

output :

Split Name Into First, Middle and Last Name In Sql Server

Last Week one of my Friend ask "how to split name into first middle and last name.” Over this he also said he tried it but its very confusing and complex So I Thought of writing a blog on it.

Create Following Table For Sample

Create Table Employee (EmpId int Identity(1,1),EmpName Varchar(200))

Go

Insert Into Employee (EmpName)

Values('Kuldeep Singh Bisht'),('Sandeep Verma'),('Manisha')

Go

Select * From Employee

EmpId	EmpName
1	Kuldeep Singh Bisht
2	Sandeep Verma
3	Manisha

So Now We Need a  Query Which Can Split EmpName Into FirstName, LastName and MiddleName.

Soluton 1 :

Split Name In Query

Select Ltrim(SubString(EmpName,1,Isnull(Nullif(CHARINDEX(' ',EmpName),0),1000))) As FirstName,
Ltrim(SUBSTRING(EmpName,CharIndex(' ',EmpName),
CAse When (CHARINDEX(' ',EmpName,CHARINDEX(' ',EmpName)+1)-CHARINDEX(' ',EmpName))<=0 then 0 
else CHARINDEX(' ',EmpName,CHARINDEX(' ',EmpName)+1)-CHARINDEX(' ',EmpName) end )) as MiddleName,
Ltrim(SUBSTRING(EmpName,Isnull(Nullif(CHARINDEX(' ',EmpName,Charindex(' ',EmpName)+1),0),CHARINDEX(' ',EmpName)),
Case when Charindex(' ',EmpName)=0 then 0 else LEN(EmpName) end)) as LastName
From Employee

Go

Output:

EmpId	EmpName	FirstName	MiddleName	LastName
1	Kuldeep Singh Bisht	Kuldeep	Singh	Bisht
2	Sandeep Verma	Sandeep		Verma
3	Manisha	Manisha

 

Solution 2 :

You Create Computed Column In your table So that whenever EmpName Field Is Updated or Inserted FirstName,MiddleName and LastName Fields Are Calculated Automatically. See Followng How ?

Create Following Table

CREATE TABLE [dbo].[Employee](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [Name] [varchar](120) NULL,
    [FirstName]  AS 
                (CONVERT([varchar](60),ltrim(substring([Name],(1),
                isnull(nullif(charindex(' ',[Name]),(0)),(1000)))),0)),
    [MiddleName]  AS 
                (CONVERT([varchar](60),ltrim(substring([Name],
                charindex(' ',[Name]),case when (charindex(' ',
                [Name],charindex(' ',[Name])+(1))-charindex(' ',[Name]))<=(0) 
                then (0) else charindex(' ',[Name],charindex(' ',
                [Name])+(1))-charindex(' ',[Name]) end)),0)),
    [LastName]  AS 
                (CONVERT([varchar](60),ltrim(substring([Name],
                isnull(nullif(charindex(' ',[Name],charindex(' ',[Name])+(1)),(0)),
                charindex(' ',[Name])),case when charindex(' ',[Name])=(0) 
                then (0) else len([Name]) end)),0)),
)

Go

Note Above FirstName, MiddleName and LastName Fields as Computed Column.

Now Try Inserting Records Into Table

Insert into Employee(Name)
Values('Kuldeep Singh Bisht'),('Sandeep Verma'),('Manisha')
Go

Now See The Result.

EmpId	EmpName	FirstName	MiddleName	LastName
1	Kuldeep Singh Bisht	Kuldeep	Singh	Bisht
2	Sandeep Verma	Sandeep		Verma
3	Manisha	Manisha