• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 352
  • Last Modified:

Table Partition in SQL Server 2012

Hi All,

We have table called employee and this table doesn't have primary key column. This table contains heavy records and to improve performance of query we are planning to implement Table Partition.

Table structure :
create table employee
( X_Name varchar(10), Sent date, Y_Lastname varchar(5));

We have tried to create Table partition with the column Sent (create secondary files on the basis of date). But this column contains duplicate values and we are not able to create primary key constraint for partition schema.

Please suggest how to create table partition with existing table if table does not have primary key column.

NOTE: we have tried to create new column with the name of ID as a identity and we didn't get any performance improvements by adding new column to existing table.

We are referring below URL to setup Table Partition in our environment.

http://dotnetstories.wordpress.com/2012/05/05/create-and-manage-partitioned-tables-in-sql-server/

Thanks in advance.
0
sqldba2013
Asked:
sqldba2013
  • 2
  • 2
2 Solutions
 
Scott PletcherSenior DBACommented:
An employee table without unique columns?  How do you tell one emp from another??

Is the combination of ( X_Name, Y_Lastname ) unique?

You very likely just need to properly cluster the table to get performance, not partition.
0
 
Anthony PerkinsCommented:
This table contains heavy records and to improve performance of query we are planning to implement Table Partition.
Please don't make this assumption.  Table Partitioning does not imply better performance,  It can, it can also be worse.  Typically the reason you implement table partitioning is to improve maintenance, especially if you split the partitions in different FileGroups.

But to answer your question: consider creating a (non-unique) clustered index (not a Primary Key) on that column.  But I agree with Scott, that it is odd that you do not have an appropriate primary key.
0
 
Scott PletcherSenior DBACommented:
Whoa: to be technically accurate, I did not specify "primary key" but "unique columns".  Now, logically they are exactly the same.  But in the context of a physical table, as here, a PK constraint can be different from unique clustering column(s).
0
 
Anthony PerkinsCommented:
Whoa: to be technically accurate, I did not specify "primary key" but "unique columns"
Good point!  I misread.
0
 
sqldba2013Author Commented:
Thanks for your advise.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now