Changing Filegroup names and Filegroup files - midstream. . .

  • GilaMonster (4/30/2012)


    I need the EXACT create index statement that you ran, the one that failed.

    p.s. If you're partitioning on dates, partition RIGHT, not LEFT. Partitioning left with dates is horrid, and your partition function allows for some dates to slip into the wrong partition, because there are valid dates between 2011-05-31 23:59:59 and 2011-06-01 00:00:00.

    Gila to the rescue! I knew you would have more insight into this!

    Please let me know if the code enough is sufficient to help me troubleshoot this. Those are the major 4 pieces (1 of which is just the current Index on the Partitioned Table) to look at that I am using.

    🙂

  • No offence, but did you look in Books Online?

    Under create index, there's a clear example of creating a partitioned index

    CREATE NONCLUSTERED INDEX IX_TransactionHistory_ReferenceOrderID

    ON Production.TransactionHistory (ReferenceOrderID)

    ON TransactionsPS1 (TransactionDate);

    When you specify a partition scheme, you need to tell it which column is going to be used for the partition function, as in the above example.

    CREATE UNIQUE CLUSTERED INDEX PK_BW_RECORD_EVENT_NEWFORMAT2

    ON dbo.BW_RECORD_EVENT_NEWFORMAT2 (ID, GPS_RECORD_DATE)

    WITH (DROP_EXISTING = ON) ON [RecordsPartitionScheme] (GPS_RECORD_DATE)

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • No offense taken - I did look in BOL, but the examples I was looking for were specific to CREATE UNIQIUE CLUSTERED INDEX and primarily, just under the CREATE INDEX catalog entry.

    My aplogies there Gail, but let me give this a try, and I will make that one modification you recommended with partition RIGHT instead of LEFT in the FUNCTION, and I will report back more afterwards.

    Thank you both again!

    EDIT: P.S. Yep - there it is...example J. Creating a partitioned Index (it's the very last example in the doc...My apologies again Gail).

Viewing 3 posts - 31 through 32 (of 32 total)

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