Wednesday, March 26, 2014

70-461 Training Kit Chapter 2, Lesson 1: 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.

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
<expression> AS <alias>
<expression> <alias>
<alias> = <expression>

Standard SQL require a query to have FROM and SELECT clause
TSQL query can have SELECT clause without FROM clause
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)
"Employees this year", "2014 Employees", "2014"

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

Credits: Microsoft 70-461 Training Kit

No comments:

Post a Comment