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


What is this?


What is this?

Author
Message
amit_adarsh
amit_adarsh
Right there with Babe
Right there with Babe (732 reputation)Right there with Babe (732 reputation)Right there with Babe (732 reputation)Right there with Babe (732 reputation)Right there with Babe (732 reputation)Right there with Babe (732 reputation)Right there with Babe (732 reputation)Right there with Babe (732 reputation)

Group: General Forum Members
Points: 732 Visits: 169
Comments posted to this topic are about the item What is this?
Fal
Fal
Mr or Mrs. 500
Mr or Mrs. 500 (560 reputation)Mr or Mrs. 500 (560 reputation)Mr or Mrs. 500 (560 reputation)Mr or Mrs. 500 (560 reputation)Mr or Mrs. 500 (560 reputation)Mr or Mrs. 500 (560 reputation)Mr or Mrs. 500 (560 reputation)Mr or Mrs. 500 (560 reputation)

Group: General Forum Members
Points: 560 Visits: 1803
Very nifty datatype to know if you ever need to store an object name.


S.
Richard M.
Richard M.
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1392 Visits: 2680
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
Brnbngls
Brnbngls
SSC-Addicted
SSC-Addicted (478 reputation)SSC-Addicted (478 reputation)SSC-Addicted (478 reputation)SSC-Addicted (478 reputation)SSC-Addicted (478 reputation)SSC-Addicted (478 reputation)SSC-Addicted (478 reputation)SSC-Addicted (478 reputation)

Group: General Forum Members
Points: 478 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.
Richard M.
Richard M.
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1392 Visits: 2680
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 doesWink
Cheers

_______________________________________________________________________
For better assistance in answering your questions, click here
SQLRNNR
SQLRNNR
SSC-Dedicated
SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)

Group: General Forum Members
Points: 31920 Visits: 18551
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

Oleg Netchaev
Oleg Netchaev
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1773 Visits: 1813
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 Smile

declare @v sysname;



will be parsed just fine regardless of case sensitivity.

Oleg
john.arnott
john.arnott
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: 1958 Visits: 3059
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
Paul White
SSCoach
SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)

Group: General Forum Members
Points: 15536 Visits: 11354
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
SQLblog.com
@SQL_Kiwi
vk-kirov
vk-kirov
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: 3692 Visits: 4408
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.).
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