I recently saw a tweet stating that you could prevent other developers from reading from a table using the SELECT * FROM TableName by building your table in the following way:
CREATE TABLE [TableName] ( [ID] INT IDENTITY NOT NULL, [Name] VARCHAR(50) NOT NULL, [DontUseStar] AS (1 / 0) );
It’s easy to see that using the SELECT * here would try to read the blank column name as 1 divided by 0 (thus causing a divide by zero error), but without a datatype assigned to the column.
Why does SQL allow you to create a column with no assigned data type, with a name it knows will be illegal?
Advertisement
Answer
It a perfectly valid syntax for a computed column. Computed columns can be used to calculate some value on select without actually storing them, although the value can be persisted.
The reason why 1/0 is allowed, is because the actual computed column value not evaluated until runtime. For example the computed column can be defined as columna/columnb, the same error will occur if any row in columnb has a 0 and is , but only if that row/column was selected.
if object_id('tempdb..#t') IS NOT NULL
drop table #t;
CREATE TABLE #t
(a int
,b int
,div as (a/b)
);
INSERT INTO #t
values
(1,1)
,(2,0);
SELECT * FROM #t WHERE a = 1;
SELECT a from #t; -- NO ERRORS
SELECT * FROM #t WHERE a=2; --WILL RESULT IN AN ERROR
SELECT * FROM #t; --WILL RESULT IN AN ERROR