T-SQL SELECT TOP returns duplicates

Question

I am using SQL Server 2008 R2.

I'm not sure if I discovered a strange SQL quirk, or (more likely) something in my code causes this strange behavior, especially since Google did not find anything. I have a view called vwResponsible_Office_Address.

SELECT * FROM vwResponsible_Office_Address 

.. returns 403 rows

This code:

 SELECT TOP 1000 * FROM vwResponsible_Office_Address 

.. returns 409 rows, since it includes 6 duplicates.

However, this:

 SELECT TOP 1000 * FROM vwResponsible_Office_Address ORDER BY ID 

.. returns 403 rows again.

I can post code for the view if it matters, but does it make sense for SELECT TOP to work this way? I understand that SELECT TOP can return records in any order, but I don’t understand why the number of returned records should change.

Does the view use cross apply, which might affect the result set in some way?

EDIT: View Definition on Demand

 CREATE VIEW [dbo].[vwResponsible_Office_Address] AS SELECT fp.Entity_ID [Reg_Office_Entity_ID], fp.Entity_Name [Reg_Office_Entity_Name], addr.Address_ID FROM [dbo].[Entity_Relationship] er INNER JOIN [dbo].[Entity] fp ON er.[Related_Entity_ID] = fp.[Entity_ID] INNER JOIN [dbo].[Entity_Address] ea ON ea.[Entity_ID] = fp.[Entity_ID] CROSS APPLY ( SELECT TOP 1 Address_ID FROM [dbo].[vwEntity_Address] vea WHERE [vea].[Entity_ID] = fp.Entity_ID ORDER by ea.[Address_Type_ID] ASC, ea.[Address_ID] DESC ) addr WHERE [Entity_Relationship_Type_ID] = 25 -- fee payment relationship UNION SELECT ets.[Entity_ID], ets.[Entity_Name], addr.[Address_ID] FROM dbo.[vwEntity_Entitlement_Status] ets INNER JOIN dbo.[Entity_Address] ea ON ea.[Entity_ID] = ets.[Entity_ID] CROSS APPLY ( SELECT TOP 1 [Address_ID] FROM [dbo].[vwEntity_Address] vea WHERE vea.[Entity_ID] = ets.[Entity_ID] ORDER by ea.[Address_Type_ID] ASC, ea.[Address_ID] DESC ) addr WHERE ets.[Entitlement_Type_ID] = 40 -- registered office AND ets.[Entitlement_Status_ID] = 11 -- active 

Answer 1

I would suggest that some non-determinism is occurring, which means that different access methods can return different results.

Looking at the definition of a view, the only place that seems likely would be if vwEntity_Address has some duplicates for Entity_ID .

This would make top 1 Address_ID returned arbitrary in this case, which would result in the result of the union operation when deleting duplicates.

It definitely looks very suspicious

 SELECT TOP 1 [Address_ID] FROM [dbo].[vwEntity_Address] vea WHERE vea.[Entity_ID] = ets.[Entity_ID] ORDER by ea.[Address_Type_ID] ASC, ea.[Address_ID] DESC 

You order values ​​from an external query in the cross. This will have absolutely no effect, as they will be persistent for a specific CROSS APPLY call.

Can you try switching to

 SELECT TOP 1 [Address_ID] FROM [dbo].[vwEntity_Address] vea WHERE vea.[Entity_ID] = ets.[Entity_ID] ORDER by vea.[Address_ID] DESC 

Answer 2

I was wondering if there is a function in your view until I get to the end where you say that you are using a cross-order. I would suggest that this is your problem, if you are interested in details, take a look at the various query plans.

EDIT: answer extension That is, your function is not deterministic and can return more than one line per input or return the same line for different input data. Combined, this means that you get exactly what you see: duplicate lines under some environments. Adding a clear view to your presentation is an expensive way to solve your problem, the best way would be to change your function so that only one line output is output for any input, and only one input is output for a line. This line.

EDIT: I have not seen that you now include a definition of your kind. Your problem, of course, is cross-application, in particular, you sort inside the cross, applying values ​​from the OUTSIDE of the cross, applying the top 1 effectively random.