Data Warehousing :: Primary Key Has Both A Clustered And Non-clustered Constraint
Sep 30, 2015
I have a really super slow stored proc that does something simple. it updates a table if certain values are received.
In looking at this the matching is done on the Primary Key, which is set as a Clustered index, looking further I have another constraint, that sets the same column to a Unique, Non-Clustered.
I am not sure why this was done, but it seems to be counter productive. I have read only references to Which one is better on a primary key, but not can their be both and if it is "Smart".
I have created two tables. table one has the following fields,
Id -> unique clustered index. table two has the following fields, Tid -> unique clustered index Id -> foreign key of table one(id).
Now I have created primary key for the table one column 'id'. It's created as "nonclustered, unique, primary key located on PRIMARY". Primary key create clustered index default. since unique clustered index existed in table one, it has created "Nonclustered primary key".
My Question is, What is the difference between "clustered, unique, primary key" and "nonclustered, unique, primary key"? Is there any performance impact between these?
I've a table with primary key defined as non-clusterd, now without dropping it can I modify the existing index to clustered through tsql as I had to write some migration script and in that script I wanna do this.
We have a table, which has one clustered index and one non clustered index(primary key). I want to drop the existing clustered index and make the primary key as clustered. Is there any easy way to do that. Will Drop_Existing support on this matter?
We are going to use SQL Sever change tracking. The problem is that some of our tables, which are to be tracked, have no primary keys. There are only unique clustered indexes. The question is what is the best way to turn on change tracking for these tables in our circumstances.
I desire to have a clustered index on a column other than the Primary Key. I have a few junction tables that I may want to alter, create table, or ...
I have practiced with an example table that is not really a junction table. It is just a table I decided to use for practice. When I execute the script, it seems to do everything I expect. For instance, there are not any constraints but there are indexes. The PK is the correct column.
CREATE TABLE [dbo].[tblNotificationMgr]( [NotificationMgrKey] [int] IDENTITY(1,1) NOT NULL, [ContactKey] [int] NOT NULL, [EventTypeEnum] [tinyint] NOT NULL,
Hi there, I have a table that has an IDENTITY column and it is the PK of this table. By default SQL Server creates a unique clustered index on the PK, but this isn't what I wanted. I want to make a regular unique index on the column so I can make a clustered index on a different column.
If I try to uncheck the Clustered index option in EM I get a dialog that says "Cannot convert a clustered index to a nonclustered index using the DROP_EXISTING option.". If I simply try to delete the index I get the following "An explicit DROP INDEX is not allowed on index 'index name'. It is being used for PRIMARY KEY constraint enforcement.
So do I have to drop the PK constraint now? How does that affect all the tables that have FK relationships to this table?
I am having a little trouble getting this to work right, but have come a ways since I started this.......other tables created first and with no problems..... then these two with the last table being the problemI need to set one foreign key in the second table referencing the first table.But, the primary key is clustered with the two foreign keys and I get the error....There are no primary or candidate keys in the referenced table 'courseScores' that match the referencing column list in the foreign key 'FK_course'.CREATE TABLE dbo.courseScores ( courseId varchar(20) NOT NULL CONSTRAINT FK_courseId_courseStructure2 FOREIGN KEY (courseId) REFERENCES courseStructure (courseId), studentId varchar(20) NOT NULL CONSTRAINT FK_studentId_students2 FOREIGN KEY (studentId) REFERENCES students (studentId), CONSTRAINT PK_courseScore PRIMARY KEY CLUSTERED (courseId, studentId) )CREATE TABLE dbo.objScores ( tmp int IDENTITY(1,1) PRIMARY KEY, objective varchar(50) NOT NULL, courseId varchar(20) NOT NULL CONSTRAINT FK_course FOREIGN KEY (courseId) REFERENCES courseScores (courseId) ) Once I get it working, then the tmp will be gone and then set 3 foreign keys as the clustered primary, fyi.Not sure how to reference half a primary key?Any help is greatly appreciated.....Thanks all,Zath
I'm changing the collation sequence of a field which is a primary, clustered key field via:
ALTER TABLE [dbo].[clusterAlgorithm] WITH NOCHECK ADD PRIMARY KEY CLUSTERED ( [ClusterAlgorithmClassName] ) ON [PRIMARY] GO
Is there a way to drop the primary key designation before doing an alter table/alter column statement and then recreating the key, or must I drop and recreate the table?
I want to keep certain archive data in certain tables. One such table is currently about 190 GB in size. It has a primary key with clustered index and three non-clustered indexes. The type of queries fired are strictly selects (daily) and inserts (only monthly).
Question: Is it advisable to have a non-clustered index on the primary key column?.....I am finding that the insert performance is getting hurt due to presence of clustered index on such a large table (190 GB).
I have created a very simple table. Here is the script:
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[IndexTable]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [dbo].[IndexTable]
GO
CREATE TABLE [dbo].[IndexTable] ( [Id] [int] NOT NULL , [Code] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ) ON [PRIMARY]
GO
CREATE CLUSTERED INDEX [CusteredOnCode] ON [dbo].[IndexTable]([Id]) ON [PRIMARY]
GO
ALTER TABLE [dbo].[IndexTable] ADD CONSTRAINT [PrimaryKeyOnId] PRIMARY KEY NONCLUSTERED ( [Id] ) ON [PRIMARY] GO
The records that i added are:
Id Code
1 a 2 b 3 aa 4 bb
Now when i query like
Select * from IndexTable
I expect the results as:
Id Code
1 a 3 aa 2 b 4 bb
as i have the clustered index on column Code.
But i m getting the results as:
Id Code
1 a 2 b 3 aa 4 bb
as per the primary key order that is a non clustered index.
All of the 3 books I've read say it is not a good idea to create a clustered index on the primary key but it is created as the default. My question is has this changed in 2005? My understanding is to create the clustered index on columns used first in join clauses and then in where clauses, what is the answer?
I am trying to drop a primary key on column LID and then create a clustered index on a new identity column ID and then add the primary key back on the LID. I am not able to do so due the table being in replication. here is the error:
Cannot alter the table '' because it is being published for replication.
How do I get past the error and create the Clustered Index on ID column in both publisher and subscriber?
I would like to find information on Clustered and Non-clustered indexes and how B-trees are used. I know a clustered index is placed into a b-tree which makes sense for fast ordered searching. What data structure does a non-clustered index use and how? I tried to find info. on the web but couldn't get much detail...
I have a table<table1> with 804668 records primary on table1(col1,col2,col3,col4)
Have created non-clustered index on <table1>(col2,col3,col4),to solve a performance issue.(which is a join involving another table with 1.2 million records).Seems to be working great.
I want to know whether this will slow down,insert and update on the <table1>?
SELECT a.AssetGuid, a.Name, a.LocationGuid FROM Asset a WHERE a.AssociationGuid IN ( SELECT ada.DataAssociationGuid FROM AssociationDataAssociation ada WHERE ada.AssociationGuid = '568B40AD-5133-4237-9F3C-F8EA9D472662')
takes 30-60 seconds to run on my machine, due to a clustered index scan on our an index on asset [about half a million rows]. For this particular association less than 50 rows are returned.
expanding the inner select into a list of guids the query runs instantly:
SELECT a.AssetGuid, a.Name, a.LocationGuid FROM Asset a WHERE a.AssociationGuid IN ( '0F9C1654-9FAC-45FC-9997-5EBDAD21A4B4', '52C616C0-C4C5-45F4-B691-7FA83462CA34', 'C95A6669-D6D1-460A-BC2F-C0F6756A234D')
It runs instantly because of doing a clustered index seek [on the same index as the previous query] instead of a scan. The index in question IX_Asset_AssociationGuid is a nonclustered index on Asset.AssociationGuid.
The tables involved:
Asset, represents an asset. Primary key is AssetGuid, there is an index/FK on Asset.AssociationGuid. The asset table has 28 columns or so... Association, kind of like a place, associations exist in a tree where one association can contain any number of child associations. Each association has a ParentAssociationGuid pointing to its parent. Only leaf associations contain assets. AssociationDataAssociation, a table consisting of two columns, AssociationGuid, DataAssociationGuid. This is a table used to quickly find leaf associations [DataAssociationGuid] beneath a particular association [AssociationGuid]. In the above case the inner select () returns 3 rows.
I'd include .sqlplan files or screenshots, but I don't see a way to attach them.
I understand I can specify to use the index manually [and this also runs instantly], but for such a simple query it is peculiar it is necesscary. This is the query with the index specified manually:
SELECT a.AssetGuid, a.Name, a.LocationGuid FROM Asset a WITH (INDEX (IX_Asset_AssociationGuid)) WHERE a.AssociationGuid IN ( SELECT ada.DataAssociationGuid FROM AssociationDataAssociation ada WHERE ada.AssociationGuid = '568B40AD-5133-4237-9F3C-F8EA9D472662')
To repeat/clarify my question, why might this not be doing a clustered index seek with the first query?
Hi All,I have a database that is serving a web site with reasonably hightraffiic.We're getting errors at certain points where processes are beinglocked. In particular, one of our people has suggested that an updatestatement contained within a stored procedure that uses a wherecondition that only touches on a column that has a clustered primaryindex on it will still cause a table lock.So, for example:UPDATE ORDERS SETprod = @product,val = @valWHERE ordid = @ordidIn this case ordid has a clustered primary index on it.Can anyone tell me if this would be the case, and if there's a way ofensuring that we are only doing a row lock on the record specified inthe where condition?Many, many thanks in advance!Much warmth,Murray
So I'm reading http://www.sql-server-performance.com/tips/clustered_indexes_p2.aspx and I come across this: When selecting a column to base your clustered index on, try to avoid columns that are frequently updated. Every time that a column used for a clustered index is modified, all of the non-clustered indexes must also be updated, creating additional overhead. [6.5, 7.0, 2000, 2005] Updated 3-5-2004 Does this mean if I have say a table called Item with a clustered index on a column in it called itemaddeddate, and several non-clustered indexes associated with that table, that if a record gets modified and it's itemaddeddate value changes, that ALL my indexes on that table will get rebuilt? Or is it referring to the table structure changing? If so does this "pseudocode" example also cause this to occur: sqlstring="select * from item where itemid=12345" rs.open sqlstring, etc, etc, etc rs.Fields("ItemName")="My New Item Name" rs.Fields("ItemPrice")=1.00 rs.Update Note I didn't explicitly change the value of rs.fields("ItemAddedDate")...does rs.Fields("ItemAddedDate")=rs.Fields("ItemAddedDate") occur implicitly, which would force the rebuild of all the non-clustered indexes?
I have a requirement to only rebuild the Clustered Indexes in the table ignoring the non clustered indexes as those are taken care of by the Clustered indexes.
In order to do that, I have taken the records based on the fragmentation %.
But unable to come up with a logic to only consider rebuilding the clustered indexes in the table.
Dear All, i've observed one particular table, one column is having clustered and non clustered index. is it ok? or i need to drop the non clustered column?
the table has 16 columns and at present 8 million records are there. per day approxmately 60000 rows will be getting into the table. it has another 3 non clustered indexes.
please suggest me.
Arnav Even you learn 1%, Learn it with 100% confidence.
If I have a table that I want to create a clustered index on. For example sake, say that I have 2 columns in the table. Col1 is char(2), col2 is text (or image). If I create a clustered index on col1, the database needs 1.2% times the size of the table to create the clustered index. Does this include the size of col2, being that text and image data is stored in a separate page chain....?
I was going through the book by Kalen Delaney where she has mentioned the following paragpraph in Chapter 7 (Index Internals):
Many documents describing SQL Server indexes will tell you that the clustered index physically stores the data in sorted order. This can be misleading if you think of physical storage as the disk itself. If a clustered index had to keep the data on the actual disk in a particular order, it could be prohibitively expensive to make changes. If a page got too full and had to be split in two, all the data on all the succeeding pages would have to be moved down. Sorted order in a clustered index simply means that the data page chain is logically in order.
Then I read the book on SQL Server 2000 (on Perf Tuning) by Ken England. He says the clustered index stores data in physical order and any insert means moving the data physically. Also the same statement is echoed on the net by many articles.
What is the truth? How are really clustered index stored? What does physical order in the above statement really mean?
I just ran the Database Engine Tuning Advisor on a relative complex query to find out if a new index might help, and in fact it found a combination that should give a performance gain of 94%. Fair enough to try that.
What I wonder about: The index I should create contains 4 columns, the last of them being the Primary Key column of the table, which is also my clustered index for the table. It is an identity integer btw.
I think I remember that ANY index does include the clustered one as lookup into the data, so having it listed to the list of columns will not help. It might at worst add another duplicate 4 bytes to each index entry.
Right? Wrong? Keep the column in the index, or remove it since it is included implicit anyway?
I am trying to create a clustered index on a View of a table that has an xml datatype. This indexing ran for two days and still did not complete. I tried to leave it running while continuing to use the database, but the SELECT statements where executing too slowly and the DML statements where Timing out. I there a way to control the server/cpu resources used by an indexing process. How can I determine the completion percentage or the indexing process. How can I make indexing the view with the xml data type take less time?