July 2009 - Posts

Command Prompt shortcuts to open SSMS

Here are the Command Prompt shortcuts to open Enterprise Manager/Management Studio

SQL Server 2000
isqlw

SQL Server 2005
sqlwb

SQL Server 2008
ssms

Posted by Madhivanan with no comments

CTE in a View

It is possible to use Common Table Expression in a View. But newbies wonder why the following is not possible

create view numbers
as
with
numbers(n) as
(
select 1 as n union all select n+1 from numbers where n<10000
)

select n from numbers option(maxrecursion 0)

Which when created results to the error

Msg 156, Level 15, State 1, Procedure numbers, Line 15
Incorrect syntax near the keyword 'option'.

The correct way of doing it is to create a view without option(maxrecursion 0) and use it when querying a view

create view numbers
as
with numbers(n) as
(
select 1 as n union all select n+1 from numbers where n<10000
)

select n from numbers

GO

select * from numbers option (maxrecursion 0)

Posted by Madhivanan with 3 comment(s)
Filed under: , , ,