T-SQL 101: 98 Using System Variables and Functions in T-SQL

As well as the variables that you declare, SQL Server has a number of built-in system variables and some built-in system functions.

The system variables are the ones with @@ at the start of their name. Here is an example that’s often used:

SELECT @@VERSION;

It is documented here.

On my SQL Server 2022 system, the value returned is this:

Now while that is a reasonable example of a system variable that has been used for a long time, note that it’s a bit messy as there is a lot of information in a single returned value. There are now better ways to get at the system version in a more useful way:

SELECT SERVERPROPERTY(N'ProductUpdateLevel'),
       SERVERPROPERTY(N'Edition'),
       SERVERPROPERTY(N'ProductVersion');

That returns:

SERVERPROPERTY is an example of a system function, and specifically, a metadata function. It is documented here.

Note that it doesn’t have an @@ at the start of its name, and that it lets you get to more detail about the server configuration. It also provides values that are easier to consume in the next part of your code.

There are a large number of system variables and functions.

It’s also worth noting that in some cases, there are built-in system options that you can override, even including data types. I would encourage you to never do that.

For example, I recently was at a site where they had created a data type named date that they had mapped to a smalldatetime. Doing this, is just looking for trouble. And in replication, they found big troubles. Try to leave any system-defined values alone.

In our (free) SDU Tools, we have views that provide a list of reserved words, and also many future reserved words. Don’t use any of these for your own work.

Learning T-SQL

It’s worth your while becoming proficient in SQL. If you’d like to learn a lot about T-SQL in a hurry, our Writing T-SQL Queries for SQL Server course is online, on-demand, and low cost.

2025-01-25