Using Common Table Expressions

Common Table Expressions
- a temporary resultset that can be defined within the execution scope of a single SELECT/INSERT/UPDATE/DELETE/CREATE VIEW.
- not stored as an object and lasts for the duration of the query
- self-referencing; can be referenced multiple times within the same query

WITH <CTE_NAME>[columns...]
AS
(
<Query Definition>
)
SELECT <column list>
FROM <CTE_NAME>

Using a CTE offers the advantages of improved readability and ease in maintenance of complex queries.

CTEs can be defined in user-defined routines, such as functions, stored procedures, triggers, or views.

Structure of a CTE
A CTE is made up of an expression name representing the CTE, an optional column list, and a query defining the CTE.

WITH Sales_CTE(SalesPId, NumOrders, MaxDate)
AS
(
SELECT SalesPersonID, Count(*), Max(OrderDate)
FROM Sales.SalesOrderHeader
GROUP BY SalesPersonID

)
SELECT E.EID, OS.NumOrders, OS.MaxDate, E.MgrID,
OM.NumOrders, OM.MaxDate
FROM HR.Employee E
JOIN Sales_CTE OS ON E.EID = OS.SalesPId
LEFT OUTER JOIN Sales_CTE OM ON E.MgrID = OM.SalesPId
ORDER BY E.EID

Source

MS SQL Server

Temporary tables vs. table variables¹

Normal tables = physical tables

Local Temp tables - available to the session that created them. #LOCALTEMP

CREATE TABLE #TibetanYaks(
  YakID int,
  YakName char(30)
)

Global Temp tables - available to all users and sessions. Rarely used.

CREATE TABLE ##TibetanYaks(
  YakID int,
  YakName char(30)
)

Table variables - stored part memory, part disk. Access time faster than a temp table

DECLARE @TibetanYaks TABLE (
  YakID int,
  YakName char(30)
)

Which command using Query Analyzer will give you the version of SQL Server and Operating System?
@@VERSION
Returns version, processor architecture, build date, and operating system for the current installation of SQL Server.³

¹ SearchSQLServer.com

² SqlTeam.com

³ dev.fyicenter.com

SQL Server Indexes

Clustered index
- reorders the way records in the table are physically stored
- a table can have only one clustered index
- the leaf nodes of a contain the data pages.

Nonclustered index
- the logical order of the index does not match the physical stored order of the rows on disk
- the leaf nodes of a nonclustered index do not consist of the data pages. Instead, they contain index rows.

Sources: FAQ: clustered vs nonclustered indexes