Can a table have no columns? 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. IenCommunityServer 2.1 SP2 (Build: 61129.1)re: Can a table have no columns?, 04 Feb 2013 08:32:46 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:47463Robert L Davis<p>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.</p>re: Can a table have no columns?, 04 Feb 2013 08:50:29 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:47464Martijn Evers<p>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.</p> <p>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.</p> <p>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.</p> <p>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.</p> <p>See <a rel="nofollow" target="_new" href=""></a> for a description on those tables.</p>re: Can a table have no columns?, 04 Feb 2013 09:27:23 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:47465Greg Low<p>Hi Martijn, I have that book and just now have re-read the sections on it. </p> <p>I get his justification for them (mathematically) in relational theory. JOINs to them are like multiplying by 0 or 1. </p> <p>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?</p> <p>Regards,</p> <p>Greg</p> re: Can a table have no columns?, 04 Feb 2013 10:58:51 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:47469Martijn Evers<p>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</p> <p>There are several situations where I would prefer them even in plain SQL.</p> <p>1. Instead of writing SELECT * FROM MY_TABLE WHERE 1=0</p> <p>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).</p> <p>2. testing for existence rows: If exist(select * FROM TABLE_1) then... can be replaced by</p> <p>IF TABLE_1 UNION TABLE_DEE(=TRUE) THEN &nbsp;(UNION= relational AND and TABLE_DEE = relational TRUE. </p> <p>(syntax is not helping here so TABLE_TRUE and TABLE_FALSE would be good synonyms). </p> <p>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.</p> <p>Another way of looking at it is: don't use bits or Boolean scalar data types at all, but start using relations instead</p> <p>Use them as database wide flags encoded with tables/relations:</p> <p>CREATE TABLE DATABASE_DEPLOYED{} </p> <p>this is now equivalent to table_dum so FALSE, meaning database is not deployed</p> <p>INSERT VALUES() INTO DATABASE_DEPLOYED</p> <p>This is an empty tuple so DATABASE_DEPLOYED is now equivalent to TABLE_DEE</p> <p>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 &quot;is true is true&quot; which is superfluous and actually erroneous, because what would the statement &quot;is true is false&quot; mean?</p> <p>For SQL these tables would allow for cleaner SQL with less &quot;if then else&quot;, 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).</p> <p>A good candidate that we can implement with dee and dum would be a relational IF statement.</p>re: Can a table have no columns?, 04 Feb 2013 20:55:34 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:47479Martijn Evers<p>@Greg, Did my follow up post disappear?</p>re: Can a table have no columns?, 04 Feb 2013 23:35:54 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:47483Greg Low<p>Sorry Martijn, which post? (There is one where you give examples)</p> re: Can a table have no columns?, 05 Feb 2013 10:08:17 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:47488Martijn Evers<p>@Greg,</p> <p>Ah, Now I see my first follow up post. I was afraid it got dropped.</p> <p>To Reiterate,</p> <p>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.</p> <p>For creating base tables it does make sense, but we certainly can design around the limitation of not having table_dee and table_dum.</p> <p>If we ever get some additional serious relational operators (EQUAL, IF) I would definitely want to see table_dee and table_dum.</p>re: Can a table have no columns?, 08 Dec 2014 17:25:23 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:56934Bill Delaune<p>I am in a ETL scenario where I need to dynamically create tables based on source system meta data. &nbsp;My code would be cleaner if I could create the table and then add columns. &nbsp;I have no intention of producing a table with no columns, but would like the ability to start with an empty table.</p> <p>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. &nbsp;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.</p> <p>That said &nbsp;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.</p>