In general, table expressions do not have a positive impact on performance or adversely affect performance.
Note the following code style:
SELECT COUNT (DISTINCT as numcusts from (SELECTyear(OrderDate), CustID from as D (OrderYear, CustID) GROUP by OrderYear;
Common-Table expressions
A common table expression (cte,common table expression) is defined with a with clause in the general format:
with as ( SELECT CustID, CompanyName from sales.customers WHERE= N'USA')SELECT* from Usacusts;
As with derived tables, the lifetime of the CTE ends when an external query is completed.
How the CTE allocates column aliases-inline format and external format, inline format:
with as ( SELECT year as OrderYear, CustID from sales.orders)SELECTCOUNT(DISTINCT as numcusts from CGROUP by OrderYear;
External format:
with as ( SELECTyear(OrderDate), CustID from sales.orders) SELECT COUNT (DISTINCT as numcusts from CGROUP by OrderYear;
Define multiple CTE:
withC1 as( SELECT Year(OrderDate) asOrderYear, CustID fromsales.orders), C2 as( SELECTOrderYear,COUNT(DISTINCTCustID asnumcusts fromC1GROUP byorderyear)SELECTOrderYear, Numcusts fromC2WHERENumcusts> -;
The above modular code greatly improves the readability and maintainability of the code compared to the nested derived table code.
View
Create a view:
UseTSQLFundamentals2008;IF object_id('sales.usacusts') is not NULL DROP VIEWsales.usacusts;GOCREATE VIEWsales.usacusts asSELECTCustID, CompanyName, ContactName, ContactTitle, address, city, region, PostalCode, country, phone, fax fromsales.customersWHERECountry=N'USA';GO
Remember that the ORDER BY clause is not allowed in query statements that define table expressions because the rows between the relational tables have no order. Attempting to create an ordered view is also unreasonable, and SQL Server will make an error. You should use the ORDER BY clause in an external query that uses views.
Inline table-valued functions
The following code creates an inline table-valued Function:
UseTSQLFundamentals2008;IF object_id('dbo.fn_getcustorders') is not NULL DROP FUNCTIONdbo.fn_getcustorders;GOCREATE FUNCTIONDbo.fn_getcustorders (@cid as INT)RETURNS TABLE asRETURN SELECTOrderID, CustID, Empid, OrderDate, RequiredDate, ShippedDate, ShipperID, freight, ShipName, shipaddress, ShipCity, ShipRegion, Shippostalcode, ShipCountry fromsales.ordersWHERECustID= @cid;GO
Use this function:
SELECT OrderID, CustID from Dbo.fn_getcustorders (1 as CO;
Summarize
Table expressions can simplify code, improve code maintainability, and encapsulate query logic. When you need to use table expressions, you use a derived table or a CTE if you do not plan to reuse their definitions, and you can use view and inline table-valued functions when you need to define reusable table expressions.
Note-microsoft SQL Server 2008 Tech Insider: T-SQL language Basics-05 table expressions