rangu (6/6/2014)

---

Thanks Scott, for your valuable inputs and thoughts.

However I haven't got the answer yet, when exactly do we need to go for Table Partition and Page compression. If this...

You can only have one clustered index, but the PK does not have to be the clustered index.

When time is available:

1) Drop the existing indexes (Edit: non-clustered first, clustered last)

2)...

Lynn Pettis (6/6/2014)

---

sqldriver (6/6/2014)

---

sql-lover (6/6/2014)

---

It is my understanding that an aggregate function has to check all values in the column. So an Index on that attribute may not help too...

Actually MIN() and MAX() could use an index, even a seek, if the index was keyed on all the GROUP BY columns in the proper order.

Though it has a clustered in the name of Primary Key and a non Clustered Index in our case is a date column, as this is most frequent column used...

rangu (6/6/2014)

---

Thanks for your response Scott, [hist_row_id] acts as a Primary key for that table, so acts a Clustered Index anyway.

And today it has 34M may not look many,...

The notion that RCSI is "free" and should "always" be used is as wrong as the notion that "NOLOCK" is "free" and should "always" be used.

There are at least two...

It's also theoretically possible that the feature has been disabled by starting SQL with a "-x" switch. Not likely, but possible :-).

It sounds as if the original table wasn't clustered. That alone can cause big performance issues.

You should put the appropriate clustered index on the table before doing the partitioning....

What, specifically, are you trying to do?

If you want to remove up to the first space from the front of the string, you can do this:

SELECT SUBSTRING(address, CHARINDEX(' ', address)...

I'm not sure raw query counts can be used to determine an index's viability.

I too have seen statements to the effect that indexes with more writes than reads should be...

OK, sorry. I guess I interpreted "any other benefits" too broadly.

Just curious. Since your table has at least 100M rows, did you review the indexes, particularly the clustered indexes?

That usually makes vastly most difference than how the...

Yeah, no gaps will be a royal pain to implement.

If you can allow "voided" gaps, then I think a standard identity would handle this, don't see the need for using...

WHERE

[Entered Date] >= DATEADD(MONTH, 6, DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE())

- CASE WHEN MONTH(GETDATE()) < 7 THEN 1 ELSE 0...