In the past working with other databases, if one made a call on an indexed column in SQL, it would automatically use the associated key to do the look up. In watching the performance of one of my databases, I am believing Microsoft SQL is not doing
Here is an example of what I am speaking about.
I have a table called "orderitem".
in the this table are the following items:
UniqNumber bigint (And primary Key)
mainOrderNumber as bigint
itemname as string
itemcount as integer
itemcost as money
In addition when I go into look at the table definitions I find that I have set up an Index called IX_orderitem. This is defined as being for "mainOrderNumber" column.
based on performance I do not believe that this key is being called when I do:
Select * From orderitem WHERE mainOrderNumber = @selectedordernumber
Do I have to specifically reference the index name and if so what is the syntax?
I am using visual studio2005 (VB.net) and also SQL2005 express. I also have a Visual studio 2010 that I am porting over to and on that machine I am using SQL2008R express. I am assuming the answer for both systems will be the same.
(In VS2005 and VS2010 it offers two forms of keys Unique and Index)
View Complete Post