Horizontal partition of MS SQL server database table

Introduction

One of my friends asks me a question that, he has a large table and the table is well indexed. When he use the query (SELECT statement) he got the INDEX Seek (Not SCAN) but the problem is the response time of the query is so slow.

What u thinks? There is N number of reason related to it. Bust most common is that the table has huge data and it needs to be partitioned.

So all of you understand that, in this article we are going to discuss related to table partition.

Some general facts related to Table Partition

It is the horizontally partition the table. The technology came from MS SQL 2005 onwards. It is an MS SQL Server Enterprise edition feature. But we can test it in Developer Edition also. To get the proper effects of table partitioning we need multiple storage location (physical storage).

It allows us to different database files, which can be located on different disks to improve performance.

What is in Before MS SQL 2005

Before MS SQL 2005 we do not have the facility to make Horizontal partition of MS SQL server database table. But we can create the separate table of different file group of Database and create a VIEW by using UNION.

 How to make Horizontal partition of table

Step-1 [ Create the Database with Different File group ]

CREATE DATABASE [Employee_DB] ON

PRIMARY

 (

   NAME = N'Employee_DB',

   FILENAME = N'C:\EmployeeData\Employee_DB.mdf' ,

   SIZE = 3072KB , FILEGROWTH = 1024KB

 ),

FILEGROUP [EmployeeDBSecond]

(

  NAME = N'Employee_DB_Second',

  FILENAME = N'C:\EmployeeData\PEmployee_DB_Second.ndf' ,

  SIZE = 3072KB , FILEGROWTH = 1024KB

)

LOG ON

(

  NAME = N'Employee_DB_log',

 FILENAME = N'C:\EmployeeData\Employee_DB_log.ldf' ,

 SIZE = 9216KB , FILEGROWTH = 10%

)

GO

Here

Employee_DB.mdf	MS SQL Server Primary Database File
PEmployee_DB_Second.ndf	MS SQL Server Secondary Database File
Employee_DB_log.ldf	MS SQL Server Transaction Log File

“Please note that in this example we are using a single storage to make the primary and secondary file group. It is better if we take the different physical storage for primary and secondary file group storage to increate the IO. So increase the performance”.

Step-2 [ Create Partition Function ]

The partition function defines that how to separate the data. The function is not related to any specified table that determines how the data split occurs.

In our example we take Sales Order information where current year records stores in Primary file group and all the older data must store in secondary file group.

CREATE PARTITION FUNCTION fnEmpDBPartFunc (DATE)

AS RANGE LEFT

FOR VALUES ('2013-12-31')

1	>   2013
2	<=  2013

Step-3 [ Creating Partition Schema ]

Here the Partition function is created, so the SQL Server knows that how to segregate the data but doesn’t know that where to put the partitioned data. This is done by Partition Schema and the Partition schema is linked with Partition function.

CREATE PARTITION SCHEME EmpDBParttScheme

AS PARTITION fnEmpDBPartFunc

TO ([EmployeeDBSecond], [PRIMARY])

File Group – PRIMARY	>   2013
File Group - EmployeeDBSecond	<=  2013

Strep-4 [ Creating Partition Table ]

Now we create Table on partition schema.

CREATE TABLE Table_SalesOrders

(

      OrderID     INT,

      CustName    VARCHAR(50),

      OrderDate   DATE

)

ON EmpDBParttScheme (OrderDate)

Step- 5 [ Inserting Records ]

-- Will go to [PRIMARY] File Group

INSERT INTO Table_SalesOrders

       (OrderID, CustName, OrderDate)

VALUES (1, 'Joydeep Das', '2014-01-10')

-- Will go to [EmployeeDBSecond] File Group

INSERT INTO Table_SalesOrders

       (OrderID, CustName, OrderDate)

VALUES (2, 'Manayan Chaturvedhi', '2013-06-22')

Step-6 [ Checking the Partition ]

SELECT      partition_id, object_id, partition_number, rows

FROM        sys.partitions

WHERE       object_id = OBJECT_ID('Table_Orders')

partition_id         object_id   partition_number rows

72057594038910976    5575058     1                1

72057594038976512    5575058     2                1

Sterp-7 [ Creating CLUSTERED Index ]

CREATE CLUSTERED INDEX [Clust_Orders] ON [dbo].[Table_SalesOrders]

(

   [OrderID] ASC

) ON EmpDBParttScheme (OrderDate)

Hope you like it.

Posted by: MR. JOYDEEP DAS

Some Useful Aggregation (WITH ROLLUP/WITH CUBE/GROUPING SETS)

Introduction

In this article I am trying to provide some example related to aggregation function.

WITH ROLLUP

WITH ROLLUP allows us to do the multiple level of aggregation within a single SQL statement. To understand it, I directly jumped to an example.

Create and Insert Records in a Table Objects

IF OBJECT_ID(N'dbo.tbl_EMPLOYEE', N'U') IS NOT NULL

   BEGIN

     DROP TABLE [dbo].[tbl_EMPLOYEE];

   END

GO

CREATE TABLE [dbo].[tbl_EMPLOYEE]

       (EMPID        INT   NOT NULL,

        EMPSALYEAR   INT   NOT NULL,

        EMPSAL       INT   NOT NULL);

GO

INSERT INTO  [dbo].[tbl_EMPLOYEE]

       (EMPID, EMPSALYEAR, EMPSAL)

VALUES (101, 2012, 2000),

       (101, 2013, 3000),

       (101, 2014, 4000),

       (102, 2012, 5000),

       (102, 2013, 5500),

       (102, 2014, 6000),

       (103, 2012, 1000),

       (103, 2013, 3000),

       (103, 2014, 7000);                  

SELECT * FROM [dbo].[tbl_EMPLOYEE]

GO

EMPID       EMPSALYEAR  EMPSAL

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

101         2012        2000

101         2013        3000

101         2014        4000

102         2012        5000

102         2013        5500

102         2014        6000

103         2012        1000

103         2013        3000

103         2014        7000

Now we use Group By in it

SELECT EMPSALYEAR, SUM(EMPSAL) AS EMPSAL

FROM   [dbo].[tbl_EMPLOYEE]

GROUP BY EMPSALYEAR;

GO

EMPSALYEAR  EMPSAL

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

2012        8000

2013        11500

2014        17000

Now we want a GRAND TOTAL at the end.

We can do it like this

SELECT CONVERT(VARCHAR, EMPSALYEAR) EMPSALYEAR,

       SUM(EMPSAL) AS EMPSAL

FROM   [dbo].[tbl_EMPLOYEE]

GROUP BY EMPSALYEAR

UNION ALL

SELECT 'GRAND TOTAL' EMPSALYEAR,

       SUM(EMPSAL) AS EMPSAL

FROM   [dbo].[tbl_EMPLOYEE]

GO

EMPSALYEAR                     EMPSAL

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

2012                           8000

2013                           11500

2014                           17000

GRAND TOTAL                    36500

But it is performance overhead for SQL Server.

We can do it easily by WITH ROLLUP

SELECT EMPSALYEAR, SUM(EMPSAL) AS EMPSAL

FROM   [dbo].[tbl_EMPLOYEE]

GROUP BY EMPSALYEAR

WITH ROLLUP

GO

EMPSALYEAR                     EMPSAL

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

2012                           8000

2013                           11500

2014                           17000

NULL                           36500

By using little bit formatting

SELECT CASE WHEN GROUPING(EMPSALYEAR) = 0

            THEN CONVERT(VARCHAR, EMPSALYEAR)

            ELSE 'GRAND TOTAL'

       END AS EMPSALYEAR, SUM(EMPSAL) AS Sales

FROM [dbo].[tbl_EMPLOYEE]

GROUP BY EMPSALYEAR WITH ROLLUP;

EMPSALYEAR                     Sales

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

2012                           8000

2013                           11500

2014                           17000

GRAND TOTAL                    36500

WITH CUBE

The WITH CUBE clause gives us to compute multiple levels of aggregation in a single statement.

SELECT EMPID, EMPSALYEAR, SUM(EMPSAL) AS EMPSAL

FROM [dbo].[tbl_EMPLOYEE]

GROUP BY EMPID, EMPSALYEAR WITH CUBE

GO

EMPID       EMPSALYEAR  EMPSAL

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

101         2012        2000

102         2012        5000

103         2012        1000

NULL        2012        8000

101         2013        3000

102         2013        5500

103         2013        3000

NULL        2013        11500

101         2014        4000

102         2014        6000

103         2014        7000

NULL        2014        17000

NULL        NULL        36500

101         NULL        9000

102         NULL        16500

103         NULL        11000

New Syntax of WITH ROLLUP and WITH CUBE

SELECT EMPID, EMPSALYEAR, SUM(EMPSAL) AS Sales

FROM   [dbo].[tbl_EMPLOYEE]

GROUP BY ROLLUP(EMPID, EMPSALYEAR)

GO

EMPID       EMPSALYEAR  Sales

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

101         2012        2000

101         2013        3000

101         2014        4000

101         NULL        9000

102         2012        5000

102         2013        5500

102         2014        6000

102         NULL        16500

103         2012        1000

103         2013        3000

103         2014        7000

103         NULL        11000

NULL        NULL        36500

SELECT EMPID, EMPSALYEAR, SUM(EMPSAL) AS Sales

FROM  [dbo].[tbl_EMPLOYEE]

GROUP BY CUBE(EMPID, EMPSALYEAR)

GO

EMPID       EMPSALYEAR  Sales

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

101         2012        2000

102         2012        5000

103         2012        1000

NULL        2012        8000

101         2013        3000

102         2013        5500

103         2013        3000

NULL        2013        11500

101         2014        4000

102         2014        6000

103         2014        7000

NULL        2014        17000

NULL        NULL        36500

101         NULL        9000

102         NULL        16500

103         NULL        11000

GROUPING SETS

The GROUPING SETS syntax is more powerful.  It allows us to specify precisely which aggregations we want to compute.

SELECT EMPID, EMPSALYEAR, SUM(EMPSAL) AS EMPSAL

FROM   [dbo].[tbl_EMPLOYEE]

GROUP BY GROUPING SETS((EMPID, EMPSALYEAR), (EMPID), ())

GO

EMPID       EMPSALYEAR  EMPSAL

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

101         2012        2000

101         2013        3000

101         2014        4000

101         NULL        9000

102         2012        5000

102         2013        5500

102         2014        6000

102         NULL        16500

103         2012        1000

103         2013        3000

103         2014        7000

103         NULL        11000

NULL        NULL        36500

SELECT EMPID, EMPSALYEAR, SUM(EMPSAL) AS EMPSAL

FROM   [dbo].[tbl_EMPLOYEE]

GROUP BY GROUPING SETS((EMPID, EMPSALYEAR), (EMPID), (EMPSALYEAR), ())

GO

EMPID       EMPSALYEAR  EMPSAL

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

101         2012        2000

102         2012        5000

103         2012        1000

NULL        2012        8000

101         2013        3000

102         2013        5500

103         2013        3000

NULL        2013        11500

101         2014        4000

102         2014        6000

103         2014        7000

NULL        2014        17000

NULL        NULL        36500

101         NULL        9000

102         NULL        16500

103         NULL        11000

Hope you like it.

Posted by: MR.JOYDEEP DAS

datetimeoffset (T-SQL)

Introduction

A lot of query came to me reading datetimeoffset in MS SQL 2008.

This is a special data type introduced by MS from MS SQL Server Version 2008 and onwards. Previously we used datetime and now it added another keyword in the name of Offset.

It basically

Date  +  Time  + Offset

The Datetime takes Date and Time portions and Offset takes the Time Zone.

In Microsoft Development Network

"Defines a date that is combined with a time of a day that has time zone awareness and is based on a 24-hour clock."

Syntax

datetimeoffset [ (fractional seconds precision) ]

How to Use it

DECLARE @MyDatetimeoffset datetimeoffset(7)

CREATE TABLE Table1 

( Column1 datetimeoffset(7) )

Default Format

YYYY-MM-DD hh:mm:ss[.nnnnnnn] [{+|-}hh:mm]

Date Range

0001-01-01 through 9999-12-31

January 1,1 A.D. through December 31, 9999 A.D.

Time Range

00:00:00 through 23:59:59.9999999

Time Zone Offset Range

-14:00 through +14:00

Element Range

YYYY is four digits, ranging from 0001 through 9999, that represent a year.

MM is two digits, ranging from 01 to 12, that represent a month in the specified year.

DD is two digits, ranging from 01 to 31 depending on the month, that represent a day of the specified month.

hh is two digits, ranging from 00 to 23, that represent the hour.

mm is two digits, ranging from 00 to 59, that represent the minute.

ss is two digits, ranging from 00 to 59, that represent the second.

n* is zero to seven digits, ranging from 0 to 9999999, that represent the fractional seconds.

hh is two digits that range from -14 to +14.

mm is two digits that range from 00 to 59.

Example

SELECT 

CAST('2014-01-11 12:35:29. 1234567 +12:15' AS time(7)) AS 'time',

CAST('2014-01-11 12:35:29. 1234567 +12:15' AS date) AS 'date',

CAST('2014-01-11 12:35:29.123' AS smalldatetime) AS 'smalldatetime',

CAST('2014-01-11 12:35:29.123' AS datetime) AS 'datetime',

CAST('2014-01-11 12:35:29.1234567+12:15' AS datetime2(7)) AS 'datetime2',

CAST('2014-01-11 12:35:29.1234567 +12:15' AS datetimeoffset(7)) AS 'datetimeoffset',

CAST('2014-01-11 12:35:29.1234567+12:15' AS datetimeoffset(7)) AS 'datetimeoffset IS08601';

Hope you Like it

Posted By: MR. JOYDEEP DAS