October 4, 2012 at 10:35 am
Can someone help me to clear this situation please, I have the tables shown below:
CREATE TABLE [dbo].[Country.cat](
[b]Country_iD[/b] [smallint] NOT NULL,
[b]FIPS[/b] [char](2) NULL,
[b]Country[/b] [varchar](64) NOT NULL,
[b]Continent[/b] [char](3) NOT NULL,
CONSTRAINT [b]PK_Country_iD[/b] 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].[b]Entity.cat[/b](
[b]Country_iD[/b] [smallint] NOT NULL,
[b]Entity_iD[/b] [smallint] NOT NULL,
[b]Entity[/b] [varchar](32) NOT NULL,
CONSTRAINT [b]PK_Entity_iD[/b] 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 [b]FK_Entity_Country_iD[/b] 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
October 4, 2012 at 1:27 pm
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
October 4, 2012 at 10:05 pm
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 🙂
October 5, 2012 at 2:39 am
What will the table [dbo].[Entity.cat] contain, other than cities?
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
October 5, 2012 at 8:41 am
Yes, ChrisM@Work, next table is
CREATE TABLE [dbo].[b]city.cat[/b](
[b]Country_iD[/b] [smallint] NOT NULL,
[b]Entity_iD[/b] [smallint] NOT NULL,
[b]City_iD[/b] [smallint] NOT NULL,
[b]City[/b] [varchar](64) NOT NULL,
CONSTRAINT [PK_City_iD] PRIMARY KEY CLUSTERED (
[b]Country_iD[/b] ASC,
[b]Entity_iD[/b] ASC,
[b]City_iD[/b] 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)
October 8, 2012 at 8:39 am
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?
October 8, 2012 at 9:25 am
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)
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
October 8, 2012 at 9:27 am
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)
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
October 8, 2012 at 10:01 am
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
October 8, 2012 at 10:35 am
Carl0s_ (10/8/2012)
Thanks for reply ChrisM@WorkI 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.
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
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply