Home » SQL Server

Partition a table weekly on date column and create Clustered index on the Identity column

Hi Guys 

I am in situation here. I want to partition a table week wise and keep the data of last 4 weeks in the table. I ll be merging the oldest partition every week and adding another partition for the new records.

I thought I can partition the table on the date column in the table and it does work. My question how'll i create the clustered index in the table. I want to create on the Identity column. If I make the index this would again merge all the data and there would not be any partitioning left.


Can you please suggest me how will it work ? Or i have to change the partition function on some INT values instead of the date time.

Please give your valuable advices......





3 Answers Found


Answer 1


Could you please post the structure of your table and sample data? So, we can further more analysis your issue.

Ai-Hua Qiu


With SQL 2008 Storage Partition wizard I had noted that the script it creates for index partitioning does the following:

Drops primary key contraint.

Recreated Primary key Non Clustered

Creates Clustered index on the Partition Scheme and then drops the clustered index it just created.

Why does it drop the clustered index, doesn't that have to remain to continue the partitioning of data?







i dont have a primary key in my table and want to create a clustered index on one of the column.

Without writing scripts how do i do this?coz it creates only non clustered index through SSMS(modify table>indexes)


When we migrated a database project from VS2005 DataDude  to VS2008 Team System Database Edition GDR R2 and try to deploy the project, the error :

Error TSD01268: Cannot insert explicit value for identity column in table 'tmp_ms_xx_' when Identity_Insert is set to OFF

occurs for one of the tables. 
   When I cheked the deployment script, I noticed that during the unbinding of the table, before the INSERT statement for tmp_ms_xx_ table , there is a SET IDENTITY_INSERT tmp_ms_xx_ ON.

But when the table is rebuild - there is no such SET IDENTITY...  clause before INSERTing data and this is the place where the error occurs.
I noticed also that this is the situation with the tables which have some changes in their structure and these changes have to be aplied/deployed on the server.
Here I found some similar problem:

our changes are not related to replication, but to removing of some columns, and the column names casing is correct.



I am using vba for access 2000 to create and update some tables. However, when I use CREATE INDEX sql to create indices on multiple fields, it's not working. Even though it's creating the index specified, when I check the table design the fields are not indexed. The syntax I am using is something like this:

DoCmd.RunSql "CREATE INDEX idx_table1 on table1(field1,field2,field2)"

It's working fine when used for only one field.

Any help would greatly be appreciated.



How to change cluster index to non cluster index, if it is primary key?




I have a table with an ID column of type INT. It is not an identity column and for some reason, the dba's here do not want to change it into an autoincrement id column.

I was wondering what the best way to create a new id, insert it into the table and retreive the id into a variable all in one step.

This is my first cut, but it seems overboard:




SELECT @newID = MAX(ID) FROM TestTable

INSERT INTO TestTable VALUES(@newID, 'Bob)








hi using SSIS to import data from SQL to flat file.

want to pass date as input


Thanks and Regards


I have a table that is used for staging.  It has a unique clustered index on it that has the same partitioning as the table.  I want to know if there is a way for me to add a non-clustered, non-partitioned index to the table and still be able to do the partition swapping.  Are there any work arounds?  I have found that because the indexes are 'not-aligned' the swapping fails to insert into the table with the multiple indexes.



I am creating a partitioned table. With non-partition tables, I set up PRIMARY, DATA, INDEX and LOG filegroups to hold respective information. With table partitioning it seems like DATA and INDEX "HAS" to live in the same filegroup. I have created a function and schema that maps the filegroup to the function. I wanted to confirm that DATA and INDEX in a partitioned table have to live on the same filegroup. Or, is it possible for DATA and INDEX to live in different filegroups in a partitioned table.

Any pointers and guidelines is appreciated. I have used Technet articles from Kimberly Tripp and SQL Server 2008.


Thank you.



Few questions.

I'm trying to create a unique index on a partitioned table. Why must the partitioning column be a subset of any unique index for that table (or a primary key)?

Adding a little complexity. I want to partition a table which has a full-text indexed column. For a full text index, I need a unique single-column index. However, I cannot create such an index unless it includes the partitioning column (see above).
      -- it seems a workaround would be to create the unique index for full-text in a separate filegroup. Can you explain the reasons behind this?

And last question: Is it possible to full-text index only one partition?

Thanks a lot!


Hello all,

I was curious if anyone has been able to get around the limitation of switching data in or out of a partitioned table that an indexed view is built on.

I spoke with MS concerning the issue and I was pretty much shutdown, they said that though the underlining hobits are not necessarily bound to a specify object rather they bound to a data space which is really a way for SQL to point to the page chain for that set partition.(key thing here is they meaning the partitions are NOT schema bound) They did say that that may be integrated into the NEXT version of SQL... :-( (yet we have to wait)

Please correct me if am wrong here ... if indexed views and partitioned tables is MS way of dealing with VLDB environment... why wouldn't they design them to play nicely which each other? otherwise if you have a sliding window environment and you want to SWITCH data in or out of a table that is (1) partitioned and (2) has an indexed view built on it, your now potentially stuck now you have to drop the schema binding on the indexed view (hence dropping any indexes you may have created on it ) then perform you data switching maintenance then REBUILD your indexed views. That in its self is a BIG turn off. I don’t know about you but I don’t get a lot of maintenance time, and the time I do get I try to be as efficient as I can. (Dropping and rebuilding indexed views for 40 plus hours is NOT efficient.) The alternative is deleting data recorded by record. (And we all know how pleasant that is when you are deleting millions of records)

Any Thoughts



    I am working on sql server 2008 and i am very much confused about non-clusterIndexing ,as i know about the benefits/advantages of Clustere Indexing on a Table and there must be  one cluster Index in a Table But I dont have clear knowledge about Non-ClustereIndexing .I just Know that can be  249 in a table but my issue is:

1)Is Non-Cluster Index columns and foreign key columns are same thing

2) how Non-Cluster index helps us  to improve sqlsever Performance.

3) How Much It can be in a table and can we user it on any type of column or only Interger type of Column.

4) Which is the best way to use it

Plz give me complete knowledge about Non-Cluster Indexing..Thanks alot



Hi, can anybody tell me is this the standard syntax i should use or any suggestion for Clustered index on Non primary key column and non clustered index on primary key column.

It does work but i dont know if i should go with this..

, name varchar(25) NOT NULL
, age tinyint NOT NULL




I am evaluating whether to create an Indexed View for a frequent query. As I was digging into Indexed Views, I noticed that they require a Unique Clustered Index.

For my query, there is no single unique column. But a combination of 2 columns is unique for the query. So, is it possible to create a unique clustered index out of 2 columns for a indexed view??



I have very big database, i am running a Re-index job and it's keep failing throwing everyday error stating "Online index operation cannot be performed on clustered index as index contains column of data type text, nvarchar.

is there any way i can search and change all such datatype of culstered into non-clustered indexes or any better solution i have.

I am deleting data from a table , lets call it PKTable, which has ORDERID as the Primary Key. The SQL plan does a clustered index delete, but there is a
Nonclustered scan on a different table , lets call it FKtable, which has a column ORderID, FK referencing PKTable.OrderID. There is a single column non clustered index on the FK table on the ORderId column. During the delete on the PKTable, SQL does a non clustered index scan doing huge reads (84%), instead of a index seek. I think SQL is enforcing RI here, but Index scan is causing the slowness.

Please let me know what could be going on here. I tried to duplicate in the Dev environment, but I see a index seek in dev. I am doing a update statistics with full scan on the NCL on the FKTable, the FK table has about 24 million rows. My options are  (1)change update stats to Rebuild the index on the FK table (2) Rewrite the Proc not to use dynamic sql (the proc is using  parameterized dynamic sql  (sp_excesql) to delete the data from the PKTable).


Hi Team,

 I have one small dought,..

Can you any body correct me.

when ever we create a primary key in a table automatically clustered index is created right 

what is the task can do in Primarykey here

and what is the task can do in Clustered index here..

can you please guide me..




Hi Team,


Can you correct me.


I have one table i have create primary key on that. automatically clustered index also created right.


here what is the purpose of primaykey and clustered index also


in case i have drop the clusted index what happend..




Hi All,

Got a table in SQL 2008 which has records for different states.
What will be a better way to go for partition for... Example 26 partitions as A, B, C etc.?
Creating partitions with names of States such as CA, AZ, OH etc or all individual cases??

In either of 2 cases, what can be the partition for other exceptional data?

Please suggest.



Hi everyone,

I´m doing a heavy query from SSIS, loading about 450M records.

The query looks something like:

Select x.id, x.customer_id, x.product_id, y.Productname, z.Priceplan
From staging x
left outer join y ...
left outer join z ..

Where there is a clustered index on x.id

Now I would very much like these records to be loaded in the same order as the x.id, as they are stored in a fact table, containg the same Id as clustered index. If I run the query, the order is somewhere close, but not exactly the same as x.id from my source table. If I add "order by x.id ASC" the performance moves from pretty smooth to disaster.

I was thinking I would get the original x.id order as this was my "primary source table", but I guess I have misunderstood this completely (?).
Is there any way I can achive this without killing my server softly?
Can anyone help?


<< Previous      Next >>

Microsoft   |   Windows   |   Visual Studio   |   Sharepoint   |   Azure