February 13, 2015 at 10:06 am
Interesting question. Made me think a little since I obviously never tried it before.
Guessed it right because I was sure there had to be some sort of validation to prevent this.
---------------
Mel. 😎
February 14, 2015 at 6:49 am
Thanks for the question.
February 15, 2015 at 1:38 am
Victor Kirkpatrick (2/13/2015)
Yeah that was a good one... have never contemplated dropping the only column in a table. Interesting to see the error.
+1
Thanks & Best Regards,
Hany Helmy
SQL Server Database Consultant
February 16, 2015 at 6:26 am
'A table cannot be created without a column.' : is really irrelevant, given the context, and nature of tables in Sql Server. The table was created with a column. An attempt to drop the column happened.
February 16, 2015 at 11:13 am
mwpowellhtx (2/16/2015)
'A table cannot be created without a column.' : is really irrelevant, given the context, and nature of tables in Sql Server. The table was created with a column. An attempt to drop the column happened.
Yes, quite so. And the reference is utterly irrelevant to the question and answer too.
But despite that, it's still a nice question. quite scary that more than 20% got it wrong, though.
Tom
February 16, 2015 at 11:21 am
It's true. However, the lessons I take from these is to always at least run it on my favorite Sql Server fiddler, if not a local database instance. 🙂
February 17, 2015 at 1:34 am
A table cannot be created without a column.' : is really irrelevant, given the context, and nature of tables in Sql Server. The table was created with a column. An attempt to drop the column happened.
Yeah,you are right!!! Thank you for pointing it. I will be careful next time.
February 20, 2015 at 3:48 am
Raghavendra Mudugal (2/13/2015)
Thank you for the post, very interesting one.(so, 63 people thought that they can create a table with no cols; if this becomes real, I am not sure how and where this can be used; would be cool though if they come up with a real-time situation where this is a must...):-D
There is in fact a use for column-less tables - just not in the external surface area.
When analyzing execution plans, you will sometimes see that data flowing between two operators (which, admittedly, is not truly a table) that has no rows. And there is even an operator that stores an internal "virtual table" with zero columns (the Row Count Spool operator)
February 20, 2015 at 5:33 am
So in the instance of execution plans, its not truly columnless table, but rather rowless table (view, etc). i.e. for outer joins...
February 20, 2015 at 5:48 am
mwpowellhtx (2/20/2015)
So in the instance of execution plans, its not truly columnless table, but rather rowless table (view, etc). i.e. for outer joins...
No, no, you misunderstand. A rowless table is nothing special, just execute a CREATE TABLE, a DELETE without WHERE or a TRUNCATE to get one (the latter two best not on your prod server;-))
In an execution plan, there can be operators that pass each others rows with zero columns. You can see this by looking at the operator properties (the Output List property is missing in those operators), or by looking at the estimated row size of the data stream (it will be 9 bytes, which is exactly the number of bytes for the per-row overhead - so no extra space used for any columns).
If you can make it to London on March 4, come see my "Understanding Execution Plans" training day. I have one of these in at least one of the demos. I understand from the organization that I already have a pretty large crowd, but that there are still some seats available.
February 20, 2015 at 5:57 am
Oh, I see. The operators involved. Interesting. I'll look for that, thank you.
March 23, 2015 at 2:22 pm
easy peasy!
Viewing 12 posts - 16 through 26 (of 26 total)
You must be logged in to reply to this topic. Login to reply