Database Simplified Headline Animator

Database Simplified

Friday, 30 September 2011

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

Sunday, 25 September 2011

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

denali_future_editions_v3

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

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

image


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 ;


image




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 :

image

 

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:

image

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:

image

 

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 :

image

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

Saturday, 24 September 2011

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

image

 

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

image

 

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

image

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

image

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

image

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

image

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 :

image

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 :

image

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.

Friday, 23 September 2011

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.

image

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 :

image

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 :

image

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

image

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.

image

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

image

Thursday, 22 September 2011

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

image_thumb[2]

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.

image_thumb[10]

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 ?

image_thumb[14]

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

image_thumb[17]

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.

Saturday, 17 September 2011

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.

image

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 :

image

Saturday, 10 September 2011

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