Clustered Index On Composite Primary Key

Jul 23, 2005

Is that possible on SQL Server 2000 and onwards?

View 1 Replies


ADVERTISEMENT

Composite Key And Clustered Index

Mar 24, 2008

When we create a composite key(col1,col2) clustered index is created in both col1 and col2.So how come "only one clustered can be created for a table" is justified?

View 8 Replies View Related

Composite Clustered Index - Column Order

May 29, 2007

Want to check my thinking with you folks...

I have a table with a clustered composite index, consisting of 3 columns, which together form a unique key. For illustration, the columns are C1, C2 & C3.

Counts of distinct values for columns are C1 425, C2 300,000 & C3 4,000,000

C3 is effectively number of seconds since 01/01/1970.

The usage of the table is typically, insert a row, do something else, then update it.

Currently, the index columns are ordered C3,C1,C2. Fill factor of 90%.

My thinking is that this composite index is better ordered C1,C2,C3.

My reasoning is that having C3 as the leading column, biases all the inserts towards one side of the indexes underlying B-tree, causing page splits. Also, there'll be a bunch of "wasted" space across the tree, as the values going into C3 only ever get bigger (like an identity), so the space due to the fill factor in lower values never gets used.

Welcome your thoughts.

View 3 Replies View Related

Varchar Vs Char In 1st Field Of Composite Clustered Index

Jul 23, 2005

Would it be OK to use varchar(5) instead of char(5) as the first field of acomposite clustered index?My gut tells me that varchar would be a bad idea, but I am not finding muchinformation on this topic on this when I Google it.Currently the field is Char(4), and there is a need to increase it to hold 5characters.TIA

View 2 Replies View Related

DB Design :: Composite Clustered Index Key Based On Column

Nov 24, 2015

I created composite clustered index key based on Gender and Salary column 

The Query executed Successfully and <g class="gr_ gr_135 gr-alert gr_tiny gr_spell undefined ContextualSpelling multiReplace" data-gr-id="135" id="135">i</g>

got composite index key id Gender(-), Salary I <g class="gr_ gr_310 gr-alert gr_gramm undefined Grammar only-ins replaceWithoutSep" data-gr-id="310" id="310">want</g> know why Gender(-) display like this?

And Gender is <g class="gr_ gr_391 gr-alert gr_spell undefined ContextualSpelling ins-del multiReplace" data-gr-id="391" id="391">nvarchar</g> (20) 

View 2 Replies View Related

DB Engine :: How To Convert Unique Clustered Index Into Clustered Primary Key To Use With Change Tracking

Sep 4, 2015

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.

View 4 Replies View Related

DB Design :: Script To Create Table With Primary Key Non-clustered And Clustered Index

Aug 28, 2015

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,

[code]....

View 20 Replies View Related

Data Warehousing :: Difference Between Primary Key With Clustered And Non-clustered Index

Jul 19, 2013

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?

View 5 Replies View Related

Primary Key Without Clustered Index

Jan 28, 2008

Dear All,
i've read one article that with some option, we can avoid creating clustered index on the primary key column. is ti possible?

how can we create a primary key without allowing sql server to create automaticaly a clustered index?

Vinod
Even you learn 1%, Learn it with 100% confidence.

View 1 Replies View Related

Primary Key With Non-clustered Index

Dec 11, 2007

Dear all,


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).

Let me know your views.


Regards,

Chetan

View 3 Replies View Related

Creating A Primary Key As A Non Clustered Index

Jul 18, 2007

Hi,



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.

Can anyone explain why it is happening?


Thanks

Nitin

View 3 Replies View Related

Dropping Clustered Index Associated With Primary Key.

Aug 24, 2007

Hi all,


I have a huge table with million of rows, which has ONE Clustered index associated with the PRIMARY KEY, and there are some NON_Clustered indexes.


So,now i decided that, i dont need any more indexes ( not even one) on that table, but i need to maintain primary key on that table.


(a) So, how can i accomplish this (i.e.) having primay key but not having indexes on the table.


Thanks.

View 6 Replies View Related

Transact SQL :: Dropping Clustered Index On Primary Key Which Is In Replication?

Oct 2, 2015

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?

View 2 Replies View Related

Composite Primary Keys Versus Composite Unique Indexes

Feb 20, 2007

Hello,

I have a table which has a composite primary key consisting of four columns, one of them being a datetime called Day.

The nice thing afaik with this composite key is that it prevents duplicate entries in the table for any given day. But the problem is probably two-fold

1. multiple columns need to be used for joins and I think this might degrade performance?
2. in client applications such as asp.net these primary keys must be sent in the query string and the query string becomes long and a little bit unmanagable.

A possible solutions I'm thinking of is dropping the existing primary key and creating a new identity column and a composite unique index on the columns from the existing composite key.

I would like to have some tips, recommendations and alternatives for what I should do in this case.

View 1 Replies View Related

Update Statement Performing Table Lock Even Though Where Condition On Clustered Primary Index?

Jul 20, 2005

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

View 1 Replies View Related

Simple Query Chooses Clustered Index Scan Instead Of Clustered Index Seek

Nov 14, 2006

the query:

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?

View 15 Replies View Related

SQL Server 2008 :: Change Primary Key Non-clustered To Primary Key Clustered

Feb 4, 2015

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?

View 2 Replies View Related

Convert Composite Primary Key Into Simple Primary Key

Jan 11, 2007

Uma writes "Hi Dear,
I have A Table , Which Primary key consists of 6 columns.
total Number of Columns in the table are 16. Now i Want to Convert my Composite Primary key into simple primary key.there are already 2200 records in the table and no referential integrity (foriegn key ) exist.

may i convert Composite Primary key into simple primary key in thr table like this.



Thanks,
Uma"

View 1 Replies View Related

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".

View 4 Replies View Related

Create Clustered Or Non-clustered Index On Large Table ( SQL Server 7 )

Jan 4, 2008

I have large table with 10million records. I would like to create clustered or non-clustered index.

What is the quick way to create? I have tried once and it took more than 10 min.

please help.

View 1 Replies View Related

How To Alter A Non Clustered Primary Key Constraint To Clustered

Feb 26, 2008

Hello,


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.


Thanks in Advance,


Rohit

View 5 Replies View Related

Converting A Clustered Index On A PK Identity Field To Non-clustered

Sep 8, 2006

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?

Thanks

View 3 Replies View Related

Composite Primary Key

Mar 2, 2004

WHile designing a Database should one go for composite Primary Keys.
Or what are the Pros and Corns of Composite Primary Keys

Thanx

View 5 Replies View Related

When To Use Composite Primary Key

Feb 24, 2014

I've been facing this situation since long but today i am asking here. Suppose i have a following tables;

AdmissionInfo, AdmID, AdmDate, AdmFee etc.

SubjectInfo i.e. SubID, SubName, SubStatus etc.

The Result table is like this:

ResID, AdmID, SubID, TheoryMarks, PracticalMarks, ObtMarks, TotalMarks, ResultTerm, SubPercentage.

ResID is PK, AdmID and SubID are Foreign Keys, right. Now What i want to ask/ learn that Am i suppose to combine ResID, AdmID and SubID as a composite PK or should i simply make the ResID as PK and other 2 be there as FK? When do we really need to use Composite PK and using it a good thing or not?

View 1 Replies View Related

Composite Primary Key

Jul 20, 2005

i have a master table with around 15 columns and i am trying to findthe appropriate primary keys and indexes for the table.To make the records unique, i need to include two datetime columns (start and end dates ) and two integer columns ( attributes of therecord ) to make up a composite primary key. Both of these fourcolumns are found in the WHERE clause of my queries.Is it acceptable in the view of performance and how should i createthe indexes?

View 5 Replies View Related

Composite Primary Key Or Not?

Jul 3, 2006

This is really not a T_SQL question but there's no good category for it so I thought I'd just put it here.
 
I have a table that will contain a large amoutn of data in one field, and every piece of this data is uniquely identitied by six other attributes, that is, six other fields (e.g. user_ID, type_ID, year, country, state, item_ID).  I can either make these six fields into a composite primary key, or add an additonal field (say an identity column) as the primary key and add a unique constraint on these six fields.  What are the pros and cons of both designs?  The one data field is of nvarchar(2000) type and the table is likely to have 50 million+ rows in a couple of years of real use.
 
This table is not referenced by any other tables so whatever the primary key is, there's no FK reference.  However, there could be FK references to this table in the future.  Does the FK possibility make a difference in the design considerations?

View 13 Replies View Related

Composite Primary Key

Aug 4, 2007

Hello,

Does composite primary key affect performance on the table that contains the composite primary key or tables that references this table?

When composite primary key should be used?

View 2 Replies View Related

Creating Composite Primary Key

Feb 2, 2004

Hi

I'm trying to create a composite Primary Key on a table. This is the SQL I've written:

CREATE TABLE BookingItems
(
BookingID INT NOT NULL
REFERENCES Bookings(BookingID),
EquipmentTypeID INT NOT NULL
REFERENCES EquipmentType(EquipmentTypeID),
CONSTRAINT PK_BookingItems_id PRIMARY KEY
(BookingID, EquipmentTypeID)
)

Is this right? I'm trying to define a Primary Key made up of BookingID and EquipmentTypesID, which are both Foreign Keys as defined in the column definition.

Thanks
Jon

View 1 Replies View Related

Composite Primary Keys

Jun 25, 2002

Newbie question...

I have two tables (categories & listings) which create a many-to-many relationship.

I have created an interim table with the primary keys from each table as a composite primary key...(cat_id & list_id).

How does the interim table get populated with the id's?

When I do an insert statement to insert data into the categories table, the cat_id field is automatically generated...same with the listings table, but when (and how) does the primary key data get into the interim table.

Thanks in advance for the assistance.

View 1 Replies View Related

Composite Primary Key Syntax

Nov 27, 2007

I'm trying this


Code:

CREATE TABLE Rating
(ContentID int NOT NULL PRIMARY KEY REFERENCES Content_(ContentID),
UserID int NOT NULL PRIMARY KEY REFERENCES Usr(UserID),
rating tinyint DEFAULT 2,
LastRead smalldatetime NOT NULL DEFAULT CURRENT_TIMESTAMP)



And it is telling me this:

Msg 8110, Level 16, State 0, Line 14
Cannot add multiple PRIMARY KEY constraints to table 'Rating'.


So how do I combine two non-unique foreign keys, the combination of which is unique, into one primary key?

View 2 Replies View Related

Surrogate Or Composite Primary Key?

Aug 23, 2004

My previous post was not really clear, so I'll try again with a (hopefully) better (even if longer) example...

Consider the following...

A JOB describes the processment of a document.
Each document can exist in two versions: English and French.
A JOB can have 1 or 2 TASK, each describing the processement of either the English or French version.
So we have the following:

A: JOB (JobNum [PK], DocReference, StartDate, EndDate, ...)
B: TASK (JobNum [PK] [FKa], Version [PK], Priority, ...)

that is there is an identifying 1:M (where maxium allowed for M is 2) relationship between JOB and TASK; TASK being identified by JobNum and Version (where the domain for Version is {E, F}).

Each TASK may require a TRANSLATION sub_task.
Each TASK may require a TYPING sub_task.
Each TASK may require a DISTRIBUTION sub_task.

For example, for a given doc, the English TASK requires TRANSLATION and DISTRIBUTION, while the French only DISTRIBUTION.

That is, there is a 1:1 not-required relationship between TASK and TRANSLATION, TYPING and DISTRIBUTION.
So we have the following:

A: JOB (JobNum [PK], DocReference, StartDate, EndDate, ...)
B: TASK (JobNum [PK] [FKa], Version [PK], Priority, ...)

C: TRANSLATION (JobNum [PK] [FKb], Version [PK] [FKb], DueDate, ...)
D: TYPING (JobNum [PK] [FKb], Version [PK] [FKb], DueDate, ...)
E: DISTRIBUTION (JobNum [PK] [FKb], Version [PK] [FKb], Copies, ...)

As you can see I am using the PK of TASK as FK and PK for each of the three SUB_TASKs.

To complicate things, each SUB_TASK has one or more assignments. The assignments for each SUB_TASK records different information from the others.
So we have...

A: JOB (JobNum [PK], DocReference, StartDate, EndDate, ...)
B: TASK (JobNum [PK] [FKa], Version [PK], Priority, ...)

C: TRANSLATION (JobNum [PK] [FKb], Version [PK] [FKb], DueDate, ...)
D: TYPING (JobNum [PK] [FKb], Version [PK] [FKb], DueDate, ...)
E: DISTRIBUTION (JobNum [PK] [FKb], Version [PK] [FKb], Copies, ...)

F: TRA_ASSIGN (JobNum [PK] [FKc], Version [PK] [FKc], Index [PK], Translator, ...)
G: TYP_ASSIGN (JobNum [PK] [FKd], Version [PK] [FKd], Index [PK], Typyst, ...)
H: REP_ASSIGN (JobNum [PK] [FKe], Version [PK] [FKe], Index [PK], Pages, ...)

that is there is an identifying 1:M relationship between each SUB_TASK and its ASSIGNMENTs, each ASSIGNMENT being identified by the SUB_TASK it belongs to and an Index.

I wish I could send a pic of the ER diagram...

Maybe there is another and better way to model this: if so, any suggestion?

Given this model, should I use for TRANSLATION, TYPING and DISTRIBUTION a surrogate key, instead of using the composite key, like for example:

C: TRANSLATION (TranslationID [PK], JobNum [FKb], Version [FKb], DueDate, ...)
D: TYPING (TypingID [PK], JobNum [FKb], Version [FKb], DueDate, ...)
E: DISTRIBUTION (DistributionID [PK], JobNum [FKb], Version [FKb], Copies, ...)

this will "improve" the ASSIGNMENTs tables:

F: TRA_ASSIGN (TranslationID [PK] [FKc], Index [PK], Translator, ...)
G: TYP_ASSIGN (TypingID [PK] [FKd], Index [PK], Typyst, ...)
H: REP_ASSIGN (DistributionID [PK] [FKe], Index [PK], Pages, ...)

I could even go further using a surrogate key even for TASK, which leads me to the following:

A: JOB (JobNum [PK], DocReference, StartDate, EndDate, ...)
B: TASK (TaskID [PK], JobNum [FKa], Version , Priority, ...)

C: TRANSLATION (TaskID [PK] [FKb], DueDate, ...)
D: TYPING (TaskID [PK] [FKb], DueDate, ...)
E: DISTRIBUTION (TaskID [PK] [FKb], Copies, ...)

F: TRA_ASSIGN (TaskID [PK] [FKc], Index [PK], Translator, ...)
G: TYP_ASSIGN (TaskID [PK] [FKd], Index [PK], Typyst, ...)
H: REP_ASSIGN (TaskID [PK] [FKe], Index [PK], Pages, ...)

I don't really like this second solution, but I'm still not sure about the first solution, the one with the surrogate key only in the SUB_TASks tables.

View 2 Replies View Related

Composite Primary Key Constraint

Mar 21, 2007

Hi All,

I have a table that has 3 columns, two of them make a composite primary key. The table is populated with data. What I need to do is to add a third column to a composite primary key. I have tried to do that with the following command:

alter table databasesize
add constraint pk_dbsize primary key (dbid)

But I get the error message:

Table 'databasesize' already has a primary key defined on it.

How can I do this?

View 2 Replies View Related

Composite Primary Key And Foreign Key

Feb 8, 2008



Hi,
In my table1, I have a composite primary key ssn+firstname+lastname.
In table2 I have ssn, firstname, lastname, col1. In table2 I want to create a foreign key which references table1 primary key?
table1 and table2 has the primary, foreign key relationships. Still table2 need primary key. In table2 Can I make the ssn+Firstname+Lastname as primary key? or one column id as auto increment?

Which is best way?

Thanks

View 27 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved