Views as virtual tables can be misused to replace tables in certain circumstances, either by storing values within one or multiple rows, like in the below examples:
-- parameters for a BI solution
CREATE VIEW dbo.vLoV_Parameters
AS
SELECT Cast('ABC' as nvarchar(20)) AS DataAreaId
, Cast(GetDate() as Date) AS CurrentDate
, Cast(100 as int) AS BatchCount
GO
SELECT *
FROM dbo.vLoV_Parameters
GO
-- values for a dropdown
CREATE VIEW dbo.vLoV_DataAreas
AS
SELECT Cast('ABC' as nvarchar(20)) AS DataAreaId
, Cast('Company ABC' as nvarchar(50)) AS Description
UNION ALL
SELECT 'XYZ' DataAreaId
, 'Company XYZ'
GO
SELECT *
FROM dbo.vLoV_DataAreas
GO
These solutions aren’t elegant, and typically not recommended because they go against one of the principles of good database design, namely “data belong in tables”, though they do the trick when needed. Personally, I used them only in a handful of cases, e.g. when it wasn’t allowed to create tables, when it was needed testing something for a short period of time, or when there was some overhead of creating a table for 2-3 values. Because of their scarce use, I haven’t given them too much thought, not until I discovered Jared Ko’s blog posting on pseudo-constants. He considers the values from the first view as pseudo-constants, and advocates for their use especially for easier dependency tracking, easier code refactoring, avoiding implicit data conversion and easier maintenance of values.
All these are good reasons to consider them, therefore I tried to take further the idea to see if it survives a reality check. For this I took Dynamics AX as testing environment, as it makes extensive use of enumerations (aka base enums) to store list of values needed allover through the application. Behind each table there are one or more enumerations, the tables storing master data abounding of them. For exemplification let’s consider InventTrans, table that stores the inventory transactions, the logic that governs the receipt and issued transactions are governed by three enumerations: StatusIssue, StatusReceipt and Direction.
-- Status Issue Enumeration
CREATE VIEW dbo.vLoV_StatusIssue
AS
SELECT cast(0 as int) AS None
, cast(1 as int) AS Sold
, cast(2 as int) AS Deducted
, cast(3 as int) AS Picked
, cast(4 as int) AS ReservPhysical
, cast(5 as int) AS ReservOrdered
, cast(6 as int) AS OnOrder
, cast(7 as int) AS QuotationIssue
GO
-- Status Receipt Enumeration
CREATE VIEW dbo.vLoV_StatusReceipt
AS
SELECT cast(0 as int) AS None
, cast(1 as int) AS Purchased
, cast(2 as int) AS Received
, cast(3 as int) AS Registered
, cast(4 as int) AS Arrived
, cast(5 as int) AS Ordered
, cast(6 as int) AS QuotationReceipt
GO
-- Inventory Direction Enumeration
CREATE VIEW dbo.vLoV_InventDirection
AS
SELECT cast(0 as int) AS None
, cast(1 as int) AS Receipt
, cast(2 as int) AS Issue
To see these views at work let’s construct the InventTrans table on the fly:
-- creating an ad-hoc table
SELECT *
INTO dbo.InventTrans
FROM (VALUES (1, 1, 0, 2, -1, 'A0001')
, (2, 1, 0, 2, -10, 'A0002')
, (3, 2, 0, 2, -6, 'A0001')
, (4, 2, 0, 2, -3, 'A0002')
, (5, 3, 0, 2, -2, 'A0001')
, (6, 1, 0, 1, 1, 'A0001')
, (7, 0, 1, 1, 50, 'A0001')
, (8, 0, 2, 1, 100, 'A0002')
, (9, 0, 3, 1, 30, 'A0003')
, (10, 0, 3, 1, 20, 'A0004')
, (11, 0, 1, 2, 10, 'A0001')
) A(TransId, StatusIssue, StatusReceipt, Direction, Qty, ItemId)
Here are two sets of examples using literals vs. pseudo-constants:
--example issued with literals
SELECT top 100 ITR.*
FROM dbo.InventTrans ITR
WHERE ITR.StatusIssue = 1
AND ITR.Direction = 2
GO
--example issued with pseudo-constants
SELECT top 100 ITR.*
FROM dbo.InventTrans ITR
JOIN dbo.vLoV_StatusIssue SI
ON ITR.StatusIssue = SI.Sold
JOIN dbo.vLoV_InventDirection ID
ON ITR.Direction = ID.Issue
GO
--example receipt with literals
SELECT top 100 ITR.*
FROM dbo.InventTrans ITR
WHERE ITR.StatusReceipt= 1
AND ITR.Direction = 1
GO
--example receipt with pseudo-constants
SELECT top 100 ITR.*
FROM dbo.InventTrans ITR
JOIN dbo.vLoV_StatusReceipt SR
ON ITR.StatusReceipt= SR.Purchased
JOIN dbo.vLoV_InventDirection ID
ON ITR.Direction = ID.Receipt
As can be seen the queries using pseudo-constants make the code somehow readable, though the gain is only relative, each enumeration implying an additional join. In addition, when further business tables are added to the logic (e.g. items, purchases or sales orders) it complicates the logic, making it more difficult to separate the essential from nonessential. Imagine a translation of the following query:
-- complex query
SELECT top 100 ITR.*
FROM dbo.InventTrans ITR
<several tables here>
WHERE ((ITR.StatusReceipt<=3 AND ITR.Direction = 1)
OR (ITR.StatusIssue<=3 AND ITR.Direction = 2))
AND (<more constraints here>)
The more difficult the constraints in the WHERE clause, the more improbable is a translation of the literals into pseudo-constraints. Considering that an average query contains 5-10 tables, each of them with 1-3 enumerations, the queries would become impracticable by using pseudo-constants and quite difficult to troubleshoot their execution plans.
The more I’m thinking about, an enumeration data type as global variable in SQL Server (like the ones available in VB) would be more than welcome, especially because values are used over and over again through the queries. Imagine, for example, the possibility of writing code as follows:
-- hypothetical query
SELECT top 100 ITR.*
FROM dbo.InventTrans ITR
WHERE ITR.StatusReceipt = @@StatusReceipt .Purchased
AND ITR.Direction = @@InventDirection.Receipt
From my point of view this would make the code more readable and easier to maintain. Instead, in order to make the code more readable, one’s usually forced to add some comments in the code. This works as well, though the code can become full of comments.
-- query with commented literals
SELECT top 100 ITR.*
FROM dbo.InventTrans ITR
WHERE ITR.StatusReceipt <=3 – Purchased, Received, Registered
AND ITR.Direction = 1-- Receip
In conclusion, pseudo-constants’ usefulness is only limited, and their usage is against developers’ common sense, however a data type in SQL Server with similar functionality would make code more readable and easier to maintain.
PS: It is possible to simulate an enumeration data type in tables’ definition by using a CHECK constraint.
