Indexing fields for JOINS and WHERE

Question

Post reply

Indexing fields for JOINS and WHERE

Digs

SSCertifiable

Points: 7333
More actions
February 1, 2010 at 1:25 pm

#216230

REF: http://doc.ddart.net/mssql/sql70/create_2.htm

I have a small database where I have PK and FK.

The PK are already index due to there creation, some of the FK have not ( I assume just because they are FK they are not already indexed)

I want to index all fields that have been used in JOINS and WHERE clauses of the stored Procs.

I assume that I use NON CLUSTERED like this ...

Like :

CREATE NONCLUSTED INDEX <indexname> ON <table> (<column>)

Please confirm, advise, etc

Viewing 15 posts - 1 through 15 (of 23 total)

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

John Rowan SSC Guru Points: 56440 More actions · Answer 1

Best practice is to index your FK columns for join performance.



CREATE INDEX <index name> ON <table name>(<column name>)

John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

WayneS SSC Guru Points: 95405 More actions · Answer 2

confirm.

Note that your <column> can be multiple columns, and the sort order can be ASC or DESC on each column.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

John Rowan SSC Guru Points: 56440 More actions · Answer 3

John Rowan

SSC Guru

Points: 56440

February 1, 2010 at 1:45 pm

#1111928

See BOL for the full list of options for the CREATE INDEX statement.

John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

Digs SSCertifiable Points: 7333 More actions · Answer 4

Digs

SSCertifiable

Points: 7333

February 1, 2010 at 3:12 pm

#1111978

A sort order for an Index..

Is that wise ???

For example I have one column filled with BIT or -1 or 0

Shouldnt need to sort that !

When is it wise to sort or unsort..

thanks

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 5

John Rowan (2/1/2010)

Best practice is to index your FK columns for join performance.

CREATE INDEX <index name> ON <table name>(<column name>)

The FK's should probably be on the PK of the "remote table" meaning that there should already be an index.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 6

Digs (2/1/2010)

A sort order for an Index..

Is that wise ???

For example I have one column filled with BIT or -1 or 0

Shouldnt need to sort that !

When is it wise to sort or unsort..

thanks

I probably wouldn't put an index on a BIT column. Heh... for that matter, I probably wouldn't have a BIT column to begin with. 🙂

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

RP_DBA SSCertifiable Points: 5116 More actions · Answer 7

RP_DBA

SSCertifiable

Points: 5116

February 2, 2010 at 7:01 am

#1112239

Digs (2/1/2010)

When is it wise to sort or unsort..

We'll frequently sort DESC on date columns for audit/historical tables - latest record first deal.

_____________________________________________________________________
- Nate

@nate_hughes

John Rowan SSC Guru Points: 56440 More actions · Answer 8

Jeff Moden (2/1/2010)

John Rowan (2/1/2010)

Best practice is to index your FK columns for join performance.

CREATE INDEX <index name> ON <table name>(<column name>)

The FK's should probably be on the PK of the "remote table" meaning that there should already be an index.

I guess I should have been more clear on this. Yes, the FK should be the PK in the 'remote' table and therefore be indexed already. I was referring to the table that you would build the FK on.

John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 9

Digs (2/1/2010)

A sort order for an Index..

Is that wise ???

Indexes are always sorted. By default ASC if no option is specified.

For example I have one column filled with BIT or -1 or 0

Shouldnt need to sort that !

It's uncommon to index a bit column alone, though it may be very useful to create a multi-column (composite) index with one of the columns in it being a bit.

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

Digs SSCertifiable Points: 7333 More actions · Answer 10

Ok check this..

Field1 Name: Smith,Brown,Green

Field2 Deleted:0,0,-1

NOTE:-1 means deleted.

So I do WHERE Name = 'Brown" AND Deleted = 0

A good INDEX would be <tablename>(Name,Deleted)

BUT what if WHERE clause is only

Name='Brown'

or

Deleted =0

Hows that fit with a multi column index

Cause at the moment I have just index each separare column on its own.

RP_DBA SSCertifiable Points: 5116 More actions · Answer 11



create table #tbl ([Name] varchar(10), [Deleted] int)

insert into #tbl values ('Smith', 0)

insert into #tbl values ('Brown', 0)

insert into #tbl values ('Green', -1)

create index ix_tbl on #tbl ([Name], [Deleted])

--create index ix_tbl_1 on #tbl ([Name])

--create index ix_tbl_2 on #tbl ([Deleted])

-- qry1

select [Name]

from #tbl

where [Name] = 'Brown'

and [Deleted] = 0

-- qry2

select [Name]

from #tbl

where [Name] = 'Brown'

-- qry3

select [Name]

from #tbl

where [Deleted] = 0

drop table #tbl

I think this is what you're trying to do.

Looking at the execution plan in this scenario using only index "ix_tbl": qry1 and qry2 are doing index seeks and qry3 is doing an index scan.

Using indexes "ix_tbl_1" and "ix_tbl_2": qry2 is doing an index seek while qry1 and qry3 are doing index scans.

Me, I'd go with the composite index (name, deleted).

_____________________________________________________________________
- Nate

@nate_hughes

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 12

Digs (2/2/2010)

BUT what if WHERE clause is only

Name='Brown'

That could easily use the composite index on Name, deleted

or

Deleted =0

That, however, cannot.

How often is deleted filtered on alone?

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

David Data SSCrazy Points: 2965 More actions · Answer 13

David Data

SSCrazy

Points: 2965

February 3, 2010 at 8:11 am

#1112928

Could you explain why

Deleted = 0

cannot use the composite index on Name, Deleted?

Surely as Deleted is in the index, it can be tested in an index scan, without having to read the complete table data? Or does SQL Server only test for the first item in a composite index?

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 14

Sorry. It can not seek on that index because deleted is not a left-based subset of the index key. Yes, it can use that index for a scan operation.

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