One of the things I always tell people that I love about consulting/mentoring work is that you see things that you just can't make up. They provide interesting material for training classes.
This week I came across something that I wasn't expecting. I was migrating data from DB2 and in my scripts, I had made the presumption that a table would have at least one column. Turns out that in DB2 you can have a table with no columns.
I can only imagine that comes from creating a table then dropping all the columns. I wondered if SQL Server would do the same, so it was time to find out.
CREATE TABLE dbo.TableWithNoColumns
( FirstColumn int,
ALTER TABLE dbo.TableWithNoColumns DROP COLUMN SecondColumn;
ALTER TABLE dbo.TableWithNoColumns DROP COLUMN FirstColumn;
It doesn't allow this. It won't let you delete the last column. The following message is returned.
Msg 4923, Level 16, State 1, Line 2
ALTER TABLE DROP COLUMN failed because 'FirstColumn' is the only data column in table 'TableWithNoColumns'. A table must have at least one data column.
Whenever I get asked why I like SQL Server so much, these are the sorts of things that don't immediately spring to mind but they are the sorts of reasons why I love working with SQL Server and get frustrated when working in other environments.