Post reply

Doubt on Index

  • April 6, 2012 at 5:42 am

    #253739

    Hi all,

    I was reading Stairways to Index, in that it was mentioned that " NonClustered index are a separate object and occupy their own space."

    Suppose I have an Employee table with FirstName,LastName,MiddleInitial ,Age and Address as the columns. I know that if we create a NonClustered index on this table on LastName,FirstName then the NonClustred index will be created an index key and a bookmark.

    My question is can we write some select statement ( or some how ) to see the content of this Index created above?

    Thanks & Regards,
    MC

  • April 6, 2012 at 5:56 am

    #1470044

    You can't directly see the index, but then there's no reason to need to most of the time.

    If you create an index on LastName,FirstName, then the index rows will have LastName,FirstName in them and there will be one row in the index for each row in the table.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • April 6, 2012 at 6:31 am

    #1470061

    Thanks Gila, so that means there is no way to see the index is it? That is what I wanted to know, thanks for the reply.

    Thanks & Regards,
    MC

  • April 6, 2012 at 6:37 am

    #1470065

    Why do you want to see it, and what do you want to see?

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • April 6, 2012 at 6:47 am

    #1470072

    I want to see the Index Key ( I know that it will be created on the Last Name and First Name ) and the Book Mark. I read how these are getting created , but just wanted to see how it looks like.

    Thanks & Regards,
    MC

  • April 6, 2012 at 6:55 am

    #1470075

    The index key is LastName and FirstName columns. Nothing more complex than that. The 'bookmark' will be the clustered index key (if there's a clustered index) or the RID (row identifier) of the row in the base table.

    If you want to poke around in the internals of the index, you'll need undocumented commands. DBCC IND and DBCC PAGE.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • April 6, 2012 at 7:09 am

    #1470080

    Ok, Thank you Gila.

    Thanks & Regards,
    MC

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply