Lol, that's exactly what I came up with:

;WITH cte_city_months AS (
    SELECT CT.CityID, CT.MonthID, ROW_NUMBER() OVER(PARTITION BY CT.CityID ORDER BY CT.MonthID) AS row_num
 ...

Of course that's just the general technique.  You'd want to use random values in a range to represent NULLs -- such as any date between, say, Jan 01, 1901 and...

And here's the trigger.  The app INSERTs / UPDATEs NULL values, and SELECTs return NULL values, but a NULL never actually appears in the table.

Again, I've never had this type...

Here's a sample table and the corresponding view, with one non-nullable datetime and one non-nullable int column that have placeholder values that return NULL when querying the table.

For now, I'll...

Same...

(1) What is the 'cost threshold for parallelism' on that instance?  If it's too low, SQL could be trying use parallelism far too often.

(2) Check the SQL log for error-type...

Restore the qa to prod under a different db name.

Once you verify that the restored db is what you want, then:

DROP the original db;

RENAME the restored db to be the...

I'd jury-rig this one: replace the 3 chars with a single char, then use the "standard" splitter:

;WITH test_data AS (
    SELECT ' a=abc;&;b=1;&;c=ddd' AS...

It was not "obvious" that you were speaking of VARCHAR because they are NOT affected by row compression at all.  Only CHAR is affected by row compression and you...

• This reply was modified 3 years ago by ScottPletcher.

When you read this, Scott, remember that I LOVE page compression...

You say the following but you need to qualify, especially to a person who might not know what compression...

It doesn't take much to cause a page split with compression enable on tables that aren't suffering from page splits to being with.  If you're going to use compression...

It depends.  Unfortunately SQL can only trap certain errors; for some errors, even a CATCH won't "catch" an error.

For example, if you use and invalid column name -- one that...

Just as a bit of a sidebar, remember that page compression causes CI rebuilds to take about 3 times longer.  I'm NOT saying that makes it not worth it...

Are you page-compressing that table?  If not, why not??  Especially since it is read-only almost all the time.

Actually, if you have LOB off-page data, that data will not be moved simply by CREATEing the clustered index on another filegroup.