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...

select times, LEFT(times, len(times) - 3) * 60 + RIGHT(times, 2) as seconds

from @time

The restores themselves per se wouldn't cause big tempdb growth.

However, I think it's possible that some settings in those dbs might (for example, snapshot settings).

As noted by all,...

You can use different RAID levels for data vs logs. Data often does best with RAID5, whereas logs often do best with RAID10.

sirishgreen (6/3/2014)

---

I thought I had cracked this issue but now....

and DE_Date between

'20140906' and '20140906'

This is being read as The 9th of June by SQL server.

After I looked...