January 6, 2010 at 4:47 pm
I am a little confused.
If the UPDATE statement has a WHERE clause of CategoryID = 7 but no records have been previously loaded then it cannot write any data as the condition cannot be met.
There the answer must be 0, 0.
January 6, 2010 at 4:58 pm
robert_edis (1/6/2010)
I am a little confused.If the UPDATE statement has a WHERE clause of CategoryID = 7 but no records have been previously loaded then it cannot write any data as the condition cannot be met.
There the answer must be 0, 0.
Read it again carefully. The UPDATE statement references the pre-existing Categories table in the Northwind database.
January 7, 2010 at 1:45 am
Hi,
When I tried
DECLARE @Table TABLE
(
Matrix_User_ID INT --PRIMARY KEY IDENTITY(1,1)
,OldFirst_Name VARCHAR(20)
,NewFirst_Name VARCHAR(20)
)
BEGIN TRAN
UPDATE MSTR_User
SET First_Name=UPPER(First_Name)
OUTPUT inserted.Matrix_User_ID,deleted.First_name,inserted.First_name INTO @Table
WHERE Matrix_User_Id =1294
SELECT * FROM @Table
ROLLBACK TRAN
SELECT * FROM @Table
It returned row after ROLLBACK.
and if I use
--DECLARE @Table TABLE
CREATE TABLE #Table
(
Matrix_User_ID INT --PRIMARY KEY IDENTITY(1,1)
,OldFirst_Name VARCHAR(20)
,NewFirst_Name VARCHAR(20)
)
BEGIN TRAN
UPDATE MSTR_User
SET First_Name=UPPER(First_Name)
OUTPUT inserted.Matrix_User_ID,deleted.First_name,inserted.First_name INTO #Table
WHERE Matrix_User_Id =1294
SELECT * FROM #Table
ROLLBACK TRAN
SELECT * FROM #Table
It returned 1 row before ROLLBACK and after it returned no row.
It means Table variable retains the row while temp table does not.
Can anybody share the reason behind this?
Reply will be highly appreciated.
😀
-Jinesh.
January 7, 2010 at 4:30 am
jinesh.i (1/7/2010)
Hi,When I tried
DECLARE @Table TABLE
(
Matrix_User_ID INT --PRIMARY KEY IDENTITY(1,1)
,OldFirst_Name VARCHAR(20)
,NewFirst_Name VARCHAR(20)
)
BEGIN TRAN
UPDATE MSTR_User
SET First_Name=UPPER(First_Name)
OUTPUT inserted.Matrix_User_ID,deleted.First_name,inserted.First_name INTO @Table
WHERE Matrix_User_Id =1294
SELECT * FROM @Table
ROLLBACK TRAN
SELECT * FROM @Table
It returned row after ROLLBACK.
and if I use
--DECLARE @Table TABLE
CREATE TABLE #Table
(
Matrix_User_ID INT --PRIMARY KEY IDENTITY(1,1)
,OldFirst_Name VARCHAR(20)
,NewFirst_Name VARCHAR(20)
)
BEGIN TRAN
UPDATE MSTR_User
SET First_Name=UPPER(First_Name)
OUTPUT inserted.Matrix_User_ID,deleted.First_name,inserted.First_name INTO #Table
WHERE Matrix_User_Id =1294
SELECT * FROM #Table
ROLLBACK TRAN
SELECT * FROM #Table
It returned 1 row before ROLLBACK and after it returned no row.
It means Table variable retains the row while temp table does not.
Can anybody share the reason behind this?
Reply will be highly appreciated.
😀
-Jinesh.
There are few difference between a table variable and temporary table. One of the difference is that temporary table is affected by transactions (e.g. rollback effect it) but table variables are not affected by transaction.
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
January 7, 2010 at 6:15 am
Thanks a lot.:-)
January 7, 2010 at 8:14 am
robert_edis (1/6/2010)
I am a little confused.If the UPDATE statement has a WHERE clause of CategoryID = 7 but no records have been previously loaded then it cannot write any data as the condition cannot be met.
There the answer must be 0, 0.
Exactly...and WADR to Lynn et al., that is "out of the box" thinking ;-). I'm glad approximately half of us thought the QotD was great, but my point is that the other half have legitimate objections to it, and IMHO the editor should consider such objections as criteria for future QotDs. Thanks.
March 16, 2010 at 2:20 pm
I thought it was reasonable to assume that there would be at least one record in the table that would be returned with CategoryID = 7, so that left only one possible answer. Trick questions are lame and I never expect to see them in a professional forum. When you are not given enough info, it should be safe to assume many things that we are used to seeing.
But then again, I got it right, so why would I be unhappy 😀
Great question, made me read carefully!
Peter Trast
Microsoft Certified ...(insert many literal strings here)
Microsoft Design Architect with Alexander Open Systems
Viewing 7 posts - 31 through 36 (of 36 total)
You must be logged in to reply to this topic. Login to reply