4

I looking to create a table that will store information about stored procedures. I need to store SP_Name. My original plan was to use SP_Name NVARCHAR(128) NOT NULL.

From procedure I will use OBJECT_NAME(@@PROCID) function to get value for SP_Name column.

This technet does not provide much information about the SYSNAME datatype. What is SYSNAME data type in SQL Server? question only explains what it is but not how it works, additionally answer is almost 3 years old and Microsoft made many upgrades to SQL Server in 2012 and 2014 editions.

Will I again any benefits of storing SP_Name in SYSNAME over NVARCHAR(128) column? Or should I avoid use of proprietary data types that might be dropped or changed in later version?

Community
  • 1
  • 1
  • 1
    To the best of my knowledge there won't be any gains using SYSNAME over NVARCHAR(128). I personnally wouldn't use it but it's much more a matter of opinion than anything else. – Crono Feb 19 '14 at 15:03
  • 1
    just as a heads up, `SYSNAME` can't be `NULL`, so it would be a `NVARCHAR(128) NOT NULL` – Lamak Feb 19 '14 at 15:12
  • I thought I already specified that in my question. Also I have read over the possible duplicate that is why I mentioned it in my question. –  Feb 19 '14 at 15:13
  • 1
    If I see `sysname`, I'm immediately put into the frame of mind "okay, I'm going to find the name of a SQL Server object in here". Might serve as an additional bit of "documentation" for free. – Damien_The_Unbeliever Feb 19 '14 at 15:21
  • Disagree with the closure as duplicate so reopened. This question asks the benefits of Sysname not what Sysname is. – Martin Smith May 28 '14 at 18:38

2 Answers2

7

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.

Martin Smith
  • 438,706
  • 87
  • 741
  • 845
0

Will I again any benefits of storing SP_Name in SYSNAME over NVARCHAR(128) column?

You will gain no measurable benefit; however there are several use case outlined in the question you referenced that suggest less tangible gains.

Or should I avoid use of proprietary data types that might be dropped or changed in later version?

As Martin Smith points out, you should instead prefer the use of those type that encapsulate sql server stuff that may change.

I'll add explicitly that technet indicates the following, specifically for this datatype and not the other special ones:

In databases that are case-sensitive, or that have a binary collation, sysname is recognized as a SQL Server system data type only when it appears in lowercase.

Karl Kieninger
  • 8,841
  • 2
  • 33
  • 49