How does this create view code work?

Question

Ok, so here is example code for view. Second part with select RT.RoleID[...] seems clear to me. That's the part that's going to be displayed when the query will be run to use the view. first part is unclear though.

First two lines are standard as i understand, then goes the whole with section. Could someone explain it to me? Never seen with "something" as (select) formula

    CREATE VIEW [dbo].[sviw_System_MyPermissions_CurrentDomain]
AS  

WITH MyDomainRoles AS (
  SELECT RM.Domain, RM.RoleID
    FROM stbl_System_RolesMembersDomains AS RM WITH (NOLOCK)
    WHERE RM.Domain = (SELECT CurrentDomain FROM stbl_System_Users WITH (NOLOCK) WHERE Login = SUSER_SNAME())
      AND RM.Login = SUSER_SNAME()
)

SELECT RT.RoleID, RT.TableName, DR.Domain, RT.GrantUpdate, RT.GrantInsert, RT.GrantDelete
  FROM stbl_System_RolesTables AS RT WITH (NOLOCK) 
    JOIN MyDomainRoles AS DR ON RT.RoleID = DR.RoleID

GO

After your first edit, your question text no longer matches your sample code. — Shannon Severance, Aug 08 '17 at 19:30
Not to reinvent the wheel: https://stackoverflow.com/questions/4740748/when-to-use-common-table-expression-cte. a WITH (Common Table Expression (CTE)) block is like inline view/subquery which has been refactored out into it's own select. This is often done if the select must be executed multiple times to save performance and maintenance time. — xQbert, Aug 08 '17 at 19:37
-- Syntax for SQL Server and Azure SQL Database CREATE [ OR ALTER ] VIEW [ schema_name . ] view_name [ (column [ ,...n ] ) ] [ WITH [ ,...n ] ] AS select_statement [ WITH CHECK OPTION ] [ ; ] ::= { [ ENCRYPTION ] [ SCHEMABINDING ] [ VIEW_METADATA ] } — Channa, Sep 20 '19 at 20:12

Lukasz Szozda · Accepted Answer · 2017-08-08T19:25:52.807

It is called Common Table Expresion and basically your view is the same as:

CREATE VIEW vSalesStaffQuickStats
AS
 SELECT E.EmployeeID, 
         EmployeeOrders = OS.NumberOfOrders, 
         EmployeeLastOrderDate = OS.MostRecentOrderDate, 
         E.ManagerID, 
         ManagerOrders = OM.NumberOfOrders, 
         ManagerLastOrderDate = OM.MostRecentOrderDate 
  FROM   HumanResources.Employee AS E 
       INNER JOIN    (
            SELECT SalesPersonID, COUNT(*) NumberOfOrders
                  , MAX(OrderDate) MostRecentOrderDate
            FROM Sales.SalesOrderHeader
            GROUP BY SalesPersonID
      ) AS OS 
         ON E.EmployeeID = OS.SalesPersonID 
       LEFT OUTER JOIN    (
            SELECT SalesPersonID, COUNT(*) NumberOfOrders
                 , MAX(OrderDate) MostRecentOrderDate
            FROM Sales.SalesOrderHeader
            GROUP BY SalesPersonID
      ) AS OM 
         ON E.ManagerID = OM.SalesPersonID

As you see you could easily exchange it with subquery. But in your case you need do it twice (with CTE you do it only once).

EDIT:

With new query after update:

CREATE VIEW [dbo].[sviw_System_MyPermissions_CurrentDomain]
AS  
SELECT RT.RoleID, RT.TableName, DR.Domain, RT.GrantUpdate, RT.GrantInsert, RT.GrantDelete
FROM stbl_System_RolesTables AS RT WITH (NOLOCK) 
JOIN (
    SELECT RM.Domain, RM.RoleID
    FROM stbl_System_RolesMembersDomains AS RM WITH (NOLOCK)
    WHERE RM.Domain = (SELECT CurrentDomain FROM stbl_System_Users WITH (NOLOCK) WHERE Login = SUSER_SNAME())
      AND RM.Login = SUSER_SNAME()
) AS DR ON RT.RoleID = DR.RoleID

i see i made a mistake taking random example from internet. could u edit an answer using the query ill put in there in a moment? — NulisDefo, Aug 08 '17 at 19:22
So its like a function in other languages (php, c++) where i make a query i can reuse later by inserting function name it would seem — NulisDefo, Aug 08 '17 at 19:29
It's just syntactic sugar for embedded sub-selects. The performance is (almost) always the same. It just makes the code a lot more readable. It's not really like a 'function' because the scope of the CTE is only the very next command (SELECT/UPDATE/DELETE)... you can't reuse it for several SELECTs, for example. It's like creating a dynamic virtual table for the SELECT to operate on. — pmbAustin, Aug 08 '17 at 19:36

score 0 · Answer 2 · answered Aug 08 '17 at 20:21

The WITH keyword just refers to Common Table Expression ( read more here: https://learn.microsoft.com/en-us/sql/t-sql/queries/with-common-table-expression-transact-sql ) Basically everything within the WITH statement could be considered as externalized subquery which result set is then used in the main query.

i.e.:

WITH MyDomainRoles AS (

  --- sub-query Start
  SELECT RM.Domain, RM.RoleID
    FROM stbl_System_RolesMembersDomains AS RM WITH (NOLOCK)
    WHERE RM.Domain = (SELECT CurrentDomain FROM stbl_System_Users WITH (NOLOCK) WHERE Login = SUSER_SNAME())
      AND RM.Login = SUSER_SNAME()
  --- sub-query End

)

... and the result set from the sub-query above is held within the CTE named MyDomainRoles. Then you can refer to MyDomainRoles as to a table. Makes things simpler to read and cleaner to write.

Since this CTE is so simple, you could easily re-write it as follows but it just does not look as neat:

WITH MyDomainRoles AS (

)

SELECT RT.RoleID, RT.TableName, DR.Domain, RT.GrantUpdate, RT.GrantInsert, RT.GrantDelete
  FROM stbl_System_RolesTables AS RT WITH (NOLOCK) 
    JOIN (

SELECT RM.Domain, RM.RoleID
    FROM stbl_System_RolesMembersDomains AS RM WITH (NOLOCK)
    WHERE RM.Domain = (SELECT CurrentDomain FROM stbl_System_Users WITH (NOLOCK) WHERE Login = SUSER_SNAME())
      AND RM.Login = SUSER_SNAME()

) AS DR ON RT.RoleID = DR.RoleID

How does this create view code work?

2 Answers2