GSquared (9/7/2012)

---

Sorry for the delay in replying. Been recovering from surgery.

Try this:

Expand the number of documents beyond 5 up to some reasonable number, like 10,000 or 1-million. Add...

It's not clear exactly what output you're expecting, but see if this helps

WITH Recur AS (

SELECT TaskID, Task, BaseTask, BaseTaskID, BaseTaskInterval, 1 AS Level

FROM #tblTasks

WHERE TaskID = BaseTaskID

UNION ALL

SELECT t.TaskID,...

Shadab Shah (9/5/2012)

---

Your solution works only when every thing exact is pasted in myquery area in the above query. By the...

SELECT name

FROM sys.objects

WHERE type='P' AND OBJECT_DEFINITION(object_id) LIKE '%myquery here%'

See if this helps

select

MainLock.Process.value('@id', 'varchar(100)') AS LockID,

OwnerList.Owner.value('@id', 'varchar(200)') AS ProcessId,

...

Use a filtered index

CREATE UNIQUE INDEX IX ON [dbo].[ProjectFacilities] (ProjectNumber) WHERE [IsPrimaryFacility]=1

Try this

;with cte1 as(

select t4.adinfid, t5.membername, t5.memberid, t3.adforumname

...

dwain.c (8/29/2012)

---

Gus,

That's an interesting point on the IOs but in the below test harness (5 docs) it still runs pretty fast, although clearly not as fast as Mark's.

Create table #Doc...

Another way

WITH CombinedRanges AS (

SELECT s1.NAME,

s1.LOCATION,

s1.DT_START,

MIN(t1.DT_END) AS DT_END...

Here's a way to generate some simple timing tests.

TRUNCATE TABLE GR;

TRUNCATE TABLE GE;

TRUNCATE TABLE Ref;

Insert into GR(DocNum, GR, Seq)

SELECT 1,'GR_'+CAST(number AS VARCHAR(10)),number

FROM master.dbo.spt_values

WHERE number BETWEEN 1 AND 500 AND type='P'

Insert...

RBarryYoung (8/28/2012)

---

Lynn Pettis (8/28/2012)

---

And this is the ANSI standard way of doing the same update as above:

UPDATE Orders SET

some_col = (select

...

If you're willing to jump through a load of hoops, you can update both tables with a view

CREATE TABLE ABC (Id INT NOT NULL , Name VARCHAR(10), Tab CHAR(1) NOT...

WITH AllSeq AS (

SELECT Seq FROM GR

UNION

SELECT Seq FROM GE

UNION

SELECT Seq FROM Ref)

SELECT d.DocNum,

gr.GR,

gr.Seq AS GR_Seq,

...

Just for completeness, here's a recursive CTE way.

DECLARE @tTABLE(id INT,value INT)

INSERT @t(id, value)

VALUES (1, 2),(1, 3),(1, 4),(2, 5),(2, 6),(3, 7);

WITH CTE AS (

SELECT id,value,

...

RBarryYoung (8/23/2012)

---

Actually, Jeff and I learned...