Post reply

SQL SERVER INDEXING

  • April 11, 2012 at 11:22 am

    #272123

    Can anyone please describe the indexing ? How I understand it improve the performance or not ? Please give some examples (Before / After indexing)?

  • April 11, 2012 at 11:48 am

    #1472085

    well entire books have been written on indexing and improving performance;

    i think you'll need to read up via a google search first, and come back with specific questions.

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • April 11, 2012 at 11:57 am

    #1472092

    Well, index helps to read data faster. There are two types of indexes, Clustered and Non-Clustered Indexes.

    Clustered Index - provide the actual data page at the leaf level of the B-Tree.

    Non-Clustered Index - provide the pointers to the actual data page at the leaf level of the B-Tree.

    This is my understanding.

    Regards,
    SQLisAwe5oMe.

  • April 11, 2012 at 12:05 pm

    #1472100

    Indexing good.

    Think of it like a book. If you know the term you are searching for, you turn to the index in the back of the book. The index shows the page where the term may be found. You turn to that page and get your information.

    A table index works the same. Your query uses the (non clustered) index to look up the page of the term (column value) in your search. The query then goes to that page and pulls back the data.

    If a book has no index, you must go through every page searching for your information. It's the same with a table without an index on your search column. Your query must scan all rows looking for your information until it finds matches. It's not too bad with small books (and small tables). But try doing that with a 20 volume encyclopedia (they don't publish those any more), or a table with tens of millions of rows.

    Converting oxygen into carbon dioxide, since 1955.

  • April 11, 2012 at 12:23 pm

    #1472118

    Is there any unique format for query using index?

  • April 11, 2012 at 12:25 pm

    #1472120

    Nope. You write the query the same way.

    Actually, your indexing will be dependent on how you query your data.

  • April 11, 2012 at 12:26 pm

    #1472122

    Maybe start with these

    http://www.sqlservercentral.com/articles/Indexing/68439/

    http://www.sqlservercentral.com/articles/Indexing/68563/

    http://www.sqlservercentral.com/articles/Indexing/68636/

    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 11, 2012 at 3:28 pm

    #1472269

    Thank you all

    Its really helpful.

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

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