Creating Clustered Index on SQL2008

  • The DB table I ran this on has about 44,000,000 rows of data and already has 5 non-clustered indexes. The Size on disk currently sits at approx. 57GB.

    After the clustered index completed the size was about 56GB. Why is this so big? Between the table itself and just one of the indexes that's enormous.

    Am I doing something wrong?

    CREATE CLUSTERED INDEX [idx_clu_MyBigTable] ON [dbo].[MyBigTable]

    (

    [pos-date] ASC,

    [ts-number] ASC,

    [tc-number] ASC,

    [ctrl-number] ASC,

    [prod-code] ASC,

    [reprice-date] ASC,

    [date-sent-tc] ASC

    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 98) ON [INDEX]

    ______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience

  • It took so long because after the clustered index was built, all the non-clustered indexes had to be rebuilt as well. Can't really say why it's so big not knowing what is in the table and the width of the indexes.

  • Since your 'HEAP' table has 57GB on disk, your 'Cluster indexed' table should have almost the same size on the disk.

  • It took so long because adding a clustered index is like rewriting your whole table into a new table + a sort + rebuilding the other non-clustered indexes.

    The clustered index size is your table size (the clustered index is your table).

  • Maybe I'm misunderstanding here, but you seem to be complaining because your database has got SMALLER after adding the clustered index (57Gb to 56Gb). Not sure why this is an issue! :ermm:

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

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