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 Saturday, May 11, 2013 11:44 AM


SSC-Dedicated

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

Group: Administrators
Last Login: 2 days ago @ 3:11 PM
Points: 31,368, Visits: 15,837
Comments posted to this topic are about the item INTO Identities






Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1451843
Posted Sunday, May 12, 2013 7:02 PM


SSC Eights!

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

Group: General Forum Members
Last Login: Thursday, December 4, 2014 7:54 PM
Points: 818, Visits: 662
I may be wrong here, but this bug could have been around even longer. SQL2008 doco http://msdn.microsoft.com/en-us/library/ms188029(v=sql.100).aspx also shows the same comment about the effect of GROUP BY clause on the 'Identity' property inheritence, but if I run the following code on our 2008 installation, the results of the final SELECT statement show a 1 in the 'is_identity' column of CustomerID for both objects.

create table #customer (CustomerID int identity, city int)

insert into #customer values (1),(2),(3),(4),(2),(3),(4),(3),(4),(4)

Select CustomerID, count(city) as City_Count
into #temp
from #customer group by CustomerID

select * from tempdb.sys.columns

I note that unless I provide a name for the aggregation, SQL2008 will complain that 'An object or column name is missing or empty. For SELECT INTO statements, verify each column has a name. For other statements, look for empty alias names. Aliases defined as "" or [] are not allowed. Change the alias to a valid name.'

From a 'usefulness' perspective, I also note that since CustomerID is an IDENTITY column, the results of the COUNT(city) aggregate should always be 1. Maybe the fact that aggregation against an Identity column has such limited usefulness has kept the bug hidden for all this time?
Post #1451957
Posted Sunday, May 12, 2013 11:45 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, February 6, 2014 4:15 AM
Points: 1,242, Visits: 1,546
Good Basic question. But i surprised that still 51% people are wrong.




Thanks
Vinay Kumar
-----------------------------------------------------------------
Keep Learning - Keep Growing !!!
www.GrowWithSql.com

Post #1451976
Posted Sunday, May 12, 2013 11:57 PM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Monday, December 8, 2014 11:26 PM
Points: 3,379, Visits: 534
This question is stated wrong, cont(...) does not have any alias for the name, so, while you will try to execute this code, you will get:

Msg 1038, Level 15, State 5, Line 1
An object or column name is missing or empty. For SELECT INTO statements, verify each column has a name. For other statements, look for empty alias names. Aliases defined as "" or [] are not allowed. Change the alias to a valid name.

My guess is that is why only ~50% percent people guessed correctly, the rest might assume that lack of an alias is the "tricky" part of this question...


Slawomir Swietoniowski, MCITP:DBA+Developer (2005/2008)
Post #1451977
Posted Monday, May 13, 2013 12:35 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, February 6, 2014 4:15 AM
Points: 1,242, Visits: 1,546
Slawomir Swietoniowski (5/12/2013)
This question is stated wrong, cont(...) does not have any alias for the name, so, while you will try to execute this code, you will get:

Msg 1038, Level 15, State 5, Line 1
An object or column name is missing or empty. For SELECT INTO statements, verify each column has a name. For other statements, look for empty alias names. Aliases defined as "" or [] are not allowed. Change the alias to a valid name.

My guess is that is why only ~50% percent people guessed correctly, the rest might assume that lack of an alias is the "tricky" part of this question...


I agree with you. I think this question missed one another option in answer, its "Error".
Question ask about copy identity property, For this answer is "YES", But Script contains error. Due to this, it's completely mess-up.
I think nobody check the question before published and everybody should get point for this.




Thanks
Vinay Kumar
-----------------------------------------------------------------
Keep Learning - Keep Growing !!!
www.GrowWithSql.com

Post #1451982
Posted Monday, May 13, 2013 1:17 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Thursday, November 20, 2014 4:29 AM
Points: 3,559, Visits: 2,671
I understand there was an error. But keeping an eye on the subject I ignored it (though it should have been rectified). I remembered it as my real time experience but that time it was into temporary table. So here I tried it for physical table and it gave same result. Didn't check BOL but looking at past comments seems to be a bug.

Nevertheless a good question and good start to week.
Post #1451989
Posted Monday, May 13, 2013 2:07 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, December 19, 2014 8:43 AM
Points: 280, Visits: 552
Good question to start the week off.

Given the available answers, the lack of an alias doesn't really matter, anyone reading the question and options to choose from should realise they'll need an alias if writing it out so it doesn't really affect the question at all.
Post #1451992
Posted Monday, May 13, 2013 2:45 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Friday, December 19, 2014 2:08 AM
Points: 1,808, Visits: 1,192
I agree this was obviously not a question about the error from lack of an alias. Interesting one which I did not know.
Post #1451997
Posted Monday, May 13, 2013 2:59 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 5:04 AM
Points: 13,641, Visits: 11,514
Danny Ocean (5/12/2013)
Good Basic question. But i surprised that still 51% people are wrong.


Yes, because I use INTO combined with a GROUP BY daily to create new tables...




How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1452006
Posted Monday, May 13, 2013 3:20 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, October 28, 2014 6:04 AM
Points: 1,127, Visits: 1,599
Slawomir Swietoniowski (5/12/2013)
This question is stated wrong, cont(...) does not have any alias for the name, so, while you will try to execute this code, you will get:

Msg 1038, Level 15, State 5, Line 1
An object or column name is missing or empty. For SELECT INTO statements, verify each column has a name. For other statements, look for empty alias names. Aliases defined as "" or [] are not allowed. Change the alias to a valid name.

My guess is that is why only ~50% percent people guessed correctly, the rest might assume that lack of an alias is the "tricky" part of this question...


+1.


Vinu Vijayan

For better and faster solutions please check..."How to post data/code on a forum to get the best help" - Jeff Moden
Post #1452018
« Prev Topic | Next Topic »

Add to briefcase 12345»»»

Permissions Expand / Collapse