Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Problem (doubt) with an Index on a composite key Expand / Collapse
Author
Message
Posted Thursday, October 4, 2012 10:35 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, July 8, 2013 9:54 AM
Points: 13, Visits: 89
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
Posted Thursday, October 4, 2012 1:27 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Today @ 8:30 AM
Points: 8,835, Visits: 9,393
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
Post #1368667
Posted Thursday, October 4, 2012 10:05 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, July 8, 2013 9:54 AM
Points: 13, Visits: 89
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
Posted Friday, October 5, 2012 2:39 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Friday, September 12, 2014 9:43 AM
Points: 7,284, Visits: 13,824
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
Posted Friday, October 5, 2012 8:41 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, July 8, 2013 9:54 AM
Points: 13, Visits: 89
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
Posted Monday, October 8, 2012 8:39 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, July 8, 2013 9:54 AM
Points: 13, Visits: 89
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
Posted Monday, October 8, 2012 9:25 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Friday, September 12, 2014 9:43 AM
Points: 7,284, Visits: 13,824
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

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 #1369890
Posted Monday, October 8, 2012 9:27 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Friday, September 12, 2014 9:43 AM
Points: 7,284, Visits: 13,824
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

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 #1369893
Posted Monday, October 8, 2012 10:01 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, July 8, 2013 9:54 AM
Points: 13, Visits: 89
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
Posted Monday, October 8, 2012 10:35 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Friday, September 12, 2014 9:43 AM
Points: 7,284, Visits: 13,824
Carl0s_ (10/8/2012)
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


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

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 #1369946
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse