Professional Web Applications Themes

Primary key or constraints in temporary table - Microsoft SQL / MS SQL Server

Hi all, i'm using in some stored procedures a # table that contains a discreet number of records (about 70000). I choose to add a primary key to encrease performances ad that it happens. The code i used is the below: alter table #tmptree ADD constraint [pk_tmptree] primary key ( cNodeID, cNodeType, cLevel ) If i try to execute the procedure twice at the same time, sql server warn me that the object [pk_tmptree] already exist. I avoid the problem, generating a guiid for every primary key generated. The code is this: set rndmid = substring(convert(varchar(64),NewID()),0,8) set pkeystr = 'alter ...

  1. #1

    Default Primary key or constraints in temporary table

    Hi all,
    i'm using in some stored procedures a # table that contains a discreet
    number of records (about 70000). I choose to add a primary key to
    encrease performances ad that it happens. The code i used is the below:
    alter table #tmptree ADD
    constraint [pk_tmptree] primary key
    (
    cNodeID,
    cNodeType,
    cLevel
    )

    If i try to execute the procedure twice at the same time, sql server
    warn me that the object [pk_tmptree] already exist.

    I avoid the problem, generating a guiid for every primary key generated.
    The code is this:


    set rndmid = substring(convert(varchar(64),NewID()),0,8)
    set pkeystr =
    'alter table #tmptree ADD
    constraint [pk_tmptree'+rndmid+'] primary key clustered
    (
    cNodeID,
    cNodeType,
    cLevel
    )'

    Now, my question is: the object [pk_tmptree] (which is a constraint), is
    linked to the table's life? When the connection is dead, the object
    persist on the database or disappear whit the table?

    Thanks in advance! By all
    Simone Greci
    ORS s.r.l.
    Italy


    *** Sent via Developersdex http://www.developersdex.com ***
    Don't just participate in USENET...get rewarded for it!
    Simone Guest

  2. #2

    Default Re: Primary key or constraints in temporary table

    Hi Simone,

    Thats correct because PK_tmptree needs to be unique within the database.

    The constraint will disappear when the table disappears as its linked to the
    table.

    --
    Tony Rogerson
    SQL Server MVP
    http://www.sqlserverfaq.com?mbr=21
    (Create your own groups, Forum, FAQ's and a ton more)


    Tony Guest

  3. #3

    Default Re: Primary key or constraints in temporary table

    Can we not create an index instead of a constraint to avoid the dynamic
    creation code?? For example:

    CREATE PROCEDURE LongProcedure AS
    BEGIN
    CREATE TABLE #Temp (colA INT NOT NULL)
    CREATE INDEX myIndex ON #Temp(colA)

    WAITFOR DELAY '00:00:20'

    DROP TABLE #Temp
    END

    Since we are looking at improving the speed of the query, is there a need to
    create a primary key constraint (the values in the table are anyways
    discreet)?? I guess this will improve the performance of the query and at
    the same time remove the code to create the constraint dynamically??
    --
    HTH,
    SriSamp
    Please reply to the whole group only!
    http://www32.brinkster.com/srisamp

    "Simone Greci" <it> wrote in message
    news:%phx.gbl... 


    SriSamp Guest

  4. #4

    Default Re: Primary key or constraints in temporary table

    Your index suggestion is a good idea but it should be declared as unique
    to maximize performance. This can provide the optimizer with important
    information.

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

    "SriSamp" <co.in> wrote in message
    news:%23g1%phx.gbl... 
    dynamic 
    need to 
    at [/ref]
    discreet [/ref]
    below: [/ref]
    generated. [/ref]
    constraint), is 
    >
    >[/ref]


    Dan Guest

  5. #5

    Default Re: Primary key or constraints in temporary table

    This type of code don't generate the same error of persistence?
    So, the index is related only to the Table and not to db?
    If i create 2 index whit the same name but associated to a different
    table, sql-server don't give me an error?



    "SriSamp" <co.in> wrote in message news:<#g1#phx.gbl>... [/ref]
    Simone Guest

Similar Threads

  1. Temporary table
    By Sauron in forum Informix
    Replies: 11
    Last Post: January 21st, 05:32 PM
  2. How to avoid a temporary table
    By Peter in forum Oracle Server
    Replies: 6
    Last Post: October 25th, 10:42 PM
  3. Replies: 6
    Last Post: October 21st, 09:31 PM
  4. Should I use temporary table?
    By Tom in forum Microsoft SQL / MS SQL Server
    Replies: 8
    Last Post: August 13th, 07:39 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