mah_j (12/16/2012)
You mean besides that, I should check other options like indexing ,memory management,.....?
I mean that if you want to performance tune your query, then performance tune your query. Don't expect to partition the table and magically get a massive speed improvement.
In my implicit table (with 2 fields for clustered index(primary key)),is it possible to have partition key on a field which is not the subset of index key?
when I want to create the clustered index it says:
Column 'kind' is partitioning column of the index 'PK_Trans'. Partition columns for a unique index must be a subset of the index key.
The message is pretty clear there. "Partition columns for a unique index must be a subset of the index key."
and then if I use ON [PRIAMARY] like following script,I don't have partitioning on that table any more.
Well, no. ON PRIMARY says to put the table on the primary file group. To partition you put the table ON <partition scheme>
So how can I implement partitioning on this table with partitioning key which is defferent from index key?
When partitioning a unique index (and the primary key is unique), the partition key must be part of the index key. To partition that primary key, you have to add the partition column to the index. There's no way around that.
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