Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 347
  • 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

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

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