Professional Web Applications Themes

Temp table question - Microsoft SQL / MS SQL Server

Hello! I need someone will shed light on this strange behaviour of temp. table SQL 7.0 (sp2) on NT4.0 I try to create #tem. table with follow syntax. CREATE TABLE #Items1 ( Id int INT NOT NULL , ItemId int NOT NULL , CONSTRAINT PK_Items1 PRIMARY KEY NONCLUSTERED ( Id ) ) A process runs a lot of time and I canceled it When I remove CONSTRAINT PK_Items1 PRIMARY KEY NONCLUSTERED ( Id ) It runs 0 sec ----- PS. I have noticed that It does not happen always I mean sometimes the first DDL works fine.There are a long ...

  1. #1

    Default Temp table question

    Hello!
    I need someone will shed light on this strange behaviour of temp. table
    SQL 7.0 (sp2) on NT4.0

    I try to create #tem. table with follow syntax.

    CREATE TABLE #Items1 (
    Id int INT NOT NULL ,
    ItemId int NOT NULL ,
    CONSTRAINT PK_Items1 PRIMARY KEY NONCLUSTERED
    (
    Id
    )
    )
    A process runs a lot of time and I canceled it
    When I remove CONSTRAINT PK_Items1 PRIMARY KEY NONCLUSTERED
    (
    Id
    )
    It runs 0 sec
    -----
    PS. I have noticed that It does not happen always I mean sometimes the first
    DDL works fine.There are a long periods in a day that I need just remove
    constraint creation in order to allow user to continue to work.
    I looked at temp DATABASE ,so it is not full.

    Than you for your help.





    Uri Dimant Guest

  2. #2

    Default Re: Temp table question

    Uri,

    Don't know why that is happening but is there any particular reason why you
    have a PK constraint on a temporary table? Have you tried using just a
    unique index?

    --

    Andrew J. Kelly
    SQL Server MVP


    "Uri Dimant" <uridiscar.co.il> wrote in message
    news:eE%23io55QDHA.3768tk2msftngp13.phx.gbl...
    > Hello!
    > I need someone will shed light on this strange behaviour of temp. table
    > SQL 7.0 (sp2) on NT4.0
    >
    > I try to create #tem. table with follow syntax.
    >
    > CREATE TABLE #Items1 (
    > Id int INT NOT NULL ,
    > ItemId int NOT NULL ,
    > CONSTRAINT PK_Items1 PRIMARY KEY NONCLUSTERED
    > (
    > Id
    > )
    > )
    > A process runs a lot of time and I canceled it
    > When I remove CONSTRAINT PK_Items1 PRIMARY KEY NONCLUSTERED
    > (
    > Id
    > )
    > It runs 0 sec
    > -----
    > PS. I have noticed that It does not happen always I mean sometimes the
    first
    > DDL works fine.There are a long periods in a day that I need just remove
    > constraint creation in order to allow user to continue to work.
    > I looked at temp DATABASE ,so it is not full.
    >
    > Than you for your help.
    >
    >
    >
    >
    >

    Andrew J. Kelly Guest

  3. #3

    Default Re: Temp table question

    If your doing a lot of searching and joining you also might consider
    clustering the unique index as well. It's certainly worth a try to see if
    it speeds up your process overall.

    --

    Andrew J. Kelly
    SQL Server MVP


    "Uri Dimant" <uridiscar.co.il> wrote in message
    news:eIImC38QDHA.1556TK2MSFTNGP10.phx.gbl...
    > Hi
    > Thanks ,Andrew
    > We move a lot of data to the temp table and perfome some search and join
    > operations.
    > But you are right I will try to use an unique index.
    >
    >
    > "Andrew J. Kelly" <sqlmvpnooospamshadhawk.com> wrote in message
    > news:#q$XXh7QDHA.304tk2msftngp13.phx.gbl...
    > > Uri,
    > >
    > > Don't know why that is happening but is there any particular reason why
    > you
    > > have a PK constraint on a temporary table? Have you tried using just a
    > > unique index?
    > >
    > > --
    > >
    > > Andrew J. Kelly
    > > SQL Server MVP
    > >
    > >
    > > "Uri Dimant" <uridiscar.co.il> wrote in message
    > > news:eE%23io55QDHA.3768tk2msftngp13.phx.gbl...
    > > > Hello!
    > > > I need someone will shed light on this strange behaviour of temp.
    table
    > > > SQL 7.0 (sp2) on NT4.0
    > > >
    > > > I try to create #tem. table with follow syntax.
    > > >
    > > > CREATE TABLE #Items1 (
    > > > Id int INT NOT NULL ,
    > > > ItemId int NOT NULL ,
    > > > CONSTRAINT PK_Items1 PRIMARY KEY NONCLUSTERED
    > > > (
    > > > Id
    > > > )
    > > > )
    > > > A process runs a lot of time and I canceled it
    > > > When I remove CONSTRAINT PK_Items1 PRIMARY KEY NONCLUSTERED
    > > > (
    > > > Id
    > > > )
    > > > It runs 0 sec
    > > > -----
    > > > PS. I have noticed that It does not happen always I mean sometimes the
    > > first
    > > > DDL works fine.There are a long periods in a day that I need just
    remove
    > > > constraint creation in order to allow user to continue to work.
    > > > I looked at temp DATABASE ,so it is not full.
    > > >
    > > > Than you for your help.
    > > >
    > > >
    > > >
    > > >
    > > >
    > >
    > >
    >
    >

    Andrew J. Kelly Guest

  4. #4

    Default Re: Temp table question

    Hi
    Thanks ,Andrew
    We move a lot of data to the temp table and perfome some search and join
    operations.
    But you are right I will try to use an unique index.


    "Andrew J. Kelly" <sqlmvpnooospamshadhawk.com> wrote in message
    news:#q$XXh7QDHA.304tk2msftngp13.phx.gbl...
    > Uri,
    >
    > Don't know why that is happening but is there any particular reason why
    you
    > have a PK constraint on a temporary table? Have you tried using just a
    > unique index?
    >
    > --
    >
    > Andrew J. Kelly
    > SQL Server MVP
    >
    >
    > "Uri Dimant" <uridiscar.co.il> wrote in message
    > news:eE%23io55QDHA.3768tk2msftngp13.phx.gbl...
    > > Hello!
    > > I need someone will shed light on this strange behaviour of temp. table
    > > SQL 7.0 (sp2) on NT4.0
    > >
    > > I try to create #tem. table with follow syntax.
    > >
    > > CREATE TABLE #Items1 (
    > > Id int INT NOT NULL ,
    > > ItemId int NOT NULL ,
    > > CONSTRAINT PK_Items1 PRIMARY KEY NONCLUSTERED
    > > (
    > > Id
    > > )
    > > )
    > > A process runs a lot of time and I canceled it
    > > When I remove CONSTRAINT PK_Items1 PRIMARY KEY NONCLUSTERED
    > > (
    > > Id
    > > )
    > > It runs 0 sec
    > > -----
    > > PS. I have noticed that It does not happen always I mean sometimes the
    > first
    > > DDL works fine.There are a long periods in a day that I need just remove
    > > constraint creation in order to allow user to continue to work.
    > > I looked at temp DATABASE ,so it is not full.
    > >
    > > Than you for your help.
    > >
    > >
    > >
    > >
    > >
    >
    >

    Uri Dimant Guest

  5. #5

    Default Re: Temp table question

    Uri Dimant (uridiscar.co.il) writes:
    > I need someone will shed light on this strange behaviour of temp. table
    > SQL 7.0 (sp2) on NT4.0
    >
    > I try to create #tem. table with follow syntax.
    >
    > CREATE TABLE #Items1 (
    > Id int INT NOT NULL ,
    > ItemId int NOT NULL ,
    > CONSTRAINT PK_Items1 PRIMARY KEY NONCLUSTERED
    > (
    > Id
    > )
    > )
    > A process runs a lot of time and I canceled it
    > When I remove CONSTRAINT PK_Items1 PRIMARY KEY NONCLUSTERED
    > (
    > Id
    > )
    > It runs 0 sec
    > -----
    This is because another process also have created this temp table, and
    done so in a transaction. Therefor it holds a lock on the row in
    sysobjects where name = 'PK_Items1'.

    If you don't name the constraint, there will be no blocking.


    --
    Erland Sommarskog, SQL Server MVP, [email]sommaralgonet.se[/email]

    Books Online for SQL Server SP3 at
    [url]http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp[/url]
    Erland Sommarskog Guest

Similar Threads

  1. Check for existance of a temp table
    By don hunt in forum Informix
    Replies: 2
    Last Post: November 13th, 05:27 AM
  2. Temp table help...
    By trtpravin@yahoo.com in forum Informix
    Replies: 1
    Last Post: September 26th, 10:26 PM
  3. create temp table with no log
    By cathy wang in forum Informix
    Replies: 11
    Last Post: July 9th, 08:42 PM
  4. insert into temp table
    By Dhiren_Dave@ril.com in forum Informix
    Replies: 0
    Last Post: June 26th, 05:30 AM
  5. SQL - getting rowid into a temp table [368]
    By ART KAGEL, BLOOMBERG/ 65E 55TH in forum Informix
    Replies: 0
    Last Post: June 24th, 07:07 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