Execute the following Microsoft SQL Server T-SQL scripts in SSMS Query Editor to create views with sequence number and the SalesBySalesPerson CTE.

-- View returning sequential integers - Sequence number view

CREATE VIEW vSequence AS

WITH cteNumber AS

 (SELECT Nbr=ROW_NUMBER() OVER(ORDER BY (SELECT 1))

  FROM master.dbo.spt_values a CROSS JOIN master.dbo.spt_values b)

SELECT SeqNo=Nbr FROM cteNumber

GO

SELECT TOP(10000) SeqNo from vSequence ORDER BY SeqNo

------------

-- SQL create view with CTE (Common Table Expression)

USE AdventureWorks

GO

CREATE VIEW vSalesStaffQuickStats

AS

  WITH SalesBySalesPerson (SalesPersonID, NumberOfOrders, MostRecentOrderDate)

      AS

      (

            SELECT SalesPersonID, COUNT(*), MAX(OrderDate)

            FROM Sales.SalesOrderHeader

            GROUP BY SalesPersonID

      )

  SELECT E.EmployeeID,

         EmployeeOrders = OS.NumberOfOrders,

         EmployeeLastOrderDate = OS.MostRecentOrderDate,

         E.ManagerID,

         ManagerOrders = OM.NumberOfOrders,

         ManagerLastOrderDate = OM.MostRecentOrderDate

  FROM   HumanResources.Employee AS E

       INNER JOIN SalesBySalesPerson AS OS

         ON E.EmployeeID = OS.SalesPersonID

       LEFT OUTER JOIN SalesBySalesPerson AS OM

         ON E.ManagerID = OM.SalesPersonID

GO

-- T-SQL test view

SELECT * FROM vSalesStaffQuickStats

ORDER BY EmployeeID

GO

/* Partial results

EmployeeID  EmployeeOrders    EmployeeLastOrderDate

268         48                2004-06-01 00:00:00.000

275         450               2004-06-01 00:00:00.000

276         418               2004-06-01 00:00:00.000

277         473               2004-06-01 00:00:00.000

*/

Related articles:

Using Common Table Expressions

WITH common_table_expression (Transact-SQL)

CREATE VIEW (Transact-SQL)