• Rick Harker (5/21/2015)


    Shouldn't the subqueries contain a TOP 1 ?

    No. It would change the meaning of the queries. As posted, they implement "give me the department ID for Human Resource and make sure to throw a tantrum if there is a data quality issue". Adding TOP 1 changes this to "give me a department ID for Human Resource and if there is a data quality issue, pretend you never noticed and definitely don't tell anyone".

    I know which version I prefer. (Allthough in reality, I would (a) not use a subquery for this anyway, but rather a join; and (b) have declared a UNIQUE constraint on the Name column in the Department table to prevent data quality issues.)


    Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
    Visit my SQL Server blog: https://sqlserverfast.com/blog/
    SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/