Why a table have maximum one clustered index ?

  • Hi all,

    1)Why a table have maximum one clustered index ?

    2)Please give me basic definitions of clustered and non-clustered indexes!

    3)What is fragementation and defragmentation?

    Please help me in this regard.

    Thanks in advance

    vijay

  • prvreddy (3/12/2009)


    Hi all,

    1)Why a table have maximum one clustered index ?

    Because the clustered index is the table. The leaf pages of the cluster are the actual data pages of the table.

    2)Please give me basic definitions of clustered and non-clustered indexes!

    Have you looked in Books Online? There's several pages on indexes, clustered and nonclustered

    3)What is fragementation and defragmentation?

    http://sqlinthewild.co.za/index.php/2008/10/20/what-is-fragmentation/

    Defragmentation is the removal of fragmentation. Check books Online under ALTER INDEX, specifically the sections REBUILD and REORGANISE

    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
  • prvreddy (3/12/2009)


    Hi all,

    1)Why a table have maximum one clustered index ?

    Because the database physically stores the data in the order of the clustered index and you can only physically store the data in way.

    prvreddy (3/12/2009)


    2)Please give me basic definitions of clustered and non-clustered indexes!

    Check out this article in the Online - Books on Line - http://msdn.microsoft.com/en-us/library/ms175049(SQL.90).aspx

    prvreddy (3/12/2009)


    3)What is fragementation and defragmentation?

    Here are 2 links that discuss fragmentation:

    http://www.sql-server-performance.com/articles/per/index_fragmentation_p1.aspx

    http://blog.sqlauthority.com/2007/06/24/sql-server-comparison-index-fragmentation-index-de-fragmentation-index-rebuild-sql-server-2000-and-sql-server-2005/

  • Darn, Gail beat me to it. Don't know why your blog wasn't on page 1 of my google search.:D

  • Hi,

    all the answers can easily be found in BOL (aka "SQL Server Books Online" in the "Documentation and Tutorials" folder of your SQL Server installation).

    Example:

    Question 1) looking for "clustered indexes, about clustered indexes" in BOL will give you a detailed answer.

    One statement will be:

    ... There can only be one clustered index per table, because the data rows themselves can only be sorted in one order. ...

    Question 2) can be answered by using BOL as well.

    Question 3) is unspecific and will end in a multiple choice answer based on BOL...

    Btw.: Does your homework/test or whatever you need the answers for allow multiple choice answers as a reply to the question?

    Sorry if it sounds like being rude but those questions unlikely refer to a "real life problem" of somebody who works with SQL Server.

    Even if it would be somebody who's completely new to SQL Server, those questions are unlikely to show up all at the same time...

    If you're intended to use SQL Server as part of your job: getting familiar with BOL will help you to resolve most of the questions that show up in your daily business. And therewith you wouldn't have to wait for an answer coming out of a forum...



    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

  • Gail, Jack:

    How can you be sure that the fragmentation question relates to indexing?

    Why not Message Fragmentation in Service Broker? 🙂



    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

  • lmu92 (3/12/2009)


    How can you be sure that the fragmentation question relates to indexing?

    Context.

    Why not Message Fragmentation in Service Broker? [Smile]

    Why not File System fragmentation? Why not IP fragmentation? Why not memory fragmentation?

    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
  • GilaMonster (3/12/2009)


    lmu92 (3/12/2009)


    How can you be sure that the fragmentation question relates to indexing?

    Context.

    Ditto.

  • lmu92 (3/12/2009)


    Even if it would be somebody who's completely new to SQL Server, those questions are unlikely to show up all at the same time...

    I don't agree there. They look like typical questions from someone just getting started with indexing.

    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
  • GilaMonster (3/12/2009)


    lmu92 (3/12/2009)


    Even if it would be somebody who's completely new to SQL Server, those questions are unlikely to show up all at the same time...

    I don't agree there. They look like typical questions from someone just getting started with indexing.

    Funny. They look to me more like questions in an interview or a test. Maybe I'm getting cynical in my old age...

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • GSquared (3/12/2009)


    ...

    Funny. They look to me more like questions in an interview or a test. Maybe I'm getting cynical in my old age...

    I had the same impression like GSquared and therefore I replied like I did...

    Looks like we won't know the reason for posting until the OP tells us.

    Until then I think my answer wasn't as polite as the ones from Gail and Jack. So I apologize and step back. :Whistling:



    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

  • Hi guys,

    Nobody gave definitions to all my questions.You are all giving links or BOL links.But, i need definition, please help me in this regard

    Thanks

    Vijay

  • prvreddy (3/12/2009)


    Hi guys,

    Nobody gave definitions to all my questions.You are all giving links or BOL links.But, i need definition, please help me in this regard

    Thanks

    Vijay

    The links are where the definitions are. Take the time to read them. Who is going to define it better than the ones who developed it?

  • prvreddy (3/12/2009)


    Hi guys,

    Nobody gave definitions to all my questions.You are all giving links or BOL links.But, i need definition, please help me in this regard

    Thanks

    Vijay

    Why rewrite things when you can be pointed directly to things that are already written? Hint, make Books Online your friend. I have been working with SQL Server for 12 years, and I still read it on many occasions. It is a good resource. If, after reading everything you have been pointed to and you still have questions or need clarification, we are right here to help you out.

  • [font="Verdana"]The definitions are in SQL Server Books Online, which is why you have been given the links to that. You can download SQL Server Books Online from Microsoft if you don't already have a copy. Otherwise, if you have SQL Server Enterprise Manager open, just press F1.

    As it happens, Gail has already given you excellent short definitions for two of your questions.

    [/font]

Viewing 15 posts - 1 through 15 (of 17 total)

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