http://sqlblog.com/blogs/adam_machanic/archive/2009/04/28/sqlclr-string-splitting-part-2-even-faster-even-more-scalable.aspx

SELECT caseid FROM dbo.view_CaseLogDetail

EXCEPT

SELECT caseid FROM dbo.view_CaseLogDetail WHERE closedate <> ' ';

?

I am surprised that nobody suggested reusing the existing code here. In any case T-SQL is not the place to do compute-intensive stuff with vertices and points. This...

Just to expand on Jack's point a bit, because it is an important one:

COALESCE( expression [ ,...n ] ) returns the data type of the expression with the highest data...

Jeff Moden (12/13/2009)

---

As a side bar... now you know why I don't care to store flags as bits. A single bit flag in a row still takes a whole...

I wouldn't expect that to work...? You need to prefix with zeroes in some circumstances. One solution is:

declare @year int,

@month...

Also take a look at MVP Adam Machanic's excellent WhoIsActive procedure. I rarely use Activity Monitor now.

Jeff Moden (12/13/2009)

---

Steve is correct. It's not what you're logged in as... it's what the server is logged in as. Do you know how to find which login...

One more, just to labour the point. This one features no OPTION clause (same bushy plan again):

SELECT D.D

FROM #B B

INNER

MERGE

JOIN #A A

...

No parentheses version:

SELECT D.D

FROM ...

There is at least one case where the written order of the tables does matter. Ordinarily, the optimizer only considers plans where at least one input to each join...

Post

Thanks Jeff,

Yes one should be careful. I wouldn't advocate using the method for billions of rows 😉

I had much smaller maximum values for 'Quantity' in mind - maybe thousands.

(And...

Certainly one trick that has moved up...

True, true, true. But I'm...