What is this?

Question

Post reply

What is this?

amit_adarsh

Ten Centuries

Points: 1273
More actions
February 11, 2010 at 10:27 pm

#215568

Comments posted to this topic are about the item What is this?

Viewing 14 posts - 1 through 13 (of 13 total)

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

Fal Hall of Fame Points: 3143 More actions · Answer 1

Very nifty datatype to know if you ever need to store an object name.

S.

Richard M. SSCertifiable Points: 7401 More actions · Answer 2

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[/url]

Brnbngls SSC Eights! Points: 823 More actions · Answer 3

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.

Richard M. SSCertifiable Points: 7401 More actions · Answer 4

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[/url]

SQLRNNR SSC Guru Points: 281334 More actions · Answer 5

Thanks for taking the time to create a QOD.

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

Oleg Netchaev SSCertifiable Points: 5276 More actions · Answer 6

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-2 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-2 sysname;

will be parsed just fine regardless of case sensitivity.

Oleg

john.arnott SSChampion Points: 11882 More actions · Answer 7

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.

Paul White SSC Guru Points: 150467 More actions · Answer 8

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
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

vk-kirov SSCertifiable Points: 7686 More actions · Answer 9

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.).

Paul White SSC Guru Points: 150467 More actions · Answer 10

vk-kirov (5/26/2010)
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).

I was referring to Oleg's post. Perhaps the reason it struck me as odd is because it isn't true, as you point out 🙂

My point about hierarchyid is valid though:

DECLARE @a HIERARCHYID;

SET @a = hierarchyid::GetRoot();

SET @a = HIERARCHYID::GetRoot();

The second SET statement will not compile in a case-sensitive database. (I don't mind too much, since it is a method call but still...)

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.

That's odd too. For me, DECLARE @b-2 sYsNaMe; works just fine in a case-sensitive collation (SQL Server 10.0.2775 Dev x86).

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.).

I was referring to T-SQL types and aliases, yes. I use camelCase or PascalCase in C# depending on scope.

Paul

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

vk-kirov SSCertifiable Points: 7686 More actions · Answer 11

Paul White NZ (5/26/2010)
For me, DECLARE @b-2 sYsNaMe; works just fine in a case-sensitive collation (SQL Server 10.0.2775 Dev x86).

Hmmm... Microsoft changed it in SQL Server 2008. In SQL 2005, 'DECLARE @b-2 sYsNaMe' produces an error. Well, SQL 2008 became more consistent here.

Paul White NZ (5/26/2010)
vk-kirov (5/26/2010)
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).
I was referring to Oleg's post. Perhaps the reason it struck me as odd is because it isn't true, as you point out 🙂

Oleg said about sysname only, and it was true for SQL 2005 🙂

Paul White NZ (5/26/2010)
My point about hierarchyid is valid though

The same is true for geometry and geography:

DECLARE @g1 GEOMETRY = geometry::Parse('MULTILINESTRING((0 1, 0 0, 1 0, 0 1), (1 1, 1 0))');

DECLARE @G2 GEOMETRY = GEOMETRY::Parse('MULTILINESTRING((0 1, 0 0, 1 0, 0 1), (1 1, 1 0))');

DECLARE @g3 GEOGRAPHY = geography::STGeomFromText('LINESTRING(47 -12, 47 -22)', 4326)

DECLARE @g4 GEOGRAPHY = GEOGRAPHY::STGeomFromText('LINESTRING(47 -12, 47 -22)', 4326)

Paul White NZ (5/26/2010)
I was referring to T-SQL types and aliases, yes. I use camelCase or PascalCase in C# depending on scope.

Sorry for the confusion, I was saying about 'CLR data types used in T-SQL code' (my specific case) 🙂

I hope all C# developers use camel case and pascal case as it is a Microsoft standard.

Paul White SSC Guru Points: 150467 More actions · Answer 12

vk-kirov (5/26/2010)
Hmmm... Microsoft changed it in SQL Server 2008. In SQL 2005, 'DECLARE @b-2 sYsNaMe' produces an error. Well, SQL 2008 became more consistent here.

I didn't know that - I didn't bother testing it on my 2005 installation.

Would make a tricky QotD.

The same is true for geometry and geography

On reflection, I think I prefer lower case for these method calls. What do you think?

I hope all C# developers use camel case and pascal case as it is a Microsoft standard.

Yikes! I didn't mean to suggest that I'm a C# developer - not by a long chalk!

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

vk-kirov SSCertifiable Points: 7686 More actions · Answer 13

Paul White NZ (5/26/2010)
The same is true for geometry and geography
On reflection, I think I prefer lower case for these method calls. What do you think?

I like to upper-case all blue keywords in SSMS. Geometry and geography are 'blue', so if I should use them in a case sensitive database, it will be an unsolvable problem to me 🙂

Paul White NZ (5/26/2010)
I hope all C# developers use camel case and pascal case as it is a Microsoft standard.
Yikes! I didn't mean to suggest that I'm a C# developer - not by a long chalk!

:laugh:

It's always useful to know how those C# developers use our database stuff 🙂