Index Help - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Index Help

Author

Topic

cmspot
Starting Member

44 Posts

Posted - 2007-10-19 : 02:03:12

Hello Everybody...
My table is:

Rent2id int identity ,
Rentid int ,
RentCust int,
RentDate datetime,
RentValue decimal (7,2),
Rentcode int ,
RentName VarChar(150),
returndate datetime ,
returnvalue decimal (7,2)
delaydays smallint ,
returned bit default 0
And 6 more Fields............

My table Contains More than 1 million rows
There is a special reason that I want to count the description (RentName)of the rent item and not the code (RentCode) of it..
My most often query is this:
Select Count(rent2id) from rents Where RentCust = @rentCust and RentName = @rentName

1) my question is What kind of index to use?
2)Is it a good idea to put Multi fields in primary key So you can search rows More quivkly?
like:
Alter table rents2 Add Constraint
PK_Rents2 Primary key (rent2id , rentcust , Rentname)

3)Is there any special tactic of using indexes?
4)Do We use indexes in Where fields or in select Fields?
5)When do we use Multiple field in an index? I mean this:
Create NonClusetered index
I_rents On Rents2 (rent2id , rentcust , Rentname)

Guys these are my first Steps and you are a great help
I ought you
Thank you very Very much!

I sell my mother in law.Is anybody interested?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-10-19 : 02:05:00

Your primary key depends on your data. But don't confuse primary keys with indexes.

I'd create an index on RentCust, RentName. If performance isn't great, then you could also add rent2id to the end.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

cmspot
Starting Member

44 Posts

Posted - 2007-10-19 : 02:21:39

quote:
Originally posted by tkizer

Your primary key depends on your data. But don't confuse primary keys with indexes.

I'd create an index on RentCust, RentName. If performance isn't great, then you could also add rent2id to the end.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Do you mean this?
Create NonClusetered index I_rents2
On rents2 (rentCust , RentName)

Or This
Create NonClusetered index I_rents2A
On rents2 (rentCust)

Create NonClusetered index I_rents2B
On rents2 (RentName)

I sell my mother in law.Is anybody interested?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-10-19 : 02:36:47

I'd recommend a composite index for your query.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2007-10-19 : 22:59:26

In sql2k5, you can add non-key column into index.

jackv
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2007-10-20 : 04:31:33

Also, make sure your Statistics are up to date.
In terms of comapring performance for various index implementation , it's worth being consistent.
i.e try and replicate the production environment

Jack Vamvas
--------------------
Search IT jobs from multiple sources- http://www.ITjobfeed.com

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources