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