Partitioning a table in MS SQL Server 2005

Technology: Database, SQL Server 2005

Level: Advanced

VLDB, very large database, doesn’t necessarily indicate the size of tables within it. This article assumes that the table, itself, is very large and has huge number of rows; so huge that the performance is severely degraded. To the rescue, partitioning is a technique, introduced in 2005 version of MS SQL Server family, which helps distribute the data into smaller, more manageable sections. Sections, here, refer to horizontal partitioning, in which large groups of rows will be stored in multiple separate partitions.

Please note that you need Enterprise Edition or Developer Edition of Microsoft SQL Server 2005 for following the codes herein.

As an example, let us take a table ‘Activity’ that should store daily activity of students. Students are identified by their roll numbers and activity by text descriptions. We will see how the rows can be stored in multiple partitions w.r.t. student roll number.

Step 1:

– create files and filegroups ALTER DATABASE database_name ADD FILEGROUP [roll_fg1]; ALTER DATABASE database_name ADD FILE (NAME = N‘roll_f1′, FILENAME = N‘C:\Program Files\Microsoft SQL Server\MSSQL.2\MSSQL\Data\roll_f1.ndf’) SIZE = 5MB, MAXSIZE = 100MB, FILEGROWTH = 5MB) TO FILEGROUP [roll_fg1] ALTER DATABASE database_name ADD FILEGROUP [roll_fg2]; ALTER DATABASE database_name ADD FILE (NAME = N‘roll_f2′, FILENAME = N‘C:\Program Files\Microsoft SQL Server\MSSQL.2\MSSQL\Data\roll_f2.ndf’) SIZE = 5MB, MAXSIZE = 100MB, FILEGROWTH = 5MB) TO FILEGROUP [roll_fg2]

Step 2:

– create partition function CREATE PARTITION FUNCTION RollFunction(int) AS RANGE LEFT FOR VALUES (50, 100);

Step 3:

– create partition scheme CREATE PARTITION SCHEME RollScheme AS PARTITION RollFunction TO ([roll_fg1], [roll_fg2], [PRIMARY])

Step 4:

– create the table itself CREATE TABLE [activity]( [id] [int] IDENTITY(1,1) NOT NULL, [student_roll] [int] NOT NULL, [activity_desc] [text] NOT NULL, CONSTRAINT [PK_activity] PRIMARY KEY NONCLUSTERED ( [id] ASC )WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY] ) ON [RollScheme]([student_roll])

The above 4 steps first create two files groups and two files. Since the database already has the primary file and file group, there are now three file groups. The partition function is created such that there are three partitions, viz. [values <= 50], [values > 50 and <= 100] and [values > 100]. The rows are now distributed depending upon the roll number of the student.

All the CRUD operations are done with the same statements as they are done for an unpartitioned table. Quite nice, isn’t it. Please note that SQL Server 2005 only supports up to 1000 partitions including the primary partition.

To determine how many rows are stored in each partition, fire up the following query:

SELECT $PARTITION. RollFunction (student_roll) AS Partition, COUNT(*) AS [COUNT] FROM activity GROUP BY $PARTITION. RollFunction (student_roll) ORDER BY Partition ;

Enforcing single login

Technology: ASP.NET/C#, Database (Any)

Level: Intermediate/Expert

Depth: Brief

In my previous article [ Single login using ASP.NET ], I discussed on how to enforce single login in an ASP.NET web application with the help of [Session_End event] and [Cache].

Here, I discuss yet another way of doing the same job. But this time, I am using the database for the purpose.

STEP 1: In a table that stores user_credential, create an extra column for [last_activity].

–

– Sample Table

–

CREATE TABLE USER_CREDENTIAL_EXAMPLE

(

USER_ID INTEGER,

FULL_NAME VARCHAR2(100 BYTE),

ADDRESS VARCHAR2(200 BYTE),

LOGIN_ID VARCHAR2(50 BYTE),

LOGIN_PASSWORD VARCHAR2(100 BYTE),

LAST_ACTIVITY_ON DATE

);

STEP 2: Before initiating any server side logic, update the [LAST_ACTIVITY_ON] field with the current date_time.

STEP 3: Authentication logic goes like following:

1. Login_ID and Password are correct.

2. if (Math.Abs(LAST_ACTIVITY_ON.TotalMinutes) <= (double)HttpContext.Current.Session.Timeout)

   {

   //login successfull

   }

STEP 4: While logging out, set the [LAST_ACTIVITY_ON] field with the past date, something like [1-1-1999].

The logic is straight and obvious, isn’t it.

Cheers !!!