Can I select 0 columns in SQL Server?

Question

I am hoping this question fares a little better than the similar Create a table without columns. Yes, I am asking about something that will strike most as pointlessly academic.

It is easy to produce a SELECT result with 0 rows (but with columns), e.g. SELECT a = 1 WHERE 1 = 0.

Is it possible to produce a SELECT result with 0 columns (but with rows)? e.g. something like SELECT NO COLUMNS FROM Foo. (This is not valid T-SQL.)

I came across this because I wanted to insert several rows without specifying any column data for any of them. e.g. (SQL Server 2005)

CREATE TABLE Bar (id INT NOT NULL IDENTITY PRIMARY KEY)
INSERT INTO Bar SELECT NO COLUMNS FROM Foo
-- Invalid column name 'NO'.
-- An explicit value for the identity column in table 'Bar' can only be specified when a column list is used and IDENTITY_INSERT is ON.

One can insert a single row without specifying any column data, e.g. INSERT INTO Foo DEFAULT VALUES.

One can query for a count of rows (without retrieving actual column data from the table), e.g. SELECT COUNT(*) FROM Foo. (But that result set, of course, has a column.)

I tried things like

INSERT INTO Bar () SELECT * FROM Foo
  -- Parameters supplied for object 'Bar' which is not a function.
  -- If the parameters are intended as a table hint, a WITH keyword is required.

and

INSERT INTO Bar DEFAULT VALUES SELECT * FROM Foo
  -- which is a standalone INSERT statement followed by a standalone SELECT statement.

I can do what I need to do a different way, but the apparent lack of consistency in support for degenerate cases surprises me.

I read through the relevant sections of BOL and didn't see anything. I was surprised to come up with nothing via Google either.

Answer 1

This is one significant limitation of SQL and one reason why SQL is not relationally complete.

As you may know, Hugh Darwen invented the names DUM and DEE for the two zero-degree relations. SQL has no equivalent of them.

Answer 2

No it's a SQL limitation. SQL has lots of warts, contradictions and asymmetries. It's unfortunately not mathematically precise or complete unlike the relational theory and algebra it was inspired by and meant to be used to work with.