SQL: Partition switching in SQL Server 2005

http://www.techrepublic.com/blog/datacenter/partition-switching-in-sql-server-2005/143

Partition switching in SQL Server 2005
2Comments<http://www.techrepublic.com/blog/datacenter/partition-switching-in-sql-server-2005/143#talkback>
more +

* Email
* Print
* Add to Favorites
* Del.icio.us
* Digg
* Hacker News
* LinkedIn
* Reddit
* Technorati
By Tim Chapman<http://www.techrepublic.com/search?q=tim+chapman>
July 23, 2007, 11:21 AM PDT

Takeaway: Tim Chapman demonstrates how you can modify defined partitions in SQL Server 2005 so that you can easily and efficiently add new data.

Last week, I showed how you can use the new table and index partitioning features in SQL Server 2005 to split your table data across one or more filegroups<http://blogs.techrepublic.com.com/datacenter/?p=139>. In this article, I demonstrate how you can modify those defined partitions so that you can easily and efficiently add new data.

Partitioning

Partitioning is the separation of database rows from one large table into one or more smaller tables. There can be performance and administrative advantages in using partitioning because you are essentially using one or more tables as one large table. These advantages can be even greater when you take into account the ability to easily add and delete data to and from your partitions.

Partition switching

Partition functions are used to map table field values to defined partitions. These partitions are initially defined when the function is created. However, it is sometimes necessary to change how your partitions are set up to accommodate new data, move current data, or delete data. Partition switching is the process of moving a block of data around in one or more tables.

An example
One common use for partition is the archiving of data, such as moving data from your OLTP database to your data warehouse for reporting purposes. This example looks at the importing of MP3 product data into a SalesHistoryArchive table. For the purpose of this example, assume that a list of MP3Player product sales have been imported into the database and placed in a table named MP3Import.

This example is built upon the partitioning example I illustrated in my article on creating table partitions in SQL Server 2005<http://blogs.techrepublic.com.com/datacenter/?p=139>. The following script recreates the objects I used in that article. These objects include the partition function, partition scheme, and the SalesHistoryArchive table.

CREATE PARTITION FUNCTION [pf_Product_Partition](VARCHAR(10)) AS RANGE LEFT

FOR VALUES (N'BigScreen', N'Computer', N'PoolTable')

Go



CREATE PARTITION SCHEME [ps_Product_Scheme] AS PARTITION [pf_Product_Partition]

ALL TO ([PRIMARY])

GO



IF OBJECT_ID('SalesHistoryArchive')>0

DROP TABLE [SalesHistoryArchive];

GO

CREATE TABLE [dbo].[SalesHistoryArchive]

(

[SaleID] [int] IDENTITY(1,1),

[Product] [varchar](10) NULL,

[SaleDate] [datetime] NULL,

[SalePrice] [money] NULL

)ON [ps_Product_Scheme](Product)

GO



DECLARE @i SMALLINT

SET @i = 1

WHILE (@i <=10000)

BEGIN

INSERT INTO SalesHistoryArchive(Product, SaleDate, SalePrice)

VALUES('Computer', DATEADD(mm, @i, '3/11/1919'), DATEPART(ms, GETDATE()) + (@i + 57))

INSERT INTO SalesHistoryArchive(Product, SaleDate, SalePrice)

VALUES('BigScreen', DATEADD(mm, @i, '3/11/1927'), DATEPART(ms, GETDATE()) + (@i + 13))

INSERT INTO SalesHistoryArchive(Product, SaleDate, SalePrice)

VALUES('PoolTable', DATEADD(mm, @i, '3/11/1908'), DATEPART(ms, GETDATE()) + (@i + 29))

SET @i = @i + 1

END

GO

Now that I have data to use for the example, I can dig into the details of the new product import. The MP3Player product records that I will be importing will be placed in the SalesHistoryArchive table. This table is currently partitioned, and I want the MP3Player product to be placed on its own partition. To accomplish this, I will need to alter the partition function that I use on the SalesHistoryArchive table.

ALTER PARTITION FUNCTION [pf_Product_Partition] ()

SPLIT RANGE ('MP3Player')

Run the following query to verify that a new partition has been added for the SalesHistoryArchive table:

SELECT * From sys.partitions

WHERE OBJECT_NAME(object_id) = 'SalesHistoryArchive'

At this point, data can be inserted directly into the SalesHistory archive table, and any product of MP3Player will be placed in its own partition. However, data is inserted into the database from some type of external source and placed in its own individual database table. One great feature of partitioning in SQL Server 2005 is that it allows you to switch data from one table into another table very easily and quickly. It is so fast because the switching of the partition data only involves the changing of SQL Server's internal metadata, so no data is actually moved. The script below creates the MP3Import table, which I will assume has been imported into the database from an external data source.

CREATE TABLE [dbo].MP3Import

(

[SaleID] [int] IDENTITY(1,1),

[Product] [varchar](10) NULL,

[SaleDate] [datetime] NULL,

[SalePrice] [money] NULL

)ON [ps_Product_Scheme](Product)

GO

ALTER TABLE MP3Import

ADD CONSTRAINT ck_Product CHECK(Product = 'MP3Player')

GO

There are a couple of specific things to notice in the script above. First, the table structure of the MP3Import table is the same as the SalesArchiveHistory table, and the MP3Import table uses the same partitioning scheme as the SalesHistoryArchive table. Using the same partitioning scheme isn't really necessary to complete what I want to complete, but I think it makes it easier. Next, I am creating a check constraint on the table to ensure that only the MP3Player product is imported into the table. This will ensure that when I switch the data that all of the data goes into the exact partition that I want.

The next step is to load some data into the import table:

DECLARE @i SMALLINT

SET @i = 1

WHILE (@i <=10000)

BEGIN

INSERT INTO MP3Import(Product, SaleDate, SalePrice)

VALUES('MP3Player', DATEADD(mm, @i, '3/11/1919'), DATEPART(ms, GETDATE()) + (@i + 57))

SET @i = @i + 1

END

GO

I want to switch the data that is in the MP3Import table to the SalesHistoryArchive table. In previous versions of SQL Server, this would entail using an INSERT statement to add the data to the table. With SQL Server 2005, I can simply use the SWITCH statement to move the data pointers to point to the SalesHistoryArchive table. In the script below, I alter the MP3Import table and switch the data that resides in partition 3, where the data was initially inserted, to partition 3 on the SalesHistory archive table.

ALTER TABLE MP3Import

SWITCH PARTITION 3 TO SalesHistoryArchive PARTITION 3

The following query shows that the SalesHistoryArchive table now contains 10000 records in partition 3. Because the data in the MP3Import table is no longer needed, I can simply issue a DROP command on the table to remove it from the database.

DROP TABLE MP3Import

Because I have associated the data pointers with the new partition in the SalesHistoryArchive table, I can safely remove the MP3Import table.

To switch or not to switch

This article explores one scenario where it is advantageous to use partitioning to easily add data to your partitoned tables. It is worth noting that data can be removed as easily as it is added when the full partition is involved. While partitioning is a great advantage when it is suitable, it is not appropriate for all circumstances. Only through testing and analysis can you determine if partitioning, and thus partitioning switching, can improve your overall database scenario.

Assign to a T-SQL variable from a CASE statement

References: http://stackoverflow.com/questions/6945979/assign-to-a-t-sql-variable-from-a-case-statement


The example you've given should work. You can assign to variables from a case statement. Just pretend that the entire CASE..WHEN..THEN..ELSE..END block is a field. Here is a generic example:


declare
@string1 nvarchar(100) = null
,@string2 nvarchar(100) = null
;

select top 1
@string1 = case when 1=1 then 'yes' else 'no' end
,@string2 = case when 1=0 then 'yes' else 'no' end

print 'string1 = ' + @string1
print 'string2 = ' + @string2
Gives:
string1 = yes
string2 = no

SQL Partitioning (Horizontal & Vertical )

References: http://msdn.microsoft.com/en-us/library/ms178148(v=sql.105).aspx


Partitioning

Partitioning a database improves performance and simplifies maintenance. By splitting a large table into smaller, individual tables, queries that access only a fraction of the data can run faster because there is less data to scan. Maintenance tasks, such as rebuilding indexes or backing up a table, can run more quickly.

Partitioning can be achieved without splitting tables by physically putting tables on individual disk drives. Putting a table on one physical drive and related tables on a separate drive can improve query performance because, when queries that involve joins between the tables are run, multiple disk heads read data at the same time. SQL Server filegroups can be used to specify on which disks to put the tables.
Hardware Partitioning<javascript:void(0)>
________________________________

Hardware partitioning designs the database to take advantage of the available hardware architecture. Following are examples of hardware partitioning:

* Multiprocessors that enable multiple threads of operations, permitting many queries to run at the same time. Alternatively, a single query may be able to run faster on multiple processors by letting components of the query run at the same time. For example, each table referenced in the query can be scanned at the same time by a different thread.

* RAID (redundant array of independent disks) devices that enable data to be striped across multiple disk drives, permitting faster access to the data, because more read and write heads read data at the same time. A table striped across multiple drives can typically be scanned faster than the same table stored on a single drive. Alternatively, storing tables on separate drives from related tables can significantly improve the performance of queries joining those tables. For more information, see RAID<http://msdn.microsoft.com/en-us/library/ms184252(v=sql.105).aspx>.
Horizontal Partitioning<javascript:void(0)>
________________________________

Horizontal partitioning divides a table into multiple tables. Each table then contains the same number of columns, but fewer rows. For example, a table that contains 1 billion rows could be partitioned horizontally into 12 tables, with each smaller table representing one month of data for a specific year. Any queries requiring data for a specific month only reference the appropriate table.

Determining how to partition the tables horizontally depends on how data is analyzed. You should partition the tables so that queries reference as few tables as possible. Otherwise, excessive UNION queries, used to merge the tables logically at query time, can affect performance. For more information about querying horizontally partitioned tables, see Scenarios for Using Views<http://msdn.microsoft.com/en-us/library/ms188250(v=sql.105).aspx>.

Partitioning data horizontally based on age and use is common. For example, a table may contain data for the last five years, but only data from the current year is regularly accessed. In this case, you may consider partitioning the data into five tables, with each table containing data from only one year.

For more information, see Partitioned Tables and Indexes<http://msdn.microsoft.com/en-us/library/ms188706(v=sql.105).aspx>.
Vertical Partitioning<javascript:void(0)>
________________________________

Vertical partitioning divides a table into multiple tables that contain fewer columns. The two types of vertical partitioning are normalization and row splitting:

* Normalization is the standard database process of removing redundant columns from a table and putting them in secondary tables that are linked to the primary table by primary key and foreign key relationships.

* Row splitting divides the original table vertically into tables with fewer columns. Each logical row in a split table matches the same logical row in the other tables as identified by a UNIQUE KEY column that is identical in all of the partitioned tables. For example, joining the row with ID 712 from each split table re-creates the original row.

Like horizontal partitioning, vertical partitioning lets queries scan less data. This increases query performance. For example, a table that contains seven columns of which only the first four are generally referenced may benefit from splitting the last three columns into a separate table.

Vertical partitioning should be considered carefully, because analyzing data from multiple partitions requires queries that join the tables. Vertical partitioning also could affect performance if partitions are very large.

SQL: How to Truncate Log File in SQL Server 2005

http://www.codeproject.com/Articles/14400/How-to-Truncate-Log-File-in-SQL-Server-2005


How to Truncate Log File in SQL Server 2005

[cid:image001.jpg@01CD8B98.8F2B1E70]
Introduction
SQL Server 2005 is quite different from SQL Server 2000. To truncate log file is one thing which is different from SQL Server 2000. In SQL Server 2000, you just use Shrink to whatever file size you like. In SQL Server 2005, sometimes I cannot shrink the log file at all.
Here I want to describe some tricks to truncate log file for a database in SQL Server 2005. The work environment is Microsoft SQL Server Management Studio.
I. Shrink the Log File Size at the Right Time
I found out this trick:
Immediately after I use the SSIS package or Import the data to the database (highlight the database->Tasks->Import data ...), or Export the data from the database (highlight the database->Tasks->Export data ...), I can shrink the log file to the desired size, for example, 1MB. That is, highlight the database->Tasks->Shrink->Files , set the file size, say, 1MB.
Then, click OK and you are done.
II. Eliminate the Log File Completely
Sometimes, we just do not need the big log file. For example, I have a 40GB log file. I am sure I do not need this log file and want to get rid of it completely to free up the hard drive space. The logic is:

1. Detach the database
2. Rename the log file
3. Attach the database without the log file
4. Delete the log file
Let's say, the database name is testDev. In the SQL Server Management Studio,

1. Highlight the database-> Tasks->Detach..-> Click OK
2. Go to log file folder -> rename the testDev_log.ldf to be like testDev_log-aa.ldf
3. Highlight Databases->Attach...-> Click Add -> add the database testDev, highlight the log file and click the 'Remove' button. This means you only attach testDev.mdf
4. After this is done, you can verify the contents of the attached database and then delete the log file
This way we can safely delete the log file and free up the space.

SQL : Row_Number() to delete duplicate records

http://www.databasejournal.com/features/mssql/article.php/3577481/RowNumber-function-in-SQL-Server-2005-150-Part-II.htm
Row_Number() function in SQL Server 2005 - Part II

By Muthusamy Anantha Kumar aka The MAK<http://www.databasejournal.com/feedback.php/http:/www.databasejournal.com/features/mssql/article.php/3577481/RowNumber-function-in-SQL-Server-2005-150-Part-II.htm>

In part I<http://www.databasejournal.com/features/mssql/article.php/3572301> of this article, we saw how to generate row numbers and delete a simple single duplicate row. This article walks you through how to delete multiple duplicate rows. This article uses the new feature Row_Number() function, common table expression and the ANSI co-related subquery.

Let us assume we have the following table, Mytable, in the database MyDB. Let us create the database MyDB and MyTable by using the following script.

USE [MASTER]

GO

IF EXISTS

[cid:image001.gif@01CD8B62.46AF4580] (SELECT NAME FROM SYS.DATABASES

[cid:image001.gif@01CD8B62.46AF4580] WHERE NAME = N'MYDB')

DROP DATABASE [MYDB]

GO

CREATE DATABASE MYDB

GO

USE [MYDB]

GO

IF EXISTS

[cid:image001.gif@01CD8B62.46AF4580] (SELECT * FROM SYS.OBJECTS

[cid:image001.gif@01CD8B62.46AF4580] WHERE OBJECT_ID =

[cid:image001.gif@01CD8B62.46AF4580] OBJECT_ID(N'[DBO].[MYTABLE]')

[cid:image001.gif@01CD8B62.46AF4580] AND TYPE IN (N'U'))

DROP TABLE [DBO].[MYTABLE]

GO

CREATE TABLE MYTABLE

[cid:image001.gif@01CD8B62.46AF4580] (ID INT, FNAME VARCHAR(50),

[cid:image001.gif@01CD8B62.46AF4580] LNAME VARCHAR(50))

GO

insert into mytable select 1,'Jen','Ambelang'

insert into mytable select 11,'Jiong','Hong'

insert into mytable select 25,'Sandra','Mator'

insert into mytable select 35,'Chun','Chang'

insert into mytable select 21,'Yuki','Fukushima'

insert into mytable select 1,'Jen','Ambelang'

insert into mytable select 1,'Jen','Ambelang'

insert into mytable select 25,'Sandra','Mator'

insert into mytable select 25,'Sandra','Mator'

go

Let us query all the rows from the table MyTable using the following Select query.

SELECT * FROM MYTABLE ORDER BY ID

This query displays the following results.

Related Articles

* Row_Number() function in SQL Server 2005<http://www.databasejournal.com/features/mssql/article.php/3572301/RowNumber-function-in-SQL-Server-2005.htm>

1


Jen


Ambelang


1


Jen


Ambelang


1


Jen


Ambelang


11


Jiong


Hong


21


Yuki


Fukushima


25


Sandra


Mator


25


Sandra


Mator


25


Sandra


Mator


35


Chun


Chang


From the results, it is very clear that we have duplicate rows: 1, Jen, Ambelang and 25, Sandra, Mator.

Step 1

The first step in deleting duplicate rows is to generate a unique row id for the entire table. This can be done using the Row_Number() function.

Let us generate the unique numbers by executing the following query:

SELECT ROW_NUMBER() OVER (ORDER BY ID) AS ROW,

ID,LNAME,FNAME FROM MYTABLE

This query produces the following results.

1


1


Ambelang


Jen


2


1


Ambelang


Jen


3


1


Ambelang


Jen


4


11


Hong


Jiong


5


21


Fukushima


Yuki


6


25


Mator


Sandra


7


25


Mator


Sandra


8


25


Mator


Sandra


9


35


Chang


Chun


Step 2

The second step in deleting duplicate rows is to generate unique row ids for every group.

Now, by using a co-related sub-query we can produce unique row ids for each group.

SELECT ROW,GROUPROW= CASE WHEN ID=ID

THEN

(SELECT COUNT(*) FROM (SELECT ROW_NUMBER() OVER (ORDER BY ID) AS ROW,

ID,LNAME,FNAME FROM MYTABLE

) AS A WHERE A.ID=B.ID AND

A.ROW<B.ROW)+1 END,ID,FNAME,LNAME FROM (SELECT ROW_NUMBER() OVER (ORDER

BY ID) AS ROW,

ID,LNAME,FNAME FROM MYTABLE

)AS B

This query would produce the following results with a unique id for each group.

1


1


1


Jen


Ambelang


2


2


1


Jen


Ambelang


3


3


1


Jen


Ambelang


4


1


11


Jiong


Hong


5


1


21


Yuki


Fukushima


6


1


25


Sandra


Mator


7


2


25


Sandra


Mator


8


3


25


Sandra


Mator


9


1


35


Chun


Chang


Step 3

The last step in deleting duplicate rows is to use the common table expression, as shown below.

WITH DUPLICATE(ROW,GROUPROW,ID,FNAME,LNAME)

AS

(

SELECT ROW,GROUPROW= CASE WHEN ID=ID

THEN

(SELECT COUNT(*) FROM (SELECT ROW_NUMBER() OVER (ORDER BY ID) AS ROW,

ID,LNAME,FNAME FROM MYTABLE

) AS A WHERE A.ID=B.ID AND

A.ROW<B.ROW)+1 END,ID,FNAME,LNAME FROM (SELECT ROW_NUMBER() OVER (ORDER

BY ID) AS ROW,

ID,LNAME,FNAME FROM MYTABLE

)AS B

)

DELETE FROM DUPLICATE WHERE GROUPROW<>1

Let us query all the rows from the source table, Mytable, by using the following Select Query.

SELECT * FROM MYTABLE ORDER BY ID

This query displays the following results:

1


Jen


Ambelang


11


Jiong


Hong


21


Yuki


Fukushima


25


Sandra


Mator


35


Chun


Chang


From the results, it is clear that duplicates from the table, Mytable, have been removed.

Conclusion

The main intent of this article was to demonstrate the use of SQL Server 2005's new feature Row_number() function and Common Table Expression, with the help of co-related sub-query, to delete duplicate rows.

SQL: Data partitioning in SQL Server 2005 - Part V (nice example)

Reference: http://www.databasejournal.com/features/mssql/article.php/3647561/Data-partitioning-in-SQL-Server-2005---Part-V.htm

Data partitioning in SQL Server 2005 - Part V


By Muthusamy Anantha Kumar aka The MAK<http://www.databasejournal.com/feedback.php/http:/www.databasejournal.com/features/mssql/article.php/3647561/Data-partitioning-in-SQL-Server-2005---Part-V.htm>

In Part IV<http://www.databasejournal.com/features/mssql/article.php/3647271> of this article series, we have seen how to partition an existing table based on certain range of dates.

Part V illustrates how to merge and split existing partitions.

First let us create a database with a table. Then let us insert data and then partition the table.

Step 1
Let us assume we have created the folder, C:\Data2, and the following subfolders. [Refer Fig 1.0]
C:\Data2\Primary
C:\Data2\FG1
C:\Data2\FG2
C:\Data2\FG3
C:\Data2\FG4
[cid:image001.jpg@01CD8ACB.2C95A050]
Fig 1.0
Step 2
Let us assume that we have a database, Data Partition DB3, with five different file groups, as shown below.
use master
IF EXISTS (SELECT name FROM sys.databases WHERE name = N'Data Partition DB5')
DROP DATABASE [Data Partition DB5]
GO
CREATE DATABASE [Data Partition DB5]
ON PRIMARY
(NAME='Data Partition DB5',
FILENAME=
'C:\Data2\Primary\Data Partition DB5.mdf',
SIZE=5,
MAXSIZE=500,
FILEGROWTH=1 ),
FILEGROUP [Data Partition DB5 FG1]
(NAME = 'Data Partition DB5 FG1',
FILENAME =
'C:\Data2\FG1\Data Partition DB5 FG1.ndf',
SIZE = 5MB,
MAXSIZE=500,
FILEGROWTH=1 ),
FILEGROUP [Data Partition DB5 FG2]
(NAME = 'Data Partition DB5 FG2',
FILENAME =
'C:\Data2\FG2\Data Partition DB5 FG2.ndf',
SIZE = 5MB,
MAXSIZE=500,
FILEGROWTH=1 ),
FILEGROUP [Data Partition DB5 FG3]
(NAME = 'Data Partition DB5 FG3',
FILENAME =
'C:\Data2\FG3\Data Partition DB5 FG3.ndf',
SIZE = 5MB,
MAXSIZE=500,
FILEGROWTH=1 ),
FILEGROUP [Data Partition DB5 FG4]
(NAME = 'Data Partition DB5 FG4',
FILENAME =
'C:\Data2\FG4\Data Partition DB5 FG4.ndf',
SIZE = 5MB,
MAXSIZE=500,
FILEGROWTH=1 )
Step 3
Let us assume that we have a table on the primary file group. You could create that table by executing the following T-SQL statement.
USE [Data Partition DB5]
go
CREATE TABLE MyTable
(ID INT NOT NULL,
Date DATETIME,
Cost money ) on [primary]
Step 4
Now let us create a unique clustered index on the table by executing the following T-SQL statement.
USE [Data Partition DB5]
go
CREATE UNIQUE CLUSTERED INDEX MyTable_IXC
ON MyTable(ID) on [PRIMARY]
Step 5
Now let us insert some data onto the table, MyTable, using the following T-SQL statements.
USE [Data Partition DB5]
go
declare @count int
set @count =-25
while @count <=100
begin
insert into MyTable select @count,getdate(),100.00
set @count=@count+1
end
set @count =101
while @count <=200
begin
insert into MyTable select @count,getdate(),200.00
set @count=@count+1
end
set @count =201
while @count <=300
begin
insert into MyTable select @count,getdate(),300.00
set @count=@count+1
end
set @count =301
while @count <=400
begin
insert into MyTable select @count,getdate(),400.00
set @count=@count+1
end
set @count =401
while @count <=800
begin
insert into MyTable select @count,getdate(),500.00
set @count=@count+1
end
Let us query the table using the T-SQL statement shown below.

select * from MyTable

ID Date Cost

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

-25 2006-11-09 15:12:01.177 100.00

-24 2006-11-09 15:12:01.207 100.00

-23 2006-11-09 15:12:01.207 100.00

-22 2006-11-09 15:12:01.223 100.00

-21 2006-11-09 15:12:01.223 100.00

-20 2006-11-09 15:12:01.223 100.00

-19 2006-11-09 15:12:01.240 100.00

.

.

.

0 2006-11-09 15:12:01.347 100.00

1 2006-11-09 15:12:01.347 100.00

2 2006-11-09 15:12:01.363 100.00

3 2006-11-09 15:12:01.363 100.00

.

.

792 2006-11-09 15:12:06.270 500.00

793 2006-11-09 15:12:06.270 500.00

794 2006-11-09 15:12:06.287 500.00

795 2006-11-09 15:12:06.287 500.00

796 2006-11-09 15:12:06.287 500.00

797 2006-11-09 15:12:06.300 500.00

798 2006-11-09 15:12:06.300 500.00

799 2006-11-09 15:12:06.317 500.00

800 2006-11-09 15:12:06.317 500.00

Now let us query the partition information of this table by executing the following query.

select * from sys.partitions where object_name(object_id)='MyTable'

You can see the result as shown below [Refer Fig 1.1]. It is obvious that all of the 826 rows are in the same partition.

[cid:image002.jpg@01CD8ACB.2C95A050]
Fig 1.1

Step 6

Partitioning of the tables depends on the partition range defined by Partition Function. Let us assume that we are going to partition the table into four parts, onto four different file groups.

use [Data Partition DB5]

GO

CREATE PARTITION FUNCTION [Data Partition Range](int)

AS RANGE LEFT FOR VALUES (100,200,300)

When we use LEFT clause in the range, the first partition could hold values from negative infinity to 100, the second partition could hold values from 101 to 200, the third partition could hold values from 201 to 300 and the fourth partition could hold values from 301 to infinity or whatever the max range of the integer value.

When we use RIGHT clause in the range, the first partition could hold values from negative infinity to 99, the second partition could hold values from 100 to 199, the third partition could hold values from 200 to 299 and the fourth partition could hold values from 300 to Infinity or whatever the max range of the integer value.

Execute the following query to see the partition information.

SELECT * FROM sys.partition_range_values

Step 7

Partition Function is not useful, unless it is associated with the proper file groups that we have created.

Let us assume that we are going to use file group [Data Partition DB5 FG1], [Data Partition DB5 FG2], [Data Partition DB5 FG3], [Data Partition DB5 FG4] for the partition table that we are going to create. This can be created as shown below.

USE [Data Partition DB5]

go

CREATE PARTITION SCHEME [Data Partition Scheme]

AS PARTITION [Data Partition Range]

TO ([Data Partition DB5 FG1], [Data Partition DB5 FG2], [Data Partition DB5 FG3],[Data Partition DB5 FG4]);

Step 8
Now, let us move the table to the new partition by using the following TSQL command.

Drop index MyTable_IXC on MyTable with (Move To [Data Partition Scheme] (ID) )

Now, let us query the partition information by using the following query.

select * from sys.partitions where object_name(object_id)='MyTable'

You can see the result as shown below [Refer Fig 1.2]. It is obvious that all of the 826 rows are spread across all four file groups, based on the partition range.

[cid:image003.jpg@01CD8ACB.2C95A050]
Fig 1.2

Now we have MyTable partitioned in to four different partitions. It is time to merge the first two partitions.

Merge partition

Execute the following T-SQL statement to merge the first two partitions together.

Alter partition function [Data Partition Range]()

merge range(100)

Now, let us query the partition information by using the following query.

select * from sys.partitions where object_name(object_id)='MyTable'

You will the see the following results showing the merge of partition number 1 and 2. [Refer Fig 1.3]

[cid:image004.jpg@01CD8ACB.2C95A050]
Fig 1.3

Split partition

Execute the following T-SQL statement to split last partition into two partitions.

Alter partition function [Data Partition Range]()

split range(300)

When executed you will get the following error.

Msg 7707, Level 16, State 1, Line 1

The associated partition function 'Data Partition Range' generates

more partitions than there are file groups mentioned in the scheme

'Data Partition Scheme'.

In order to avoid this, we have to add another file group to the database.

Let us execute the below T-SQl statement, which will add a new file group to the database.

Alter database [Data Partition DB5] add FILEGROUP [Data Partition DB5 FG5]

go

alter database [Data Partition DB5]

ADD FILE

(NAME = 'Data Partition DB5 FG5',

FILENAME =

'C:\Data2\FG4\Data Partition DB5 FG5.ndf',

SIZE = 5MB,

MAXSIZE=500,

FILEGROWTH=1 )

TO FILEGROUP [Data Partition DB5 FG5]

Go

Now let us try again to split the partition range by executing the following T-SQL statement.

Use [Data Partition DB5]

go

ALTER PARTITION SCHEME [Data Partition Scheme]

NEXT USED [Data Partition DB5 FG5]

GO

Alter partition function [Data Partition Range]()

split range(500)

Note: We should make SQL Server aware that it should use the next partition when it is splitting. The clause NEXT USED does that.

Now, let us query the partition information by using the following query.

select * from sys.partitions where object_name(object_id)='MyTable'

You will the see the following results showing the split of partition number 3. [Refer Fig 1.4]

[cid:image005.jpg@01CD8ACB.2C95A050]
Fig 1.4

You can see that any ID values between 300 and 500 are moved to partition 3 and any ID values more than 500 are moved to partition 4.

Conclusion

Part V of this article series has illustrated how to merge and split existing partitions.