Problem (doubt) with an Index on a composite key

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Database Design » Design Ideas and Questions » Problem (doubt) with an Index on a composite...

Problem (doubt) with an Index on a composite key

Rate Topic

Display Mode

Topic Options

Author

Message

Carl0s_

Posted Thursday, October 04, 2012 10:35 AM

Grasshopper

Group: General Forum Members
Last Login: Sunday, May 12, 2013 11:03 PM
Points: 13, Visits: 84

Can someone help me to clear this situation please, I have the tables shown below:

CREATE TABLE [dbo].[Country.cat](
	[Country_iD] [smallint] NOT NULL,
	[FIPS] [char](2) NULL,
	[Country] [varchar](64) NOT NULL,
	[Continent] [char](3) NOT NULL,

	CONSTRAINT [PK_Country_iD] PRIMARY KEY CLUSTERED (
		[Country_iD] ASC
 )

Data sample

Country_iD: 32 (Pk) (Clustered index)
FIPS: AR
Country: Argentina
Continent: SA

As we know MSSQL generate clustered index for primary key if I not specify another thing, until here all fine and clear for me, now I have Entity table:

-- Entity refer to states or provinces

 CREATE TABLE [dbo].[Entity.cat](
	[Country_iD] [smallint] NOT NULL,
	[Entity_iD] [smallint] NOT NULL,
	[Entity] [varchar](32) NOT NULL,
	
	CONSTRAINT [PK_Entity_iD] PRIMARY KEY CLUSTERED (
		[Pais_iD] ASC,
		[Entity_iD] ASC
 )
 ... -- I deleted default properties for simplify sample
GO
... -- more default code and

 ALTER TABLE [dbo].[Entity.cat]  WITH CHECK ADD CONSTRAINT [FK_Entity_Country_iD] FOREIGN KEY([Country_iD])
 REFERENCES [dbo].[Country.cat] ([Country_iD])
GO
 ALTER TABLE [dbo].[Entity.cat] CHECK CONSTRAINT [FK_Entity_Country_iD]
GO

Data sample

Country_iD:32 (Pk)(Fk)
Entity_iD: 1 (Pk)
Entity:Buenos Aires

The doub is here:

Must I create a nonclustered index for my Fk ([FK_Entity_Country_iD])?? or not because is part of a clustered index.

Thanks in advance for help

Post #1368571

L' Eomot Inversé

Posted Thursday, October 04, 2012 1:27 PM

SSCertifiable

Group: General Forum Members
Last Login: Today @ 11:54 AM
Points: 7,112, Visits: 7,188

First, I assume Pais_iD is a typo, since in the foreign key declaration it appears to be called Country_iD.

Second, you can certainly create a nonclustered index on Country_iD in the Entity.cat table, but it would be a very silly thing to do because Country_iD is the first field of the primary key so the index supporting the primary key acts as a index for Country_iD. The extra index would add storage ndoverhead and processing and IO overheads for row insertion and for row deletion (and for update of the Country_iD if that ever happens) but would not do anything useful. It seems more likely that a nonclustered index on Entity could be useful, to support search by name.

Before deciding whether to cluster on the primary key you should consider what sort of queries will happen. For example ask whether things will ever be selected by specifying a range for some attribute, because if they will you may want to consider that atribute for the first element of the cluster key.

Tom
Is minic a gheibheann béal oscailte dorn dúnta.
Is minig a cheapas beul fosgailte dòrn dùinte.

Post #1368667

Carl0s_

Posted Thursday, October 04, 2012 10:05 PM

Grasshopper

Group: General Forum Members
Last Login: Sunday, May 12, 2013 11:03 PM
Points: 13, Visits: 84

Sorry, the correct code in Entity.cat is:

CONSTRAINT [PK_Entity_iD] PRIMARY KEY CLUSTERED (
[Country_iD] ASC,
[Entity_iD] ASC
)

Thanks for your help and help me to clear that idea, now I understand is not necesary create an index in that case, have a nice day :)

Post #1368807

ChrisM@Work

Posted Friday, October 05, 2012 2:39 AM

SSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 9:27 AM
Points: 5,618, Visits: 10,990

What will the table [dbo].[Entity.cat] contain, other than cities?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Exploring Recursive CTEs by Example Dwain Camps

Post #1368874

Carl0s_

Posted Friday, October 05, 2012 8:41 AM

Grasshopper

Group: General Forum Members
Last Login: Sunday, May 12, 2013 11:03 PM
Points: 13, Visits: 84

Yes, ChrisM@Work, next table is

CREATE TABLE [dbo].[city.cat](
	[Country_iD] [smallint] NOT NULL,
	[Entity_iD] [smallint] NOT NULL,
	[City_iD] [smallint] NOT NULL,
	[City] [varchar](64) NOT NULL,

	CONSTRAINT [PK_City_iD] PRIMARY KEY CLUSTERED (
	[Country_iD] ASC,
	[Entity_iD] ASC,
	[City_iD] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[City.cat]  WITH CHECK ADD  CONSTRAINT [Fk_City_Entity_iD] FOREIGN KEY([Country_iD],[Entity_iD])
REFERENCES [dbo].[Entity.cat] ([Country_iD],[Entity_iD])
GO
ALTER TABLE [dbo].[City.cat] CHECK CONSTRAINT [Fk_City_Entity_iD]
GO

Sample

Country_iD: 32
Entity_iD: 1
City_iD: 1 (some id)
City: Buenos Aires (capital) (district)

Post #1369102

Carl0s_

Posted Monday, October 08, 2012 8:39 AM

Grasshopper

Group: General Forum Members
Last Login: Sunday, May 12, 2013 11:03 PM
Points: 13, Visits: 84

Can someone tell me an opinion about this design?, is bad to use composite key in that way, is better to use subrogate? and what is better way to perform this?

Post #1369858

ChrisM@Work

Posted Monday, October 08, 2012 9:25 AM

SSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 9:27 AM
Points: 5,618, Visits: 10,990

Carl0s_ (10/8/2012)

Can someone tell me an opinion about this design?, is bad to use composite key in that way, is better to use subrogate? and what is better way to perform this?

Why do you need an 'entity' table to link your countries table and your cities table? Surely it introduces a complication. Your cities table could be like this:

CityID   CityName       State_Region         CountryID
   1      Dallas          Texas               1 (USA)
   2      Dallas          Morayshire          2 (Scotland)

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

Post #1369890

ChrisM@Work

Posted Monday, October 08, 2012 9:27 AM

SSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 9:27 AM
Points: 5,618, Visits: 10,990

ChrisM@Work (10/8/2012)

Carl0s_ (10/8/2012)

Can someone tell me an opinion about this design?, is bad to use composite key in that way, is better to use subrogate? and what is better way to perform this?

Why do you need an 'entity' table to link your countries table and your cities table? Surely it introduces a complication. Your cities table could be like this:

CityID   CityName       State_Region         CountryID
   1      Dallas          Texas               1 (USA)
   2      Dallas          Morayshire          2 (Scotland)

Or even like this:

CityID   CityName       State_RegionID         CountryID
   1      Dallas          1 (Texas)               1 (USA)
   2      Dallas          2 (Morayshire)          2 (Scotland)

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

Post #1369893

Carl0s_

Posted Monday, October 08, 2012 10:01 AM

Grasshopper

Group: General Forum Members
Last Login: Sunday, May 12, 2013 11:03 PM
Points: 13, Visits: 84

Thanks for reply ChrisM@Work

I have the same structure in my city table, I refer to (state region) with entity cat (federative entity), my doubt here is, is correct to apply composite key in each table like that? I mean, is not bad to use composite primary key clustered ?, some people say is very bad for performance to use it composite primary key clustered, i dont know what to do, if use subrogate key now, or ..

tnx in advance for help

Post #1369918

ChrisM@Work

Posted Monday, October 08, 2012 10:35 AM

SSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 9:27 AM
Points: 5,618, Visits: 10,990

Carl0s_ (10/8/2012)

On one or two occasions I've used clustered indexes which included most of the columns in the table and the performance has been excellent. Very similar to your configuration here. If queries against the table(s) will almost always use all of the columns of the clustered index in joins, then it's very likely that a composite clustered index will work well.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

Post #1369946

« Prev Topic | Next Topic »

Permissions