Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Using the "With Clause" SQL Server 2008

Can someone show me a sample SQL server script that I can look at that uses the "With Clause"?

I am trying to use this clause to iterate through 200 databases that contain the same table that I am trying to run a query on. I am trying to avoid using a cursor because the query time takes too long as well as using a while a loop.

Can someone advise me as to what I can do.

Thank you.

like image 255
Jeff Avatar asked Jan 14 '11 21:01

Jeff


People also ask

Can we use with clause in SQL Server?

The SQL WITH clause was introduced by Oracle in the Oracle 9i release 2 database. The SQL WITH clause allows you to give a sub-query block a name (a process also called sub-query refactoring), which can be referenced in several places within the main SQL query.

Can we use with clause in SELECT statement?

The WITH clause allows you, as part of your select statement, to assign a name to a subquery and utilise its results by referencing that name. It is, on first glance, quite jarring. Because the subquery factoring clause brutally transforms the look of a query, making it no longer start with the SELECT keyword.

Where do we use with in SQL?

WITH: With clause is used for creating a common table expression or temporary tables. temp_table_name ( column_name1, column_name2, …): Here, temp_table_name is the name of CTE and ( column_name1, column_name2, …) is the definition of column names of the CTE which we will be using further in the main query.

IS WITH clause more efficient?

The WITH clause may be processed as an inline view or resolved as a temporary table. The advantage of the latter is that repeated references to the subquery may be more efficient as the data is easily retrieved from the temporary table, rather than being requeried by each reference.


3 Answers

Just a poke, but here's another way to write FizzBuzz :) 100 rows is enough to show the WITH statement, I reckon.

;WITH t100 AS (  SELECT n=number  FROM master..spt_values  WHERE type='P' and number between 1 and 100 )                  SELECT     ISNULL(NULLIF(     CASE WHEN n % 3 = 0 THEN 'Fizz' Else '' END +     CASE WHEN n % 5 = 0 THEN 'Buzz' Else '' END, ''), RIGHT(n,3))  FROM t100 

But the real power behind WITH (known as Common Table Expression http://msdn.microsoft.com/en-us/library/ms190766.aspx "CTE") in SQL Server 2005 and above is the Recursion, as below where the table is built up through iterations adding to the virtual-table each time.

;WITH t100 AS (  SELECT n=1  union all  SELECT n+1  FROM t100  WHERE n < 100 )                  SELECT     ISNULL(NULLIF(     CASE WHEN n % 3 = 0 THEN 'Fizz' Else '' END +     CASE WHEN n % 5 = 0 THEN 'Buzz' Else '' END, ''), RIGHT(n,3))  FROM t100 

To run a similar query in all database, you can use the undocumented sp_msforeachdb. It has been mentioned in another answer, but it is sp_msforeachdb, not sp_foreachdb.

Be careful when using it though, as some things are not what you expect. Consider this example

exec sp_msforeachdb 'select count(*) from sys.objects' 

Instead of the counts of objects within each DB, you will get the SAME count reported, begin that of the current DB. To get around this, always "use" the database first. Note the square brackets to qualify multi-word database names.

exec sp_msforeachdb 'use [?]; select count(*) from sys.objects' 

For your specific query about populating a tally table, you can use something like the below. Not sure about the DATE column, so this tally table has only the DBNAME and IMG_COUNT columns, but hope it helps you.

create table #tbl (dbname sysname, img_count int);  exec sp_msforeachdb ' use [?]; if object_id(''tbldoc'') is not null insert #tbl select ''?'', count(*) from tbldoc'  select * from #tbl 
like image 87
RichardTheKiwi Avatar answered Oct 03 '22 07:10

RichardTheKiwi


There are two types of WITH clauses:

Here is the FizzBuzz in SQL form, using a WITH common table expression (CTE).

;WITH mil AS (
 SELECT TOP 1000000 ROW_NUMBER() OVER ( ORDER BY c.column_id ) [n]
 FROM master.sys.all_columns as c
 CROSS JOIN master.sys.all_columns as c2
)                
 SELECT CASE WHEN n  % 3 = 0 THEN
             CASE WHEN n  % 5 = 0 THEN 'FizzBuzz' ELSE 'Fizz' END
        WHEN n % 5 = 0 THEN 'Buzz'
        ELSE CAST(n AS char(6))
     END + CHAR(13)
 FROM mil

Here is a select statement also using a WITH clause

SELECT * FROM orders WITH (NOLOCK) where order_id = 123
like image 37
Simon Hughes Avatar answered Oct 03 '22 08:10

Simon Hughes


Try the sp_foreachdb procedure.

like image 36
Joel Coehoorn Avatar answered Oct 03 '22 08:10

Joel Coehoorn