0 Server Name and Instance Name

To get the SQL Server Server name

Select * from sys.servers 

select @@SERVERNAME

sp_helpserver 

To get the instance name

select @@SERVICENAME

For the default instance. the @@servicename always show as ‘MSSQLSERVER’

The SSMS –> Instance name –> Server Property –> General –> Name represents the name of the instance. ( http://msdn.microsoft.com/en-us/library/ms178496  ) , however for the default instance, it would show as HOSTNAME.

We can change the server name quite easily . In fact there are some application has dependence on the server name.

sp_dropserver 'Servername';

sp_addserver 'servername',local;

After the name has been changed, bounce the sql server instance. However there is no easy way to change the instance name.

In theory, you can update the register key and recreate the sql server service by using SC command. I have done so for the SSAS but not ever do it for SQL Server.