Can someone please explain the datatype SYSNAME

Question

Can someone please explain the datatype SYSNAME

Leeland

SSCrazy Eights

Points: 8959
More actions
July 19, 2007 at 10:03 am

#177663

OK I have come across this a few times and I did a little research on it to figure out why it was used in some of our process at work and also in scripts I have found online...and the definition is just not clicking for me...
I found this on the SQL Development Center as a definition
sysname
Instances of SQL Server include a user-defined data type named sysname. sysname is used for table columns, variables, and stored procedure parameters that store object names. The exact definition of sysname is related to the rules for identifiers; therefore, it can vary between instances of SQL Server. sysname is functionally equivalent to nvarchar(128). SQL Server version 6.5 or earlier only supports only smaller identifiers; thus, in earlier versions, sysname is defined as varchar(30).

Please bare with me as I ask a couple ignorant questions because I am sure there are good/simple answers for them but I can't come up with a reason why you would vs. just defining the variables in their place.

Senerio #1
I want to define a variable to hold the @@ServerName I have seen this done two ways...
declare @cmd varchar(30)
set @cmd = @@Servername
select @cmd
GO
declare @cmd sysname
set @cmd = @@Servername
select @cmd

Both seem to give the right value back...so I guess i am wondering what is the advantage of declaring a variable as SYSNAME vs. just defining it as VARCHAR(xxx) or NVARCHAR(128) (which is what it is equivalent to).

Is it the fact that you are explisitly defining that variable and saying "hey this variable is going to be used to house a database object and I want to make that clear to everyone vs. just making a variable that will just happen house a value.

If someone can give me a good reason that it is in your best interest to use SYSNAME when referencing database objects I would REALLY appreciate it cause for the life of me right now I can't see a real reason or senerio why you would...

Thanks In Advance!!!

Leeland

Viewing 7 posts - 1 through 6 (of 6 total)

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

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 1

A server name, database, login, user, table, view, stored procedure, function, etc. are only restricted to having names that are SYSNAME (NVARCHAR(128)).

Just because your current names may fit in VARCHAR(30) does not mean this will be true in the future.

If you want to be able to support all object names, you should use SYSNAME.

David McFarland SSChampion Points: 11815 More actions · Answer 2

sysname is version independent. If tomorrow, databases are allowed to have 1000 character names, sysnames should allow for 1000 characters. For most practical purposes, in SS 7 and later, you can think of it as an nvarchar(128).

Edit: What he said.

Mark Harr SSCrazy Eights Points: 9921 More actions · Answer 3

I'm not sure, but I think that sysname also has some data rules defined for it, so that the value stored in it conforms to rules for identifiers. Also note that you cannot create a user table with a column of sysname.

Mark

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 4

You can create a user table with a column of type SYSNAME. I just ran this code OK in SQL 7.0, 2000, and 2005:

create table MyTableWithSYSNAME ( MySYSNAME sysname not null )
go
exec sp_help MyTableWithSYSNAME
go
drop table MyTableWithSYSNAME

Leeland SSCrazy Eights Points: 8959 More actions · Answer 5

OK let me branch off of this to see if I can get a different perspective on your statement...

By default the max length in characters that an Identifier can be is 128 (SQL 2000 & SQL 7.0)

Basically are you saying that if I am going to reference a database object (all of which might have different lengths/data types...ect) It would be best practice to use the data type of SYSNAME vs. creating my own reference to the identifier (i.e. VARCHAR(50)...CHAR(25)...ect) as it should encompass all objects of all sizes.

And if in the future any changes made to the max length an identifier can be it should update the SYSNAME datatype and account for the change...where as if I had my own specific definition it could cause the process to break...and require manual intervention.

In my example I just did whatever for a size...that was prior to me knowing that the max length was 128 characters...

I guess it breaks down like this to me now...please correct me if I am wrong.

By default the max length in characters that an Identifier can be is 128 (SQL 2000 & SQL 7.0)
The SYSNAME data type is NVARCHAR(128) -- so the max size is the same
I could create a variable to house database objects and explicitly make it a NVARCHAR(128) and it would be the same thing...however if in the future, the value I am placing in that variable gets expanded, changes...ect to exceed the 128 character limit...my custom definition will fail as a result...
vs
Me just using SYSNAME as the data type which should reflect any and all changes to data definition rules thus requiring no maintenance.

Lee

David McFarland SSChampion Points: 11815 More actions · Answer 6

That's basically it in a nutshell. For instance, when upgrading from 6.5 to 7.0, sysname changed it's behind-the-scenes datatype from varchar(30) to nvarchar(128), so code that used sysname won't break for that reason, but code that had varchar(30) for various object names would break on any object with a longer name.

On a side note, if you do use it, write it all in lowercase in case you run across case-sensitive setups.