Can a table have no columns?

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.

USE tempdb;
GO

CREATE TABLE dbo.TableWithNoColumns
( FirstColumn int,
  SecondColumn int
);
GO

ALTER TABLE dbo.TableWithNoColumns DROP COLUMN SecondColumn;
GO

ALTER TABLE dbo.TableWithNoColumns DROP COLUMN FirstColumn;
GO

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.

2013-02-04