70-461 – notes from ch. 2 – table and alias naming

ground plan of belfry from Wilts Arch and Nat Hist MagSome notes from reading Chapter 2 of Querying Microsoft SQL Server 2012 (MCSA) by By Dejan Sarka, Itzik Ben-Gan, Ron Talmage

Tables and aliases

If you alias a table, then you can’t use the full table name. i.e. If you use the table name having defined the alias like so:

SELECT Employees.empid, firstname, lastname
FROM HR.Employees AS E;

…the error you get is:

Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "hr.employees.empid" could not be bound.

Aliasing in the select clause

The three forms of aliasing of expressions are:

SELECT e.firstname + ' ' + e.lastname as name
FROM HR.Employees E;

SELECT e.firstname + ‘ ‘ + e.lastname name
FROM HR.Employees E;

SELECT name = e.firstname + ‘ ‘ + e.lastname
FROM HR.Employees E;

Table naming – invalid first characters

For each of these:

create table aa.1tax_dodgers (name varchar)
create table aa.$tax_dodgers (name varchar)
create table aa.tax dodgers (name varchar)
create table aa.tax~dodgers (name varchar)
create table aa.fillfactor (name varchar)

…you get a similar-ish error message:

Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '.1'.
Msg 102, Level 15, State 1, Line 2
Incorrect syntax near '$tax_dodgers'.
Msg 102, Level 15, State 1, Line 3
Incorrect syntax near 'dodgers'.
Msg 102, Level 15, State 1, Line 4
Incorrect syntax near '~'.
Msg 156, Level 15, State 1, Line 5
Incorrect syntax near the keyword 'fillfactor'.

…although it’s worth noting that sql flags up slightly different bits of the prospective table name in the first four instances and it gives a different error message when you try to use the keyword.

If you enclose with square brackets:

create table aa.[1tax_dodgers] (name varchar)
create table aa.[$tax_dodgers] (name varchar)
create table aa.[tax dodgers] (name varchar)
create table aa.[tax~dodgers] (name varchar)
create table aa.[fillfactor] (name varchar)

….all the tables get created:

tables

Alias naming – invalid alias name

If, as in the book, you do this:

SELECT S.shipperid, companyname, phone AS phone number
FROM Sales.Shippers AS S;

The error you get is:

Msg 102, Level 15, State 1, Line 2
Incorrect syntax near 'number'.

Advertisements