The topic for today’s post is one that has frequently come up in the minds of a lot of developers:

Why should you use SERVERPROPERTY(‘ServerName’) over @@SERVERNAME?

Both the @@SERVERNAME and the SERVERNAME property return similar result sets. So, why are two configuration functions/options required?

One look at Books-On-Line for @@SERVERNAME shows why:

“Although the @@SERVERNAME function and the SERVERNAME property of SERVERPROPERTY function may return strings with similar formats, the information can be different. The SERVERNAME property automatically reports changes in the network name of the computer.

In contrast, @@SERVERNAME does not report such changes. @@SERVERNAME reports changes made to the local server name using the sp_addserver or sp_dropserver stored procedure.”

Essentially, what this means is that @@SERVERNAME will return the instance name set by the user. It can end up returning an incorrect server name after a pre-configured/stored disk image is restored (Refer my script: IT Admins-Rename your local SQL Server instance-Ghost image restores, renaming host server). The SERVERNAME property on the other hand, will return the correct servername in this case.

Have a great day ahead!

Further Reading:

• @@SERVERNAME [Link]


• SERVERPROPERTY [Link]

Until we meet next time,

Be courteous. Drive responsibly.