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

Christmas is just near and happy holidays to all of you. Today is Christmas eve and 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.

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?

Reference: Pinal Dave (http://blog.SQLAuthority.com)

About these ads

26 thoughts on “SQL SERVER – A Simple Puzzle and Simple Solution of Datatype and Computed Column

  1. Hi pinal sir,
    i have a small question.
    what you did is throught a script
    [here i m focussing on second column]
    after running the script and opened the table in design mode i can see the data type for second column is blank/empty.
    if i try to make the same table structure through designer, it wont let me proceed unless i specify the data type or i cannot keep the data type blank.

    so how can i create a table in designer mode rather than Query mode, to full fill this requirement. thanks

  2. Hi Pinal,

    I have used c1 and c2 instead of ! and !!.

    CREATE TABLE MyTabs ([c1] INT, [c2] AS [c1]);—Works fine
    INSERT INTO MyTabs VALUES (1);—shows 1 row affected
    SELECT * FROM MyTabs1—No inserted records are displayed,only empty table.

    Regards,
    Deepraj

  3. hi sir,

    i also agree with Zoheb Mohammeds query.bcoz using script it works fine.but when i am trying to use same logic in design mode then it shows invalid data type error.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s