SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Problem (doubt) with an Index on a composite key


Problem (doubt) with an Index on a composite key

Author
Message
Carl0s_
Carl0s_
Grasshopper
Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)

Group: General Forum Members
Points: 13 Visits: 91
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

Tom Thomson
Tom Thomson
One Orange Chip
One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)

Group: General Forum Members
Points: 26203 Visits: 12506
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

Carl0s_
Carl0s_
Grasshopper
Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)

Group: General Forum Members
Points: 13 Visits: 91
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 Smile
ChrisM@Work
ChrisM@Work
SSC-Forever
SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)

Group: General Forum Members
Points: 42190 Visits: 20012
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
Carl0s_
Carl0s_
Grasshopper
Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)

Group: General Forum Members
Points: 13 Visits: 91
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)
Carl0s_
Carl0s_
Grasshopper
Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)

Group: General Forum Members
Points: 13 Visits: 91
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?
ChrisM@Work
ChrisM@Work
SSC-Forever
SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)

Group: General Forum Members
Points: 42190 Visits: 20012
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
ChrisM@Work
ChrisM@Work
SSC-Forever
SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)

Group: General Forum Members
Points: 42190 Visits: 20012
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
Carl0s_
Carl0s_
Grasshopper
Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)

Group: General Forum Members
Points: 13 Visits: 91
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
ChrisM@Work
ChrisM@Work
SSC-Forever
SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)

Group: General Forum Members
Points: 42190 Visits: 20012
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search