Using indexes in SQL Server 2005

Tags:
SQL Server 2005
SQL Server index strategy
SQL Server Indexes
What is the main purpose of using indexes in SQL Server 2005?

Answer Wiki

Thanks. We'll let you know when a new response is added.

Indexes are used to decrease the amount of time to query the table.

Assume that you have a table setup like this.

CREATE TABLE Emp
(EmpId INT PRIMARY KEY,
FirstName varchar(50),
LastName varchar(50),
DepartmentId INT,
ManagerId INT)

If you were going to often query based by the DepartmentId you would want an index on the DepartmentId column. An index is a separate copy of the data from the table, sorted on the indexed column. So in this case it would be sorted by the DepartmentId. This allows the SQL Server to more quickly find the records for the department that you are looking for.

So assuming that you are looking for all employees in Department #1 you would run this query.

SELECT EmpId
FROM Emp
WHERE DepartmentId = 1

Without an index on the DepartmentId column the SQL Server would need to scan the Emp table looking for records with a value of 1 in the departmentId column. With an index on the DepartmentId column the SQL Server knows where the first record is that has a value of 1, and it reads the records until it comes to a record with a different value (as they are stored in order). It then returns these records. This operation is called an index sek.

You may want to check out one of the presentations I’m giving at this weekends SoCal Code Camp one of which is on indexes.

Discuss This Question: 4  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • Kccrosser
    A simple analogy of why we use indexes: Assume you have a phone book with names and numbers, but they aren't in any particular order. That is what a common database table might look like - unless certain constraints (which are quite similar to indexes!) have been put on the table, there is no guarantee as to the order of the records. So how do you find "Prefect, Ford" to call him? You would have to look at all the entries in the phone book until you happened to find his entry. If you had 1 Million names in your phone book, on average you would have to look at 500,000 name entries to find the one you wanted, every time you wanted to call someone. On the other hand, if you put an index on the names, then (in general) you only need to look at about 17-19 records in the index, and then one record in the actual phone book (table). Depending on databases and specific index choices, the size may vary, but using the simplest form of index (a B-Tree), a search through the index would only require looking at 17-19 index records (for details, see "B-Tree" and "Binary Search Tree" in Wikipedia). Obviously, the indexed search in this example would be about 26,000 times faster than scanning the whole book (known as a "full table scan" in a database). The tricky part is deciding what indexes to create. In general, if you often want too look up something (the result) in a table based on the values in one or more columns (the criteria), then some or all of those criteria columns should be indexed. However, some index structures are much more efficient than others, and indexes on some columns (or combinations of columns) might never be used. Since every index adds some amount of database overhead when adding, updating, or deleting records in a table, indexes that are never (or very rarely) used just consume overhead (CPU and storage space) and should be avoided. One key note many beginners (and even some experienced engineers) overlook - if a table is relatively small (a few hundred or less records), an index may be useless, as the database engine may decide it is just as efficient to load the whole table into memory and scan it for the requested data directly, rather than going to a separate index to look up the record and then going back to the table to fetch the data. Your first focus with indexes should be on the larger tables, particularly ones that are frequently queried.
    3,830 pointsBadges:
    report
  • Pravara
    Hi, i would like to know if applying indexing on database tables have any overheads. Suppose i have a Stored procedure. in that procedure i insert values in a table, say "Emp". after that i use values from Emp for further processing. and all this happens in a while loop. If i apply index on Emp table before loop, then while inserting values, will it increase time reqed for insert process or it will help in performance improvement.
    15 pointsBadges:
    report
  • carlosdl
    If you have an index on a table, then besides the insertion of the data in that table, the database engine needs to insert some data in the index, so it will affect insert performance (although it could not be noticeable). Indexes could improve performance when querying the tables (that's their purpose), or in other operations where you need to search for records in the table.
    68,470 pointsBadges:
    report
  • Kccrosser
    Indexes always add some overhead to table insert/update/delete transactions. However, except in very rare applications, the additional index update overhead is negligible compared to the performance gain achieved when querying the table and using the index. Some simple criteria to decide whether to add an index on column "MyData": 1. Is MyData ever used in a query as part of the query criteria (i.e., is it in the Where clause or in a Join On clause)? If so, an index is probably a good thing. 2. What is the rate of queries against the table as opposed to inserts into the table? If the rate of queries is at least 3-4x the rate of inserts, then the insert overhead is likely much less than the query overhead savings. 3. Is the table relatively large (i.e. more than a few hundred rows)? If the table is very small, and index may not be useful, as the query optimizer may decide loading the whole table is cheaper than loading an index and using that to access the table. 4. Is MyData useful as a "discriminant" value? Before indexing a column, consider whether the index will really help a lot. Some examples of questionable index columns are: Gender (only 2 or 3 different values), Race (only 5 NCIC values), and similar columns that have a very limited set of distinct values. HOWEVER, in some cases, indexing these columns CAN significantly improve performance of some queries. I would look at other columns first, and then look at indexing such "non-discriminant" columns only when I have queries that must search on these columns and cannot use any more discriminant columns. Also - when your application behavior is known (lots of predefined queries with specific where clause contents, instead of ad-hoc queries with random columns in the where clause), you should create indexes that are optimized for the actual queries. These should normally be multi-column indexes, which are more efficient than a bunch of single-column queries.
    3,830 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following