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

SSIS package

In my previous article I am trying to explain related to What is data warehousing. If you don’t read it please follow this link before going to this…

http://sqlknowledgebank.blogspot.in/2012/05/data-warehousing.html

In this article I am trying to explain related to SSIS package.

A Package is the core object within SQL server Integration Services (SSIS) that contains the business logic to handle workflow and data processing. SSIS package can be used to move data from source to destinations and also handle the timing precedence of when thing process.

**BIDS [ Microsoft SQL Server Business Intelligence Development Studio ]

SSIS package can be accomplished by two ways.

Built-in wizard
By using the Built-in wizard in SQL Server 2005 that asks you to move the data from source to destination and automatically generate the SSIS package.


SSIS BIDS
By explicitly create a project in SSIS BIDS. We need to create projects the new package is automatically created and developed.

So we now trying to discuss about our first option and that is

By Built-in Wizard

In SQL Server 2005 we can use the Import and the Export Wizard to Import and Export the data. For Import Wizard the source is the SQL Server 2005 table and destination should be SQL Server database, ORACLE database, Flat file, Microsoft Excel spread sheet, Microsoft Access database.

Exporting data with the wizard lets us send the data from SQL Server 2005 tables, Views or custom query to flat file or database connection.

Initialize the Import Export Wizard

To initialize, please follow this steps mentioned bellow.

What we want to do

We want to import a flat file to our existing database.

1.    Through the SSMS connects to the installed database engine. That should be your source or destination.

2.    Click on view menu select Object Explorer (or press F8). From the database folder select the desired database. Then right click of the desired database and select Tasks. From Tasks we can select Import or Export wizard.

3.    Select the Tasks. If the database is source of data that needed to send out to the different system, select the “Export Data” and if the database is destination for the file currently exists outside the system, than select “Import Data”. Here is this example we are choosing “Import data”.

Database is source of data  à Export Data

Database is destination for the file àImport Data

4.     If we choose any one the “Welcome to SQL Server Import Export Wizard” appears. Then click the next button on the wizard. “Choose the data source” allow you to specify from the data is coming from. Here in this example I am choosing Flat file source and brows the flat file. Please specify others options if needed.

“Choose a Destination” allow us to specify the destination where the data will be sending. We can choose the destination if needed. The server name and the security settings must be specified. If we select a relational database source that allow customer queries.

5.    For now in “Save and Execute” page of wizard we choose the options Execute Immediate for now. In the complete the wizard gives us all the information that we selected. If needed we can go back and modified it. Now use the SQL query to see the result output.

SELECT * FROM <table name>

In my next session we are discussing about saving and Editing Package created by wizard.

Hope you like it.

Posted by: MR. JOYDEEP DAS

IDENTITY Columns Violation

We all know about the identity columns of SQL Server and how important it is. This article is related to it but I am representing this article to solving a common proble.

First take a quick look about the identity columns and how it works. Then we are going to discuss about the problem and how to solve it programmatically.

Definition of Identity Columns

An IDENTITY column contains a value for each row, generated automatically by Adaptive Server that uniquely identifies the row within the table.

Each table can have only one IDENTITY column. You can define an IDENTITY column when you create a table with a create table or select into statement, or add it later with an alter table statement. IDENTITY columns cannot be updated and do not allow nulls.

You define an IDENTITY column by specifying the keyword identity, instead of null or not null, in the create table statement. IDENTITY columns must have a datatype of numeric and scale of 0. Define the IDENTITY column with any desired precision, from 1 to 38 digits, in a new table:

CREATE TABLE  table_name

             (column_name  INT NOT NULL IDENTITY(1,1))

The Problem comes for Identity

One of my friends told me that, he has a problem for IDENTITY columns. He told me that when he make the INSERT entry into table objects, he never check the uniqueness of the records for that the primary key violation error occurs. He just shows the error.

The problem is the table objects contain identity columns. The value of the identity columns increases each time, even the PK violation exists.

The problem is mentioned by T-SQL statements

Step-1 [ Create the Base Table with IDENTITY columns ]

-- Table defination

DROP TABLE tbl_Example

CREATE TABLE tbl_Example

    (

       ROLL     INT          NOT NULL PRIMARY KEY,

       SNAME    VARCHAR(50)  NULL,

       SCLASS   INT          NULL,

       ROWNUM   INT          NOT NULL IDENTITY(1,1)

    ) 

Step-2 [ Normal Insertions of Values ]

INSERT INTO tbl_Example

            (ROLL,          

                   SNAME,   

                   SCLASS)

VALUES (1, 'JOYDEEP', 1)                     

INSERT INTO tbl_Example

            (ROLL,          

             SNAME,   

             SCLASS)

VALUES (2, 'SUKAMAL', 1)

Step-3 [ See the Output ]

SELECT * FROM tbl_Example

-- Output

ROLL  SNAME       SCLASS      ROWNUM

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

1     JOYDEEP     1           1

2     SUKAMAL     1           2

Step-4 [ Make an Invalid entry that generate an error ]

-- Violation of Primary Key

INSERT INTO tbl_Example

            (ROLL,          

             SNAME,   

             SCLASS)

VALUES (2, 'RAJESH', 1)

-- Output

Msg 2627, Level 14, State 1, Line 1

Violation of PRIMARY KEY constraint 'PK__tbl_Exam__44C28DB623BDA346'.

Cannot insert duplicate key in object 'dbo.tbl_Example'.

The statement has been terminated.

Step-5 [ Now Make the Correction entry after correction of data ]

-- Correction of Entry

INSERT INTO tbl_Example

            (ROLL,          

             SNAME,   

             SCLASS)

VALUES (3, 'RAJESH', 1)

Step-6 [ Look the INDENTITY columns has Increased it gives 4 instead of 3 ]

SELECT * FROM tbl_Example

--Output

ROLL        SNAME       SCLASS      ROWNUM

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

1           JOYDEEP     1           1

2           SUKAMAL     1           2

3           RAJESH      1           4

Note that: It is the Normal behavior of the IDENTITY columns. It maintains the uniqueness. Here we are going to break it depends on our needs and it is not good for development. However I am just demonstrating it that we can do it if needed.

Here I am going to make a stored procedure to demonstrate it.

CREATE PROCEDURE my_proc

    (

       @param_roll   INT,

       @param_name   VARCHAR(50),

       @param_calss  INT

    )

AS

   DECLARE @v_NOREC INT

   BEGIN

          BEGIN TRY

            BEGIN TRANSACTION

                  INSERT INTO tbl_Example

                             (ROLL,    

                                            SNAME,   

                                            SCLASS)

                          VALUES (@param_roll, 

                                  @param_name,

                                  @param_calss)           

            COMMIT TRANSACTION

          END TRY

          BEGIN CATCH

            ROLLBACK TRANSACTION

            PRINT 'ERROR EXISTS'

            SELECT @v_NOREC=COUNT(*) FROM tbl_Example

            DBCC CHECKIDENT (tbl_Example, reseed, @v_NOREC)

          END CATCH

   END   

Now we execute it and see the result step by step.

   -- Execution-1

   EXECUTE my_proc

           @param_roll   = 1,

           @param_name   = 'JOYDEEP',

           @param_calss  = 1

          

   SELECT * FROM tbl_Example

 

   ROLL     SNAME       SCLASS      ROWNUM

   1        JOYDEEP     1           1

   -- Execution-2

   EXECUTE my_proc

           @param_roll   = 2,

           @param_name   = 'SUKAMAL',

           @param_calss  = 1

          

  

   SELECT * FROM tbl_Example

   ROLL     SNAME       SCLASS      ROWNUM

   1        JOYDEEP     1           1

   2        SUKAMAL     1           2

   -- Execution-3 [ Primary Key Error ]

   EXECUTE my_proc

           @param_roll   = 2,

           @param_name   = 'SANGRAM',

           @param_calss  = 1

   0 row(s) affected)

    ERROR EXISTS

    Checking identity information: current identity value '3', current column value '2'.

    DBCC execution completed. If DBCC printed error messages, contact your system administrator.

          

   SELECT * FROM tbl_Example

   ROLL     SNAME       SCLASS      ROWNUM

   1        JOYDEEP     1           1

   2        SUKAMAL     1           2

      -- Execution-4       

   EXECUTE my_proc

           @param_roll   = 3,

           @param_name   = 'SANGRAM',

           @param_calss  = 1   

          

   SELECT * FROM tbl_Example

   ROLL     SNAME       SCLASS      ROWNUM

   1        JOYDEEP     1           1

   2        SUKAMAL     1           2

   3        SANGRAM     1           3  

    

Hope you like it.

Posted by: MR. JOYDEEP DAS

CROSS APPLY on SQL 2005

A new feature of Microsoft SQL Server 2005 is "CROSS  APPLY". It restricted "INNER JOIN" between a table (outer query) and a table-valued function (common usage), or derived table from correlated subquery. The table-valued function is evaluated only for the parameter values supplied by the outer query.

The result of the CROSS APPLY quay can be achieved by using temporary table, CTE or Table Variable. But CROSS APPLY provides a very powerful solution in a single query.

Let's takes an example, which can help us to build the concepts of CROSS APPLY.  Our motto is to supply the result of outer query as a parameters value of table value function.

[ Outer Query]  à result of outer query à [ Parameters value of Table valued Function]

Step-1 [ Create the Table Objects ]

CREATE TABLE People_details

(

   PersonID   int         NOT NULL,

   MotherID   int         NULL,

   FatherID   int         NULL,

   Name       varchar(50) NOT NULL,

   CONSTRAINT PK_People PRIMARY KEY(PersonID),

)

GO

Step-2 [ Insert Values Into Table Objects ]

INSERT INTO People_details

VALUES(1  , NULL, NULL, 'Rajesh Das')

INSERT INTO People_details

VALUES(2  , NULL, NULL, 'Raja Barma')

INSERT INTO People_details

VALUES(3  , NULL, NULL, 'Anand Kimmel')

INSERT INTO People_details

VALUES(4  , NULL, NULL, 'Sajan Kimmel')

INSERT INTO People_details

VALUES(5  , 2, 1,       'Joga Benavides')

INSERT INTO People_details

VALUES(6  , 3, 4,       'Giban Kimmel')

INSERT INTO People_details

VALUES(7  , 5, 6,       'Kalyan Hemenway')

INSERT INTO People_details

VALUES(8  , 5, 6,       'Dinesh Kimmel')

INSERT INTO People_details

VALUES(9  , 5, 6,       'Deb Kimmel')

INSERT INTO People_details

VALUES(10 , 5, 7,       'Rohit Benavides')

INSERT INTO People_details

VALUES(11 , 5, 7,       'Nitin Benavides')

INSERT INTO People_details

VALUES(12 , 5, 6,       'Jana Kimmel')

INSERT INTO People_details

VALUES(13 , 5, 6,       'Palu Kimmel')

INSERT INTO People_details

VALUES(14 , NULL, NULL, 'Logar Kimmel')

INSERT INTO People_details

VALUES(15 , NULL, NULL, 'David Benavides')

INSERT INTO People_details

VALUES(16 , 14, 13,     'Alex Kimmel')

INSERT INTO People_details

VALUES(17 , 14, 13,     'Noah Kimmel')

SELECT * FROM People_details

Step-3 [ The T-SQL Statements that I used in Function ]

SELECT p1.Name as MyName,

       p2.Name AS Mother,

       p3.Name As Father

FROM   People_details p1

       LEFT JOIN People_details p2 ON p1.MotherID = p2.PersonID

       LEFT JOIN People_details p3 ON p1.FatherID = p3.PersonID

Step-4 [ Create the Function ]

CREATE FUNCTION [fnGetParents](@PersonID int)

RETURNS @Parents TABLE

(

   [PersonID] [int] PRIMARY KEY NOT NULL,

   [Self]     [varchar](25),

   [Mother]   [varchar](25) NULL,

   [Father]   [varchar](25) NULL

)

AS

BEGIN

   INSERT INTO @Parents

   SELECT

      p1.PersonID,

      p1.Name AS [Self],

      p2.[Name] AS Mother,

      p3.[Name] AS Father

   FROM

      People_details p1

      INNER JOIN People_details p2 ON p1.MotherID = p2.PersonID

      INNER JOIN People_details p3 ON p1.FatherID = p3.PersonID

   WHERE

      p1.PersonID = @PersonID;

   RETURN;

END;

Step-5 [ This Query give you an error that's why we have to use CROSS APPLY ]

SELECT p1.PersonID, p1.Name, dbo.fnGetParents(p1.PersonID)

FROM People_details p1

Step-6 [ The CROSS APPLY ]

SELECT p1.PersonID, p1.Name, p2.Mother, p2.Father

FROM People_details p1

CROSS APPLY fnGetParents(p1.PersonID)p2

Analyzing the Step-6 that is the CROSS APPLY

[ Outer Query]  à result of outer query à [ Parameters value of Table valued Function]

SELECT p1.PersonID, p1.Name, p2.Mother, p2.Father

FROM People_details p1

CROSS APPLY fnGetParents(p1.PersonID)p2

Lets brake down the query into parts to understand it properly as above definition of CROSS APPLY says.

Here the Outer Query is

SELECT p1.PersonID, p1.Name, p2.Mother, p2.Father

FROM People_details p1

The Result of the Outer Query is

p1.PersonID

Parameters value of the Table valued Function is

fnGetParents(p1.PersonID)p2

I hope you now you understand it and thanking you to provide your valuable time on it.

Posted by: MR. JOYDEEP DAS