April 30, 2012 at 4:36 pm
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.
🙂
April 30, 2012 at 4:50 pm
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
April 30, 2012 at 4:54 pm
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