SQL Server Version and Service pack

The select statement below will return the version of SQL Server (2000,2005, 2008), the product level (Standard, Enterprise) and the edition (32/64 bit, Service pack or RTM.)

SELECT 'Version '
+ CAST(SERVERPROPERTY('productversion') AS VARCHAR) + ' - '
+ CAST(SERVERPROPERTY('productlevel') AS VARCHAR) + ' ('
+ CAST(SERVERPROPERTY('edition') AS VARCHAR) + ') :: ' + @@VERSION as 'SQL Server '

Simple version:

SELECT SERVERPROPERTY('productversion'), SERVERPROPERTY ('productlevel'), SERVERPROPERTY ('edition')
select @@VERSION

Show the database collation:

SELECT DATABASEPROPERTYEX('your_database_name', 'Collation') SQLversion;

“I'd call it a new version of voodoo economics, but I'm afraid that would give witch doctors a bad name” - Geraldine A. Ferraro

Related:

Q321185 - Find the SQL Server version
SQL Server Version numbers