GilaMonster (6/13/2012)

---

Monitor the avg page density and if it is too low (for whatever your definition of too low is) rebuild or reorg the index, just as you do with...

I don't know what to say... It works for me (except the @@rowcount because the EXEC is out of scope and only returns 1) and I see both print statements.

GilaMonster (6/13/2012)

---

Oh, and one more clarification... There will never be an issue with free space being fragmented within a particular index/data page, as SQL automatically compacts pages if that is...

Good point Cathy and David, however, it does not solve why the OP is not seeing any printed output. I can see it with that block, so I think the...

Well, I ran this on some test tables I have and got the following results:

declare @table_id int

declare @schema_name varchar(128)

declare @table_name varchar(128)

declare @str varchar(4000)

declare @aud_flag char(1)

declare @aud_str varchar(4000)

set @aud_flag = 0

set...

Lynn Pettis (6/13/2012)

---

WayneS (6/13/2012)

---

Gotta love it when my messed up solution (unused cte) actually worked and solved it.

... Actually, I just plain forgot to remove it. I think they...

Ha! I think Wayne's is right without the CTE. I did all that work to display rank when all I needed to do was droup by the new group...

I'm so confused because I mixed up what...

Lynn Pettis (6/13/2012)

---

SQLKnowItAll (6/13/2012)

---

Run this:UPDATE sample SET tech_mode_id = 6 WHERE ID = 3

Then tell me if your code still works. Not defending mine because I know it is...

I don't see a declaration for @audit_flag. I say that knowing that you are leaving out script, and without all of it we will have a hard time helping...

Run this:UPDATE sample SET tech_mode_id = 6 WHERE ID = 3

Then tell me if your code still works. Not defending mine because I know it is a mess... Just...

I;m not sure that you can do a DENSE_RANK OVER(ORDER BY grp) because grp will not always be unique to all groups. It is in this set of data,...

Ah yes... Sorry Wayne! REFERENCE_ID, not ID...

WayneS (6/13/2012)

---

This seems like it works:

WITH SAMPLE (REFERENCE_ID,TECH_MODE_ID,BEGIN_TIME,END_TIME) AS

(

SELECT 1, 6, CONVERT(DATETIME,'2012-05-03 20:29:22'),CONVERT(DATETIME,'2012-05-03 20:29:52') UNION ALL

SELECT 1, 5, '2012-05-03 20:29:53','2012-05-03 20:29:55' UNION ALL

SELECT 1, 5, '2012-05-03 20:29:56','2012-05-03 20:30:03' UNION ALL

SELECT...

The idea I used was to separate the same grouping of TECH_MODE_ID into separate groups by subtracting the rank from within the grouping from the rank of all rows. ...