Friday, November 07, 2014

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...

No comments:

Post a Comment