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 (https://blog.sqlauthority.com)
26 Comments. Leave new
wow… Great..
Hi Pinal,
This is really interesting one.
Thanks,
Sreelekha
Hi Pinal,
Its Very Interesting.Never came across this before.Thanks for this post.Enjoyed…
That’s a good one for Christmas Eve!
bit of code and logical but wow…thanks
Can’t wait for christmas day!!!
Hi pinal sir,
This really is interesting and out of box question.
I will definitely try this.
Regard$
Chirag Satasiya.
Hello Sir,
This is great post.
Thankxx
Hi Pinal,
You are Really Awesome.The Script is really Great.
Thanks,
Narendra
Really amazing !
Thank you
2day I had very gud learning from this post. Thank You very much for this post.
Hi Pinal,
Amazing Script
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
Yes you cannot create a table with out assigning a datatype to a column, the Management studio is designed like that.
Hai Sir,
It’s a very interesting t-sql tips..
Thanks a lot..
Regards,
Nikhildas
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
Are they on a transaction? See what happens when you commit them
MyTabs != MyTabs1
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.
That is gr8.
I was really not knowing we can use As in Create statement too…!
Surprising .. :-)
Very Interesting.
Thanks for sharing.
Second column in the example is created using Computed Column Specification by applying the Formula.
Shyam
Hi Pinal,
Please give us a day of time to post answers for such questions going forward if any.