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 12»»

What is this? Expand / Collapse
Author
Message
Posted Thursday, February 11, 2010 10:27 PM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: 2 days ago @ 5:40 AM
Points: 698, Visits: 157
Comments posted to this topic are about the item What is this?
Post #864453
Posted Thursday, February 11, 2010 11:15 PM


SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Thursday, July 3, 2014 9:19 PM
Points: 605, Visits: 1,691
Very nifty datatype to know if you ever need to store an object name.


S.
Post #864457
Posted Friday, February 12, 2010 6:38 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, August 13, 2014 11:45 AM
Points: 1,093, Visits: 2,616
Fal (2/11/2010)
Very nifty datatype to know if you ever need to store an object name.


S.


Indeed, but what I don't get is that about 33% of respondents think it is a function or variable! I mean, this is something as basic to know as a varchar or int.....




_______________________________________________________________________
For better assistance in answering your questions, click here
Post #864583
Posted Friday, February 12, 2010 7:37 AM


SSC-Addicted

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

Group: General Forum Members
Last Login: Wednesday, July 24, 2013 2:32 PM
Points: 454, Visits: 172
If not for the fact that for the last week or so I've been messing with sp_Tables_ex and sp_columns_ex on Linked Oracle servers, I wouldn't have known about sysname either. For those like me still learning, i wouldn't say it was an obvious choice.

The distance between genius and insanity is measured only by success.
Post #864634
Posted Friday, February 12, 2010 8:08 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, August 13, 2014 11:45 AM
Points: 1,093, Visits: 2,616
Brnbngls (2/12/2010)
If not for the fact that for the last week or so I've been messing with sp_Tables_ex and sp_columns_ex on Linked Oracle servers, I wouldn't have known about sysname either. For those like me still learning, i wouldn't say it was an obvious choice.


didn't mean to come across like that. sysname is mentioned in many samples. Usually the proportion of correct answers vs wrong ones in the QotD is better (unless the question is flawed of course)...
The idea is to learn from these QotD, and everyone does;)
Cheers




_______________________________________________________________________
For better assistance in answering your questions, click here
Post #864663
Posted Friday, February 12, 2010 9:21 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 4:53 PM
Points: 21,617, Visits: 15,271
Thanks for taking the time to create a QOD.



Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #864714
Posted Friday, February 12, 2010 9:40 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Thursday, July 10, 2014 12:49 PM
Points: 1,676, Visits: 1,754
This datatype has an interesting twist to it. If you have a database with case-sensitive collation, such as, for example SQL_Latin1_General_CP1_CS_AS then the following line of code will produce error:

declare @v sysName;

This is because in databases with case-sensitive collations the sysname will only be recognized if it is spelled in lower case :)

declare @v sysname;

will be parsed just fine regardless of case sensitivity.

Oleg

Post #864737
Posted Friday, February 12, 2010 10:02 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Sunday, April 27, 2014 6:26 AM
Points: 1,521, Visits: 3,036
Richard M. (2/12/2010)
... Usually the proportion of correct answers vs wrong ones in the QotD is better (unless the question is flawed of course)...


or the answer cannot be determined by simply copying/pasting a script and running it in SSMS.

This QOD did bring my attention to a new (to me) data type, one not listed in the main list of data types on my SQL 2005 BOL. Yes, I got it right, but the answer "function" did tempt me as "sysname" is also documented as a .NET property of the DataType class, so I understand why some respondents would have chosen that answer.
Post #864758
Posted Tuesday, March 30, 2010 8:47 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 10:59 AM
Points: 11,194, Visits: 11,135
It seems odd to me that type names must be lower cased in a case-sensitive database.
Also the case with hiercarchyid in SQL 2008.
Am I the only one that prefers to upper case my types?




Paul White
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #892865
Posted Wednesday, May 26, 2010 6:27 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: Friday, May 9, 2014 12:47 AM
Points: 3,448, Visits: 4,407
Paul White NZ (3/30/2010)
It seems odd to me that type names must be lower cased in a case-sensitive database.

It's not true (or I don't understand what you mean). The names of system data types (except 'sysname') may be written in whatever case you want. The names of user data types (plus 'sysname') must be written in the same case as they are defined in the database.

DECLARE @1 Int, @2 int, @3 INT -- works fine in a case-sensitive DB
GO
CREATE TYPE MyAwesomeType FROM VARCHAR(30)
GO
DECLARE @1 MyAwesomeType -- works fine
GO
DECLARE @1 myAwesomeType -- fails in a case-sensitive DB
GO
DROP TYPE MyAwesomeType

SQL Server considers 'sysname' as a user data type and a system data type at the same time. Here is a script which confirms it:

SELECT name, system_type_id, user_type_id, is_user_defined
FROM sys.types
WHERE name = 'sysname'

-- name system_type_id user_type_id is_user_defined
-- ---------- -------------- ------------ ---------------
-- sysname 231 256 0

And here is a quote from BOL (http://msdn.microsoft.com/en-us/library/ms188021.aspx):
For system data types, user_type_id = system_type_id
...
is_user_defined
1 = User-defined type.
0 = SQL Server system data type.


Sysname has system_type_id = 231, which means 'nvarchar'. Looks like sysname is created by the statement 'CREATE TYPE sysname FROM nvarchar(128)'.

Paul White NZ (3/30/2010)
Am I the only one that prefers to upper case my types?

It depends on what you call 'my types' For 'standard data types in my code', I prefer upper case (INT, VARCHAR, SYSNAME etc.). For 'data types defined by me' (which is equivalent to 'CLR data types' in my case), I prefer Pascal case (ParameterSet, VarArray etc.).
Post #928169
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse