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 Wednesday, May 15, 2013 8:48 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Yesterday @ 1:49 PM
Points: 32,768, Visits: 14,929
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.







Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1453148
Posted Friday, May 17, 2013 5:37 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: 2 days ago @ 4:56 PM
Points: 8,271, Visits: 8,717
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
Post #1453944
Posted Friday, May 24, 2013 6:22 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: Yesterday @ 5:16 AM
Points: 905, Visits: 553
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..
Post #1456437
Posted Friday, May 24, 2013 8:01 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: 2 days ago @ 4:56 PM
Points: 8,271, Visits: 8,717
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
Post #1456518
Posted Wednesday, May 29, 2013 1:13 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: Yesterday @ 5:16 AM
Points: 905, Visits: 553
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..
Post #1457600
« Prev Topic | Next Topic »

Add to briefcase «««12345

Permissions Expand / Collapse