Fun with dot

The symbol dot (.) plays a major role when you write queries. It is mainly used to speicify the columns for the respective tables when a join is used as you see in the following example

select
t1.col1,t1.col2,t2.col3,t2.col4
from
table1 as t1 inner join table1 as t2 on t1.col1=t2.col1

But sometimes the usage of dot may surprise you. Let us consider the following example

create table #test(i int)
insert into #test(i) select 10
select * from #test

As you see the result is 10. Now see what happens when you use the following queries

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

The result is 10 for all the queries. You may think to get an error when you see the queries

However what happens is
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

You will get an error if you use more than three dots

Read more: Beyond Relational

Posted via email from .NET Info