May 11, 2013 at 11:44 am
Comments posted to this topic are about the item INTO Identities
May 12, 2013 at 7:02 pm
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? 😀
May 12, 2013 at 11:45 pm
Good Basic question. But i surprised that still 51% people are wrong. :w00t:
Thanks
Vinay Kumar
-----------------------------------------------------------------
Keep Learning - Keep Growing !!!
May 12, 2013 at 11:57 pm
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)
May 13, 2013 at 12:35 am
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 !!!
May 13, 2013 at 1:17 am
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. 🙂
May 13, 2013 at 2:07 am
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.
May 13, 2013 at 2:45 am
I agree this was obviously not a question about the error from lack of an alias. Interesting one which I did not know.
May 13, 2013 at 2:59 am
Danny Ocean (5/12/2013)
Good Basic question. But i surprised that still 51% people are wrong. :w00t:
Yes, because I use INTO combined with a GROUP BY daily to create new tables... :rolleyes:
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
May 13, 2013 at 3:20 am
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.
May 13, 2013 at 3:22 am
I wrongly assumed the point of the question was that the table wouldn't have an identity column because it wouldn't be created at all due to the lack of a column alias 🙁
May 13, 2013 at 3:27 am
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 here too. As far as I know you should be able to copy the query and run it.
But something I found out, which was more interesting, is that when you use a table from a linked server, that it won't create a field with the identity property, even not when the linked server is that instance itself (so a four part name to the current server).
May 13, 2013 at 3:45 am
There is a syntax error in the select statement.
I assumed this was another one of those "trick" questions.
If you're going to submit a QOTD, you do need to make sure
your question is correct and your syntax is valid.
May 13, 2013 at 5:01 am
Toni-256719 (5/13/2013)
There is a syntax error in the select statement.I assumed this was another one of those "trick" questions.
If you're going to submit a QOTD, you do need to make sure
your question is correct and your syntax is valid.
This was just Steve's method for having his code consume fewer resources. 😉
[font="Verdana"]Please don't go. The drones need you. They look up to you.[/font]
Connect to me on LinkedIn
May 13, 2013 at 7:00 am
This was removed by the editor as SPAM
Viewing 15 posts - 1 through 15 (of 44 total)
You must be logged in to reply to this topic. Login to reply