sqldba2013
asked on
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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Scott Pletcher
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).
Whoa: to be technically accurate, I did not specify "primary key" but "unique columns"
Good point! I misread.
Good point! I misread.
ASKER
Thanks for your advise.