Table Variables

Question

Table Variables

Viewing 15 posts - 31 through 44 (of 44 total)

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

Lon-860191 SSCrazy Points: 2449 More actions · Answer 1

Good question although the unlustered index thing got me as well.

TomThomson SSC Guru Points: 104773 More actions · Answer 2

arthur.teter (10/19/2012)
While I know you are not supposed to be able to add multiple GUIDs to any table, on the systems I have worked on I have found that with table variables it is possible. When I run the following I do not get any errors
declare @TableVar table(MyID INT IDENTITY PRIMARY KEY CLUSTERED
, NEXTFIELD varchar(10), nextfield2 varchar(10)
, ROWGUID UNIQUEIDENTIFIER
, ROWGUID2 UNIQUEIDENTIFIER
)
insert into @TableVar (NEXTFIELD, nextfield2, ROWGUID, ROWGUID2)
values ('Test1', 'TEST1', NEWID(), NEWID())
, ('Test2', 'TEST2', NEWID(), NEWID())
, ('TEST3', 'HOW', NEWID(), NEWID())
select * from @TableVar

Actually, there's a misprint in the question (my stupid error) - it should say @ROWGUIDCOL, not ROWGUID. You can have as many rowguid columns as you like in any kind of table (just as you can have as many int columns as you like in any kind of table - after all, rowguid is a type like any other type), but only one of them can have the ROWGUIDCOL property (just as only one column can have the IDENTITY property). I'll ask Steve to correct that wording (and 2011) if possible.

Tom

Hugo Kornelis SSC Guru Points: 64780 More actions · Answer 3

L' Eomot Inversé (10/19/2012)
Actually, there's a misprint in the question (my stupid error) - it should say @ROWGUIDCOL, not ROWGUID. You can have as many rowguid columns as you like in any kind of table (just as you can have as many int columns as you like in any kind of table - after all, rowguid is a type like any other type), but only one of them can have the ROWGUIDCOL property (just as only one column can have the IDENTITY property). I'll ask Steve to correct that wording (and 2011) if possible.

I disagree with this. Yes, the question was technically not completely accurate. But your explanation here is worse. The term "ROWGUID" does not exist in SQL Server. It is not a type like any other type. uniqueidentifier is a type like any other type, and the commonly accepted short form for that data type name is guid. Not ROWGUID.

To me, it was obvious that "ROWGUID columns" was intended as "columns with the ROWGUIDCOL attribute. Especially since "ROWGUIDCOL columns" would have sounded redundant.

Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
Visit my SQL Server blog: https://sqlserverfast.com/blog/
SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

Nils Gustav Stråbø SSChampion Points: 11259 More actions · Answer 4

arthur.teter (10/19/2012)
While I know you are not supposed to be able to add multiple GUIDs to any table, on the systems I have worked on I have found that with table variables it is possible. When I run the following I do not get any errors
declare @TableVar table(MyID INT IDENTITY PRIMARY KEY CLUSTERED
, NEXTFIELD varchar(10), nextfield2 varchar(10)
, ROWGUID UNIQUEIDENTIFIER
, ROWGUID2 UNIQUEIDENTIFIER
)
insert into @TableVar (NEXTFIELD, nextfield2, ROWGUID, ROWGUID2)
values ('Test1', 'TEST1', NEWID(), NEWID())
, ('Test2', 'TEST2', NEWID(), NEWID())
, ('TEST3', 'HOW', NEWID(), NEWID())
select * from @TableVar

I'm pretty sure that the ROWGUID options are not about the UNIQUEIDENTIFIER data type, but about the ROWGUIDCOL property for a column, and only one column per table can have this property, just like only one column can have the IDENTITY property.

Creating and Modifying Identifier Columns

TomThomson SSC Guru Points: 104773 More actions · Answer 5

patrickmcginnis59 (10/19/2012)
L' Eomot Inversé (10/19/2012)
Maybe some programmers, but other programmers consider the statement
that SQL Server doesn't correctly manage the size of tempdb to be either incorrect or at least uncertain, so we don't
consider it to be a bug.
Its a new heads up to me, I'm still digesting it and could be misinterpretting the whole situation. Here was the first heads up for me, posted by Sean Lange:
http://www.sqlservercentral.com/Forums/FindPost1369994.aspx
which refers to
http://support.microsoft.com/kb/307487
which coming direct from Microsoft does give some legitimacy to the issue. I may have spoken too soon about it "not being considered a bug" though as books on line (on my install) gives:
"The database being shrunk does not have to be in single user mode; other users can be working in the database when it is shrunk. This includes system databases."
so given the conflicting info, maybe the situation is not quite "not a bug", but I certainly am at least now aware of the issue.

Ah. I think I misunderstood you.

If you had said "shrink" (implying either shrinkdatabase or shrinkfile) I would have agreed that was bad (and I do agree MS have a bug there). In my opinion they do have a bug there - but they have clearly admitted that doing this can cause corruption of tempdb, so I don't think they are denying it. Whether shrink (as opposed to alter database modify) should be considered a legitimate method for the user to reduce the size of tempdb without ensuring a quiesced system by going into single user mode is neither here nor there (lets be clear: I don't consider it a legitimate method at all): until the documentation says you can't do that and the user interface prevents you from doing it, either they fix it so that it wroks reliably or they have a bug.

When you referred to SQL Server not managing the size of tempdb correctly, I assumed you were complaining about SQL Server not managing the size correctly - but actually you are referring to a bug that happens when the user (not SQL Server) attempts to manage the size of tempdb instead of letting SQL Server carry out its own management, guided by required start sizes, growth rates, and max sizes specified by the user. So you weren't assertuing a bug in the part that I thought you were.

Hence the misunderstanding.

Tom

TomThomson SSC Guru Points: 104773 More actions · Answer 6

Hugo Kornelis (10/19/2012)
L' Eomot Inversé (10/19/2012)
Actually, there's a misprint in the question (my stupid error) - it should say @ROWGUIDCOL, not ROWGUID. You can have as many rowguid columns as you like in any kind of table (just as you can have as many int columns as you like in any kind of table - after all, rowguid is a type like any other type), but only one of them can have the ROWGUIDCOL property (just as only one column can have the IDENTITY property). I'll ask Steve to correct that wording (and 2011) if possible.
I disagree with this. Yes, the question was technically not completely accurate. But your explanation here is worse. The term "ROWGUID" does not exist in SQL Server. It is not a type like any other type. uniqueidentifier is a type like any other type, and the commonly accepted short form for that data type name is guid. Not ROWGUID.
To me, it was obvious that "ROWGUID columns" was intended as "columns with the ROWGUIDCOL attribute. Especially since "ROWGUIDCOL columns" would have sounded redundant.

Well, since ROWGUID (as opposed to GUID or ROWGUIDCOL) is not a legitimate term, it's possible for people to mistake it as meaning GUID (ie uniqueidentifier) type rather than the column property ROWGUIDCOL, and it seems clear that this is what Arthur.Teter did; so I wanted to eliminate the ambiguity, to make sure that more people don't fall into that trap. I agree about the sound of redundancy, though.

I think I picked up using ROWGUID instead of ROWGUIDCOL from the appalling habit displayed in certain third party databases I had which I had the misfortune to look into where the vendors had the awful habit of using uniqueidentifier surrogate keys and calling every surrogate just rowguid in the table where it was primary key (instead of something useful like tablename_surrogate_key); I think their developers must have been used to working in an old dialect of Fortran where the maximum allowed identifier length was 8 characters and believed the same length restriction applied in SQL.

Tom

patrickmcginnis59 SSCertifiable Points: 6483 More actions · Answer 7

L' Eomot Inversé (10/19/2012)
When you referred to SQL Server not managing the size of tempdb correctly, I assumed you were complaining about SQL Server not managing the size correctly.

True! I really didn't give the proper terminology, sorry to divert the thread!

TomThomson SSC Guru Points: 104773 More actions · Answer 8

patrickmcginnis59 (10/19/2012)
L' Eomot Inversé (10/19/2012)
When you referred to SQL Server not managing the size of tempdb correctly, I assumed you were complaining about SQL Server not managing the size correctly.
True! I really didn't give the proper terminology, sorry to divert the thread!

Oh M.G!

I didn't mean to say quite that - that was very rude of me. :blush: All I meant top say was that there were different ways of interpreting it.

Sorry!

Tom

Iulian -207023 SSCertifiable Points: 7639 More actions · Answer 9

Iulian -207023

SSCertifiable

Points: 7639

October 21, 2012 at 1:32 am

#1551138

Good question. thank you

Iulian

Koen Verbeeck SSC Guru Points: 259085 More actions · Answer 10

What's an unclustered index? On BOL I only see clustered and nonclustered 😛

Great question, but a wee bit too much for in one single question actually.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

kalyani.k478 Default port Points: 1400 More actions · Answer 11

kalyani.k478

Default port

Points: 1400

October 22, 2012 at 1:07 am

#1551248

Intresting question...got it wrong though...

SQLRNNR SSC Guru Points: 281334 More actions · Answer 12

Suds. one stinking mis-click though I intended to click permit identity I slipped and hit the wrong check box.

Grrrrr...:-)

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