Quick Suggestion on Table Variable

Question

Post reply

Quick Suggestion on Table Variable

Ather M

SSChasing Mays

Points: 607
More actions
February 17, 2010 at 10:38 am

#219228

Can I use Table variable to store varchar(max) value ?
That table variable will contain ID int and a field with varchar(max) and later on I will use this with join of many tables which return approx 1 lakh of records.
Please let me know if you need further information on this.

Viewing 7 posts - 1 through 6 (of 6 total)

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

steveb. SSC-Forever Points: 46733 More actions · Answer 1

you 'could' howver you cannot created indexes on table variables so this could cause problems with 100,000 rows.

A temp table would be better or perhaps create a perm. staging/lookup table.

What are you trying to acheive.?

SQLRNNR SSC Guru Points: 281334 More actions · Answer 2

I think I would likely go with the temp table. But I would test both first. It would depend on how you join the tables - assuming it would be joined on ID. In that case you could create an index by means of creating a primary key on the table variable. Thus you might possibly be fine with either a temp table or table variable. You should test both methods and determine which will provide the best results.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Paul White SSC Guru Points: 150467 More actions · Answer 3

If the VARCHAR(MAX) column is intended to store large values (and I suppose the chances of that are quite good), a temporary table has one other advantage: you can use the 'large value types out of row' option of sp_tableoption to choose whether to store LOB data in-row or not.

Offhand, I don't recall whether table variables store LOB data in-row or not by default. Books Online is also a bit vague on that.

If anyone is wondering if I am too lazy to test it and find out - it turns out I am 😉

Paul

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Ather M SSChasing Mays Points: 607 More actions · Answer 4

Thanks a lot for all your suggestions...

I will definetly compare the performance....but before starting I need some expert comments thts why I posted it 🙂

I am really looking for some more negative commments...like memory issue,performance issue etc

and if anybody has some alternatives like all of u said temp table but if I use temp table then there is compilation issue i guess...please suggest ?

Toby White SSCertifiable Points: 6187 More actions · Answer 5

I am willing to bet that the procedure will compile in under one second in all cases. Given that you are creating a temp table, stuffing it, and then joining it I am also willing to bet that the compilation time will be insignifcant compared to the execution time of the procedure. That having been said, a third and fourth option is to use a CTE or a derived table.

If the source table(s) already have the indexes like you need them then a derived table might be a performance gain. However, if you truely need to stuff the values and index them then I would recommend a permanant table. Just truncate it at the begining of the stored procedure. You can then experiment with taking the index off, and then placing it back on after stuffing, or just leaving it on.

Regards,

Toby

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 6

Ather M (2/18/2010)
Thanks a lot for all your suggestions...
I will definetly compare the performance....but before starting I need some expert comments thts why I posted it 🙂
I am really looking for some more negative commments...like memory issue,performance issue etc
and if anybody has some alternatives like all of u said temp table but if I use temp table then there is compilation issue i guess...please suggest ?

Yes... I have an alternative suggestion. Don't believe everything you get off the internet even on a great site like this. If you're going to test it anyway, then test for the questions you just asked. If you're not going to test it, shame, shame... even experts can be opinionated and wrong.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)