August 30, 2010 at 4:17 am
Does partitioning table need clustered Index on key( example Id or Date)
If i have a table ( ArticleDate datatime, ArticleId int ,ArticleTitle ,.....) .
when I want to partitioning this table on ArticleDate I must create clustered index on ArticleDate?
If I create clustered index on ArticleId can in create partition by ArticleDate?
September 1, 2010 at 2:38 am
adnani (8/30/2010)
Does partitioning table need clustered Index on key( example Id or Date)If i have a table ( ArticleDate datatime, ArticleId int ,ArticleTitle ,.....) .
when I want to partitioning this table on ArticleDate I must create clustered index on ArticleDate?
See creating a index on partitioned table is optional (but recommended).
adnani (8/30/2010)
Does partitioning table need clustered Index on key( example Id or Date)If I create clustered index on ArticleId can in create partition by ArticleDate?
See the basic idea abt table partitioning is to handle the size of the table and then divide the data logically in groups, based on some partitioning key now if your clustered index is on different field then I don't think it make sense. And I don't think it's possible too...
Lets wait for other members to post there thoughts on this...
Rohit
September 1, 2010 at 3:15 am
Partitioning does not require a clustered index at all.
Here's a script I use to demonstrate that fact.
It also demonstrates the indexed view switching possible in SQL Server 2008.
If you are stuck with 2005, just skip creating the view.
USE master;
GO
-- Demo database
CREATE DATABASE [03B5CBA0-87C1-4504-B849-4FA3A4963082];
GO
-- Create file groups
ALTER DATABASE [03B5CBA0-87C1-4504-B849-4FA3A4963082] ADD FILEGROUP FG1;
ALTER DATABASE [03B5CBA0-87C1-4504-B849-4FA3A4963082] ADD FILEGROUP FG2;
ALTER DATABASE [03B5CBA0-87C1-4504-B849-4FA3A4963082] ADD FILEGROUP FG3;
ALTER DATABASE [03B5CBA0-87C1-4504-B849-4FA3A4963082] ADD FILEGROUP FG4;
-- Create files (uses c:\temp folder)
ALTER DATABASE [03B5CBA0-87C1-4504-B849-4FA3A4963082] ADD FILE (NAME = file1, FILENAME = 'c:\temp\file1.ndf', SIZE = 512KB) TO FILEGROUP FG1;
ALTER DATABASE [03B5CBA0-87C1-4504-B849-4FA3A4963082] ADD FILE (NAME = file2, FILENAME = 'c:\temp\file2.ndf', SIZE = 512KB) TO FILEGROUP FG2;
ALTER DATABASE [03B5CBA0-87C1-4504-B849-4FA3A4963082] ADD FILE (NAME = file3, FILENAME = 'c:\temp\file3.ndf', SIZE = 512KB) TO FILEGROUP FG3;
ALTER DATABASE [03B5CBA0-87C1-4504-B849-4FA3A4963082] ADD FILE (NAME = file4, FILENAME = 'c:\temp\file4.ndf', SIZE = 512KB) TO FILEGROUP FG4;
GO
-- Switch to demo database
USE [03B5CBA0-87C1-4504-B849-4FA3A4963082];
GO
-- Create partition function and scheme
CREATE PARTITION FUNCTION PF (INTEGER)
AS RANGE RIGHT
FOR VALUES (2008, 2009, 2010);
GO
CREATE PARTITION SCHEME PS
AS PARTITION PF
TO ([FG1], [FG2], [FG3], [FG4]);
GO
-- Test table 1
CREATE TABLE dbo.Example
(
row_id INTEGER IDENTITY NOT NULL,
some_date DATE NOT NULL,
some_value SMALLMONEY NOT NULL,
the_year AS
YEAR(some_date)
PERSISTED
NOT NULL
)
ON PS(the_year);
GO
-- Test table 2
CREATE TABLE dbo.Archive
(
row_id INTEGER IDENTITY NOT NULL,
some_date DATE NOT NULL,
some_value SMALLMONEY NOT NULL,
the_year AS
YEAR(some_date)
PERSISTED
NOT NULL
)
ON PS(the_year);
GO
-- Add some rows
INSERT dbo.Example (some_date, some_value)
SELECT TOP (365 * 4 + 1)
some_date = DATEADD(DAY, ROW_NUMBER() OVER (ORDER BY C1.object_id) - 1, '2007-01-01'),
some_value = RAND(CHECKSUM(NEWID())) * 200 - 50
FROM master.sys.columns C1,
master.sys.columns C2,
master.sys.columns C3;
GO
-- Shows 365 rows per partition
SELECT CA.partition_number,
row_count = COUNT_BIG(*)
FROM dbo.Example T1
CROSS
APPLY (SELECT $PARTITION.PF(T1.the_year)) CA (partition_number)
GROUP BY CA.partition_number
ORDER BY CA.partition_number;
GO
CREATE VIEW dbo.YearMonthSummary
WITH SCHEMABINDING
AS
SELECT T1.the_year,
the_month = MONTH(T1.some_date),
row_count = COUNT_BIG(*),
gah = SUM(CONVERT(INTEGER, T1.some_value))
FROM dbo.Example T1
GROUP BY
T1.the_year,
MONTH(T1.some_date);
GO
CREATE UNIQUE CLUSTERED INDEX [CUQ dbo.YearMonthSummary the_year, the_month]
ON dbo.YearMonthSummary
(the_year, the_month)
ON PS (the_year);
GO
-- Select from a single partition
DECLARE @Year INTEGER = 2008;
SELECT E.row_id,
E.some_date,
E.some_value
FROM dbo.Example E
WHERE E.the_year = @Year;
-- Partition switch
ALTER TABLE dbo.Example
SWITCH PARTITION 2
TO dbo.Archive
PARTITION 2;
GO
-- Show tables and partitions
WITH CTE
AS (
SELECT source_table = 'Example', *
FROM dbo.Example
UNION ALL
SELECT source_table = 'Archive', *
FROM dbo.Archive
)
SELECT CTE.source_table,
CA.partition_id,
row_count = COUNT_BIG(*)
FROM CTE
CROSS
APPLY (SELECT $PARTITION.PF(CTE.the_year)) CA (partition_id)
GROUP BY
CTE.source_table,
CA.partition_id
ORDER BY
CA.partition_id;
GO
-- Same thing, done a different way
SELECT source_table = 'Example',
CA.partition_id,
row_count = COUNT_BIG(*)
FROM dbo.Example T1
CROSS
APPLY (SELECT $PARTITION.PF(T1.the_year)) CA (partition_id)
GROUP BY CA.partition_id
UNION ALL
SELECT 'Archive',
CA.partition_id,
row_count = COUNT_BIG(*)
FROM dbo.Archive T1
CROSS
APPLY (SELECT $PARTITION.PF(T1.the_year)) CA (partition_id)
GROUP BY CA.partition_id
ORDER BY CA.partition_id;
GO
-- Page compress Example table online
ALTER TABLE dbo.Example
REBUILD
PARTITION = ALL
WITH (
DATA_COMPRESSION = PAGE,
ONLINE = ON
);
GO
-- Change partition 4 to row compression offline
ALTER TABLE dbo.Example
REBUILD
PARTITION = 4
WITH (
DATA_COMPRESSION = ROW
);
GO
-- Show index and heap partition information
SELECT SP.index_id,
SP.partition_number,
SP.partition_id,
SP.[rows],
SP.data_compression_desc
FROM sys.partitions SP
WHERE SP.[object_id] = OBJECT_ID(N'dbo.Example', N'U')
ORDER BY
SP.index_id,
SP.partition_number;
GO
-- Tidy up
USE master;
GO
DROP DATABASE [03B5CBA0-87C1-4504-B849-4FA3A4963082];
GO
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply