Transact SQL Command Syntax Reference - Professional SQL Server 2005 Reporting Services - page 620

Object Reference Use and Context

A four-part name is valid in the context of a remote server or the

server.database.schema

local server, in the local or a different database, and for any user or
schema.

.object

The database owner or schema in the third position can be omitted
to use the default dbo owner or schema.

server.database..object

The database name may be omitted to use the default database on
that server. This is a not a typical practice.

server..schema.object

Omitting the database and owner or schema name uses the default
database and the default dbo user or schema. This is not a typical
practice.

server...object

Transact-SQL Commands, Clauses, and
Predicates
Following are the core components of the Transact-SQL language. New commands for SQL Server 2005
are explicitly called out in this section.

WITH
This is a new method in SQL Server 2005 for defining an alias for the result set returned by a SELECT
expression.

WITH MyCTE
AS
( SELECT * FROM Product WHERE ListPrice < 1000 )

Optionally, column aliases can be defined in parentheses following the Common Table Expression (CTE)
name:

WITH MyCTE ( ID, ProdNumber, ProdName, Price )
AS
( SELECT
ProductID
, ProductNumber
, Name
, ListPrice
FROM Product WHERE ListPrice < 1000

)

SELECT
Return all columns from a table or view: