As a side note, but still critical, let me warn everyone to use lowercase only for data type names in SQL Server. That is, "int" not "INT", "datetime" not...

Using an in-line tally table would be more efficient:

/* add in-line tally table (CROSS JOINs of 10 x 10 = 100; 100 x 100 = 10K) here,

...

George Wei (3/28/2015)

---

Dear all,

Our system runs a SQL Server 2012 DB, it has a table (table_a) which has over 10M records. Our system have to receive data file from previous...

You could consider clustering the table on the datetime involved. Then you can delete old data with minimal effect on current rows, particularly by reducing the number of non-clustered...

I've also seen vast numbers of errors created by explicit conversions. If you're forced to deal with temporal data stored as char/varchar, then store it in a universal format...

Sorry:

WHERE (@IsProcessed IS NULL OR @IsProcessed = <table_column>)

Edit: general pattern, you may need to change it slightly.

WHERE IsProcessed IS NULL

If you want to check for new rows to process while existing rows are being processed, look into the "READPAST" hint.

If you just need to quickly find...

Eirikur Eiriksson (3/27/2015)

---

Until the locale driven implicit conversion starts...

Just add them and SQL will implicitly convert the varchar to a datetime anyway:

SELECT datetime_column + time_varchar AS new_datetime, ...

FROM ...

Not absolutely, but the best chance is to specify "SORT_IN_TEMPDB = ON" when you rebuild the index for compression. That will SQL will use tempdb for the work space...

Brandie Tarvin (3/27/2015)

---

There isn't one. I don't know why they don't have one, except that...

That's a very viable solution, but keep in mind that you can get potential security issues if you do reference any 'dbo.' objects from a different schema you create.

Would need to see the actual code. Overall this is just not adding up.

Triggers aren't bad practice per se. Some people write horribly inefficient triggers, which would cause problems. Triggers must be written for efficiency, but they are a great tool...

You don't really need a separate indexed view if that's the full query. You could instead add a flag to the dm_document table of whether a matching row existing...