Mike Dougherty-384281 (5/13/2013)
Why should identity column promotion ever happen?Ok, I understand that creating a table on-the-fly with an identity column might be pretty useful for further query against its clustered index, etc.
My question though, is why should that be assumed in any way by the context of the query?
Wouldn't it be more clear to have an extension on the INTO clause?
[ex: ... into table with( index_on(ixexpr,ixname) ) ]
I feel like ambiguity is the bane of a DBA, so why use context-dependent SQL? I would rather get nothing I didn't ask for, and get exactly what I DO ask for.
thoughts?
I titally agree, Mike. In fact, I got the question wrong - not because of the missing column alias, not because I was unaware of the undocumented behaviour with GROUP BY, but because I expected an IDENTITY to never propagate on INSERT INTO.
It makes no sense. Computed columns don't propagate. Sparse columns don't propagate. Indexes don't propagate, Constraints don't propagate. So why the weird exception for the IDENTITY property?
As you said - statements should do exactly what you ask, nothing more. And especially not this kind of weird, inconsistent situation where one property is copied and the rest isn't.