How to work with Index on sql server 2008 ?

Question

Hi

is it faster to search with index ?

if I have this table:

MEN: ID|Fname|Age

1. how I insert index to this table

2. how i can search all the person that the age between 20-30 using index ?

I work with sql server 2008

thank's in advance

Answer 1

To make the queries faster you just create an index on the Age field (I don't have 2008, but there should be an option on the right click when modifying a table).

If the index is there it will be used automatically on your next query with conditions on the Age field.

For example if you have a lot of data in the table Men this query:

SELECT * FROM Men WHERE Age BETWEEN 20 AND 30

will work much faster with that index.

The idea behind this is that if there's no index, the DB engine must check each row of data to see if the age fits the range. The index maintains a separate copy of ages and IDs, ordered by age, so the DB engine can find quickly the list of IDs that match the condition and doesn't have to check each row (this is a very simple explanation, there are some extra elements, but this is the principle).

Edit: The difference between clustered and non-clustered is this: a clustered index means the data is physically sorted on the disk based on the index. This provides the fastest access to the data, but there can be only one clustered index, so it's usually made on the primary key of the table. Also a clustered index can make the inserts a little slower since it must move records around to keep them ordered (this shouldn't be an issue if you use auto-incremented integer IDs, since the records always come at the end).

A non-clustered index uses more space to keep a sorted copy of the data with links to the primary keys. There can be as many non-clustered indexes as you need in a DB.

There are many other nuances to indexes and it's a good think to learn about them in more detail. This post: http://stackoverflow.com/questions/1175966/clustered-indexing/1176040#1176040 has some interesting links, which I recommend for reading.

Answer 2

Hi, if you are trying to make search by id , age faster add index ID,Name index is added most easy by clicking with right button of the mouse on the table in the diagram and in the context menu there is "indexses" from there you add it most easyly bets regards, Iordan

Answer 3

SQL Server maintains index behind the scene for you. You only instructs it to build an index on field(s) you want. Primary keys automatically get a clustered (unless there is another clustered exist already) index by default.

Check this article

One important thing to remember: never build indexes prematurally unless you 100% know that it will be used often enough. When your product goes in production the database will be hit with many kinds of different queries and statistic will be collected (SQL Server does that for you automatically). You then can analyze the statistic and see which indexes need to be build based on most often executed queries.

One another thing to remember when maintaining databases: indexes can get fragmented over time which will goes performance degradation. So its to have a script which rebuilds indexes periodically.

Answer 4

You get a clustered index automatically on your primary key (which i suppose is the ID column).You can add a non-clustered index on AGE if you really want. After that every search that searches by age will use that index automatically

Answer 5

CREATE NONCLUSTERED INDEX IXMenAge ON Men(Age)

The query processor will use this index automatically (and depending on your data it might decide not to use the index; remember, the query processor usually knows better than you do). To force use of the index:

SELECT * FROM Men WITH (INDEX(IXMenAge)) WHERE Age BETWEEN 20 AND 30