MSSQL - @@microsoftversion and EngineEdition

 Sometimes, its the little things that make me happy.  Like learning something that is just a little bit more efficient than the way I accomplished something just last week.

I end up writing more than a few stored procedures to catch logs of various system activity.  I used to grab the MSSQL major version and edition as  below, in order to decide if I should compress me log table.  That's one way to verify that the version is past MSSQL 2005 and that the edition allows for compression.  But, I'm not happy with it.  Today I learned about @@microsoftversion and and server property EngineEdition.  So I give an example of their use below.

~~~~

CREATE PROCEDURE QSP_SUPER_DUPER_DIAGNOSTIC
AS

DECLARE @version NVARCHAR(MAX)
DECLARE @major   INT
DECLARE @edition NVARCHAR(MAX)
DECLARE @sqltext NVARCHAR(MAX)

SET @version = CAST(SERVERPROPERTY('productversion') as NVARCHAR(MAX))
SET @major   = CAST(LEFT(@version,CHARINDEX('.',@version)-1) AS INT)
SET @edition = CAST(SERVERPROPERTY('edition') AS NVARCHAR(MAX))

IF OBJECT_ID(N'TB_SUPER_DUPER_DIAGNOSTIC') IS NULL
  BEGIN
    SET @sqltext=
      N'CREATE TB_SUPER_DUPER_DIAGNOSTIC
          (sample_dt DATETIME NOT NULL,
           session_id INT,
           sql_handle VARBINARY(64),
           is_next_candidate BIT
           PRIMARY KEY CLUSTERED (sample_dt,session_id)
           )'
    IF @major > 9 AND @edition LIKE '%ENTERPRISE%'
      SET @sqltext = @sqltext + N' WITH (DATA_COMPRESSION = PAGE)'
    EXEC sp_executesql @sqltext
  END

~~~~




Now I can determine eligibility for compression much more cleanly:

CREATE PROCEDURE QSP_SUPER_DUPER_DIAGNOSTIC
AS

DECLARE @major        INT
DECLARE @engEdition INT
DECLARE @sqltext       NVARCHAR(MAX)

SET @major = @@microsoftversion / 0x01000000
SET @engEdition = CAST(SERVERPROPERTY('EngineEdition') AS INT)
 
IF OBJECT_ID(N'TB_SUPER_DUPER_DIAGNOSTIC') IS NULL
  BEGIN
    SET @sqltext=
      N'CREATE TB_SUPER_DUPER_DIAGNOSTIC
          (sample_dt DATETIME NOT NULL,
           session_id INT,
           sql_handle VARBINARY(64),
           is_next_candidate BIT
           PRIMARY KEY CLUSTERED (sample_dt,session_id)
           )'
    IF @major > 9 AND @engEdition = 3  --after 2005, Enterprise, Evaluation, or Developer
      SET @sqltext = @sqltext + N' WITH (DATA_COMPRESSION = PAGE)'
    EXEC sp_executesql @sqltext
  END

~~~~