Help with Alias rules - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Help with Alias rules

Author

Topic

craigwg
Posting Yak Master

154 Posts

Posted - 2010-05-14 : 16:55:14

Hi,

My boss and I were having a discussion about aliases in a join. I have used numbers as aliases in the past. For example:


Select Mickey, Mouse
from this_table 1
join another_table 2 on 1.id=2.id

I say that will work just fine. He disagrees. We tried it on a table and it failed. I was joining three tables and used the aliases as 0, 1 and 2.

I just ran this query with success though:

select  OBJECT_NAME(l.resource_associated_entity_id) LockedObj
,       s.status
,       s.login_name 
from    sys.dm_tran_locks l 
inner   join sys.dm_exec_sessions s 
on      l.request_session_id = s.session_id 
where   l.resource_type = 'object'

So, who is right? Can we join using digits as aliases or not?

Thanks

Craig Greenwood

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-05-14 : 17:01:11

No.

No, you're never too old to Yak'n'Roll if you're too young to die.

craigwg
Posting Yak Master

154 Posts

Posted - 2010-05-14 : 17:04:14

Fred, your answer was brief. If "no" means that you can't alias using digits then why does the following query work:


select  OBJECT_NAME(l.resource_associated_entity_id) LockedObj
,       s.status
,       s.login_name 
from    sys.dm_tran_locks l 
inner   join sys.dm_exec_sessions s 
on      l.request_session_id = s.session_id 
where   l.resource_type = 'object'

Craig Greenwood

craigwg
Posting Yak Master

154 Posts

Posted - 2010-05-14 : 17:07:40

DOH! Because mine has an L, not a 1. Curse the latin alphabet!

Craig Greenwood

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-05-14 : 17:09:40

quote:
Originally posted by craigwg

Fred, your answer was brief. If "no" means that you can't alias using digits then why does the following query work:

select OBJECT_NAME(l.resource_associated_entity_id) LockedObj
, s.status
, s.login_name
from sys.dm_tran_locks l
inner join sys.dm_exec_sessions s
on l.request_session_id = s.session_id
where l.resource_type = 'object'

Craig Greenwood

"l" and "s"....where are the digits you are talking about?

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-05-14 : 17:09:51

yes.

No, you're never too old to Yak'n'Roll if you're too young to die.

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-05-14 : 17:10:24

Ohh...you figured that was an "L"...

...

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-05-14 : 17:55:03

Yes and No...

Per BOL with regards to Rules for Regular Identifiers:
When identifiers are used in Transact-SQL statements, the identifiers that do not comply with these rules (The rules for regular identifiers that I did not include) must be delimited by double quotation marks or brackets.

SO if you ignore the rules of regular identifiers and bracket them you can:

DECLARE @Foo TABLE (ID INT)

SELECT *
FROM @Foo AS [1]
INNER JOIN @Foo AS [2]
ON [1].ID = [2].ID

I'd agree with your boss that you can't without delimiting the alias and thus you shouldn't.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources