"If you’ve used SQL Server for a while, you’re probably familiar with the @@version function. @@ variables are system variables that are automatically populated by SQL Server. Selecting the @@version function in a query window produces the following results on my system.
in the results, you can easily see that I’m running SQL Server 2008 R2 Developer Edition on an Intel X86 processor. Notice that the results also contain operating system information as well. In this case, it shows that SQL Server is installed on a Windows NT 6.1 machine.
This can cause confusion for many who are unfamiliar with the @@version variable. Frequently, the service pack of the operating system is placed toward the end of the text. It’s easy to assume that this is the service pack level for the SQL Server, after all we are asking SQL Server for the information. That’s not the case. It’s the operating system system pack level.
The ServerProperty Function
To determine SQL Server’s service pack level, use the built-in SERVERPROPERTY function with appropriate parameters – EDITION, PRODUCTLEVEL, and PRODUCTVERSION.
The question of what service pack a given SQL Server was on came up just this week, as did the fun of trying to interpret the @@Version results. Looks like using ServerProperty function would have saved us a bit of time...
Below is the thumbnail of the SERVERPROPERTY help page. As you can see, there's an official boat load of options/parameters;
BTW, this works for SQL Server 2005+ (thought there might be differences, etc, but at the basic level, it's time to retire using @@Version... ;)
Related Past Post XRef:
SQL Server Version Numbers - That @@version number means my SQL Server is running what SP?