Using index correctly - 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 2008 Forums

Transact-SQL (2008)

Using index correctly

Author

Topic

zhshqzyc
Posting Yak Master

240 Posts

Posted - 2012-07-13 : 15:34:59

I have a table "Equipment" that uses clustered index. The table has a primary key "ID" and two foreign keys "Lease_ID" and "County_ID".
Now I have a query that has a join.
Please correct my script if consider index for fast query, I am not strong on it.
Thanks.

SELECT 
		e.Cost_Center_ID,
		e.County_ID,
		e.Date_Replaced,
		e.Equipment_Description,
		e.Lease_ID,
		e.Equipment_Type_ID,
		e.GL_Account,
		e.ID,
		e.Maintenance_End_Date,
		e.Maintenance_Notes,
		e.Maintenance_Start_Date,
		e.Manufacturer,
		e.Model,
		e.Original_Cost,
		e.PO_Number,
		e.Date_Replaced,
		e.Replaced_ID,
		CASE 
			WHEN e.Sales_Tax > -1 THEN e.Sales_Tax
			ELSE c.Sales_Tax_Rate
		END AS Sales_Tax,
		e.Serial_Number,
		e.Warrenty,
		e.Warrenty_End_Date,
		e.Warrenty_Start_Date
	FROM
		dbo.Equipment as e
		LEFT OUTER JOIN dbo.County as c on e.County_ID = c.ID
	WHERE
		e.ID = @ID
		AND e.Active = 'true'

jackv
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2012-07-16 : 01:37:01

Do you have any Non Cluestered Indexes as well?
Could you supply the DDL of the table?

Jack Vamvas
--------------------
http://www.sqlserver-dba.com

lionofdezert
Aged Yak Warrior

885 Posts

Posted - 2012-07-16 : 05:07:48

If you already have Clustered Index on ID, it should perform better with this index, as it will return a single row everytime with most of table columns.

--------------------------
http://connectsql.blogspot.com/

zhshqzyc
Posting Yak Master

240 Posts

Posted - 2012-07-16 : 08:09:18

There are no "Non Cluestered" indexes in the table. There are two foreign keys "Lease_ID" and "County_ID".
Do they have an impaction?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources