Get Schooled at New Horizons

Programmatically determine which named instance you’re connected to (SQL Server 2000/2005)

If you’re using SQL Server named instances, you may wonder how to determine–from inside a stored procedure, for example–which instances you’re connected to at any given time. Actually, there are two ways, and both are quite simple.

First, you can use the system-supplied global variable @@SERVERNAME. Go ahead and connect to a named instance, then try the following statement:

SELECT InstanceName = @@SERVERNAME

As you’ll observe, the result consists of the physical server name, followed by a backslash, followed by the name of the instance. It’s also a simple matter to parse out just the instance name, using the available T-SQL string functions, if you need to.

The other, and essentially equivalent, method is to use the built-in SERVERPROPERTY function. Here’s an example, which should produce the same results as our previous demonstration:

SELECT InstanceName = SERVERPROPERTY(‘ServerName’)

For more information on these two options, see the appropriate entries in SQL Server Books Online.

Check out our SQL classes!

When performing intense calculations, an obvious language choice is C or C++. However, PL/SQL is a natural choice for logic that interacts heavily with database data. If all you need to do is add or multiply a few large numbers, there’s no reason you can’t leverage the appropriate algorithms to perform the arithmetic in PL/SQL and still get good performance.

Another alternative to consider is Java stored procedures, which let you use extensive libraries for doing math while also having the advantage of running on the database.