does partitioning table need clustered Index?

  • 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?

  • 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

  • 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