SQL Server 2008 R2 Non-Clustered Candidate Indices

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
	
)

Select allOpen in new window

>> 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.

By orders of magnitude the most important index is the clustered one.  Get that right and the rest can be added later.

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
)

Select allOpen in new window

OR

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

Select allOpen in new window

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?

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.

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
)

Select allOpen 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
)

Select allOpen 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 
)

Select allOpen in new window

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.

Thank you very much for your help!

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".