Wednesday 29 January 2014

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

Monday 13 January 2014

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

Saturday 11 January 2014

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