How to know where AS keyword should be used ?

Question

Hi everyone,

There is a AS keyword in TSQL but in what situations I should use it ?

For example : Create View StatisticalData AS Select * from People

We used AS keyword in this statement but when creating tables we don't use it , I mean I am kinda confused.

Could you please tell me, in what kinda position I should use AS. I mean it is used to assign value to a variable?

Thanks in advance.

Answer 1

AS is simply telling SQL that you want to name or type the item before as the name or statment after for example.

SUM(A + B) AS MyTotal

CREATE View MyView Column1, Column2 AS SELECT * From TABLE

SELECT MyColumn(s) FROM table_name AS MyAlias

So basically, AS just casts the item before as the item after, being an alias.

See http://www.w3schools.com/SQl/sql%5Falias.asp for a better explanation than I could give.

and many more examples.

Regards RE

Answer 2

It's part of the statement in that case defining what to declare the View 'as'.

In other cases, where you can use the AS statement in a SELECT statement on either a field or table, it's option.

Answer 3

The as keyword is used when you create or alter views, procedures, functions and triggers.

Example:

create procedure Test
as
select 1

The keyword is also used with a different meaning to create aliases for tables and fields in a query.

Example:

select u.usnm as UserName, u.pwd as UserPassword
from applicationusertable as u

Answer 4

Main uses:

• Aliases for tables and columns
• Between CREATE and definition
• CAST AS newtype

Examples

SELECT
   foo AS tom,
   foo + bar AS dick,
   CAST(bar AS varchar(50)) AS harry
FROM
   fizz AS f


CREATE VIEW/PROC/FUNCTION etc
AS
... proc or view of udf etc definition
GO

Answer 5

The AS keyword is primarily used to create aliases (view, table, column, etc.).

View
Your example works fine. The View name you specified is an alias for the query that follows.

Table

select * from Table1 as t
inner join Table2 as t2
    on t.id = t2.tid

Column

--will return a column called sum with a single row of 3
select 1+2 as sum

Answer 6

When you use the CREATE TABLE or VIEW statement, the AS keyword pulls in the records from the statement following, to fill the Table or View with initially.

So your example View will start as an exact copy of the People table.

This allows you choose the fields that you want to load into your Table or View.

Answer 7

AS is used all over the place:

CAST('1' AS INT)

SELECT CAST('1' AS INT) AS Col1

WITH ThisCTE AS (SELECT CAST('1' AS INT) AS Col1) 
SELECT Col1 FROM ThisCTE AS a

CREATE VIEW ThisView AS 
  WITH ThisCTE AS (SELECT CAST('1' AS INT) AS Col1) 
  SELECT Col1 FROM ThisCTE AS a

etc ad nauseum.

Bite the bullet, open up BOL, and start reading!

Answer 8

The AS keyword basically means that you want to 'alias' someting to something else.

The most comman being table anmes and field names...

SELECT
   [p].fn AS FirstName
FROM
   people AS [p]

Line 2 aliases the field [fn] to the new name [FirstName]. Line 4 aliases the table [people] to the new name [p].

In your view example, and for Stored Procedures, etc, the AS is saying treat this AS . So when you use the name, the database engine treats it AS the code yuou just wrote...

SQL was once referred to as "Structured English Query Language". It's all trying to make the programatic syntax as similar as possible to English syntax.