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.
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.
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 http://flylib.com/books/en/2.214.1.38/1/ for a description on those tables.
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?
Regards,
Greg
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
IF TABLE_1 UNION TABLE_DEE(=TRUE) THEN (UNION= relational AND and TABLE_DEE = relational TRUE.
(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:
CREATE TABLE DATABASE_DEPLOYED{}
this is now equivalent to table_dum so FALSE, meaning database is not deployed
INSERT VALUES() INTO DATABASE_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.
@Greg, Did my follow up post disappear?
Sorry Martijn, which post? (There is one where you give examples)
@Greg,
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.
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.
Interestingly, this would be possible in PostgreSQL, though:
https://blog.jooq.org/2017/03/17/creating-tables-dum-and-dee-in-postgresql/