T-SQL – Get customized row when it exists

We have a table where we keep rows with vanilla settings for products, and customized rows per product per customer as they see fit to override. When a customer does not override, the vanilla rows with vanilla settings are selected but when customized rows exists for products and customers, the customized rows must be returned and the vanilla rows thrown out.

I have the sample code and my solution using ROW_NUMBER() to guide the row selection. This ROW_NUMBER seems to make our query run longer so I am asking if anyone see better options to achieve the same results.

Thank you!

IF OBJECT_ID('tempdb..#TItems') IS NOT NULL
DROP TABLE #TItems
IF OBJECT_ID('tempdb..#TAttrib') IS NOT NULL
DROP TABLE #TAttrib

CREATE TABLE #TItems (ItemID int, Name varchar(50))
CREATE TABLE #TAttrib (AttribID  int, ClinicID int , ItemID int, AutoReorder bit, isFavorite bit, IsControlled bit )

Insert into #TItems (ItemId, Name) 
Select 1 as ItemID, 'Item1' as Name UNION
Select 2 as ItemID, 'Item2' as Name UNION
Select 3 as ItemID, 'Item3' as Name ;

INSERT INTO #TAttrib (AttribID , ClinicID, ItemID, AutoReorder , isFavorite , IsControlled  )
SELECT 10, NULL, 1,1,1,1 UNION
SELECT 20, NULL, 2,1,1,1 UNION
SELECT 30, NULL, 3,1,1,1 UNION
SELECT 40, 200, 1, 0,0,1 UNION
SELECT 50, 500, 2,1,0,0  UNION
SELECT 60, 500, 3,1,1,0  ;


-- Clinics 100, 300, 400 do not have Attrib.  They will use the vanilla attrib rows for items 1-3.
-- Clinic 200 has customized item 1, clinic 500 has customized item 3.
-- expected Attrib result set for clinic 100: AttribIDs 10, 20, 30
-- expected Attrib result set for clinic 200: AttribIDs  40, 20, 30
-- expected Attrib result set for clinic 500: AttribIDs  10, 50, 60

Declare @ClinicID int = 500
SELECT * FROM
(
SELECT AttribID, ClinicID , ItemID, ROW_NUMBER() OVER (
        PARTITION BY ItemID ORDER BY ClinicID DESC
        ) RN FROM #TAttrib
where ClinicID is NULL or ClinicID = @ClinicID ) RS
WHERE RN = 1

Answer

Try using a correlated subquery and union all:

select a.*
from #TAttrib a
where a.ClinicId = (select max(a2.ClinicId)
                    from #TAttrib a2
                    where a2.ItemID = a.ItemId
                   )
union all
select a.*
from #TAttrib a
where not exists (select 1
                  from #TAttrib a2
                  where a2.ItemID = a.ItemId and
                        a2.ClinicId is not null
                 );

The first gets the clinicid when there is a non-NULL value. The second gets the rest.

Now, very importantly, you want an index on #TAttrib(ItemID, ClinicID) on the able for these queries:

create index idx_tattrib_itemid_clinicid on #TAttrib(ItemID, ClinicID);

This would also benefit your query.