sql query statements and indexes

[TechBoyJK]

When you create an index on a database, how does it work with your sql query statements in code?

Do you create an additional datasource on the server that connects to the index rather than the database?

Or do your query structures remain the same, but since you have an index, results come back quicker?

ex. if you use a datasource named table1 that connects to table1. you then create an index. do you create a new datasource pointing to the index and call it table1_index, and then query that new datasource in your code?

 

[KLin]

http://en.wikipedia.org/wiki/Index_(database)

 

[imported_Dhaval00]

Is this specific to SQL Server? If so:

Indexes are essentially balanced trees that are analogous to a phone directory. They make row lookups faster. But in order for you to use indexes effectively, you need to be cognizant of your WHERE clauses and JOINs. From what you have posted so far, I guarantee that you'll burn yourself if you don't read up on indexes and various other constructs with the SQL realm. A datasource has nothing to do with an index - a datasource is simply a "pointer" to your entire DB.

One example: Let's say you have a table Students with LastName and FirstName. If you were to create a nonclustered index on LastName and have a query like
SELECT * FROM Students WHERE LastName='%Smith%'
SQL Server will end up using the nonclustered index [it is very important that you make use of the execution plan in order for you to know if a particular index is being used]. On the contrary, if you have a query like
SELECT * FROM Students WHERE LastName='%Smith%' AND FirstName='%Paul%'
the index on LastName may or may not get used - it all depends on the SQL Server optimizer [the optimizer is affected by various things like the table size, relations, etc. and something that works today, may not work 3 months down the road].

Again, this discussion is specific to SQL Server. From what I see, if time is an issue, you better consult a DBA or someone who has deeper knowledge of DB engines.

 

[TechBoyJK]

Originally posted by: Dhaval00
Is this specific to SQL Server? If so:

Indexes are essentially balanced trees that are analogous to a phone directory. They make row lookups faster. But in order for you to use indexes effectively, you need to be cognizant of your WHERE clauses and JOINs. From what you have posted so far, I guarantee that you'll burn yourself if you don't read up on indexes and various other constructs with the SQL realm. A datasource has nothing to do with an index - a datasource is simply a "pointer" to your entire DB.

One example: Let's say you have a table Students with LastName and FirstName. If you were to create a nonclustered index on LastName and have a query like
SELECT * FROM Students WHERE LastName='%Smith%'
SQL Server will end up using the nonclustered index [it is very important that you make use of the execution plan in order for you to know if a particular index is being used]. On the contrary, if you have a query like
SELECT * FROM Students WHERE LastName='%Smith%' AND FirstName='%Paul%'
the index on LastName may or may not get used - it all depends on the SQL Server optimizer [the optimizer is affected by various things like the table size, relations, etc. and something that works today, may not work 3 months down the road].

Again, this discussion is specific to SQL Server. From what I see, if time is an issue, you better consult a DBA or someone who has deeper knowledge of DB engines.

gotcha. i just slapped myself considering i actually passed the sql 2000 admin test for the mcdba cert. i never passed the design test though.. it was tough.

you are right and i really just woke up.

a datasource points to a database. within that database are tables and other objects. when you need to access any object in that database, and depending on the language and sql server u are using, you typically point the query to the database via datasource and then call a table or object that resides in that datasource.

so. basically, audit the programming code and note all the different queries. once you have fine tuned those queries, build indexes based on those queries. if things are in line, the query will take advantage of the index and be able to use it to locate data faster.

code doesnt change. build the indexes around your queries. the db engine will use the index to locate the data it needs faster than having to search page by page for everything.

does that sound right?

 

[imported_Dhaval00]

in a nutshell, yes. but always easier said than done. sorry, i am still being skeptic because of your first post .

i wouldn't know if code changes or not - depends on how much and what you extracted. if you're building your indexes based on existing CRUD statements, then yea... code wouldn't change.