What is this?

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

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

    S.

  • 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]

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

  • 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]

  • 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

  • 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

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

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

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

  • 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 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 🙂

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

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