Included Columns

Question

Post reply

Included Columns

kapil_kk

SSC-Insane

Points: 21316
More actions
January 31, 2014 at 6:52 am

#303668

Hi all,
I have a table with strucutre below:
CREATE TABLE [dbo].[Supervisor](
[SupervisorId] [int] IDENTITY(1,1) NOT NULL,
[InstanceId] [nchar](3) NOT NULL,
[SiteId] [nchar](3) NOT NULL,
[LocalSupervisorId] [nvarchar](20) NOT NULL,
[LocalSupervisorNumber] [nvarchar](20) NOT NULL,
[OfficeId] [int] NOT NULL,
[Prefix] [nvarchar](10) NULL,
[FirstName] [nvarchar](50) NULL,
[MiddleName] [nvarchar](50) NULL,
[LastName] [nvarchar](50) NULL,
[WorkPhoneNumber] [nvarchar](25) NULL,
[CellPhoneNumber] [nvarchar](25) NULL,
[EmailAddress] [nvarchar](75) NULL,
[DisplayName] [nvarchar](125) NULL,
[PersonTypeId] [int] NULL,
[StatusFlag] [bit] NOT NULL,
[DateAdded] [datetime] NOT NULL,
[AddedBy] [nvarchar](75) NOT NULL,
[DateChanged] [datetime] NULL,
[ChangedBy] [nvarchar](75) NULL,
[LocalDateChanged] [datetime] NOT NULL,
[LocalChangedBy] [nvarchar](75) NOT NULL,
[SyncTimeStamp] [timestamp] NOT NULL,
CONSTRAINT [PK_Supervisor] PRIMARY KEY CLUSTERED
(
[SupervisorId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
CONSTRAINT [uq1_Supervisor] UNIQUE NONCLUSTERED
(
[InstanceId] ASC,
[SiteId] ASC,
[LocalSupervisorId] 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
ALTER TABLE [dbo].[Supervisor] WITH CHECK ADD CONSTRAINT [FK_Supervisor_PersonType] FOREIGN KEY([PersonTypeId])
REFERENCES [dbo].[PersonType] ([PersonTypeId])
GO
ALTER TABLE [dbo].[Supervisor] WITH CHECK ADD CONSTRAINT [FK_SupervisorOfficeId] FOREIGN KEY([OfficeId])
REFERENCES [dbo].[Office] ([OfficeId])
GO
As this table alreadys has Unique Non nonclustered on columns ([InstanceId] ,[SiteId] ,[LocalSupervisorId] )
But this table also has a nonclustered index on columns (InstanceID ,SiteiD, OfficeId,PersonTypeId) in which OfficeId,PersonTypeId used in a foreign key constraint.
So my question is if I add these columns OfficeId, PersonTypeId in Unique constraint as Included Columns will it makes any affect rather than these two seperate index in which InstanceId, SiteID is repeated in both indexes.
Please suggest
_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Keith Tate SSCrazy Eights Points: 9888 More actions · Answer 1

Can you supply the DDL for PersonType and Office?

Microsoft Certified Master - SQL Server 2008
Follow me on twitter: @keith_tate

Forum Etiquette: How to post data/code on a forum to get the best help[/url]

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

Depends on the queries that run against that table. If you have queries that filter on Instance, Site and Office or Instance, Site, Office and PersonType it could impact the performance of those queries. The original two queries and the merged one you suggest are not equivalent.

You'll need to examine the queries that run against that DB and test them, see if there's a performance impact.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass