DECLARE @start_year int
DECLARE @start_month char(2)
DECLARE @month_count smallint
SET @start_year = 18
SET @start_month = '01'
SET @month_count = 12

;WITH
cte_tally10 AS (
    SELECT * FROM (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) AS numbers(number)
),
cte_tally1000...

Solution:

Use COALESCE instead of ISNULL, i.e.:

select COALESCE(nullif('',''),' ')

After all these decades. I don't remember why the inmate number was that long. I believe we display the first nine digits because that matches the Social Security number....

I just notice this: is there supposed to be a space in the @CustNum value?  Just curious.  Since that appears to be your own custom data type, it might be...

Since all the people who have ever existed fit very comfortably into 12 digits, I can't imagine why you'd need 18 digits for your person id.

Interesting behavior.  Have not seen that occur "naturally" before.

Edit: Now that I think about it, I thought all changes made w/i a trans were visible to that trans, even if...

• This reply was modified 4 years, 8 months ago by ScottPletcher.

Maybe not.  If...

Lets say in 5 years someone makes a stored procedure that should only be run after hours due to the duration and resource usage.  Since it should only be...

If it would be OK to force the NULL value to be added to lowest date:

;WITH test_data AS (
    SELECT col1 = 7213
 ...

I would say the modern approach to doing this is using ROW_NUMBER(), partly because of its inherent efficiency:

SELECT theDate, ID, val
FROM (
    SELECT *,...

Man... talk about a worse practice ever.  If you're going to do something this silly, use a GUID... at...

SELECT 
    'DataLake' AS DatabaseName, SchemaName, TableName, 
    p.Rows AS Row_Count, 'Row count same as yesterday''s row count' AS Message
FROM 
DataLake.sys.tables...

Doesn't need to be a PK, just the clustering index.  The Quality_ID by itself would typically be the (nonclustered) PK.

Create the _test table from scratch.  Add the UNIQUE CLUSTERED index...

Proper clustering would make the archiving process very fast and easy, since all rows would be contiguous by Quality_Date.

If you cluster the table properly -- Quality ( Quality_Date, Quality_ID ) -- you likely won't have to purge them now.  That is one of the benefits of properly clustering...