Getting Started with Adobe After Effects - Part 6: Motion Blur


Upload Image Close it
Select File

Browse by Tags · View All
sql_server 217
t-sql 211
tsql 116
sqlserver 96
BRH 78
#SQLServer 66
#TSQL 56
SQL Server 34
function 11
SSMS 9

Archive · View All
August 2007 17
August 2010 8
June 2012 7
June 2011 7
November 2007 7
August 2012 6
May 2012 6
November 2011 6
August 2011 6
October 2011 6

Madhivanan's TSQL Blog

SQL Server - Having fun with DOT while writing TSQL code

Jul 12 2010 6:15AM by Madhivanan   

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

Consider another example queries

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

All will return the value 10

Here what happens is the dots between square braces become table alias for #test
Now all the following will work correctly

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

Consider another example

select 10.35

The result is 10.35 Now see what happens when try this

select 10.test

You must have expected an error incorrect syntax near '.' But it wont throw error and the result is

test
-----
10

So the parser ignores the dot when it is succeeded by non numbers and make it as a alias name
Now execute this and see the error

select 10 .test

The error message is

Msg 258, Level 15, State 1, Line 1
Cannot call methods on int.

I think one would have never come across such an error

Tags: t-sql, sql_server, tsql, BRH, dot, #TSQL,


Madhivanan
3 · 40% · 12924
3
 
0
Lifesaver
 
0
Refreshed
 
0
Learned
 
0
Incorrect



Submit

2  Comments  

  • This is really interesting. Good one Madhivanan!

    commented on Jul 31 2010 8:25AM
    Dattatrey Sindol (Datta)
    42 · 4% · 1336
  • Thanks Dattatrey Sindol

    commented on Aug 4 2010 3:57AM
    Madhivanan
    3 · 40% · 12924

Your Comment


Sign Up or Login to post a comment.

"SQL Server - Having fun with DOT while writing TSQL code" rated 5 out of 5 by 3 readers
SQL Server - Having fun with DOT while writing TSQL code , 5.0 out of 5 based on 3 ratings
    Copyright © Rivera Informatic Private Ltd Contact us      Privacy Policy      Terms of use      Report Abuse      Advertising      [ZULU1097]