How do I know which version of SQL Server I'm running?

When working with SQL Servers or other features that are version-specific, you often need to know what version of SQL Server is running on the target server.

For SQL 7.0 and earlier, You can query

SELECT @@VERSION

And the following query will work on SQL Server 2000 and up:

SELECT 'SQL Server ' 
+ CAST(SERVERPROPERTY('productversion') AS VARCHAR) + ' - ' 
+ CAST(SERVERPROPERTY('productlevel') AS VARCHAR) + ' (' 
+ CAST(SERVERPROPERTY('edition') AS VARCHAR) + ')'