SQL Server 2005 Partitioning:



This document is prepared for the benefit of projects that are being deleveloped using MS SQL SERVER 2005 in consideration with some performance issues. The intention of this document is to highlight how to provide solutions in case of handling huge data or any recursive complex computations. I hope this will help the reader in understanding beyond the basics of MS SQL SERVER 2005 and aid in overcoming similar situations in their projects

Beyond Basics in SQL Server 2005

1. SQL Server 2005 Partitioning

Partitioning is a very interesting and powerful feature in SQL Server 2005. Here partitioning means horizontal partition of data rows from the table. How to do this partitioning? This is where a Partition Function comes into play.

|To know |Description |

|Partition Function |A partition Function is a logic that maps rows of data from tables|

| |into partition based on values in the field. |

|File Group |It is the place where each data rows are placed. |

|Partition Scheme |Partition Scheme used to associate the partition functions with |

| |the file groups created. |

Creating Partition:

Step 1: Creating a partition function.

CREATE PARTITION FUNCTION TestPF(DATETIME)

AS RANGE RIGHT FOR VALUES ('20080401')

From the above create script; we are creating partition based on Date values. So the values before the date will go into the first partition and the values from and after the date will go into the second partition.

Step 2: Adding Filegroups which will contain the partitioned values.

ALTER DATABASE TestPartition ADD FILEGROUP LessYear

ALTER DATABASE TestPartition ADD FILEGROUP GreaterYear

Step 3: Adding file to Filegroups.

-- File Group 1

ALTER DATABASE TestPartition

ADD FILE (NAME = 'LessYear', FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\TestParF1.ndf')

TO FILEGROUP LessYear;

-- File Group 2

ALTER DATABASE TestPartition

ADD FILE (NAME = 'GreaterYear', FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\TestParF2.ndf')

TO FILEGROUP GreaterYear;

Step 4: Associating partition function (PF) to the created filegroups (LessYear, GreaterYear) using the partition scheme (PS).

CREATE PARTITION SCHEME TestPS

AS PARTITION TestPF TO (LessYear, GreaterYear)

Step 5: Create a new table that uses the partition scheme

CREATE TABLE tblTestData

(

Id INT NOT NULL IDENTITY(1,1),

CreatedDate DATETIME NOT NULL,

Comment VARCHAR(50) NULL

) ON TestPS(CreatedDate)

Step 6: Inserting some records into the table.

INSERT INTO tblTestData VALUES(GETDATE()-200 ,'Testing Partition')

INSERT INTO tblTestData VALUES(GETDATE()-100 ,'Testing Partition')

INSERT INTO tblTestData VALUES(GETDATE(),'Testing Partition')

INSERT INTO tblTestData VALUES(GETDATE()+100 ,'Testing Partition')

INSERT INTO tblTestData VALUES(GETDATE()+200 ,'Testing Partition')

Step 7: From the following query, we can see where our data fall into the partition.

SELECT *, $PARTITION.TestPF(CreatedDate) FROM tblTestData

Advantage:

• Allows operating on data even on critical operation, such as reindexing, without affecting the others.

• During restore of data into the database, as soon a partition is available; all the data in that partition are available for quering, even if the restore is not yet fully completed.

2. SQL Server 2005 CLR integration

SQL Server 2005 allows us to write Stored Procedures, Triggers and other objects in a .NET complaint language like C# & and this can be compiled as dynamic link libraries. These .dll files can then be registered in SQL Server known as CLR integration. We can realise the necessity of CLR Stored Procedures in the situations like very complex mathematical computations, recursive operations and so on which cannot be easily possible in T-SQL.

Creating CLR Stored Procedures:

Steps to create and build CLR stored procedures.

• Write the .NET (C#) code for the required functionality.

• Compile the .NET code

• Enable CLR environment in the database

• Create an assembly in the database based on the compiled .NET code

• Create a SQL Server stored procedure that uses CLR stored procedure.

• Executing the SQL server stored procedure.

Step 1: Creating a stored procedure in .NET:

Right Click on project and choose Add. Select the item StoredProcedure and name it as TestCLR.cs.

using System;

using System.Data;

using System.Data.Sql;

using System.Data.SqlTypes;

using Microsoft.SqlServer.Server;

namespace Testing

{

public partial class TestCLRSP

{

[Microsoft.SqlServer.Server.SqlProcedure]

public static void DisplayInfo(string info)

{

SqlPipe p;

p = SqlContext.Pipe;

p.Send(info);

}

}

}

The object SqlPipe is used to send data back to the calling query.

Step 2: Compile the class file.

Compile the class TestCLRSP and generate the assembly file TestCLRSP.dll file.

csc /target:library Testing.TestCLRSP.cs /reference:"C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Binn\sqlaccess.dll"

This will create an assembly file Testing.TestCLRSP.dll for the class we created.

(Kindly ensure that you have given the correct path where SQL Server is installed for producing the assembly Testing.TestCLRSP.dll. Path given above is just a sample)

Step 3: Enable CLR environment.

Before continuing, ensure that the CLR is enabled on SQL Server. If not, then enable it using the procedure sp-configure.

EXEC sp_configure @configname = 'CLR ENABLED', @configvalue = 1

RECONFIGURE WITH OVERRIDE

GO

Step 4: Creating an assembly in SQL Server.

CREATE ASSEMBLY TestCLRASM

FROM 'Testing\TestCLRSP\Testing.TestCLRSP.dll'

WITH PERMISSION_SET = SAFE

GO

Step 5: Creating a SQL Server stored procedure that consume CLR stored

procedure.

CREATE PROC usp_InvokeCLRSP

@txtInfo NVARCHAR(255)

AS

EXTERNAL NAME TestCLRASM.[Testing.TestCLRSP].DisplayInfo

GO

Step 5: Executing the SQL Server stored procedure.

EXEC sp_DotNetFunSProcPrint 'Successfully Completed Calling CLR SP'

Advantage:

CLR stored procedures can be pipelined – means if a stored procedure returns 1000 rows, you cannot read the first row in T-SQL until the stored procedure completely executing the 1000th row. But in CLR, we can streamline the output without completely executing the result set.

................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download