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