SQL Server 2008 R2 Non-Clustered Candidate Indices

I have a SQL Server 2008 R2 database with tables that are shared by multiple different companies and multiple users. My tables all have one integer column that identifies which company the record belongs to. Through views, users will only be able to see the data that belongs to the company they are logged in under.

So my question is: when creating my non-clustered indices for speed (only on the columns mostly frequently referenced by where clauses) does it make sense to create all the indices as a candidate index, or only create indices on the individual fields I am trying to index?

Create Table Department
(
DepartmentISN int Identity Primary Key,
CompanyISN int not null, 
DepartmentID varchar(10) not null,
DepartmentName varchar(40) not null default '',
Extension varchar(10) not null default '',
LastModifiedDate datetime2 not null default SYSUTCDATETIME(),
LastModifiedUserISN int default NULL
)


-- This index is necessary to prevent duplicate department
-- IDs within the same company. I'm certain this is correct.
Create Unique Index "IX_DepartmentID" on Department (DepartmentID, CompanyISN)


-- Should I build indices like this?
Create Index "IX_DepartmentISN" on Department(DepartmentISN)
Create Index "IX_DepartmentName" on Department(DepartmentName)


-- Or like this?
Create Index "IX_DepartmentISN" on Department(DepartmentISN, CompanyISN)
Create Index "IX_DepartmentName" on Department(DepartmentName, CompanyISN)

Open in new window

ContrAcctAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

jetskij16Commented:
You will want to create an index on the columns that are contained in your where clause so option 2 of your choices above.

Also, the order that you specify the columns of the index may impact performance.
It will probably look something like this:
CREATE NONCLUSTERED INDEX [test] ON [dbo].[Department] 
(
	[CompanyISN] ASC
	[DepartmentISN] ASC
	
)

Open in new window

0
Scott PletcherSenior DBACommented:
>> My tables all have one integer column that identifies which company the record belongs to. Through views, users will only be able to see the data that belongs to the company they are logged in under. <<

Then that integer should 100% be the first column in the *clustered* index.


For non-clus indexes, your lead column in the index should be one you requently use in WHERE clauses and has a high selectivity.  SQL is extremely restrictive about using a non-clus index, so a non-selective one will almost certainly almost never get used.
0
Scott PletcherSenior DBACommented:
By orders of magnitude the most important index is the clustered one.  Get that right and the rest can be added later.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

ContrAcctAuthor Commented:
Let me specify. I understand I need indices for columns that will be in my where clasuse. However, which option would be better, or are these essentially the same?

Create Nonclustered Index [IX_CompanyISN] on [dbo].[Department]
(
[CompanyISN] ASC
)

Create Nonclustered Index [IX_DepartmentISN] on [dbo].[Department]
(
[DepartmentISN] ASC
)

Open in new window


OR

Create Nonclustered Index [IX_DepartmentISN] on [dbo].[Department]
(
[CompanyISN] ASC
[DepartmentISN] ASC
)

Open in new window

0
jetskij16Commented:
They are not the same thing one is creating to seperate indexes on 2 different columns that is the first option.

The bottom option is creating a single index on 2 columns.

If your where clause uses both columns then they both need to be included in the index.  Are you able to provide the where clause as well?
0
Scott PletcherSenior DBACommented:
Again, the odds of a non-clus index being used are relatively low.

You should concentrate on getting the proper clustered index.

You can easily add and drop non-clus indexes live in relatively little time.
0
ContrAcctAuthor Commented:
Scott, my clustered index was just an ascending value on the unique integer DepartmentISN that was automatically generated by defining DepartmentISN as the primary key (which is also the identity field). Are you saying that the following code will not produce an adequate clustered index?

Create Table Department
(
DepartmentISN int Identity Primary Key,
CompanyISN int not null, 
DepartmentID varchar(10) not null,
DepartmentName varchar(40) not null default '',
Extension varchar(10) not null default '',
LastModifiedDate datetime2 not null default SYSUTCDATETIME(),
LastModifiedUserISN int default NULL
)

Open in new window


The DepartmentISN field will always be unique, and almost never used in a where clause, but it will often be used in join clauses. A user, however, will only be able to see the records that match the CompanyISN number (which is not a unique value to each record). Additionaly, the where clause will mostly likely filter results be the DepartmentID.

Are you saying it would be best to use the following code after having created the Department table?:

ALTER TABLE [dbo].[Department] DROP CONSTRAINT [PK__Departme__1853C0FB7F60ED59]
ALTER TABLE [dbo].[Department] ADD PRIMARY KEY NONCLUSTERED 
(
	[DepartmentISN] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

Create Clustered Index [IX_DepartmentISN] on [dbo].[Department]
(
	[CompanyISN] Asc,
	[DepartmentISN] Asc,
        [DepartmentID] Asc
)

Open in new window


And as far as the order of the index columns, am I correct in assuming the keys should be ordered from least specific values to most specific values (like I have done in the above code example)?

FYI, I have a unique index to ensure that there are no duplicate DepartmentIDs per company:

Create Unique Index [IX_DepartmentID] on [dbo].[Department]
(
	[CompanyISN] Asc,
	[DepartmentID] Asc 
)

Open in new window

0
Scott PletcherSenior DBACommented:
>> Scott, my clustered index was just an ascending value on the unique integer DepartmentISN <<

Yeah, that's what most people do, just default it to that.  But the clus index is by far the most important index on a table.


>> am I correct in assuming the keys should be ordered from least specific values to most specific values (like I have done in the above code example)? <<

Not really.  The first column should be the one that is specific (almost) all the time for most of queries.

In this case, CompanyISN is correct because:
>> users will only be able to see the data that belongs to the company they are logged in under <<

So CompanyISN will be a part of every query.

Then DepartmentID most likely.  You can define that as a unique clus index and so won't need a separate index to guarantee uniqueness on those two.

Create a non-clus index on DepartmentISN for joining to it.  Single-row joins can use non-clus indexes very effectively.  By definition, a unique value is very highly selective :-) .

If you picture the physical positioning of the rows in the table you can see the advantage to SQL of having the CompanyISN as the lead clustered column.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Scott PletcherSenior DBACommented:
Some other columns might still need to be indexed, so naturally you can continue to investigate other WHERE columns for non-clus indexes.  SQL can use mutliple indexes to satisfy one query if it makes sense for it to do so.
0
ContrAcctAuthor Commented:
Thank you very much for your help!
0
Scott PletcherSenior DBACommented:
No problem !

REWORDING:

>>The first column should be the one that is specific (almost) all the time for most of queries<<

"Specified", not "specific", for "most queries".
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Databases

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.