In older versions of SQL Server, we just used SELECT @@VERSION and we were happy. You can still use that if you wish, but the "new and improved and Microsoft recommended" way is the following:
SELECT SERVERPROPERTY('productversion'), SERVERPROPERTY ('productlevel'), SERVERPROPERTY ('edition')
I just ran this on one of our servers and got:
9.00.2047.00 SP1 Standard Edition
For those interested in associating version #'s with service packs:
| Release | Sqlservr.exe |
| RTM 2005 | 2005.90.1399 |
| SQL Server 2005 Service Pack 1 | 2005.90.2047 |
| RTM 2000 | 2000.80.194.0 |
| SQL Server 2000 SP1 | 2000.80.384.0 |
| SQL Server 2000 SP2 | 2000.80.534.0 |
| SQL Server 2000 SP3 | 2000.80.760.0 |
| SQL Server 2000 SP3a | 2000.80.760.0 |
| SQL Server 2000 SP4 | 2000.8.00.2039 |