InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comFri, 24 May 2013 14:07:12 GMT20http://www.sqlservercentral.com/Forums/Topic1368571-373-1.aspx[quote][b]Carl0s_ (10/8/2012)[/b][hr]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[/quote]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 [i]almost always [/i]use [i]all [/i] of the columns of the clustered index in joins, then it's very likely that a composite clustered index will work well.Mon, 08 Oct 2012 10:35:33 GMTChrisM@Workhttp://www.sqlservercentral.com/Forums/Topic1368571-373-1.aspxThanks 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 helpMon, 08 Oct 2012 10:01:47 GMTCarl0s_http://www.sqlservercentral.com/Forums/Topic1368571-373-1.aspx[quote][b]ChrisM@Work (10/8/2012)[/b][hr][quote][b]Carl0s_ (10/8/2012)[/b][hr]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?[/quote]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:[code="plain"]CityID CityName State_Region CountryID 1 Dallas Texas 1 (USA) 2 Dallas Morayshire 2 (Scotland)[/code][/quote]Or even like this:[code="plain"]CityID CityName State_RegionID CountryID 1 Dallas 1 (Texas) 1 (USA) 2 Dallas 2 (Morayshire) 2 (Scotland)[/code]Mon, 08 Oct 2012 09:27:44 GMTChrisM@Workhttp://www.sqlservercentral.com/Forums/Topic1368571-373-1.aspx[quote][b]Carl0s_ (10/8/2012)[/b][hr]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?[/quote]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:[code="plain"]CityID CityName State_Region CountryID 1 Dallas Texas 1 (USA) 2 Dallas Morayshire 2 (Scotland)[/code]Mon, 08 Oct 2012 09:25:45 GMTChrisM@Workhttp://www.sqlservercentral.com/Forums/Topic1368571-373-1.aspxCan 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?Mon, 08 Oct 2012 08:39:31 GMTCarl0s_http://www.sqlservercentral.com/Forums/Topic1368571-373-1.aspxYes, ChrisM@Work, next table is[code="sql"]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] [b]PRIMARY KEY[/b] 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]GOSET ANSI_PADDING OFFGOALTER TABLE [dbo].[City.cat] WITH CHECK [b]ADD CONSTRAINT[/b] [Fk_City_Entity_iD] [b]FOREIGN KEY[/b]([Country_iD],[Entity_iD])[b]REFERENCES [/b][dbo].[Entity.cat] ([Country_iD],[Entity_iD])GOALTER TABLE [dbo].[City.cat] CHECK CONSTRAINT [Fk_City_Entity_iD]GO[/code][b]Sample[/b][b]Country_iD[/b]: 32[b]Entity_iD[/b]: 1[b]City_iD[/b]: 1 (some id)[b]City[/b]: Buenos Aires (capital) (district)Fri, 05 Oct 2012 08:41:05 GMTCarl0s_http://www.sqlservercentral.com/Forums/Topic1368571-373-1.aspxWhat will the table [dbo].[Entity.cat] contain, other than cities?Fri, 05 Oct 2012 02:39:04 GMTChrisM@Workhttp://www.sqlservercentral.com/Forums/Topic1368571-373-1.aspxSorry, the correct code in Entity.cat is:[code="sql"]CONSTRAINT [PK_Entity_iD] PRIMARY KEY CLUSTERED ([Country_iD] ASC,[Entity_iD] ASC)[/code]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 :)Thu, 04 Oct 2012 22:05:34 GMTCarl0s_http://www.sqlservercentral.com/Forums/Topic1368571-373-1.aspxFirst, 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.Thu, 04 Oct 2012 13:27:34 GMTL' Eomot Inverséhttp://www.sqlservercentral.com/Forums/Topic1368571-373-1.aspx[b]Can someone help me to clear this situation please, I have the tables shown below:[/b][code="sql"]CREATE TABLE [dbo].[[b]Country.cat][/b]( [[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 )[/code] [b]Data sample[/b][b]Country_iD[/b]: 32 (Pk) (Clustered index)[b]FIPS[/b]: AR[b]Country[/b]: Argentina[b]Continent[/b]: SA[b]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:[/b] -- Entity refer to states or provinces[code="sql"] 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 )[b] ... -- I deleted default properties for simplify sample[/b]GO[b]... -- more default code and[/b] 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[/code][b]Data sample[/b][b]Country_iD[/b]:32 (Pk)(Fk)[b]Entity_iD[/b]: 1 (Pk)[b]Entity[/b]:Buenos Aires [b]The doub is here:[/b] [b]Must I create a nonclustered index for my Fk ([/b][FK_Entity_Country_iD][b])?? or not because is part of a clustered index. Thanks in advance for help[/b]Thu, 04 Oct 2012 10:35:50 GMTCarl0s_