Fun with DOT

The dot (.) is used to separate database name,table name and column name when you use JOINs. The simple example is

select cust.customer_name,sum(detail.total_spend) as total_spend from
customers as cust inner join sales as detail
on cust.customer_id=detail.customer_id
group by cust.customer_name

As you see dot is used to separate the table name and column name. But did you know you can use dot in some other ways too?

create table #test(number int)
insert into #test(number) select 400
select * from #test

The result is 10. But what surprises you is that you can use the dot in the following ways too

select * from .#test
select * from ..#test
select * from #test

The result is 10 for all the statements. Did you expect error? When dots are used without prefixed by objects, what happens is that

  • When a single dot is used, by default the current user is considered so it becomes username.tablename.
  • When two dots are used, by default the current database and user are considered so it becomes dbname.username.tablename.
  • When three dots are used, by default the current server, database and user are considered so it becomes servername.dbname.username.tablename

Now, what do you think the following return?

select * from .#test[………….]
select * from ..#test[….]
select * from #test[…]

The result is 10 for the above queries. The dots in square braces become the alias name for the table #test

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s