1

In SQL Server, the following executes with no error:

create database [MyExample] with compatibility_level = 100;  -- runs! why?

It creates a database all right, but it does not set its compatibility level. Why is that syntax allowed then?

In contrast, if I do:

create database [MyExample] with nonsense_notexisting = 100;  -- fails :-)

then I get the error Incorrect syntax near 'nonsense_notexisting' as expected.

My question is: Why is the first syntax allowed if it does not set the compatibility level?

PS! I am not asking how to create a database with a particular compatibility. I know that is done with:

create database [MyExample]; alter database [MyExample] set compatibility_level = 100;
Jeppe Stig Nielsen
  • 60,409
  • 11
  • 110
  • 181
  • 3
    How did you even come by that syntax, given that it's [not currently documented](https://learn.microsoft.com//sql/t-sql/statements/create-database-sql-server-transact-sql)? (And the fact that it's not documented might give a hint as to why it does nothing. Try `COMPATIBILITY_LEVEL = 16` for fun, which is not a valid level by any measure.) – Jeroen Mostert May 09 '18 at 13:04
  • However he came across the syntax the question the OP is asking still remains, why does that incorrect syntax execute without error? – Mazhar May 09 '18 at 13:08
  • 2
    @Mazhar: my money would be on "feature not yet implemented" or "feature never to be implemented but syntax not removed". I consider it very unlikely that we'll get an MS engineer to actually answer this question, but knowing where the syntax came from (prerelease material, some internals book, or just made up entirely) might actually help in getting the answer. – Jeroen Mostert May 09 '18 at 13:12
  • I'd suggest that SO isn't the right place for asking this question. The people you should be asking is Microsoft; possibly via [UserVoice](https://feedback.azure.com/forums/908035-sql-server) – Thom A May 09 '18 at 13:12
  • It could be a 'feature' of how the parser works too, who knows. Without MS to give us an answer, this question is purely opinion unfortunately. – DavidG May 09 '18 at 13:13
  • @JeroenMostert (your first comment) Of course I just typed that syntax in SQL Server Management Studio and hit F5. Since it said it completed successfully, I thought I had correctly guessed a valid syntax. Later I saw the compatibility was not what I expected. __Only then__ did I consult the documentation you mentioned. That is how. – Jeppe Stig Nielsen May 09 '18 at 13:56
  • You say "of course" as if guessing syntax is something everyone does on a regular basis. :-) It was just about conceivable you got it from somewhere else, like a legacy code base where this used to work for old versions. Since that's not the case, we're back to "anyone's guess, except for the dev(s) responsible for it". (Personally, I'm not hugely surprised T-SQL contains stuff like this, and I suspect you've barely scratched the surface.) – Jeroen Mostert May 09 '18 at 14:10

0 Answers0