It's the typical "It depends."

If drive space is at a premium and other DBs are steadily growing and might end up needing the space, and if the purged...

If I understand the logic correctly, shouldn't 1500 return Test2 for the same reason as 1001? Maybe it was a typo and you meant 2500; if not, I'll definitely need...

Sure!

The SUBSTRING part of the expression just returns the original string without the first and last 2 characters, as you noted.

Then the CONVERT turns the current...

Jeff,

First, I'm well aware there are no dates in this particular case; that was obviously an example of the general point that requirements for "consecutive" values are not...

A simpler way to get the information you're looking at would be this:

WITH CTE AS
(
SELECT EventDate,EventTime,Temp, rn=ROW_NUMBER() OVER (PARTITION BY EventDate ORDER BY...

Jeff,

I appreciate that (and hopefully this isn't coming off as violently defensive or anything like that, because that's definitely not the intent 🙂 ), but that's exactly my...

Nowhere. That's my point. In some common query scenarios you just want the "next" value, wherever that is, and that's when you need to be careful not to assume there...

Jeff,

Well aware of that. It was not specified that an id of, say, 7 counts as consecutive with an id of 5 so long as there are no...

Thanks!

Continuation of my curiosity: why was it that that method had to be used instead of the CTEs? Performance differences, or something else? 

Cheers!

I'm glad you got it sorted out!

I'm curious, though. What exactly was the change you had to make?

Cheers!

EDIT: Fixed some wording I didn't...

I know the post is in a 2012 forum, but just in case, here's another way that will work on versions pre-2012:

WITH
grouped AS...

From a quick look at Ola's stored procedure, the part of the code that checks those input parameters throws the "not supported" error if you specify block size, number of...

LEFT and RIGHT are just functions that return a string; you can't SET the LEFT/RIGHT characters of a string like this (it would be like trying to do SET GETDATE()=...).

Heh, I'd strongly recommend taking a look at the query plans for the same query with COUNT(*)  and COUNT(1). Specifically, take a look at the aggregate operator to see the...

Heh, likely you're getting incorrect data to begin with.

43,679 is the max number of characters you can  copy/paste from a string in an SSMS grid, a fun old...