SQL indexes

Tags:
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

Answer Wiki

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

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.

Discuss This Question: 6  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
  • carlosdl
    What do you mean by "the result of each index" ?
    67,925 pointsBadges:
    report
  • carlosdl
    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.
    67,925 pointsBadges:
    report
  • Fhhdjfhjdhfjhjdfh
    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.
    135 pointsBadges:
    report
  • Kccrosser
    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.
    3,830 pointsBadges:
    report
  • Fhhdjfhjdhfjhjdfh
    Thnx sir, for the query response from u . thnx.
    135 pointsBadges:
    report
  • Fhhdjfhjdhfjhjdfh
    [...] SQL indexes asked by Fhhdjfhjdhfjhjdfh, answered by Mrdenny and discussed by Carlosdl and [...]
    0 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