SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


INTO Identities


INTO Identities

Author
Message
Toreador
Toreador
SSCrazy
SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)

Group: General Forum Members
Points: 2775 Visits: 8084
SQLRNNR (5/13/2013)
just read the query for the intent


that's what I did.
I assumed the intent related to the need for a column alias
Mike Dougherty-384281
Mike Dougherty-384281
SSC-Addicted
SSC-Addicted (460 reputation)SSC-Addicted (460 reputation)SSC-Addicted (460 reputation)SSC-Addicted (460 reputation)SSC-Addicted (460 reputation)SSC-Addicted (460 reputation)SSC-Addicted (460 reputation)SSC-Addicted (460 reputation)

Group: General Forum Members
Points: 460 Visits: 944
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?
Hugo Kornelis
Hugo Kornelis
SSChampion
SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)

Group: General Forum Members
Points: 11020 Visits: 11994
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 MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
ammon.poage
ammon.poage
SSC Veteran
SSC Veteran (244 reputation)SSC Veteran (244 reputation)SSC Veteran (244 reputation)SSC Veteran (244 reputation)SSC Veteran (244 reputation)SSC Veteran (244 reputation)SSC Veteran (244 reputation)SSC Veteran (244 reputation)

Group: General Forum Members
Points: 244 Visits: 104
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.
sestell1
sestell1
SSCrazy
SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)

Group: General Forum Members
Points: 2545 Visits: 3462
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!
Ford Fairlane
Ford Fairlane
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2218 Visits: 836
Good start to my Monday morning.. thank you.

Hope this helps...

Ford Fairlane
Rock and Roll Detective





kapfundestanley
kapfundestanley
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1153 Visits: 1228
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
Bangla
Bangla
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1697 Visits: 180
Nice one......
Mike Is Here
Mike Is Here
SSCommitted
SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)

Group: General Forum Members
Points: 1532 Visits: 513
However in SQL Server 2012, this does not appear to be the case

So the answer is No.
garrett.moffitt
garrett.moffitt
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
Points: 1 Visits: 4
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search