Subquery in the VALUES clause

Question

Here's a real-world schema:

CREATE TABLE [dbo].[Setting]
  (
     [ID]       [BIGINT] NOT NULL,
     [Module]   [NVARCHAR](400) NOT NULL,
     [Property] [NVARCHAR](400) NOT NULL,
     [Value]    [NVARCHAR](4000) NULL,
     PRIMARY KEY CLUSTERED ( [ID] ASC )
  )

And here's the INSERT statement I'm trying to execute:

INSERT INTO [Setting]
            ([ID],
             [Module],
             [Property],
             [Value])
VALUES      ((SELECT COALESCE(max(ID), 0) + 1
              FROM   [Setting]),
             N'security.catalogs.integrated.integrated',
             N'enable-integrated-authentication',
             N'true') 

For the life of me I cannot see anything special here, yet on at least two occasions this has failed on SQL Server 2008 R2 Express with the following error message:

Subqueries are not allowed in this context. Only scalar expressions are allowed.

This INSERT statement operates on a completely empty table, no strange options were allegedly configured for SQL Server, no nothing.

How can this be happening? From my understanding of SQL Server, max cannot possibly return more than one row.

Answer 1

Experimentally, your code worked for me on both 10.50.1600 (2008 R2 RTM) Developer and 10.0.4000 (2008 SP2) Express. However, it failed against 9.0.5057 (2005 SP4 + hotfix). So, I dug into the documentation.

The 2005 version says this about expressions in the VALUES clause (emphasis mine):

expression

Is a constant, a variable, or an expression. The expression cannot contain a SELECT or EXECUTE statement.

while the 2008 and higher version looks like this:

expression

Is a constant, a variable, or an expression. The expression cannot contain an EXECUTE statement.

---

From this evidence, I can only assume that the server version where you're executing these queries is not what you think it is.

Answer 2

From what I have been reading you are not allowed to use subqueries in insert statements like this. It may work when Setting is empty because it evaluates to NULL. This is the usual way to write a query like this.

 insert into [Setting] ([ID], [Module], [Property], [Value])
 select coalesce(max(ID), 0) + 1, N'security.catalogs.integrated.integrated', 
    N'enable-integrated-authentication', N'true' 
 from [Setting];