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 «««12345»»

Table Variables Expand / Collapse
Author
Message
Posted Friday, October 19, 2012 12:01 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, April 10, 2014 10:30 AM
Points: 1,310, Visits: 246
Good question although the unlustered index thing got me as well.
Post #1374979
Posted Friday, October 19, 2012 12:44 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: 2 days ago @ 12:16 AM
Points: 8,289, Visits: 8,742
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
Post #1374999
Posted Friday, October 19, 2012 12:48 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 1:59 PM
Points: 5,797, Visits: 8,017
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
Post #1375001
Posted Friday, October 19, 2012 12:55 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 1:35 AM
Points: 1,832, Visits: 3,402
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
Post #1375007
Posted Friday, October 19, 2012 1:14 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: 2 days ago @ 12:16 AM
Points: 8,289, Visits: 8,742
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
Post #1375013
Posted Friday, October 19, 2012 1:30 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: 2 days ago @ 12:16 AM
Points: 8,289, Visits: 8,742
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
Post #1375019
Posted Friday, October 19, 2012 1:55 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Wednesday, January 16, 2013 4:23 PM
Points: 415, Visits: 2,333
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!
Post #1375031
Posted Friday, October 19, 2012 7:41 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: 2 days ago @ 12:16 AM
Points: 8,289, Visits: 8,742
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. All I meant top say was that there were different ways of interpreting it.
Sorry!


Tom
Post #1375073
Posted Sunday, October 21, 2012 1:32 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Wednesday, February 05, 2014 6:49 AM
Points: 861, Visits: 793
Good question. thank you

Iulian
Post #1375148
Posted Monday, October 22, 2012 12:52 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 11:06 AM
Points: 12,212, Visits: 9,192
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.




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 #1375269
« Prev Topic | Next Topic »

Add to briefcase «««12345»»

Permissions Expand / Collapse