THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

Kalen Delaney

Did you know? -- Syntax relaxation

One of the things I want to use this blog for is to post details of system changes that are not well publicized, some of which I only learn about accidentally.

In SQL 2000, the first time I saw this strange syntax to use :: to specify a system defined, table valued function, I thought it was a typo. But now, it was required for a small handful of functions. The most common ones I used were the ones that dealt with traces, e.g.

SELECT * FROM ::fn_trace_getinfo(default)


SELECT * FROM ::fn_trace_gettable(<full path>, default)

In the SQL 2005 Internals class I was teaching last week, I was trying to point how how weird this syntax was, and that it was required. But when I tried to show the class the error message that I would get if I didn't use the ::, there was none. The strange :: syntax still works, but is no longer required.


The following work just fine in SQL Server 2005:

SELECT * FROM fn_trace_getinfo(default)


SELECT * FROM fn_trace_gettable(<full path>, default)

-- Kalen


Published Wednesday, September 6, 2006 2:34 PM by Kalen Delaney

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS



Denis The SQL Menace said:

And of course

select * from ::fn_helpcollations()

And somehow if you put 15 spaces between the () and fn_helpcollations it still works

--I am sure only one space will be shown, don't know if I can use the PRE tag
select * from ::fn_helpcollations                               ()

--just add more space to test it out
September 7, 2006 6:44 AM

Peter W. DeBetta said:

Sure, they relaxed it for built-in functions, but when using User-Defined Types, static methods of the type must still be called using the double-colon (::) syntax.

DECLARE @d Date, @dt datetime
SET @dt = '2004-02-28'
SET @d = Date::FromSqlDateTime(@dt) --static method
September 12, 2006 5:46 AM

Mark Buckle said:

Je ne le comprena pas, moi aussi, mais je trouve le fait ce que un 'MicroSerf' trouve ce fait un peu inconnu, tres tres etrange ;-)
September 21, 2006 8:34 AM

Kalen Delaney said:

Hi Peter

I guess you are saying that the double-colon is still needed for something, but you can't say it is 'still' needed when calling static methods of a UDT, since this concept did not even exist prior to SQL Server 2005. :-)

Double-colons are also required in SQL Server 2005 when granting permissions on schemas, certificates, endpoints, and a few other securables. They are optional when granting permissions on  objects, for backward compatability purposes.

Its use in UDFs seemed totally arbitrary, where in these other uses it seems that some kind of syntactic marker is needed and the :: was the winner.

September 24, 2006 6:25 PM

king said:



January 16, 2018 11:37 PM

aaaaa said:

mt0417 mt0417

April 16, 2018 8:51 PM

chenyingying said:


May 6, 2018 11:05 PM

linying123 said:


May 10, 2018 8:11 PM

hgjgjghjh said:

May 29, 2018 12:17 AM

hjkjhkjh said:

May 31, 2018 12:46 AM

Leave a Comment


This Blog


Favorite Non-technical Sites or Blogs

Privacy Statement