Professional Web Applications Themes

Clustered index on GUID - Microsoft SQL / MS SQL Server

Hi Is it OK when I have primary clustered index on uniqueidentifier field? Does it make performance better or worse? Regards Miroo...

  1. #1

    Default Clustered index on GUID

    Hi
    Is it OK when I have primary clustered index on uniqueidentifier field? Does
    it make performance better or worse?

    Regards
    Miroo

    Miroo_news Guest

  2. #2

    Default Re: Clustered index on GUID

    Like most indexing decisions, it depends on the specifics of your
    application. Clustered indexes are most useful for range queries, ORDER
    BY and retrieval of a single row based on a unique clustered index key.
    Since one doesn't usually select by uniqueidentifier range or ORDER BY a
    uniqueidentifier value, a clustered index on a uniqueidentifier value
    may not be appropriate since there may be a better choice for your
    clustered index. However, a clustered index on a uniqueidentifier
    primary key can be beneficial when your primary method of retrieval is
    single rows by uniqueidentifier value.

    Indexing focuses on SELECT performance but you need to consider DML
    performance as well. Due to the random nature of GUIDs under Windows
    2000 and above, INSERT performance can be negatively affected with large
    tables and a clustered index on a uniqueidentifier value.

    --
    Hope this helps.

    Dan Guzman
    SQL Server MVP

    -----------------------
    SQL FAQ links (courtesy Neil Pike):

    http://www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
    http://www.sqlserverfaq.com
    http://www.mssqlserver.com/faq
    -----------------------

    "Miroo_news" <poczta.fm> wrote in message
    news:bfj3oj$o0$news.tpi.pl... 
    field? Does 


    Dan Guest

  3. #3

    Default Re: Clustered index on GUID

    Użytkownik "Dan Guzman" <net> napisał w
    wiadomości news:#phx.gbl... 

    So, if I have a lot of joins on an uniqueidentifier field
    should I make it clustered?
     

    Sure, thank you!

    Best regards,
    Miroo

    Miroo_news Guest

  4. #4

    Default Re: Clustered index on GUID

    Uzytkownik "Peter Nolan" <co.uk> napisal w
    wiadomosci news:057c01c35050$75fc4a60$gbl... 

    Yes, I know, but uniqueidentifier is much easier to use
    and more flexible.
    My collegues had problems with "identity" type so I decided
    to use GUID. With GUID I can set new id in user application
    and not to ask server "which ID did you use lately? Is it
    mine for sure?"
     

    This application will be working in different places and probably
    another main application will be gathering data.
    On the other hand, a lot of data (different tables connected together)
    will be imported from main application. As I said - it's much
    easier to use GUIDS.

    Regards
    Miroo

    Miroo_news Guest

  5. #5

    Default Re: Clustered index on GUID

    Użytkownik "Dan Guzman" <net> napisał w
    wiadomości news:#phx.gbl... 
    >
    > I hate to make generalizations because the correct answer depends on a
    > number of factors. It often makes sense to have a clusered (and
    > composite) primary key on the many side of a relationship (e.g.
    > OrderDetails FK to Orders) because related data are often accessed
    > together.[/ref]

    This is the answer. Thank you.
    I should read about indexes etc, maybe I'll find
    some time during my two-week holidays?

    Best regards,
    Miroo

    Miroo_news Guest

Similar Threads

  1. GUID and CFProcParam
    By DeliK in forum Coldfusion Database Access
    Replies: 5
    Last Post: June 23rd, 11:03 PM
  2. GUID question
    By Rob in forum ASP.NET Security
    Replies: 3
    Last Post: January 22nd, 07:28 PM
  3. Retrieving GUID from DB2 UDB
    By Knut Stolze in forum IBM DB2
    Replies: 0
    Last Post: August 6th, 07:55 AM
  4. Convert index to clustered
    By Anith Sen in forum Microsoft SQL / MS SQL Server
    Replies: 3
    Last Post: July 11th, 04:57 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139