INTO Identities

  • Comments posted to this topic are about the item INTO Identities

  • 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? 😀

  • Good Basic question. But i surprised that still 51% people are wrong. :w00t:

    Thanks
    Vinay Kumar
    -----------------------------------------------------------------
    Keep Learning - Keep Growing !!!

  • 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)

  • 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 !!!

  • 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. 🙂

  • 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.

  • I agree this was obviously not a question about the error from lack of an alias. Interesting one which I did not know.

  • 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

  • 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[/url] 😉

  • 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 🙁

  • 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).

  • 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.

  • 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

  • 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