Transact SQL And Constant Value

Background

Occasionally, when writing Transact SQL Scripts one finds self re-using certain numbers quite a bit.

It is often good practice to abstract away repetitions.

 

Constant Value

Views

Upon googling for constants in Transact SQL, found a new use for Views.

The Stackoverflow response posits that one can use view to represent constants by doing the following:

  • Have View columns represent the name of the constant
  • Have View row represent the value of the constant

 

Here it goes:

View – Datatype


SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
go

if schema_id('constant') is null
begin

    exec('create schema [constant] authorization [dbo]')

end
go

IF NOT EXISTS
(
    SELECT name, type
    FROM   sys.objects
    WHERE  object_id = object_id('[constant].[datatype]')
    AND    type = N'V'
)
begin
     exec('create view [constant].[datatype] as select 1/0 as [shell]  ')
end
GO

ALTER VIEW [constant].[datatype]
(

        [tinyintMin]
      , [tinyintMax]

      , [SmallintMin]
      , [SmallintMax]

      , [intMin]
      , [intMax]

      , [bigIntMin]
      , [bigIntMax]
)
AS 

    select

        --TinyInt
          0 as [tinyintMin]
        , 255 as [tinyintMax]

        --Smallint -2^15 (-32,768) to 2^15-1 (32,767)
        , -32768 as [SmallintMin]
        , 32767  as [SmallintMax]

        -- int 2^31 (-2,147,483,648) to 2^31-1 (2,147,483,647)
        , -2147483648 as [intMin]
        , 2147483647  as [intMax]

        --bigint -2^63 (-9,223,372,036,854,775,808) to 2^63-1 (9,223,372,036,854,775,807)
        , -9223372036854775808 as [bigIntMin]
        , 9223372036854775807 as [bigIntMax]

go

grant select on [constant].[datatype] to [public]
go

 

View – Number


IF NOT EXISTS 
(
    SELECT name, type
    FROM   sys.objects 
    WHERE  object_id = object_id('[constant].[number]')
    AND    type = N'V'
)
begin
     exec('create view [constant].[number] as select 1/0 as [shell]  ')
end
GO


ALTER VIEW [constant].[number]
(
      [pi]
    , [e]
    , [phi]

)
AS 

    select
          3.141592653 as [pi]
        , 2.718281828 as [e]
        , 1.618033988 as [phi]

go

 

View Data

Here is what our constant.number view looks like:

constant

Use case

Here we use our defined constants as we try to see which identity columns are approaching their upper limits.

CROSS APPLY and OUTER APPLY are also a welcome addition to the SQL Standards.

 


select

      OBJECT_SCHEMA_NAME(tblO.object_id) as [schema]
    , tblO.name as [object]
    , tblIC.name as [column]
    , tblSCT.name
    , tblIC.last_value
    ,
        case tblSCT.name
                when 'tinyint' then vwCD.tinyIntMax
                when 'smallint' then vwCD.smallintMax
                when 'int' then vwCD.intMax
                when 'bigint' then vwCD.bigintMax
        end as [MaxValue]

    ,
        case tblSCT.name
                when 'tinyint' then vwCD.tinyIntMax - cast(tblIC.last_value as bigint)
                when 'smallint' then vwCD.smallintMax  - cast(tblIC.last_value as bigint)
                when 'int' then vwCD.intMax   - cast(tblIC.last_value as bigint)
                when 'bigint' then vwCD.bigintMax - cast(tblIC.last_value  as bigint)
        end as [MaxValue - LastValue]

from   sys.objects tblO

        inner join sys.identity_columns tblIC

            on tblO.object_id = tblIC.object_id

        inner join sys.types tblSCT

            on tblIC.system_type_id = tblSCT.system_type_id

        cross apply [constant].[datatype] vwCD

where  tblO.type = 'U'

and    tblIC.last_value is not null

order by 6 asc

 

Trial Run

Here is what happens when we run against the AdventureWorks database

 

DBAdventureWorks

 

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s