Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Partitioned table/index definition

Posted on 2009-05-11
5
Medium Priority
?
504 Views
Last Modified: 2012-05-06
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) PERSISTED.

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 
(
	[PartitionDate] ASC,
	[OrderID] ASC
)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])
GO

Open in new window

0
Comment
Question by:dbaSQL
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
5 Comments
 
LVL 51

Expert Comment

by:Mark Wills
ID: 24421876
Good question...

Yes, I believe the indexes MUST reference the partionkey...

Let me check a couple of things and I will get back soon...
0
 
LVL 51

Accepted Solution

by:
Mark Wills earned 2000 total points
ID: 24424976
Only when switching partitions as it turns out... You have to drop or disable the index first.

From BOL:

An index does not have to participate in the same named partition function to be aligned with its base table. However, the partition function of the index and the base table must be essentially the same, in that 1) the arguments of the partition functions have the same data type, 2) they define the same number of partitions, and 3) they define the same boundary values for partitions.

But, in reality, if your want any flexibility or speed gains, then your indexes really should be aligned to the partitions...

Read about "Special Guidlines for Partitioned Indexes" in BOL.

Designing a partitioned index independently (unaligned) of the base table can be useful in the following cases:
The base table has not been partitioned.
The index key is unique and it does not contain the partitioning column of the table.
You want the base table to participate in collocated joins with more tables using different join columns.

Note:  
To enable partition switching, all indexes on the table must be aligned.

0
 
LVL 17

Author Comment

by:dbaSQL
ID: 24440027
>>But, in reality, if your want any flexibility or speed gains, then your indexes really should be aligned to the partitions...

I think this is where I need to focus.
Thank you, Mr. Wills.

Correct me if I'm wrong -- forget about the computed column (PartitionDate).  Going back to my original design, my partition key is TimeField DATETIME2.  My PK is TimeField_OrderID.
That in itself makes the PK aligned.

>>>>>>>>>
Partitioning NonClustered Indexes
When partitioning a unique nonclustered index, the index key must contain the partitioning column. When partitioning a nonunique, nonclustered index, SQL Server adds the partitioning column by default as a nonkey (included) column of the index to make sure the index is aligned with the base table. SQL Server does not add the partitioning column to the index if it is already present in the index.
>>>>>>>>

Any additional indexes will be nonclustered, of course, and non-unique.  Because SQL adds the partitioning column by default --- I'm pretty much aligned, yes?

CREATE TABLE [dbo].[TableName](
	[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_TimeField_OrderID] PRIMARY KEY CLUSTERED 
(
	[TimeField] ASC,
	[OrderID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, 
ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 90) ON [schemename] ([TimeField])
) ON [schemename] ([TimeField])
GO

Open in new window

0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 24440280
Yes it does, but stating the obvious, it also means the partition must be defined first.

In books on line in sql 2008 there is a pretty good example using adventureworks on a not too dissimilar model as you are trying to do. Have you seen it ?

If you want I can try to track it down...
0
 
LVL 17

Author Comment

by:dbaSQL
ID: 24468581
nope, i got it, mr. wills.  thank you much
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

618 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question