SQL SERVER – A Simple Puzzle and Simple Solution of Datatype and Computed Column

A reader’s interview riddle about datatypes has a neat answer in a computed column. I decided to share something very simple but interesting with you. Recently some one reading my SQL Server Interview Questions and Answers book asked me following question.

“Pinal,

Instead of puzzle, or difficult interview question, I was asked following riddle in my interview. I could not answer it, do you have any idea.

Riddle: Create a table with two columns but you are allowed to specify datatypes only once. Additionally, write a mechanism that your data is copied from first column to second column without using any update statement. Also the column name should not contain any alphanumeric character.

How do I do this?”

For a moment I was confused too. As the requirement were very interesting, coping data without using update statement, no use of alphanumeric statement etc.

Finally I came up with following small and quick script that uses a computed column.

CREATE TABLE MyTabs ([!] INT, [!!] AS [!]);
INSERT INTO MyTabs VALUES (1);
SELECT * FROM MyTabs;

If you are free, can you come up with further optimize script?

Why This Trick Works

The second column [!!] is a computed column. It has no datatype of its own, because SQL Server takes the datatype from the expression, which here is simply the first column. By default the value is not stored on disk. It is calculated every time you read the row, so there is nothing to update. That is also why the INSERT statement needs only one value. SQL Server skips computed columns when you insert data, and it raises an error if you try to insert into one directly. The square brackets are what allow column names made only of symbols.

Outside of riddles, computed columns are handy for things like a full name or a line total. If you read the value very frequently, add the PERSISTED keyword so SQL Server stores it, as long as the expression is deterministic.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.