What Is Sysname Data Type in SQL Server

What is SYSNAME data type in SQL Server?

sysname is a built in datatype limited to 128 Unicode characters that, IIRC, is used primarily to store object names when creating scripts. Its value cannot be NULL

It is basically the same as using nvarchar(128) NOT NULL

EDIT

As mentioned by @Jim in the comments, I don't think there is really a business case where you would use sysname to be honest. It is mainly used by Microsoft when building the internal sys tables and stored procedures etc within SQL Server.

For example, by executing Exec sp_help 'sys.tables' you will see that the column name is defined as sysname this is because the value of this is actually an object in itself (a table)

I wouldn't worry too much about it.

It's also worth noting that for those people still using SQL Server 6.5 and lower (are there still people using it?) the built in type of sysname is the equivalent of varchar(30)

Documentation

sysname is defined with the documentation for nchar and nvarchar, in the remarks section:

sysname is a system-supplied user-defined data type that is functionally equivalent to nvarchar(128), except that it is not nullable. sysname is used to reference database object names.

To clarify the above remarks, by default sysname is defined as NOT NULL it is certainly possible to define it as nullable. It is also important to note that the exact definition can vary between instances of SQL Server.

Using Special Data Types

The sysname data type 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 the same
as nvarchar(128) except that, by default, sysname is NOT NULL. In
earlier versions of SQL Server, sysname is defined as varchar(30).

Some further information about sysname allowing or disallowing NULL values can be found here https://stackoverflow.com/a/52290792/300863

Just because it is the default (to be NOT NULL) does not guarantee that it will be!

What are the benefits of storing name as sysname instead of nvarchar(128) ?

It provides an abstraction that shields you from the implementation details. The benefit is that if the definition changes in future versions your code will still work correctly.

sysname used to be equivalent to varchar(30). If, say, SQL Server 2016 allows object identifiers to be 256 characters long you don't need to find and update all the hardcoded 128.

I also prefer using it as it seems neater semantically anyway to use that datatype for columns/variables storing object identifiers.

How to tell whether a column is of sysname type

The sys.types catalog view exposes data types that can be specified in DDL. You can join to this view on user_type_id to identify column type. As you can see from this query, sysname is not an internal secret type.

SELECT c.Name AS ColumnName, ty.name AS TypeName, c.max_length AS ColumnLengthBytes
FROM sys.objects t
JOIN sys.columns c ON t.object_id = c.object_id 
JOIN sys.types ty ON c.user_type_id = ty.user_type_id
WHERE t.name = N'test_table';

sysname is similar to a user-defined type. It differs from a UDT created with CREATE TYPE in that the is_user_defined column of sys.types will be zero instead of one since it's defined by SQL Server rather than a user.

One can also join on system_type_id to also return both the user and base system type.

SELECT c.Name AS ColumnName, ty.name AS TypeName, c.max_length AS ColumnLengthBytes
FROM sys.objects t
JOIN sys.columns c ON t.object_id = c.object_id 
JOIN sys.types ty ON ty.system_type_id = c.system_type_id
WHERE t.name = N'test_table';

How can sysname = null in this SQL Server system stored procedure if sysname cannot be null?

"SYSNAME cannot be NULL" is just not true. The linked question's answer is correct when it says that it's equivalent to NVARCHAR(128) NOT NULL as a default -- and then effectively only in column definitions. Compare:

-- When not specified, columns are NULL
SET ANSI_NULL_DFLT_ON ON  

-- Works
CREATE TABLE T(N NVARCHAR(128)); INSERT T DEFAULT VALUES; SELECT * FROM T
GO
DROP TABLE T
GO

-- When not specified, columns are NOT NULL
SET ANSI_NULL_DFLT_ON OFF

-- Error: can't insert NULL
CREATE TABLE T(N NVARCHAR(128)); INSERT T DEFAULT VALUES; SELECT * FROM T
GO
DROP TABLE T
GO

And now try the same with SYSNAME:

-- When not specified, columns are NULL
SET ANSI_NULL_DFLT_ON ON  

-- Error: SYSNAME is NOT NULL, regardless of defaults
CREATE TABLE T(N SYSNAME); INSERT T DEFAULT VALUES; SELECT * FROM T
GO
DROP TABLE T
GO

But this does not mean SYSNAME cannot be NULL, all we have to do is say it may be:

-- Works
CREATE TABLE T(N SYSNAME NULL); INSERT T DEFAULT VALUES; SELECT * FROM T
GO
DROP TABLE T
GO

In almost all other contexts where a type is used (variables, stored procedure parameters), we cannot specify NULL or NOT NULL and NULL values are always allowed, so this NOT NULL metadata is very rarely relevant. It's no accident that the above code is using regular tables: if you try the same thing with a table variable, you'll find that ANSI_NULL_DFLT_ON is ignored and NULL is always the default for columns, if not specified, so the only relevant cases are:

-- Can't insert NULL
DECLARE @T TABLE (N SYSNAME); INSERT @T DEFAULT VALUES; SELECT * FROM T@
GO

-- OK
DECLARE @T TABLE (N SYSNAME NULL); INSERT @T DEFAULT VALUES; SELECT * FROM @T
GO

If SQL Server USER_NAME() Is A sysname, Why Does It Return NVARCHAR(256)?

Well, let's take a look:

SELECT name, system_type_id, user_type_id, max_length
  FROM sys.types WHERE name IN (N'sysname', N'nvarchar');

Results:

name      system_type_id  user_type_id  max_length
--------  --------------  ------------  ----------
nvarchar  231             231           8000
sysname   231             256           256

sysname is just an alias type for nvarchar with a defined number of characters of 128 (not 256). The reason sys.types says 256 is that nvarchar has 2 bytes per character - so that's 128 characters x 2 bytes = 256 bytes. They do not mean the same thing by "length" that we usually do.

There is no answer to the reason "why" BOL says that - Books Online is just wrong, that's all. Proof:

SELECT x = USER_NAME() INTO #foo;
EXEC tempdb..sp_help '#foo';

Partial results:

Column_name  Type      Length
-----------  --------  ------
x            nvarchar  256

-----------------------^^^
---- again, this is 128 characters

And yes, you should be safe using NVARCHAR(128), but does it cost you anything extra to match the documentation, just in case? Also stop looking at deprecated system tables like sysusers for guidance on data type choices and corroborating the documentation. While sys.database_principals and sys.server_principals also use sysname in this case, they're a much more reliable place to check how SQL Server works today, unless you really are investigating how SQL Server worked 13+ years ago.

Why does Azure SQL Database and MS SQL Server documentation say sequences are type sysname when they can be configured as anything?

As marc_s said: The NAME of the SEQUENCE is of type sysname.

let me summarize:

1. sequence_name: Specifies the unique name by which the sequence is known in the database. Type is sysname. Not the data type.
2. The bigint the default data type of sequence if you don't set it.

Please don't mix the name type and the data type.

You can reference this documents:

1. Sequence Numbers.
2. Sequence Properties (General Page).

Hope this helps.

---

Related Topics