v2008, i've got a partioned structure in place. it's been in place for a while, actually, but i've made many changes as it has evolved.... long story short,
-we are partitioning by day (intake of 100M records daily)
-initially, i had created my function using the right range with 367 partitons. (365 for the days within the year, one before, one after) my partition key is 'TimeField', datetime2
-there are three tables, each on the scheme, with clustered PK on the scheme, of TimeField,OrderID
so, this was all well and good. I have migrated a TON of data from the v2000 non-partitioned version of the same database into 08. working to get things together such that we can officially upgrade.
last week, however, i found this: http://msdn.microsoft.com/en-us/library/dd578580.aspx
Within which i noted reference to using the persisted computed column as my partition key. i decided this was more to my gain, as the 'hot' data is always current day. it's not time frames within the current day. it is just today. or yesterday, or the day in question, in entirety.
I created a new partiton scheme, also daily, 367 partitions, only the partionkey is 'PartitionDate', which is a computed column, based on the 'TimeField' that I receive with each insertion, this is in place on each of the tables.
They give me TimeField, my PartitionDate is CONVERT(DATE,TimeField,112
Again, all well and good... in retrospect, however, I believe it is not do-able. The storage of the data, good, but possibly not the retrieval thereof. Nobody is going to look at PartitionDate. They're going to go in by TimeField, which is a datetime2.
I had created the table as defined below, dropped the PK and pumped a TON of data into place. I then recreated the PK just fine. I then attempted the aligned index on 'TimeField' such that the applications actually looking at the data can do so efficiently. Of course, my index creation failed with this:
Msg 7726, Level 16, State 1, Line 2
Partition column 'TimeField' has a data type of datetime2(7) which is different from the partition function 'functionname' parameter data type date.
And I am smacked in the face with the reminder that the aligned indexes must reference the partionkey, and the datatype of my new one isn't going to work in relation to the TimeField in my tables.
Not a big deal -- I didn't drop the old stuff, I just created a new scheme/function/tables alongside. Now I'm curious simply about the design of these entities.
OrderID is bigint and unique.
The paritions will be daily.
What is the optimal means of definiing the table and indices in this regard?
CREATE TABLE [dbo].[TableName](
[PartitionDate] AS CONVERT(DATE,Eventtime,112) PERSISTED,
[InsertTime] [datetime2](7) NOT NULL,
[TimeField] [datetime2](7) NOT NULL,
[OrderID] [bigint] NOT NULL,
[GID] [int] NOT NULL,
[EndPoint] [varchar](8) NOT NULL,
[UserID] [varchar](16) NOT NULL,
[AcctID] [varchar](16) NOT NULL,
[ClientID] [varchar](40) NOT NULL,
[Symbol] [varchar](16) NOT NULL,
[Side] [char](1) NOT NULL,
[TimeInForce] [varchar](5) NOT NULL,
[ExecInstruction] [varchar](10) NULL,
CONSTRAINT [PK_TableName_PartitionDate_OrderID] PRIMARY KEY CLUSTERED
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF,
ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [schemename] ([PartitionDate])
) ON [schemename] ([PartitionDate])