Me neither, double checked the constraints and seems to be setup correctly. Think it's a bug in the query plan generation.

Thanks for your reply Grant,

I understand that normal parameter sniffing behavior is oke for most cases but sometimes it will result in bad performance when using partitioned views in...

@ChrisM@Work

I've checked it by making the database user sysadmin on every linked server (testing environment so dont worry 😛 ) and ran the script again. Even with sysadmin...

I did find a solution for the problem, I was using an SQL account for my linkedserver connections. When I changed it to use the "Be made using the current...

Jeff, I did use a 4 name naming convention in my setup (little mistake in my post but corrected that one). The problem still exists, I've also set the Lazy...

There are multiple ways you could do this:

1. CTE like the suggestion above.

2. Outer Apply function

3. Using an correlated subselect with an reference to the left table in your...

The manager ID should be related to an Employee ID to create the hierarchy chain. Please provide some good sampledata first 🙂

You could add the parameter to your select statement, for example:

SELECT [id],[description],(CASE WHEN @Parameter = ...etc ) AS [order]

FROM tablename

ORDER BY [order]

The logical instruction order of SQL (from ->...

You could do that with an apply:

SELECT T1.[key1],T1.[key2],T1.[type]

FROM tablename T1

OUTER APPLY (SELECT TOP 1 T2.[key1]

FROM tablename T2

WHERE T1.[type]...