Why are dot-separated prefixes ignored in the column list for INSERT statements?

Question

I just came across SQL syntax, which I thought was invalid but actually works fine (in SQL Server at least).

Given this table:

create table SomeTable (FirstColumn int, SecondColumn int) 

The following insert is executed without error:

 insert SomeTable(Any.Text.Here.FirstColumn, It.Does.Not.Matter.What.SecondColumn) values (1,2); 

The insert statement completes without errors, and checking select * from SomeTable shows that it actually performed correctly. See the script: http://sqlfiddle.com/#!6/18de0/2

SQL Server seems to just ignore everything except the last part of the column name specified in the insert list.

Actual question:

Can one rely on this as documentary behavior?

Any explanation of why this is so will also be appreciated.

Answer 1

This was reported as a bug in Connect and, despite the initially encouraging comments, the current status of the item is closed as "will not fix it."

The Order by clause is used to behave in a similar fashion, but it was fixed in SQL Server 2005.

Answer 2

This is unlikely to be part of the SQL standard, given its dubious usefulness (although I have not specifically tested ^(a) ).

Most likely, this is that it discards the non-final part of the column specification, because it is superfluous. You have explicitly indicated which table you are inserting, with the insert into SomeTable command, and this will be the table to be used.

What you must have done here is to find a way to execute SQL statements that are less readable but have no real advantage. In this vein, it looks like C code:

 int nine = 9; int eight = 8; xyzzy = xyzzy + nine - eight; 

which could be better written as xyzzy++; :-)

I would not rely on it at all, perhaps because it is not standard, but mainly because it simplifies maintenance and not simpler, and because I know that database administrators around the world will track me and surpass me with IBM DB2 guides, their weapon choice due to volumetric size and ability to shake the skull :-)

---

^(a) I checked non-specifically, at least for ISO 9075-2: 2003, which dictates the SQL03 language.

Section 14.8 this standard covers the insert , and it seems that the following sentence may be relevant:

Each column name in the insert-column list should identify the updated column T.

Without spending a huge amount of time (this document is 1332 pages and takes several days to properly digest), I suspect that you can claim that a column can only be identified using the end of the column name (by deleting all owner / user / schema specifications )

Moreover, there is only one target table (updated views, despite the boundaries of the tables):

 <insertion target> ::= <table name> 

Fair warning: I did not check later iterations of the standard, so everything could change. But I would think that this is unlikely, since there seems to be no real use case for this function.

Answer 3

I get errors when trying to run a script on SQL Server 2012, as well as SQL Server 2014 and SQL Server 2008 R2. Therefore, you can not rely on the behavior that you see with sqlfiddle.

Even if this works, I will never rely on undocumented behavior in production code. Microsoft will include a change notification with documented features, but not undocumented. Therefore, if it was an actual T-SQL parsing error that was later fixed, it would violate the wrong code.