Interpretation of BoL - table variables

Question

Interpretation of BoL - table variables

Wayne Lawton

Hall of Fame

Points: 3532
More actions
January 24, 2005 at 1:09 pm

#183948

G'Day Everyone,
I am looking for confirmation, and ideally a place in the documentation, that specifically states table variables can or cannot be used as a parameter passed into a stored procedure. From BoL: "All data types, including text, ntext and image, can be used as a parameter for a stored procedure." This seems to imply that a table variabel can be used. However, in several attempts I have not been able to find a syntax that works.
'nuf said... Your thoughts?
Wayne

Viewing 15 posts - 1 through 15 (of 19 total)

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

PW-201837 SSC-Insane Points: 20805 More actions · Answer 1

It is not possible to pass a table variable to a SP or UDF. It appears to be a case of the BOL documentation not being kept up to date when Table variables were added in SQL2K.

It's asked often in the microsoft.public.sqlserver.* newsgroups:

http://www.google.ca/groups?num=100&hl=en&lr=&q=table+variable+parameter+group%3Amicrosoft.public.sqlserver.*

David.Poole SSC Guru Points: 76039 More actions · Answer 2

I don't have BOL to hand as a write this but I am pretty sure that I read that table variables are not allowed in the latest version of BOL.

I think the last BOL update was in Jan 2004.

Subjects that have been updated are clearly marked.

LinkedIn Profile

Wayne Lawton Hall of Fame Points: 3532 More actions · Answer 3

Thanks PW,

The link to the google search was the confirmation I was looking for. Not what I hoped to hear, but the results clearly put the question firmly to rest.

Thanks for the fast response

Wayne

Frank Kalis SSC Guru Points: 111183 More actions · Answer 4

David, wtf are you doing online here at almost a quarter past ten in the evenings???

I thought I am the only European who is that crazy after all

Anyway, I have a good reason: This evening I got nothing better to do with my time. You know why

To add to the thread: While it is true, that Microsoft greatly introduced table variables I think they now take a different direction. Temp tables are at least equally good as table variables. I use temp tables all around the place.

...and BOL isn't that specific as it should be in *many* places.

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

Antares686 SSC Guru Points: 125444 More actions · Answer 5

FYI this is from the last BOL Update

CREATE PROCEDURE

"All data types, except the table data type, can be used as a parameter for a stored procedure."

Dave-3000 SSCertifiable Points: 6186 More actions · Answer 6

OK, where do you get the latest BOL update?

There is no "i" in team, but idiot has two.

AlwaysLearning SSChasing Mays Points: 627 More actions · Answer 7

This link should get you there.

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

David.Poole SSC Guru Points: 76039 More actions · Answer 8

Ah, Frank. When you have three kids and your wife works nights the only chance you get to surf the internet is when the kids go to bed. It is the 3:00am postings that should worry you

Satellite TV seems to consist of charging you for watching programs you saw for free on BBC 10 years ago plus rubbish that wouldn't have been good enough to be included in schedules when Britain only had three channels.

LinkedIn Profile

rhunt SSCertifiable Points: 5868 More actions · Answer 9

Frank, would you mind elaborating on your commment about temp tables? I was under the impression that table variables typically incurred less server cost during use.

Or, are they typically just prefered because they are dropped at the batch level instead of the connection level?

Thanks

Ryan

David.Poole SSC Guru Points: 76039 More actions · Answer 10

Table variables are held in memory and therefore are very fast. Implicit in this is that they are fairly small tables, otherwise the memory penalty would be detrimental to server performance.

Temporary tables are physically stored in TEMPDB and can handle far bigger tables. You can also add indices and primary keys to them.

There are certain commands you cannot use on a table variable that you can on temporary tables. I think TRUNCATE is one of them.

I did do some tests on a recursive routine that had to trace a parent/child relationship between objects and came to the conclussion that in absolute terms there wasn't a fantastic difference in performance in my particular implementation.

I think one was faster for inserting data and the other was faster for joining data and the strengths and weaknesses of both cancelled each other out for the recursive routine.

LinkedIn Profile

PBirch SSCrazy Eights Points: 8493 More actions · Answer 11

David -

American cable is the same dreck - shows that were cancelled years ago rise from the dustbin to "entertain" you again. Who wants to watch 150 channels showing recurring episodes of "Wings" or "Golden Girls"?

I like the flexibility of table variables, although you cannot use EXEC stored proc to populate them. Oh well.

Quand on parle du loup, on en voit la queue

PW-201837 SSC-Insane Points: 20805 More actions · Answer 12

The following MS KB article has a useful comparison of table variables versus temp tables:

http://support.microsoft.com/default.aspx?scid=kb;en-us;305977

David.Poole SSC Guru Points: 76039 More actions · Answer 13

Do you get the programs that have 15 minutes of content but are 1 hour long?

I saw one on engineering disasters with a 125mph train crash in Germany and it used virtually every possible combination of phrases for telling us that the train was travelling at 125mph and that it crashed.

I'm at the stage where I find the adverts are more entertaining, have better background music and are less repetitive. Unfortunately (or fortunately depending on your view point) European adverts have a high nudity content so a lot of them would be banned in the USA.

LinkedIn Profile

Antares686 SSC Guru Points: 125444 More actions · Answer 14

Glad someone posted that Microsoft link as I couldn't find to save me. The statement about TempDB is incorrect with Tbale Variables see the following.

Q4: Are table variables memory-only structures that are assured better performance as compared to temporary or permanent tables, because they are maintained in a database that resides on the physical disk?

A4: A table variable is not a memory-only structure. Because a table variable might hold more data than can fit in memory, it has to have a place on disk to store data. Table variables are created in the tempdb database similar to temporary tables. If memory is available, both table variables and temporary tables are created and processed while in memory (data cache).