sysname Data Type cannot be used in SSMS

Question

sysname Data Type cannot be used in SSMS

Andy sql

SSCrazy Eights

Points: 9496
More actions
January 5, 2009 at 9:27 am

#129724

Does anyone know why, when designing a table in SSMS, I cannot assign the data type "sysname" to a column? It works fine using a CREATE TABLE script, but in SSMS I get the error "Invalid data type.".
Looking at BOL, "Using Special Data", it says that sysname is functionally equivalent to nvarchar(128). Is it preferable to define the column data type as sysname or as nvarchar(128)?
Any advice appreciated....
Thanks,
Andy

Viewing 5 posts - 1 through 4 (of 4 total)

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

Jonathan Kehayias One Orange Chip Points: 26778 More actions · Answer 1

In my own design I would go with the nvarchar(128) or use a user defined data type (UDDT) that describes the data being held.

SQLBOT SSCrazy Eights Points: 8014 More actions · Answer 2

That's irritating, isn't it.

I think for data storage purposes I use varchar(128) when I need to store sysname data.

Typecases between varchar(128) and sysname are implicit, so it doesnt' matter how you store them, unless I'm missing something.

~Craig

Craig Outcalt

Tips for new DBAs: http://www.sqlservercentral.com/articles/Career/64632
My other articles: http://www.sqlservercentral.com/Authors/Articles/Craig_Outcalt/560258

Jonathan Kehayias One Orange Chip Points: 26778 More actions · Answer 3

Actually, the implicit type conversions that could happen depending on how you implement the column could be very detremental to performance. If the column is a varchar(128), and you use a sysname variable in a parameterized query against the table you will force an implicit conversion on the column side of the query up to the nvarchar(128) which will cause a full table scan to occur. This is one of the known issues with LINQ to SQL since it sends strings as nvarchar() paramaters to the database engine, which causes an implicit conversion on the table side of the filter criteria and really degrades performance.

Andy sql SSCrazy Eights Points: 9496 More actions · Answer 4

Interesting, thanks for the info.

For the moment, I think I will carry on using the datatype sysname. This does mean I cannot use the GUI of SSMS, but I rarely use the table design part anyway.

Hopefully using sysname also avoids any nasty implicit conversions.

Andy