searching using sql index

[AngelicOne]

Could someone tell me how could setting up index would affect my select statement?

I read that it boost performance especially for finding specific values, but how?

[finito]

When you run an if statement if you give it a boolean value say true it will execute faster then a condition.

i.e

If (true)
then Work

boolean is a 1 or 0 bit operator 1 is true and 0 is false.

no when you do something like this

if ("Hello".Equals(TextBox1.Text))
then Work

it may seem instant but the machine had to make more of an effort.to generate the value.

What does this have to do with your question you might ask? Everything.

You see Select in SQL is basically an if statement.

Example

SELECT * from Products Where id = 123 and Name ='John'

would translate to

if (id = 123 and Name ='John')
then Showrecords().

When you search by index the query has less to do then it would if it didn't have an index.

Example.

There is a Record in your database

ID | Name |  Age | Department |
11 | John |  28  |    IT      |

Now if you search for the Record by index.

SELECT * FROM Employees Where ID = 11

or

SELECT * FROM Employees WHERE Name ='John'

Remember machine stores everything in binary.

Which one do you think will execute faster.

When you do these operation on a small scale it is a negligible performance gain. But When you want to get a single value from thousands of records that is where you will see the difference in performance.

Hope my explanation was satisfactory.

[Geekitygeek]

finito, i think you may have confused an index with a unique id...that or i'm misreading your explaination.

An index is a transparent process that you can enable in sql server. When you create an index on a column of a table, the server will keep a record of each value alongside its row number and physcial location on disk. When you search/join based on an indexed column the server can reference the index to find the row and jump straight to that row rather than having to go row by row to find a match.
Theres a nice rundown of guidelines and where to use different types of index here.