THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

Greg Low (The Bit Bucket: IDisposable)

Ramblings of Greg Low (SQL Server MVP, MCM and Microsoft RD) - SQL Down Under

Can a table have no columns?

This blog has moved! You can find this content at the following new location:

Published Monday, February 4, 2013 4:01 PM by Greg Low

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS



Robert L Davis said:

What about manually editing the system tables in SQL 2000 or earlier. Could you make that work? I've seen some pretty impossible things happen because people were messing around with updating the system tables manually.

February 4, 2013 3:32 AM

Martijn Evers said:

C. J. Date calls these relations TABLE_DUM and TABLE_DEE. TABLE_DUM has zero tuples of degree zero, TABLE_DEE has one tuple of degree zero.

The reason these are important is to provide some identity relations with respect to join. Joining any table to TABLE_DEE returns the original table. Joining any table to TABLE_DUM returns TABLE_DUM. You can think of this as analogous to multiplying by 1 or 0.

Unfortunately, standard SQL doesn't account for tuples of degree zero. So while it makes sense that relational theory includes these relations for completeness, SQL as written doesn't.

For a good read on the fundamental nature of tables with no columns look at C.J. Dates work talking about Table Dee and Table Dum.

See for a description on those tables.

February 4, 2013 3:50 AM

Greg Low said:

Hi Martijn, I have that book and just now have re-read the sections on it.

I get his justification for them (mathematically) in relational theory. JOINs to them are like multiplying by 0 or 1.

I don't, however, get the point of doing it with tables. Do you have a concrete example of how they could possibly be useful if, in fact, SQL Server did support them?



February 4, 2013 4:27 AM

Martijn Evers said:

Part of the issue here is SQL itself. TABLE_DEE and TABLE_DUM are quite useful in tutorial D.(They either get used to returned by operations) for example as switch

There are several situations where I would prefer them even in plain SQL.

1. Instead of writing SELECT * FROM MY_TABLE WHERE 1=0

we write SELECT * FROM MY_TABLE,TABLE_DUM. The query engine should understand and optimize this better from a relational standpoint (no optimizer tricks needed).

2. testing for existence rows: If exist(select * FROM TABLE_1) then... can be replaced by


(syntax is not helping here so TABLE_TRUE and TABLE_FALSE would be good synonyms).

Note, this is the RELATIONAL if then else, not the programmable one from T-SQL. It is very powerful since we can start doing conditional joining and reusing tables/views based on conditions.

Another way of looking at it is: don't use bits or Boolean scalar data types at all, but start using relations instead

Use them as database wide flags encoded with tables/relations:


this is now equivalent to table_dum so FALSE, meaning database is not deployed


This is an empty tuple so DATABASE_DEPLOYED is now equivalent to TABLE_DEE

Note, a flag just denotes true or false, but since a record/tuple also denotes a true proposition the table CREATE DATABASE_DEPLOYED(Boolean deployed PK) would denote DATABASE_DEPLOYED "is true is true" which is superfluous and actually erroneous, because what would the statement "is true is false" mean?

For SQL these tables would allow for cleaner SQL with less "if then else", allow for case statements to collapse result-sets etc. But to be really useful I think some extension on T-SQL would also be in order, else it will be window dressing. (Some will argue not even to bother with SQL at all since it is not relational to begin with).

A good candidate that we can implement with dee and dum would be a relational IF statement.

February 4, 2013 5:58 AM

Martijn Evers said:

@Greg, Did my follow up post disappear?

February 4, 2013 3:55 PM

Greg Low said:

Sorry Martijn, which post? (There is one where you give examples)

February 4, 2013 6:35 PM

Martijn Evers said:


Ah, Now I see my first follow up post. I was afraid it got dropped.

To Reiterate,

TABLE_DEE AND TABLE_DUM are tables encdoding TRUE and FALSE. Relational operators can use this (like EXCEPT,INTERSECTION,UNION, Relational IF, Relational EQUAL). With SELECT, MERGE and other typical operational SQL statements we sometimes can use them as well, but there is no consistent usage pattern AFAIK, just a list of handy tricks and query engine optimizations.

For creating base tables it does make sense, but we certainly can design around the limitation of not having table_dee and table_dum.

If we ever get some additional serious relational operators (EQUAL, IF) I would definitely want to see table_dee and table_dum.

February 5, 2013 5:08 AM

Bill Delaune said:

I am in a ETL scenario where I need to dynamically create tables based on source system meta data.  My code would be cleaner if I could create the table and then add columns.  I have no intention of producing a table with no columns, but would like the ability to start with an empty table.

Because of the way that I receive the metadata (first a table name then a list of columns) it would be nice to be able to create an empty table.  To workaround I'll probably do nothing for the table meta data, then for each column I receive I check to see if the table exists. If it doesn't create the table with the column I received; if it does alter the table to add the column I receive.

That said  I agree, this is an unusual scenario. Although I feel it would make my code cleaner to have a no column table, it would probably make the SQL Server code messier, and require more conditionals for the much more common flows SQL Server must support. It would be a poor trade off to support null tables for such a small need which has several easy work-arounds; in addition to the one described above I could create the table with a default dummy column, then at the end DROP all those columns from my new tables.

December 8, 2014 12:25 PM

Lukas Eder said:

Interestingly, this would be possible in PostgreSQL, though:

March 17, 2017 10:26 AM

Leave a Comment


This Blog



No tags have been created or used yet.


Privacy Statement