Professional Web Applications Themes

Table data type - thoughts? - Microsoft SQL / MS SQL Server

I have read a statement that recommends to use the new 'table' data type instead of temporary tables, as it can improve performance. What are your experiences with this? The doentation says it cannot be used for two cases, INSERT INTO's and SELECT INTO's. Any good resources on the web? The SQL doentation doesn't seem to be very thorough. - Dave...

  1. #1

    Default Table data type - thoughts?

    I have read a statement that recommends to use the new 'table' data type
    instead of temporary tables, as it can improve performance.

    What are your experiences with this? The doentation says it cannot be
    used for two cases, INSERT INTO's and SELECT INTO's. Any good resources on
    the web? The SQL doentation doesn't seem to be very thorough.

    - Dave


    Dave Slinn Guest

  2. #2

    Default Re: Table data type - thoughts?

    Dave,

    Table variables cannot replace temp tables in all scenarios.If that was the
    case, there would have been no support for the latter in SQL2000.What to use
    and when depends on the need.We have seen table variables doing good when
    theres lot of DML actions.May be because of less locks and recompiles.For
    inserting a number of rows initially , temp tables have proved good.I guess,
    the delay for table variables is in the switchover between memory and the
    spool table.So the bottomline is, test it against your code and make the
    switch where it proves wise.Heres a good faq on table variables:

    INF: Frequently Asked Questions - SQL Server 2000 - Table Variables
    [url]http://support.microsoft.com?id=305977[/url]

    --
    Dinesh.
    SQL Server FAQ at
    [url]http://www.tkdinesh.com[/url]

    "Dave Slinn" <dslinnaccesscomm.ca> wrote in message
    news:uDAO5hMRDHA.2852tk2msftngp13.phx.gbl...
    > I have read a statement that recommends to use the new 'table' data type
    > instead of temporary tables, as it can improve performance.
    >
    > What are your experiences with this? The doentation says it cannot be
    > used for two cases, INSERT INTO's and SELECT INTO's. Any good resources
    on
    > the web? The SQL doentation doesn't seem to be very thorough.
    >
    > - Dave
    >
    >

    Dinesh.T.K Guest

  3. #3

    Default Re: Table data type - thoughts?

    tb does not participate in a local transaction (i.e. it persists within the
    context regardless of the state of the transaction). It's true that you
    cannot do select/into tb. However, insert into tb is completely valid.

    Here is some discussion on tb. [url]http://tinyurl.com/g9e3[/url]


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


    "Dave Slinn" <dslinnaccesscomm.ca> wrote in message
    news:uDAO5hMRDHA.2852tk2msftngp13.phx.gbl...
    > I have read a statement that recommends to use the new 'table' data type
    > instead of temporary tables, as it can improve performance.
    >
    > What are your experiences with this? The doentation says it cannot be
    > used for two cases, INSERT INTO's and SELECT INTO's. Any good resources
    on
    > the web? The SQL doentation doesn't seem to be very thorough.
    >
    > - Dave
    >
    >

    oj Guest

  4. #4

    Default Re: Table data type - thoughts?

    [posted and mailed, please reply in public]

    Dave Slinn (dslinnaccesscomm.ca) writes:
    > I have read a statement that recommends to use the new 'table' data type
    > instead of temporary tables, as it can improve performance.
    >
    > What are your experiences with this? The doentation says it cannot
    > be used for two cases, INSERT INTO's and SELECT INTO's. Any good
    > resources on the web? The SQL doentation doesn't seem to be very
    > thorough.
    This article gives some information:
    [url]http://support.microsoft.com/default.aspx?scid=KB;EN-US;q305977[/url].

    There is one important item missig from this article: when you INSERT
    into a table variable, you can never get parallelisation of the SELECT
    statement. This you can get with a temp table. You want parallelisation
    if you have more than one processor on the machine, and a parallel
    plan is the best plan for the query.

    --
    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. Filter table by column data type
    By Brandon Taylor in forum Dreamweaver AppDev
    Replies: 3
    Last Post: August 9th, 05:24 AM
  2. Converting from data type varchar to data type money
    By schaudry in forum Coldfusion - Advanced Techniques
    Replies: 6
    Last Post: June 10th, 11:01 PM
  3. Converting data type varchar to data type money
    By schaudry in forum Coldfusion - Getting Started
    Replies: 1
    Last Post: June 10th, 07:22 PM
  4. Replies: 2
    Last Post: August 12th, 07:55 AM
  5. insert memo type data into a table
    By erdo in forum Microsoft Access
    Replies: 1
    Last Post: July 22nd, 02:24 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