• 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?