Db4Breakfast: 70-461 Training Kit Chapter 2, Lesson 1: SELECT

SELECT

Not good practice to use SELECT * because

All columns will returned (I'v seen tables with 100+ columns)

Reduce performance

Table structure can be changed later.

Good practice to use schema name.

Example: Sales = schema, Orders = table

SELECT TOP(10) orderid, custid FROM Sales.Orders.

Alias

Once the assigned an alias to a table, the original table name is NOT visible for the duration of the query.

Example: this query will fail with error: The multi-part identifier "Orders.OrderId" could not be bound.

SELECT Orders.OrderId, CustId

FROM Sales.Orders AS O;

Good practice to use keyword AS to name alias - help improve code readability

SELECT empid, lastname + ', ' + firstname AS FullName

FROM HR.Employees;

Example: this query only return the OrderId column with alias Custid because missing a comma between OrderId and CustId.

SELECT OrderId CustId

FROM Sales.Orders;

Alias Forms

Standard SQL require a query to have FROM and SELECT clause

TSQL query can have SELECT clause without FROM clause

Example:

SELECT 2 * 4 AS value;

Regular Identifier rules:

First characters in [a-z] or [A-Z], underscore, at sign, number sign

Can't include TSQL reserved keyword

Can't have space

Can't have supplementary characters??

Example: table name that follow the regular identifier rules.

Employees, Employees2014, _Employees, @Employees, #Employees

Identifier doesn't follow the rules above must be delimited with double quotes(Standard) or brackets (TSQL specific)

Examples:

"Employees this year", "2014 Employees", "2014"

[Employees 2014], [2014Employees], [2014]

Credits: Microsoft 70-461 Training Kit

Db4Breakfast