Professional Web Applications Themes

table var index? - Microsoft SQL / MS SQL Server

No. But you can create a primary key on it. e.g. declare tb table(i int primary key) -- -oj RAC v2.2 & QALite! http://www.rac4sql.net "chris" <com> wrote in message news:011a01c35d0b$0d470f10$gbl... ...

  1. #1

    Default Re: table var index?

    No. But you can create a primary key on it.

    e.g.
    declare tb table(i int primary key)

    --
    -oj
    RAC v2.2 & QALite!
    http://www.rac4sql.net


    "chris" <com> wrote in message
    news:011a01c35d0b$0d470f10$gbl... 


    oj Guest

  2. #2

    Default Re: table var index?

    No. You may declare a primary key or unique constraint at the time you declare the variable, however.

    --
    Tom

    ---------------------------------------------------------------
    Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
    SQL Server MVP
    Columnist, SQL Server Professional
    Toronto, ON Canada
    www.pinnaclepublishing.com/sql


    "chris" <com> wrote in message news:011a01c35d0b$0d470f10$gbl...

    Is it possible to add an index to a table variable?

    TIA
    Chris

    Tom Guest

  3. #3

    Default Re: table var index?

    You can effectively do this by declaring as a primary key the
    column list you want a clustered index on, and as unique any
    lists you want non-clustered indexes on, if you add the primary
    key columns to the end of each unique list.

    I am not certain whether the query optimizer handles indexes
    on table variables as well as on real tables, but I do know they
    are used in query plans.

    declare t table (
    i int primary key,
    j int not null,
    k int not null,
    l int not null,
    unique(j,k,l,i),
    unique (k,l,i)
    )

    -- Steve Kass
    -- Drew University
    -- Ref: 1470F188-33AD-4B64-BA88-23DA065B3616

    chris wrote:
     

    Steve Guest

  4. #4

    Default Re: table var index?

    actually you can specify if a PK/UQ constraint is implemented with CLUSTERED
    or NONCLUSTERED index so there is no 1:1 mapping b/n constraint type and
    impl index type.

    cheers,
    </wqw>

    "Steve Kass" <edu> wrote in message
    news:#phx.gbl... 
    >[/ref]


    Vlad Guest

Similar Threads

  1. Need Help with Incrementing an Index Into a Table
    By FusionRed in forum Coldfusion - Getting Started
    Replies: 8
    Last Post: January 12th, 09:37 PM
  2. default index for primary key of a table
    By Greg Stark in forum PostgreSQL / PGSQL
    Replies: 0
    Last Post: December 24th, 08:15 PM
  3. Adding an index to a table
    By Simon Jackson in forum PHP Development
    Replies: 1
    Last Post: August 27th, 12:56 AM
  4. GD.pm index table limitation
    By julien in forum PERL Miscellaneous
    Replies: 0
    Last Post: July 28th, 07:19 PM
  5. Large read-only table for queries - what index?
    By fred in forum Microsoft SQL / MS SQL Server
    Replies: 2
    Last Post: July 18th, 02:27 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