Note-microsoft SQL Server 2008 Tech Insider: T-SQL language Basics-05 table expressions

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