Paul White (2/21/2010)

---

That's nice code Chris. It represents a nice compromise between the benefits of an indexed view, and the drawbacks related to the maintenance of aggregates for that...

shawndidy (2/21/2010)

---

Hi,

I need to randomly select questions my database. i used the following query:

...

Rather than aggregating and analysing your data in a single step, run a full-speed preaggregate, then analyse the results. All that CASEing and SUMing one row at a time will...

Paul White (2/20/2010)

---

Matt's right: there isn't a cardinality guarantee. APPLY invokes a table-valued-function (TVF) for each row returned by the outer table expression. A TVF can have many...

D'oh, thanks Gail.

Chris

If this seems way too obvious then I apologise beforehand - table variables can very often be directly swapped out for derived tables which the optimiser knows and understands.

Are...

@ Matt: until I've posted evidence to support this statement, consider it retracted with humble apologies.

Here's where it started, some messing about with CROSS APPLY - note that this query...

Matt Miller (#4) (2/20/2010)

---

ChrisM@home (2/20/2010)

---

'Course it does, it's got a CROSS APPLY in it!:hehe:

CROSS APPLY is an awesome...

sachin1sharma (2/20/2010)

---

Guys,

thanks for your awesome replies and suggestions but i think i myself got deviated from the original requirement. Below is what I actually need.

select * from #tmp_Dates_Notional

-- The desired...

'Course it does, it's got a CROSS APPLY in it!:hehe:

CROSS APPLY is an awesome operator to use for...

Hi Arun

Follow the link to Jeff Moden's article in Wayne's post above. The article explains in great detail how to perform the quirky update and also identifies those situations where...

Here's a CTE solution to this problem - just the SELECT, but easy enough to use as the input for an UPDATE FROM:

;WITH NumberedSet AS (

SELECT RowNum = ROW_NUMBER() OVER...

arun.sas (2/20/2010)

---

Hi,

nice move with inner join.

But the situation always not with max date

UPDATE t

SET t.date2 = (SELECT MAX(date2) FROM #temp WHERE date2 <= t.date1)

from #temp t

However, you should see...

GT-897544 (2/20/2010)

---

ziangij (2/19/2010)

---

thanks...

i just checked it out.. without indexes, update query takes 30 sec.

with the indexes in places, it takes 2 min !

RBarryYoung (2/17/2010)

---

I think that it depends on a...

DROP table #temp

create table #temp

(

date1 datetime null,

date2 datetime null

)

insert into #temp (date1,date2)

select '2009-01-01','2009-01-01' union all

select '2009-01-02',null union all

select '2009-01-03',null union all

select '2009-01-04',null union all

select '2009-01-05','2009-01-05' union all

select '2009-01-06',null union all

select...