Post reply

Function or View or variable Table, which is faster???

  • December 18, 2006 at 3:08 am

    #368575

    hi all

    I am new with SQL and I have a question to ask everybody ...

    -Function which return table

    -View

    -Variable table ( "declare @a table", it for clearly , no think more )

    Which one will go faster when use in select statement , join v...v.v.v.v

    Please , tell me why and when i should use them...?

    Thanks .

  • December 18, 2006 at 3:50 am

    #678593

    Hi,

    I tried to run your select, but it gives me error message:

    Incorrect syntax near the keyword 'join'.

    (What I mean to say is, what you posted does not specify much about the type of select, so that I have no idea what you need it for and what will be faster - or what can be used).

    View is basically a saved query definition that pulls data from tables, table variable is basically a table (you have to INSERT rows into it), table valued function takes parameters and uses table variable to return the result.

    I wouldn't know how to compare them, to be honest. Seems to me a bit like comparing whether grass is more green than the sky is blue

    Maybe other people will have different opinion though...

  • December 18, 2006 at 3:59 am

    #678595

    Maybe other people will have different opinion , but I'm not amongst them.

    Those are different things, used for different purposes.

    _____________
    Code for TallyGenerator

  • December 18, 2006 at 9:28 am

    #678706

    As far as I know, there is no significant difference using either function or view when using SELECT clause.

    Using variable table is faster in general because it uses RAM, assuming your server has enough RAM.

  • December 19, 2006 at 7:08 am

    #678905

    Avoid table variables for complex joins or large data sets. SQL Server does not compute statistics for them, hence, can not determine an adequate execution plan. SQL 2005 is even worse at it than SQL 2000.

    I had a complex query in SQL2000  using table variables that took 3 minutes to run. When I ported it to SQL2005 it took 14 minutes. MS Product Support suggested I replace the table variables with temp or real tables. Execution times went to 12 seconds and 8 seconds, respectively.

    cjb

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply