Post reply

Partitioning on Existing table with non clustered index in date column.

  • June 27, 2013 at 3:33 am

    #274494

    Hi All,

    I have one table with 10 lacks records. I partitioned that table on CreatedDate column with non clustered index

    ( i am not removing clustered index on ID column, It is as part of primary key).

    It is inserting to data into relevant partition only. But i am verifying is that table partitioned or not by using below steps, in object Explorer-Database-->TestDB-->tables-->select partitioned table and Right click on table select properties --Storage

    File Group= Primary

    Table Partitioned = False

    If create Partitioned with Clustered index , it is showing correctly Table Partitioned = True But i am creating with non clustered.

    Can any one explain,

    Is that below steps are correct process to do the partition, If i create non clustered index on datetime column is there any performance issue on existing queries.

    CREATE TABLE tblPartition(ID int primary key identity(1,1),Name varchar(30),CreatedDate Datetime)

    insert into tblPartition(Name,CreatedDate)

    SELECT 'Name1','2013-05-26 13:53:47.650'

    union all

    select 'Name2','2013-05-26 13:53:47.650'

    union all

    SELECT 'Name1','2013-06-26 13:53:47.650'

    union all

    select 'Name2','2013-06-26 13:53:47.650'

    union all

    SELECT 'Name1','2013-07-26 13:53:47.650'

    union all

    select 'Name2','2013-07-26 13:53:47.650'

    go

    CREATE PARTITION FUNCTION [PartitionFunction](datetime) AS RANGE RIGHT FOR VALUES (N'2013-05-31 23:59:59', N'2013-06-30 23:59:59', N'2013-07-31 23:59:59')

    CREATE PARTITION SCHEME [PartitionScheme] AS PARTITION [PartitionFunction] TO ([FGNdf10], [FGNdf11], [FGNdf12], [PRIMARY])

    CREATE NONCLUSTERED INDEX [IX_PartitionScheme_CreatedDate] ON [dbo].[tblPartition]

    (

    [CreatedDate]

    )WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [PartitionScheme]([CreatedDate])

  • June 27, 2013 at 7:10 am

    #1627946

    Any reply pls.....?

  • July 1, 2013 at 4:46 am

    #1628660

    Waiting for your answers ...

    thanks

  • July 1, 2013 at 7:16 am

    #1628693

    You have created a partitioned index. you need to recreate the table on your partition scheme, or use the clustered index as the partitioned index.

    The probability of survival is inversely proportional to the angle of arrival.

  • July 1, 2013 at 11:30 pm

    #1628899

    Hi Sturner,

    Thanks for your reply.

    Table already existed and having 86 millions data so already table have primary key with clustered index on ID column , so not possible to remove clustered index on ID column . If i remove, it will impact on exiting queries performance, that why i crated non clustered index on crated_date column.

    How Can I alter my existing table on my existing partition scheme.

    Can you please explain how to create partition with non clustered index on existing table .

    thanks.

  • July 2, 2013 at 4:07 am

    #1628967

    I would suggest you read this article by Gail https://www.simple-talk.com/sql/database-administration/gail-shaws-sql-server-howlers/

    Also this white paper http://msdn.microsoft.com/en-us/library/dd578580(SQL.100).aspx on Partitioning and indexing strategy.

    _________________________________________________________________________
    SSC Guide to Posting and Best Practices

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply