Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««1234»»»

Table Variable :Doesn't care Expand / Collapse
Author
Message
Posted Wednesday, July 7, 2010 1:38 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, July 8, 2010 12:32 AM
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.
Post #948361
Posted Wednesday, July 7, 2010 2:07 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:23 AM
Points: 5,925, Visits: 8,173
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
Post #948376
Posted Wednesday, July 7, 2010 2:41 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 10:06 AM
Points: 13,279, Visits: 10,153
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.




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 LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #948385
Posted Wednesday, July 7, 2010 3:51 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Saturday, October 19, 2013 12:40 PM
Points: 361, Visits: 508
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
Post #948403
Posted Wednesday, July 7, 2010 4:16 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Saturday, October 19, 2013 12:40 PM
Points: 361, Visits: 508
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

Best regards,

Hrvoje Piasevoli



Hrvoje Piasevoli
Post #948414
Posted Wednesday, July 7, 2010 4:57 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, September 29, 2010 5:38 AM
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?
Post #948432
Posted Wednesday, July 7, 2010 6:03 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, July 14, 2014 4:48 AM
Points: 2,834, Visits: 3,950
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
Post #948466
Posted Wednesday, July 7, 2010 7:49 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Thursday, July 17, 2014 10:56 AM
Points: 3,924, Visits: 1,607
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.
Post #948542
Posted Wednesday, July 7, 2010 8:09 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Thursday, July 24, 2014 7:30 AM
Points: 3,871, Visits: 3,621
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.
Post #948558
Posted Wednesday, July 7, 2010 8:23 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 11:01 AM
Points: 5,571, Visits: 24,776
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
Post #948575
« Prev Topic | Next Topic »

Add to briefcase ««1234»»»

Permissions Expand / Collapse