SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Table Variables


Table Variables

Author
Message
Lon-860191
Lon-860191
SSCommitted
SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)

Group: General Forum Members
Points: 1987 Visits: 278
Good question although the unlustered index thing got me as well.
Tom Thomson
Tom Thomson
One Orange Chip
One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)

Group: General Forum Members
Points: 26076 Visits: 12500
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
Hugo Kornelis
SSCoach
SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)

Group: General Forum Members
Points: 18885 Visits: 12426
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 MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Nils Gustav Stråbø
Nils Gustav Stråbø
Hall of Fame
Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)

Group: General Forum Members
Points: 3665 Visits: 3575
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
Tom Thomson
Tom Thomson
One Orange Chip
One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)

Group: General Forum Members
Points: 26076 Visits: 12500
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

Tom Thomson
Tom Thomson
One Orange Chip
One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)

Group: General Forum Members
Points: 26076 Visits: 12500
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
patrickmcginnis59
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1555 Visits: 2333
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!
Tom Thomson
Tom Thomson
One Orange Chip
One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)

Group: General Forum Members
Points: 26076 Visits: 12500
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
Iulian -207023
SSCrazy
SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)

Group: General Forum Members
Points: 2483 Visits: 1248
Good question. thank you

Iulian
Koen Verbeeck
Koen Verbeeck
SSC Guru
SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)

Group: General Forum Members
Points: 62960 Visits: 13298
What's an unclustered index? On BOL I only see clustered and nonclustered :-P

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


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

MCSE Business Intelligence - Microsoft Data Platform MVP
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