Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Table Variable :Doesn't care


Table Variable :Doesn't care

Author
Message
rohitvermasrt
rohitvermasrt
Grasshopper
Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)

Group: General Forum Members
Points: 12 Visits: 2
Hey I m here for first time. And really its a nice question. And i also have given this answer by copying it and executing it in the query analyzer.
Hugo Kornelis
Hugo Kornelis
SSCrazy Eights
SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)

Group: General Forum Members
Points: 8337 Visits: 11580
UMG Developer (7/6/2010)
Nice question, thanks!

I'm a little surprised that so many people have gotten it correct so far, but that may be because of the copy, paste, F5 syndrome. ;-)

I doubt it. Most people consider that cheating, and try to work the answer out for themselves.

And I think that it is quite well-known that table variables (like scalar variables) are not affected by rollbacks.


Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Koen Verbeeck
Koen Verbeeck
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16501 Visits: 13207
Hugo Kornelis (7/7/2010)
UMG Developer (7/6/2010)
Nice question, thanks!

I'm a little surprised that so many people have gotten it correct so far, but that may be because of the copy, paste, F5 syndrome. ;-)

I doubt it. Most people consider that cheating, and try to work the answer out for themselves.


Quite ironic that you post this comment right under someone who says he executed the query in Query Analyzer. :-D



How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at SQLKover.

MCSA SQL Server 2012 - MCSE Business Intelligence
hrvoje.piasevoli
hrvoje.piasevoli
Old Hand
Old Hand (365 reputation)Old Hand (365 reputation)Old Hand (365 reputation)Old Hand (365 reputation)Old Hand (365 reputation)Old Hand (365 reputation)Old Hand (365 reputation)Old Hand (365 reputation)

Group: General Forum Members
Points: 365 Visits: 510
BOL: Transactions involving table variables last only for the duration of an update on the table variable. Therefore...

Hi all!
Maybe the following code example brings some light on the above statement. Note the second insert in the try block.
Try guessing the outcome...

SET NOCOUNT ON
GO

DECLARE @t TABLE (id_char VARCHAR(20))
CREATE TABLE #t (id INT)

BEGIN TRY
BEGIN TRAN
-- insert 1 into temp table and output to table var
INSERT #t
OUTPUT INSERTED.*
INTO @t
SELECT 1

-- Now insert 'A' into table var and output to temp table
-- NOTE: 'A' can not be inserted into #t !
INSERT INTO @t
OUTPUT INSERTED.*
INTO #t
SELECT 'A'
COMMIT TRAN
END TRY
BEGIN CATCH
IF XACT_STATE() >0 BEGIN
PRINT 'COMMIT transaction'
PRINT ERROR_MESSAGE()
COMMIT TRAN
END
ELSE BEGIN
IF XACT_STATE() < 0 BEGIN
PRINT 'ROLLBACK transaction'
ROLLBACK
END
PRINT ERROR_MESSAGE()
END
END CATCH

-- display unioned results
SELECT '@t' AS 'table_name', COUNT(*) AS 'row_count' FROM @t
UNION ALL
SELECT '#t' AS 'table_name', COUNT(*) AS 'row_count' FROM #t

-- clean up
DROP TABLE #t

[/quote][quote]


Regards,

Hrvoje Piasevoli

Hrvoje Piasevoli
hrvoje.piasevoli
hrvoje.piasevoli
Old Hand
Old Hand (365 reputation)Old Hand (365 reputation)Old Hand (365 reputation)Old Hand (365 reputation)Old Hand (365 reputation)Old Hand (365 reputation)Old Hand (365 reputation)Old Hand (365 reputation)

Group: General Forum Members
Points: 365 Visits: 510
Hugo Kornelis (7/7/2010)
I doubt it. Most people consider that cheating, and try to work the answer out for themselves.

I second that. Personally, giving the wrong answer highly motivates me to research, absorb information, learn and remember. And, after all, it's a safe environment for making errors:-P

Best regards,

Hrvoje Piasevoli

Hrvoje Piasevoli
thabang.mogano
thabang.mogano
Forum Newbie
Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)

Group: General Forum Members
Points: 2 Visits: 8
Nice one :-)
i did fall for the copy, paste, F5 syndrome.
but, it didn't point out something interesting.

so, would using the #table be better than using the table variable?
Bhuvnesh
Bhuvnesh
SSCrazy
SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)

Group: General Forum Members
Points: 2930 Visits: 4076
thabang.mogano (7/7/2010)
so, would using the #table be better than using the table variable?

Depends

Temp tables

Behave just like normal tables, but are created in the TempDB database. They persist until dropped, or until the connection that created them disappears. They are visible in the procedure that created them and any procedures that that proc calls.

Just like normal tables, they can have primary keys, constraints and indexes, and column statistics are kept for the table.

Temp tables, while they have space assigned to them in the tempDB database, will generally be accessed only from memory, unless the server is under memory pressure, or the amount of data in the table is large.

Table Variables

These tables behave very much like other variables in their scoping rules. They are created when they are declared and are dropped when they go out of scope. They cannot be explicitly dropped.

Like with temp tables, table variables reside in TempDB. they have entries in the system tables in tempDB, just like temp tables, and they follow the same behaviour regarding whether they are in memory or on disk.

Table variables can have a primary key, but indexes cannot be created on them, neither are statistics maintained on the columns. This makes table variables less optimal for large numbers of rows, as the optimiser has no way of knowing the number of rows in the table variable.

http://www.sql-server-performance.com/articles/per/temp_tables_vs_variables_p1.aspx

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
SanjayAttray
SanjayAttray
Hall of Fame
Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)

Group: General Forum Members
Points: 3953 Visits: 1619
tommyh (7/6/2010)
UMG Developer (7/6/2010)
Nice question, thanks!

I'm a little surprised that so many people have gotten it correct so far, but that may be because of the copy, paste, F5 syndrome. ;-)


Or copy, paste, alt+x syndrome (F5 is to far away on the keyboard... and actually clicking execute is WAY out of the question).

Good question though.


Thanks Tommy. I didn't know that I can use alt+x to execute. Always done with F5.

Sure alt+x is closer.

SQL DBA.
Cliff Jones
Cliff Jones
SSCarpal Tunnel
SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)

Group: General Forum Members
Points: 4069 Visits: 3648
Duncan Pryde (7/7/2010)
Of course, one place this behaviour comes in useful is if you want to store the values from an output clause when rolling back a transaction. Just store them in a table variable first so they don't get lost when the transaction is rolled back.

Have a look here at Martin Bell's explanation, which I found useful.

Duncan


I also find this usefull, thanks for sharing.
bitbucket-25253
bitbucket-25253
SSCertifiable
SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)

Group: General Forum Members
Points: 5695 Visits: 25280
hrvoje.piasevoli
Maybe the following code example brings some light on the above statement. Note the second insert in the try block.
Try guessing the outcome


Excellent bit of code ..... Hope you submit some QOD's they would be a real challenge and learning experience.

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read

Before posting a performance problem please read
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search