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
Could you please post the structure of your table and sample data? So, we can further more analysis your issue.
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)Read more...
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 :Read more...
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.Read more...
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:
DECLARE @newID INT
SELECT @newID = MAX(ID) FROM TestTable
INSERT INTO TestTable VALUES(@newID, 'Bob)
IF XACT_STATE() = -1
END CATCHRead more...
hi using SSIS to import data from SQL to flat file.
want to pass date as input
Thanks and RegardsRead more...
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.
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!
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 ThoughtsRead more...
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..
CREATE TABLE EMPLOYEES
empid int NOT NULL CONSTRAINT ix_pkEMPLOYEES PRIMARY KEY
, name varchar(25) NOT NULL
, age tinyint NOT NULL
CREATE CLUSTERED INDEX ixcEMPLOYEES ON EMPLOYEES (name)
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??Read more...
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.Read more...
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).
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..
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,Read more...
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?
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 (?).Read more...
Is there any way I can achive this without killing my server softly?
Can anyone help?