我应该使用 T-SQL 函数、视图还是存储过程？

Question

我有一个关于重用表数据的问题，但视图在这种情况下不起作用，因为我有一个需要传入的参数。基本上系统的这一部分需要一个 travellerid发送至程序，并为该特定旅客返回安排者列表。大约有 7 个业务规则用于确定可以返回哪些排列程序，并且它们是互斥的，因此为了适应这些可选规则，我在派生查询中使用了一系列 UNION。这运行良好，并且在相当大的数据库中性能似乎不错，但是我需要在系统的大约 4 个其他部分中重用这些规则（UNIONS）。

我最初尝试使用这些 UNION 创建一个 VIEW，但由于每个 UNION 中的逻辑不同和不同的参数要求，这不起作用，所以我在想也许一个函数可以解决这个问题？如果我创建一个函数，将 @travellerid 作为参数，并根据业务规则返回 arrangerid 列表，这会是一个理想/快速的解决方案吗？我目前在外部查询中使用 UNION ALL 和 DISTINCT，因为事实证明这比使用 UNION 的数据唯一性要快得多。

当前程序的业务规则如下 (SQL Server 2008)：

CREATE PROCEDURE [dbo].[getArrangersForTraveller]
   @travellerid int
AS
  DECLARE @costcentreid int
  DECLARE @departmentid int

-- Shorthand the traveller costcentre and department for use in queries below
SET @costcentreid = (SELECT costcentreid FROM traveller WHERE id = @travellerid)
SET @departmentid = (SELECT departmentid FROM traveller WHERE id = @travellerid)


SELECT DISTINCT t.id, t.firstname, t.lastname, ti.name AS title, dv.preferred
FROM traveller t

INNER JOIN title ti ON t.titleid = ti.id
     INNER JOIN

     (

            -- Get Preferred Arrangers linked to Department Groups
            SELECT dg.arrangerid as id
            FROM departmentGroup dg 
                INNER JOIN department_departmentGroup ddg 
                ON (dg.id = ddg.departmentGroupId AND ddg.departmentid = @departmentid)

            UNION ALL

            -- Get Preferred Arrangers linked to Cost Centre Groups
            SELECT cg.arrangerid as id
            FROM costCentreGroup cg 
                INNER JOIN costcentre_costCentreGroup ccg 
                ON (cg.id = ccg.costCentreGroupId AND ccg.costcentreid = @costcentreid)

            UNION ALL

            -- If Cost Centre Group has a linked department and this department matches 
            -- the travel arrangers department then return these travel arrangers as well     
            SELECT t3.id
            FROM costCentreGroup cg1

                INNER JOIN costcentre_costCentreGroup ccg1 
                ON (cg1.id = ccg1.costCentreGroupId AND ccg1.costcentreid = @costcentreid) 

                INNER JOIN traveller t3  
                ON t3.departmentid = cg1.departmentid    

            WHERE  t3.accesslevelid > 1       

            UNION ALL

            -- Get Direct linked travel arrangers      
            SELECT t1.travelarrangerid as id
            FROM   travelarranger_traveller t1
            WHERE  t1.travellerid = @travellerid

            UNION ALL

            -- Get Cost Centre linked arrangers
            SELECT tc.travelarrangerid as id
            FROM   travelArranger_costcentre tc 
            WHERE  tc.costcentreid = @costcentreid

            UNION ALL

            -- Get Department linked arrangers
            SELECT td.travelarrangerid
            FROM   travelArranger_department td 
            WHERE  td.departmentid = @departmentid

            UNION ALL

            -- Get Company flagged arrangers 
            SELECT t2.id
            FROM   traveller t2
                   INNER JOIN company c ON t2.companyid = c.id

            WHERE  t2.accesslevelid > 1       
            AND ((c.allowTravelArrangerDepartmentAccess = 1 AND t2.departmentid = @departmentid)
            OR  (c.allowTravelArrangerCostCentreAccess = 1 AND t2.costcentreid = @costcentreid))

     ) as dv ON dv.id = t.id

WHERE t.accessLevelid > 1 -- arranger or manager
AND t.isenabled = 1
ORDER BY dv.preferred DESC, t.lastname, t.firstname;

Answer 1

我最初尝试使用这些 UNION 创建一个 VIEW，但由于每个 UNION 中的逻辑不同以及不同的参数要求，这不起作用，所以我想也许一个 Function 可以解决这个问题？如果我创建一个函数，将 @travellerid 作为参数，并根据业务规则返回 arrayrid 列表，这会是一个理想/快速的解决方案吗？

您正在考虑过程/OO 编程，但 SQL 是基于 SET 的。
函数可以工作，但会确保当您将该函数用于决策标准等时不能使用索引。非物化视图仅稍好一些；在 SQL Server 中，可以选择使用索引视图（又名物化视图），但众所周知，它们受到限制。它违背了模块化编程概念，但是您越少尝试模块化它并且仅使用您实际需要的内容，SQL 的工作效果就越好。

我重新编写了您的查询，但注意到 dv.preferred 列在外部查询中引用，但在内部查询中不存在。由于 dv 是各种表格和数据的集合体。从逻辑上看，返回的 id 值在内部查询之外没有任何实际值，因为您需要知道该值来自哪个表。也就是说，

SELECT t.id, t.firstname, t.lastname, ti.name AS title /*, dv.preferred */
  FROM TRAVELLER t
  JOIN title ti ON t.titleid = ti.id
 WHERE (EXISTS(SELECT NULL -- Get Preferred Arrangers linked to Department Groups
                 FROM departmentGroup dg 
                 JOIN department_departmentGroup ddg ON ddg.departmentGroupId = dg.id 
                                                    AND ddg.departmentid = @departmentid
                WHERE dg.arrangerid = t.id)
    OR EXISTS(SELECT NULL -- Get Preferred Arrangers linked to Cost Centre Groups
                FROM costCentreGroup cg 
                JOIN costcentre_costCentreGroup ccg ON ccg.costCentreGroupId = cg.id 
                                                   AND ccg.costcentreid = @costcentreid
               WHERE cg.arrangerid = t.id)
    OR EXISTS(SELECT NULL -- If Cost Centre Group has a linked department and this department matches the travel arrangers department then return these travel arrangers as well     
                FROM costCentreGroup cg1
                JOIN costcentre_costCentreGroup ccg1 ON ccg1.costCentreGroupId = cg1.id 
                                                    AND ccg1.costcentreid = @costcentreid
                JOIN traveller t3 ON t3.departmentid = cg1.departmentid    
                                 AND  t3.accesslevelid > 1
               WHERE t3.id = t.id)
    OR EXISTS(SELECT NULL  -- Get Direct linked travel arrangers    
                FROM travelarranger_traveller t1
               WHERE t1.travellerid = @travellerid
                 AND t1.travelarrangerid = t.id)
    OR EXISTS(SELECT NULL -- Get Cost Centre linked arrangers
                FROM travelArranger_costcentre tc 
               WHERE tc.costcentreid = @costcentreid
                 AND tc.travelarrangerid = t.id)
    OR EXISTS(SELECT NULL -- Get Department linked arrangers
                FROM travelArranger_department td 
               WHERE td.departmentid = @departmentid
                 AND td.travelarrangerid = t.id)
    OR EXISTS(SELECT NULL -- Get Company flagged arrangers 
                FROM traveller t2
                JOIN company c ON t2.companyid = c.id
                              AND t2.accesslevelid > 1       
               WHERE (   (c.allowTravelArrangerDepartmentAccess = 1 AND t2.departmentid = @departmentid)
                      OR (c.allowTravelArrangerCostCentreAccess = 1 AND t2.costcentreid = @costcentreid))
                 AND t2.id = t.id))
   AND t.accessLevelid > 1 -- arranger or manager
   AND t.isenabled = 1
ORDER BY /*dv.preferred DESC,*/ t.lastname, t.firstname;

如果有多个子记录附加到父记录，则使用子查询（IN、EXISTS）将缓解使用联接带来的重复问题。