Monthly Archives: September 2012

SQL: Partition switching in SQL Server 2005

Posted on by mytechconnect

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

Partition switching in SQL Server 2005

2Comments

more +

  • Email
  • Print
  • Add to Favorites
  • Del.icio.us
  • Digg
  • Hacker News
  • LinkedIn
  • Reddit
  • Technorati

By 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. 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. 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

Posted on by mytechconnect

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: Data partitioning in SQL Server 2005 – Part V (nice example)

Posted on by mytechconnect

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

In Part IV 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:Data2Primary
C:Data2FG1
C:Data2FG2
C:Data2FG3
C:Data2FG4

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:Data2PrimaryData Partition DB5.mdf’,

SIZE=5,

MAXSIZE=500,

FILEGROWTH=1 ),

FILEGROUP [Data Partition DB5 FG1]

(NAME = ‘Data Partition DB5 FG1’,

FILENAME =

‘C:Data2FG1Data Partition DB5 FG1.ndf’,

SIZE = 5MB,

MAXSIZE=500,

FILEGROWTH=1 ),

FILEGROUP [Data Partition DB5 FG2]

(NAME = ‘Data Partition DB5 FG2’,

FILENAME =

‘C:Data2FG2Data Partition DB5 FG2.ndf’,

SIZE = 5MB,

MAXSIZE=500,

FILEGROWTH=1 ),

FILEGROUP [Data Partition DB5 FG3]

(NAME = ‘Data Partition DB5 FG3’,

FILENAME =

‘C:Data2FG3Data Partition DB5 FG3.ndf’,

SIZE = 5MB,

MAXSIZE=500,

FILEGROWTH=1 ),

FILEGROUP [Data Partition DB5 FG4]

(NAME = ‘Data Partition DB5 FG4’,

FILENAME =

‘C:Data2FG4Data 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