Why does MS SQL allow you to create an illegal column?

Question

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?

Answer 1

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

https://docs.microsoft.com/en-us/sql/relational-databases/tables/specify-computed-columns-in-a-table?view=sql-server-2017