Home > IT Answers > SQL Server > SQL indexes
Fhhdjfhjdhfjhjdfh
135 pts.
 SQL indexes
sql indexes, SQL Server, T-SQL
hello, there are many indexes clustered an non clustered .so how can i see the result of each index, clustered and non clustered seperately through (T-SQL) pls guide me. RINO

Software/Hardware used:
sqlserver
ASKED: June 3, 2010  12:21 PM
UPDATED: June 8, 2010  4:04 AM
RELATED QUESTIONS
Answer Wiki:
Not quite sure what you are talking about here. Indexes don't have results, they are sorted copies of the data in the table. You can't query an index to get the data from is specifically, you query the table, and if using an index to find the data is a more efficient process, then the engine will do so. The data stored within the index is identical to the data stored within the table. When the data is changed within the table, the data is also changed within the index before the calling program is told that the change has been successful. Basically what happens is this. <pre> User runs an insert, update, or delete command Transaction starts Engine Updates the table Engine updates the indexes Transaction is committed User is informed that the operation has completed</pre> If you have different data stored in an index than you do within the database table then your database engine isn't working correctly and you need to contact whoever made it.
Last Wiki Answer Submitted:  June 4, 2010  6:58 pm  by  Fhhdjfhjdhfjhjdfh   135 pts.
All Answer Wiki Contributors:  Fhhdjfhjdhfjhjdfh   135 pts.
To see all answers submitted to the Answer Wiki: View Answer History.


RSS - Discussions Help
Discuss This Question:
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _

 

What do you mean by “the result of each index” ?

carlosdl
 63,535 pts.
 

Rino, an index is a database object, not an operation, and thus, it doesn’t have a ‘result’. That’s why I asked for clarification.

carlosdl
 63,535 pts.
 

Thnx sir ,

For giuding me and clearing out my doubt .
i also want to know if i m using clustered and non-clustered index on the same table then
which index will be used clustered or non-clustered.

Thanx.

Fhhdjfhjdhfjhjdfh
 135 pts.
 

Which index will be used will be determined by the Query Optimizer in SQL Server.
If you are executing a query that could use EITHER a clustered or a non-clustered index, and the query optimizer determines that the “cost” is roughly equivalent, then it should normally use the clustered index. However, it could determine that the non-clustered index would result in better performance.

Example – assume a simple table and indexes:

create table myTable (
   col1data   varchar(255) not null,
   col2data  varchar(255) not null);
create clustered index myClusterIndex on myTable(col1data);
create index myNonClusterIndex on myTable(col2data);
select ... from myTable where col1data = ...

This query should use the clustered index – not necessarily because it is clustered, but because the index matches the query criteria.

select ... from myTable where col2data = ...

This query should use the non-clustered index, for obvious reasons.

select ... from myTable where col1data = ... and col2data = .

..
This query COULD use either index (or BOTH indexes!). Depending on the distribution of data in the two columns, the query optimizer could choose to use either index. If the data in “col2data” was highly discriminant (lots of different data values), while the data in “col1data” was “clumped”, then the non-clustered index may give better performance.

Kccrosser
 3,830 pts.
 

Thnx sir,

for the query response from u .

thnx.

Fhhdjfhjdhfjhjdfh
 135 pts.