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.
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
Here is what our constant.number view looks like:
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
Here is what happens when we run against the AdventureWorks database