December 18, 2006 at 3:08 am
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
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
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
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
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