Professional Web Applications Themes

index on table variable - Microsoft SQL / MS SQL Server

Can we create index on a table variable? Thanks....

  1. #1

    Default index on table variable

    Can we create index on a table variable?

    Thanks.


    sam Guest

  2. #2

    Default Re: index on table variable

    Only the ones the system auto-generates through primary key or unique
    constraints.

    For a comprehensive list of other limitations of table variables:
    http://www.aspfaq.com/2475





    "sam" <com> wrote in message
    news:019e01c368cd$6dc99e20$gbl... 


    Aaron Guest

  3. #3

    Default Re: index on table variable

    Only by declaring a PRIMARY KEY or UNIQUE constraint. You'll need to
    use a regular temp table if you need a non-unique index.

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

    "sam" <com> wrote in message
    news:019e01c368cd$6dc99e20$gbl... 


    Dan Guest

  4. #4

    Default Re: index on table variable

    Or add an additional uniquifying column to the index if one is available...

    declare t table (
    pk int primary key,
    non_unique_column int,
    unique(non_unique_column,pk)
    )

    -- Steve Kass
    -- Drew University
    -- Ref: BA7ADA43-EE6F-40F6-BD37-AAEC97686323

    Dan Guzman wrote:
     

    Steve Guest

Similar Threads

  1. Using Loop Index in variable
    By Stressed_Simon in forum Macromedia ColdFusion
    Replies: 3
    Last Post: April 29th, 05:55 AM
  2. Adding an index to a table
    By Simon Jackson in forum PHP Development
    Replies: 1
    Last Post: August 27th, 12:56 AM
  3. Is there any way to loop a temp table or table variable without cursor?
    By Lawrence in forum Microsoft SQL / MS SQL Server
    Replies: 7
    Last Post: August 9th, 08:25 PM
  4. table var index?
    By oj in forum Microsoft SQL / MS SQL Server
    Replies: 3
    Last Post: August 8th, 11:53 AM
  5. GD.pm index table limitation
    By julien in forum PERL Miscellaneous
    Replies: 0
    Last Post: July 28th, 07:19 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