Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««12345»»

INTO Identities Expand / Collapse
Author
Message
Posted Monday, May 13, 2013 10:18 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: 2 days ago @ 9:53 AM
Points: 1,713, Visits: 6,243
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
Post #1452206
Posted Monday, May 13, 2013 10:56 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, June 26, 2014 7:28 AM
Points: 257, Visits: 902
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?
Post #1452226
Posted Monday, May 13, 2013 11:29 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:45 AM
Points: 5,916, Visits: 8,169
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
Post #1452244
Posted Monday, May 13, 2013 1:04 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, April 24, 2014 5:47 PM
Points: 232, Visits: 99
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.
Post #1452282
Posted Monday, May 13, 2013 2:02 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 12:03 PM
Points: 1,277, Visits: 2,208
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!
Post #1452307
Posted Monday, May 13, 2013 9:45 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 6:58 PM
Points: 1,676, Visits: 750
Good start to my Monday morning.. thank you.

Hope this helps...

Ford Fairlane
Rock and Roll Detective





Post #1452388
Posted Tuesday, May 14, 2013 3:50 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Monday, May 19, 2014 2:24 AM
Points: 952, Visits: 1,156
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
Post #1452471
Posted Tuesday, May 14, 2013 4:05 AM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Monday, June 30, 2014 10:51 PM
Points: 758, Visits: 145
Nice one......
Post #1452479
Posted Tuesday, May 14, 2013 2:29 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, July 24, 2014 2:28 PM
Points: 1,393, Visits: 477
However in SQL Server 2012, this does not appear to be the case

So the answer is No.
Post #1452833
Posted Wednesday, May 15, 2013 12:19 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, May 15, 2013 12:19 AM
Points: 1, Visits: 1

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.




Post #1452933
« Prev Topic | Next Topic »

Add to briefcase «««12345»»

Permissions Expand / Collapse