INTO Identities
-
SQLRNNR (5/13/2013)
just read the query for the intentthat's what I did.
I assumed the intent related to the need for a column alias
-
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?
-
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.
Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
Visit my SQL Server blog: https://sqlserverfast.com/blog/
SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/ -
First time poster here. I got it wrong, which is fine. When I answer a question, I try to answer without running the code. Sometimes I try to research BOL or ask a teammate, but when I read from the comments of 'You got it WRONG' and it specifies that the question is about buggy behavior instead of expected outcomes - specifically opposite of what's written in specs, it crosses a strange boundary for me. When the data shows you Correct answers: 27% (207)
Incorrect answers: 73% (565)
the crowd is guessing at best or answering like I did without running the code but running to BOL to check. I guess the 'rules' don't specify 'don't run the code'. I guess I should change my strategy to just run the code and watch what happens. Thanks for the question, but I don't think it was a good one.
/rant = off.
Thanks for exposing inconsistent behavior of Sql Server.
-
ammon.poage (5/13/2013)
First time poster here. I got it wrong, which is fine. When I answer a question, I try to answer without running the code. Sometimes I try to research BOL or ask a teammate, but when I read from the comments of 'You got it WRONG' and it specifies that the question is about buggy behavior instead of expected outcomes - specifically opposite of what's written in specs, it crosses a strange boundary for me. When the data shows you Correct answers: 27% (207)Incorrect answers: 73% (565)
the crowd is guessing at best or answering like I did without running the code but running to BOL to check. I guess the 'rules' don't specify 'don't run the code'. I guess I should change my strategy to just run the code and watch what happens. Thanks for the question, but I don't think it was a good one.
/rant = off.
Thanks for exposing inconsistent behavior of Sql Server.
These are the questions I find most valuable. Since this behavior is not expected or reflected in the documentation, the only other way to discover it is through experience... I'd much rather learn about it beforehand!
-
Good start to my Monday morning.. thank you.
Hope this helps...
Ford Fairlane
Rock and Roll Detective
-
Nice and easy one for me ,thank you.
“When I hear somebody sigh, ‘Life is hard,’ I am always tempted to ask, ‘Compared to what?’” - Sydney Harris
-
Nice one......
-
However in SQL Server 2012, this does not appear to be the case
So the answer is No.
-
I have a table, Customer, with the CustomerID as an identity. I run this query:
select
customerID, count(city) as 'City'
into CustomerCities
from Customer
group by CustomerID
Does the CustomerCities table have a column with the identity property?
Maybe. Doesn't have to have one. So I'm not sure how one would give a definitive answer.
Could just have two int columns.
Of maybe it's has an identity with Identity_Insert set to on.
IF the customer ID from Customer are higher then that in Custmercities then it may be an Identity.
-
garrett.moffitt (5/15/2013)
I have a table, Customer, with the CustomerID as an identity. I run this query:select
customerID, count(city) as 'City'
into CustomerCities
from Customer
group by CustomerID
Does the CustomerCities table have a column with the identity property?
Maybe. Doesn't have to have one. So I'm not sure how one would give a definitive answer.
Could just have two int columns.
Of maybe it's has an identity with Identity_Insert set to on.
IF the customer ID from Customer are higher then that in Custmercities then it may be an Identity
You don't understand the question. The "into CustomerCities" creates a new table. The creation process has to set an identity or not set it. This isn't something you control with this statement.
-
Nice question and good find of yet another error in BOL.
But the only reason I've ever grouped by an identity column was to find duplicates when some not very bright person had been misusing dbcc checkident, so I find this code very strange.
Tom
-
So finally From which version of SQL server, Identity property is copied into new table eventhough if the query has a GROUP BY clause.
Then next question is it the intended behaviour of sql server or will it be changed in later versions?
These questions might have answered in earlier discussions itself , but i missed in the crowd.
--
Dineshbabu
Desire to learn new things.. -
Dineshbabu (5/24/2013)
So finally From which version of SQL server, Identity property is copied into new table eventhough if the query has a GROUP BY clause.Then next question is it the intended behaviour of sql server or will it be changed in later versions?
These questions might have answered in earlier discussions itself , but i missed in the crowd.
Nothing in the earlier discussions as far as I remember.
Logically, if identity property of a column is preserved then that should be independent of whether that column is subject of a group by clause or not, so I don't expect it to change. But also I don't care whether it changes or not because I never expect to use select into with group by an identity column.
Tom
-
L' Eomot Inversé (5/24/2013)
Dineshbabu (5/24/2013)
So finally From which version of SQL server, Identity property is copied into new table eventhough if the query has a GROUP BY clause.Then next question is it the intended behaviour of sql server or will it be changed in later versions?
These questions might have answered in earlier discussions itself , but i missed in the crowd.
Nothing in the earlier discussions as far as I remember.
Logically, if identity property of a column is preserved then that should be independent of whether that column is subject of a group by clause or not, so I don't expect it to change. But also I don't care whether it changes or not because I never expect to use select into with group by an identity column.
Ok Tom..
Either it should be changed in BOL or in great SQL SERVER to avoid further confusions.
--
Dineshbabu
Desire to learn new things..
Viewing 15 posts - 31 through 44 (of 44 total)
You must be logged in to reply to this topic. Login to reply