THE SQL Server Blog Spot on the Web

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

The Bit Bucket (Greg Low): IDisposable

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

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.

 

Published Monday, February 04, 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

Comments

 

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 http://flylib.com/books/en/2.214.1.38/1/ 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?

Regards,

Greg

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

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.

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:

@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.

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

Leave a Comment

(required) 
(required) 
Submit

This Blog

Syndication

Tags

No tags have been created or used yet.
Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement