TSQL Tutorial: Select Continued..

Column Lists

SELECT * returns all the columns in a table. To return a subset of a table's columns, use a comma

Delimited field list, like so:

SELECT CustomerNumber, LastName, State FROM customers

CustomerNumber LastName State

-------------- -------- -----

1 Doe TX

2 Doe TX

3 Citizen CA

A SELECT's column can include column references, local variables, absolute values, functions, and

expressions involving any combinations of these elements.

SELECTing Variables and Expressions

Unlike most SQL dialects, the FROM clause is optional in Transact-SQL when not querying database objects.

You can issue SELECT statements that return variables (automatic or local), functions, constants, and

computations without using a FROM clause. For example,

SELECT GETDATE()

returns the system date on the computer hosting SQL Server, and

SELECT CAST(10+1 AS

CHAR(2))+'/'+CAST(POWER(2,5)-5 AS CHAR(2))+'/19'+CAST(30+31 AS

CHAR(2))

returns a simple string. Unlike Oracle and many other DBMSs, SQL Server doesn't force the inclusion of a

FROM clause if it makes no sense to do so. Here's an example that returns an automatic variable:

SELECT @@VERSION

And here's one that returns the current user name:

SELECT SUSER_SNAME()

@@VERSION is an automatic variable that's predefined by SQL Server and read-only. The SQL Server

Books Online now refers to these variables as functions, but they aren't functions in the true sense of the

word—they're predefined constants or automatic variables (e.g., they can be used as parameters to stored

procedures, but true functions cannot). I like variable better than constant because the values they return

can change throughout a session—they aren't really constant, they're just read-only as far as the user is

concerned.

TSQL Tutorial...