Partitioning Tables

Congratulations to Michael Fan, a software developer for Americall Group in Naperville, Illinois, and Stephen Tregidga, a BI analyst and developer, and his colleague Ken Chenery, a DBA, from Auckland, New Zealand. Michael won first prize of $100 for the best solution to the August Reader Challenge, "Partitioning Tables." Stephen and Ken won second prize of $50. Here's a recap of the problem and the solution to the August Reader Challenge.

Problem:

Dan is a software consultant who works with SQL Server 2000. One of his clients has a table that uses a uniqueidentifier column as a primary key. The volume of data in the table is increasing considerably, resulting in performance and management problems. One of the solutions Dan is considering is to partition the existing table based on the primary key column values. The script below creates a sample table with a uniqueidentifier column:

SELECT TOP 10000 newid() AS guid
INTO SalesDet
FROM master.dbo.sysobjects AS o1 CROSS JOIN master.dbo.sysobjects AS o2 CROSS JOIN master.dbo.sysobjects as o3

ALTER TABLE SalesDet ALTER COLUMN guid uniqueidentifier NOT NULL
ALTER TABLE SalesDet ADD CONSTRAINT pk_SalesDet PRIMARY KEY( guid )

Help Dan write a query that he can use to partition the table into a specified number of buckets. For example, Dan wants to partition the table into 13 different partitions based on the guid column and have the rows evenly distributed between the partitions.

Solution:

Dan learns about the binary_checksum() function which can be used to generate a checksum value based on a row of a table or expression. This system function computes the checksum value based on the byte representation of the expression or columns in the table. A sample query that shows usage of this function on the sample table "SalesDet" is shown below:

SELECT TOP 10 guid, binary_checksum(guid) as 'Binary Checksum'
  FROM SalesDet

Now, Dan can use the checksum value to partition the table into the desired number of buckets. This is done by using the modulus operator on the absolute value of the checksum and the number of partitions. The sample query that contains the expression to generate such a bucket value is shown below:

DECLARE @n int
SET @n = 13 	-- number of buckets
SELECT TOP 10 guid, (abs(binary_checksum(guid))%@n) + 1 AS bucket
  FROM SalesDet

Dan can check the distribution of the rows based on the computed bucket values by running the query below:

DECLARE @n int
SET @n = 13 -- number of buckets
SELECT (abs(binary_checksum(guid))%@n)+1 AS bucket, COUNT(*) AS cnt
  FROM SalesDet
GROUP BY (abs(binary_checksum(guid))%@n)+1

Dan can now add the bucket expression as a computed column in the table and use that filter and migrate the rows from the table into different partitions.

SEPTEMBER READER CHALLENGE:

Now, test your SQL Server savvy in the September Reader Challenge, "Defining Extended Properties" (below). Submit your solution in an email message to [email protected] by August 18. Umachandar Jayachandran, a SQL Server Magazine technical editor, will evaluate the responses. We'll announce the winner in an upcoming SQL Server Magazine UPDATE. The first-place winner will receive $100, and the second-place winner will receive $50.

Problem:

Michael is a database administrator in a company that hosts Web sites and databases. Every customer gets a separate database, and the databases are hosted on Microsoft SQL Server 2000. As part of the setup process for each database, Michael's company defines and stores some additional properties by using the extended properties feature. The properties can be string, integer, or date types. The following sample script shows some examples (from the Northwind and Pubs databases) of properties that Michael defines.

DECLARE @v sql_variant;
EXEC northwind.dbo.sp_addextendedproperty N'Prop1', N'Value1'
EXEC northwind.dbo.sp_addextendedproperty N'Prop2', 1
SET @v = cast('2005-7-1' as smalldatetime);
EXEC northwind.dbo.sp_addextendedproperty N'Prop3', @v;

EXEC pubs.dbo.sp_addextendedproperty N'Prop1', N'Value2'
EXEC pubs.dbo.sp_addextendedproperty N'Prop2', 2
SET @v = cast('2005-7-2' as smalldatetime);
EXEC pubs.dbo.sp_addextendedproperty N'Prop3', @v;

Michael needs to write a stored procedure that can search these extended properties and return the values. Help him do the following tasks:

1. Write a stored procedure to search the databases on a server for a specific property and value.
2. Be sure the stored procedure returns a list of the databases with the property name and value.
3. Specify string values as a pattern and search for it.
4. Search only for the specified value of other types.

Comments

Plain text