.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
Gaurav Pal
Post New Web Links

Using SQL indexes

Posted By:      Posted Date: September 29, 2010    Points: 0   Category :Sql Server

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

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

More Related Resource Links

ArrayList indexes not set properly


I have some basic code to fill items in an ArrayList as follows:

Dim intSteps As ArrayList = New ArrayList()
Select Case Session("type")
Case 1
Case 2
Case Else
End Select

Dim j As Integer
	For j = 0 To intSteps.Count - 1
        Response.Write(intSteps.IndexOf(j) & " : " & intSteps.Item(j) & "<br />")

The issue is that for Case 1 the output is:

0 : 0
1 : 1
2 : 2
-1 : 6
-1 : 11
-1 : 12

For Case 2, it's:

0 : 0
1 : 1
2 : 2
-1 : 5
-1 : 6
3 : 11
4 : 12

And for Else, it's:

0 : 0
1 : 1
2 : 2
3 : 3
4 : 4
5 : 5
6 : 6
7 : 7
8 : 8
9 : 9
10 : 10
11 : 11
12 : 12

Why is the index being set as -1 for a few items?

How to remove indexes

How to remove indexes..as it is created by mistake I have read that if it will be deleted then data will be deleted also...PLease clear the case of removal of clustered and non clustered indexes..."SQLSERVER DBA" "INDIA"

Find all compressed indexes?

I've inherited a database on which a contractor was toying around with compressing indexes. As the work she did was never documented I've been asked to remove all compression from the database so that the front end vendor can test a service pack. Is there a simple way to either uncompress all of the compressed indexes within a database or to rebuild all existing indexes within the database without compression? Thanks for your time, XOXO CD.

How to tell if 2 or more indexes are interleaved?

Does anyone know if there is a quick and easy way to tell if indexes are interleaved or not?John M. Couch Insight Enterprises Inc. 6820 S. Harl Ave, Tempe, Az 85283 Email: john.couch@insight.com

not all indexes used in query?

hello, I have this query with several joined tables, and i have created an index on the columns used in the joins. in the query below I join tables hr200, hr300, hr301 and dim_datum. I created the following indexes: In hr200: index on hr200_obj in hr300: index on hr300_obj, hr200_obj in hr301: index on hr301_obj, hr300_obj in dim_datum: index on datum_id, datum But when i run below query, only the index on dim_datum is used. What to do to malke the query use the other indexes too?   SELECT   dbo.hr300.hr300_obj AS ziekmeldingID, dbo .hr300.hr300_date_start AS ZiekStartDatum, CASE   WHEN dbo.hr300.hr300_date_end IS NOT NULL   THEN dbo.hr300.hr300_date_end   ELSE getdate() END   AS ZiekEindDatum, dbo .hr300.hr300_time_start AS ZiekStartTijd, dbo .hr300.hr300_time_end AS ZiekEindTijd, dbo .hr300.hr300_open AS ZiekOpen, dbo .hr300.hr104_obj AS ZiekTypeID, dbo .hr300.hr038_obj AS ZiekRedenID, dbo .hr300.hr200_obj AS hrmID, CAST   (CONVERT(char(8), dbo.hr300.hr300_date_start, 112) AS int) AS ZiekStart_KEY, CAST   (CONVERT(char(8), dbo.hr300.hr300_date_end, 112) AS int) AS ZiekEind_KEY, DATEDIFF   (dd, dbo.hr300.hr300_date_start, dbo.hr300.hr300_date_end) AS Verzuimduur, dbo .hr301.hr301_date, dbo .hr301.hr301_percentage_sick / 100 AS PercentageZiek, dbo .hr301.hr301_percentage_recovered

Is there a way to make SPS 2003 search fields search the MOSS indexes?

Here's my situation: We're doing a gradual migration from SPS to MOSS. Can anyone tell me if there's an easy way to make the SPS search fields search the MOSS indexes? The easiest way I can think of is to great a web part for SPS which will gather the scope and search param and redirect the user to the MOSS search result page. If there's an easier way. I'm all ears.

will indexes auto sort?

  is it true that if we create indexes for our tables, we must still explicitly declare "order by <the index>" when we return a select*from table or can i assume that it is ordered as defined by my index without me having to explicitly state order by?    

Multiple indexes using same column

I am writing an extensive query and have multiple non-clustered indexs on a table.  I have one index for a single column [Column1] and another index for [Column2], [Column1].  I am using different querys where I sometimes just use [Column1] and other times use many columns including both [Column1], and [Column2]. My question is: If you have multiple indexes that contain the same column will they ever cause a conflict?

Strategy In creating indexes

Hi Team, Need a help strategy in creating indexes.. I have a Query which returns me more then 4 billion rows, which i am trying to insert into a table. There are views and table in the query. I am working on SQL Server 2000. So i thought if we create indexes on primary key columns on the table(which are used in views), on the columns used where condition of the main query. Will creating clustered index on the table which more than one primary key helpful? Is my strategy correct? any suggestions will be of great help! Regards, Eshwar.

No Indexes - Linking to MS Access

Hello, I am using MS Access 2000 as a front end to a SQL Server 2000 db.  When I link to the SQL Server db using a DSN, the SQL Server table pk's and indexes are not coming over with the link.  The pk's and indexes do exist in SQL Server.  I haven't had to link to these tables in over a year, but they used to link just fine. Do you have a suggestion as to where to look at this problem? Thanks you - Darryle

Problem with cursor and Rebuild indexes

(SQL Server 2008 SP1) Hi I have a stored procedure that runs frequentely (uses a cursor), that SP never have errors on execution, but when runs at the same time with a rebuild index task (Database maintenance task on sundays), the stored procedure show the following error: "Could not complete cursor operation because the table schema changed after the cursor was declared" My question: Is there any way to avoid this problem running the two task at the same time? Is this a bug of the SQL Server 2008?JAC

Partitioning Indexes

I have a partitioned table that has a partitioned index.  If I add another index does it have to be partitioned as well or can I have some partitioned indexes and some not? Thanks.

Does a Maintenance Plan "Rebuild Index" task on Tables + Views rebuild the full text indexes ?


As subject really.

I'm talking about a complete rebuild - not an incremental, or a re-organise.

SQL 2005

The full text indexes are only on individual tables, not Views.

I've asked a similar question before, but sufficiently different, and other is marked as Answered so thought I'd open a new Q : http://social.msdn.microsoft.com/Forums/en/sqltools/thread/5f17f4f1-fba9-436f-aedf-cfbfd89c8db2)


Are there any Bitmap(ped) indexes in SQL Server?


Faced this question a few days back in an interview for the position of Sr. TSQL Developer.

The interviewer asked "What are bit-mapped Indexes?". Clueless I replied that, I've never heard of this type in my past experience.

Googled up and found that these exists in ORACLE.


But again to confirm, is there any type of bit-mapped index/object/concept in MS SQL Server?

Re creating indexes while data load - Performance Consideration



In the ETL design we have followed drop index before data load and recreate it after load. But looks like this approach is time consuming.

Is there any better data load design approach? Will table partition solve this problem?


- JL

How to auto create (or Generate a script) which can create indexes on subscriber


In our scenario, subscriber and publishers have same tables and we want the indexes also to be same at all places..

How to auto create (or Generate a script) which can create indexes on subscriber which are already added on publisher.. Is there a auto way or is there any article somewhere which can help..


IMP : There might be a delay in posting replies due to time difference (GMT +2:00) and Friday's Holiday instead of Sunday.

Tx Replication INDEXES


The performance on Publisher db is acceptable but the Subscriber db is very poor. Please advise how can I mitigate this issue? As we understand the evolving reporting needs we can later fine tune and tune the performance. For now,

Will setting the following article properties act as a quick solution?

copy clustered index: Default-true

copy non clustered indexes: true

copy unique key constraints: Default-true

copy user-defined statistics: true

copy full text indexes: true ( please provide explanation)

ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend