check_loaded_digest.sql

-- Check two imported tables against the build that produced their CSVs.
-- Generated by python -m simd_ingest.sql_check from the output schemas and the export contract.
--
-- Run it after importing, by the recipe in ../LINKAGE_BY_ERA.md, against tables that are not
-- being changed while it runs. It reads them; it never repairs, replaces or promotes anything.
-- The expected values come from results/manifest.json, which is the build's own record. They
-- must not be taken from the loaded table, which is what is being checked.
--
-- What this does establish: the declared columns, types, nullability and order are present, the
-- natural key is unique and not null, no text contains the characters the digest frames with,
-- the row count matches, and the recomputed digest matches.
-- What it does not: the digest truncates each SHA-256 to eight bytes and adds the results, so
-- collisions and cancelling changes are possible. It detects accidental load corruption. It is
-- neither an exact cell-by-cell comparison nor a signature, and it says nothing about the
-- columns the export contract withholds.

SET NOCOUNT ON;
SET XACT_ABORT ON;

-- STEP 1. EDIT. Paste this build's numbers from results/manifest.json.
IF OBJECT_ID(N'tempdb..#expected', N'U') IS NOT NULL DROP TABLE #expected;
CREATE TABLE #expected (name sysname PRIMARY KEY, row_count bigint NOT NULL,
                        digest_total decimal(38,0) NOT NULL, digest_version int NOT NULL);
INSERT INTO #expected (name, row_count, digest_total, digest_version) VALUES
    (N'postcode_simd_history', 0, 0, 1),   -- EDIT: rows and total from manifest tables.history.csv.digest
    (N'postcode_simd', 0, 0, 1)   -- EDIT: rows and total from manifest tables.main.csv.digest
;

IF EXISTS (SELECT 1 FROM #expected WHERE digest_version <> 1)
    THROW 51000, 'This script implements digest definition version 1 only.', 1;
IF EXISTS (SELECT 1 FROM #expected WHERE row_count <= 0)
    THROW 51000, 'Expected row counts and digest totals have not been filled in from the manifest.', 1;

IF OBJECT_ID(N'tempdb..#result', N'U') IS NOT NULL DROP TABLE #result;
CREATE TABLE #result (table_name sysname, check_name nvarchar(200), expected nvarchar(200),
                      actual nvarchar(200), difference bigint NULL, passed bit NOT NULL);

-- ============================================================================================
-- postcode_simd_history: structure, key, then the digest
-- ============================================================================================
IF OBJECT_ID(N'postcode_simd_history', N'U') IS NULL
    INSERT INTO #result VALUES (N'postcode_simd_history', N'table exists', N'a table', N'not found', NULL, 0);
ELSE
BEGIN
    -- 1. Structure. A digest cannot establish any of this: a missing column, a wrong type, a
    -- column that should not accept NULL, or a changed order would all be invisible to it.
    DECLARE @declared_history TABLE (name sysname, ordinal int, type_name sysname,
                                     numeric_precision int, numeric_scale int, is_nullable bit);
    INSERT INTO @declared_history (name, ordinal, type_name, numeric_precision, numeric_scale, is_nullable) VALUES
    (N'Postcode', 1, N'nvarchar', NULL, NULL, 0),
    (N'PostcodeDistrict', 2, N'nvarchar', NULL, NULL, 0),
    (N'PostcodeSector', 3, N'nvarchar', NULL, NULL, 0),
    (N'DateOfIntroduction', 4, N'nvarchar', NULL, NULL, 0),
    (N'DateOfDeletion', 5, N'nvarchar', NULL, NULL, 0),
    (N'SplitIndicator', 6, N'nvarchar', NULL, NULL, 0),
    (N'CouncilArea2019Code', 7, N'nvarchar', NULL, NULL, 0),
    (N'UKParliamentaryConstituency2024Code', 8, N'nvarchar', NULL, NULL, 0),
    (N'ScottishParliamentaryRegion2026Code', 9, N'nvarchar', NULL, NULL, 0),
    (N'ScottishParliamentaryConstituency2026Code', 10, N'nvarchar', NULL, NULL, 0),
    (N'ElectoralWard2022Code', 11, N'nvarchar', NULL, NULL, 0),
    (N'HealthBoardArea2019Code', 12, N'nvarchar', NULL, NULL, 0),
    (N'HealthBoardArea2006Code', 13, N'nvarchar', NULL, NULL, 0),
    (N'HealthBoardArea1995Code', 14, N'nvarchar', NULL, NULL, 0),
    (N'IntegrationAuthority2019Code', 15, N'nvarchar', NULL, NULL, 0),
    (N'OutputArea2022Code', 16, N'nvarchar', NULL, NULL, 0),
    (N'OutputArea2011Code', 17, N'nvarchar', NULL, NULL, 0),
    (N'OutputArea2001Code', 18, N'nvarchar', NULL, NULL, 0),
    (N'OutputArea1991Code', 19, N'nvarchar', NULL, NULL, 0),
    (N'DataZone2022Code', 20, N'nvarchar', NULL, NULL, 0),
    (N'DataZone2011Code', 21, N'nvarchar', NULL, NULL, 0),
    (N'DataZone2001Code', 22, N'nvarchar', NULL, NULL, 0),
    (N'IntermediateZone2022Code', 23, N'nvarchar', NULL, NULL, 0),
    (N'IntermediateZone2011Code', 24, N'nvarchar', NULL, NULL, 0),
    (N'IntermediateZone2001Code', 25, N'nvarchar', NULL, NULL, 0),
    (N'CensusHouseholdCount2022', 26, N'nvarchar', NULL, NULL, 1),
    (N'CensusPopulationCount2022', 27, N'nvarchar', NULL, NULL, 1),
    (N'CensusHouseholdCount2011', 28, N'nvarchar', NULL, NULL, 1),
    (N'CensusPopulationCount2011', 29, N'nvarchar', NULL, NULL, 1),
    (N'CensusHouseholdCount2001', 30, N'nvarchar', NULL, NULL, 1),
    (N'CensusPopulationCount2001', 31, N'nvarchar', NULL, NULL, 1),
    (N'CensusHouseholdCount1991', 32, N'nvarchar', NULL, NULL, 1),
    (N'CensusPopulationCount1991', 33, N'nvarchar', NULL, NULL, 1),
    (N'ScottishIndexOfMultipleDeprivation2020Rank', 34, N'nvarchar', NULL, NULL, 0),
    (N'LAU2025Level1Code', 35, N'nvarchar', NULL, NULL, 0),
    (N'ITL2025Level2Code', 36, N'nvarchar', NULL, NULL, 0),
    (N'ITL2025Level3Code', 37, N'nvarchar', NULL, NULL, 0),
    (N'Locality2020Code', 38, N'nvarchar', NULL, NULL, 0),
    (N'Locality2022Code', 39, N'nvarchar', NULL, NULL, 0),
    (N'Locality2001Code', 40, N'nvarchar', NULL, NULL, 0),
    (N'Locality1991Code', 41, N'nvarchar', NULL, NULL, 0),
    (N'Settlement2020Code', 42, N'nvarchar', NULL, NULL, 0),
    (N'Settlement2022Code', 43, N'nvarchar', NULL, NULL, 0),
    (N'Settlement2001Code', 44, N'nvarchar', NULL, NULL, 0),
    (N'CivilParish1930Code', 45, N'nvarchar', NULL, NULL, 0),
    (N'EnterpriseRegion2008Code', 46, N'nvarchar', NULL, NULL, 0),
    (N'IslandCode', 47, N'nvarchar', NULL, NULL, 0),
    (N'LocalGovernmentDistrict1995Code', 48, N'nvarchar', NULL, NULL, 0),
    (N'LocalGovernmentDistrict1991Code', 49, N'nvarchar', NULL, NULL, 0),
    (N'NationalPark2010Code', 50, N'nvarchar', NULL, NULL, 0),
    (N'RegistrationDistrict2007Code', 51, N'nvarchar', NULL, NULL, 0),
    (N'ROACommunityPlanningPartnership2006Code', 52, N'nvarchar', NULL, NULL, 0),
    (N'ROALocal2006Code', 53, N'nvarchar', NULL, NULL, 0),
    (N'StrategicDevelopmentPlanningArea2013Code', 54, N'nvarchar', NULL, NULL, 0),
    (N'TravelToWorkArea2011Code', 55, N'nvarchar', NULL, NULL, 0),
    (N'UrbanRural6Fold2022Code', 56, N'nvarchar', NULL, NULL, 0),
    (N'UrbanRural8Fold2022Code', 57, N'nvarchar', NULL, NULL, 0),
    (N'GridLinkIndicator', 58, N'nvarchar', NULL, NULL, 0),
    (N'GridLinkPositionalAccuracy', 59, N'nvarchar', NULL, NULL, 0),
    (N'NeverDigitised', 60, N'nvarchar', NULL, NULL, 1),
    (N'LinkedSmallUserPostcode', 61, N'nvarchar', NULL, NULL, 1),
    (N'pc_norm', 62, N'nvarchar', NULL, NULL, 0),
    (N'pc_base', 63, N'nvarchar', NULL, NULL, 0),
    (N'spd_user_type', 64, N'nvarchar', NULL, NULL, 0),
    (N'spd_release', 65, N'nvarchar', NULL, NULL, 0),
    (N'introduced_on', 66, N'date', NULL, NULL, 0),
    (N'deleted_on', 67, N'date', NULL, NULL, 1),
    (N'is_current', 68, N'bit', NULL, NULL, 0),
    (N'phs_dz2001_hb', 69, N'nvarchar', NULL, NULL, 0),
    (N'phs_dz2001_hscp', 70, N'nvarchar', NULL, NULL, 0),
    (N'phs_dz2001_ca', 71, N'nvarchar', NULL, NULL, 0),
    (N'phs_dz2011_hb', 72, N'nvarchar', NULL, NULL, 0),
    (N'phs_dz2011_hscp', 73, N'nvarchar', NULL, NULL, 0),
    (N'phs_dz2011_ca', 74, N'nvarchar', NULL, NULL, 0),
    (N'simd2004_rank', 75, N'smallint', NULL, NULL, 0),
    (N'simd2004_pw_scotland_decile', 76, N'tinyint', NULL, NULL, 0),
    (N'simd2004_pw_scotland_quintile', 77, N'tinyint', NULL, NULL, 0),
    (N'simd2004_pw_hb_decile', 78, N'tinyint', NULL, NULL, 0),
    (N'simd2004_pw_hb_quintile', 79, N'tinyint', NULL, NULL, 0),
    (N'simd2004_pw_hscp_decile', 80, N'tinyint', NULL, NULL, 0),
    (N'simd2004_pw_hscp_quintile', 81, N'tinyint', NULL, NULL, 0),
    (N'simd2004_pw_ca_decile', 82, N'tinyint', NULL, NULL, 0),
    (N'simd2004_pw_ca_quintile', 83, N'tinyint', NULL, NULL, 0),
    (N'simd2004_most15pc', 84, N'tinyint', NULL, NULL, 0),
    (N'simd2004_least15pc', 85, N'tinyint', NULL, NULL, 0),
    (N'simd2004_uw_scotland_quintile', 86, N'tinyint', NULL, NULL, 0),
    (N'simd2004_uw_scotland_decile', 87, N'tinyint', NULL, NULL, 0),
    (N'simd2004_uw_scotland_vigintile', 88, N'tinyint', NULL, NULL, 0),
    (N'simd2006_rank', 89, N'smallint', NULL, NULL, 0),
    (N'simd2006_pw_scotland_decile', 90, N'tinyint', NULL, NULL, 0),
    (N'simd2006_pw_scotland_quintile', 91, N'tinyint', NULL, NULL, 0),
    (N'simd2006_pw_hb_decile', 92, N'tinyint', NULL, NULL, 0),
    (N'simd2006_pw_hb_quintile', 93, N'tinyint', NULL, NULL, 0),
    (N'simd2006_pw_hscp_decile', 94, N'tinyint', NULL, NULL, 0),
    (N'simd2006_pw_hscp_quintile', 95, N'tinyint', NULL, NULL, 0),
    (N'simd2006_pw_ca_decile', 96, N'tinyint', NULL, NULL, 0),
    (N'simd2006_pw_ca_quintile', 97, N'tinyint', NULL, NULL, 0),
    (N'simd2006_most15pc', 98, N'tinyint', NULL, NULL, 0),
    (N'simd2006_least15pc', 99, N'tinyint', NULL, NULL, 0),
    (N'simd2006_uw_scotland_quintile', 100, N'tinyint', NULL, NULL, 0),
    (N'simd2006_uw_scotland_decile', 101, N'tinyint', NULL, NULL, 0),
    (N'simd2006_uw_scotland_vigintile', 102, N'tinyint', NULL, NULL, 0),
    (N'simd2009v2_rank', 103, N'smallint', NULL, NULL, 0),
    (N'simd2009v2_pw_scotland_decile', 104, N'tinyint', NULL, NULL, 0),
    (N'simd2009v2_pw_scotland_quintile', 105, N'tinyint', NULL, NULL, 0),
    (N'simd2009v2_pw_hb_decile', 106, N'tinyint', NULL, NULL, 0),
    (N'simd2009v2_pw_hb_quintile', 107, N'tinyint', NULL, NULL, 0),
    (N'simd2009v2_pw_hscp_decile', 108, N'tinyint', NULL, NULL, 0),
    (N'simd2009v2_pw_hscp_quintile', 109, N'tinyint', NULL, NULL, 0),
    (N'simd2009v2_pw_ca_decile', 110, N'tinyint', NULL, NULL, 0),
    (N'simd2009v2_pw_ca_quintile', 111, N'tinyint', NULL, NULL, 0),
    (N'simd2009v2_most15pc', 112, N'tinyint', NULL, NULL, 0),
    (N'simd2009v2_least15pc', 113, N'tinyint', NULL, NULL, 0),
    (N'simd2009v2_uw_scotland_quintile', 114, N'tinyint', NULL, NULL, 0),
    (N'simd2009v2_uw_scotland_decile', 115, N'tinyint', NULL, NULL, 0),
    (N'simd2009v2_uw_scotland_vigintile', 116, N'tinyint', NULL, NULL, 0),
    (N'simd2012_rank', 117, N'smallint', NULL, NULL, 0),
    (N'simd2012_pw_scotland_decile', 118, N'tinyint', NULL, NULL, 0),
    (N'simd2012_pw_scotland_quintile', 119, N'tinyint', NULL, NULL, 0),
    (N'simd2012_pw_hb_decile', 120, N'tinyint', NULL, NULL, 0),
    (N'simd2012_pw_hb_quintile', 121, N'tinyint', NULL, NULL, 0),
    (N'simd2012_pw_hscp_decile', 122, N'tinyint', NULL, NULL, 0),
    (N'simd2012_pw_hscp_quintile', 123, N'tinyint', NULL, NULL, 0),
    (N'simd2012_pw_ca_decile', 124, N'tinyint', NULL, NULL, 0),
    (N'simd2012_pw_ca_quintile', 125, N'tinyint', NULL, NULL, 0),
    (N'simd2012_most15pc', 126, N'tinyint', NULL, NULL, 0),
    (N'simd2012_least15pc', 127, N'tinyint', NULL, NULL, 0),
    (N'simd2012_uw_scotland_quintile', 128, N'tinyint', NULL, NULL, 0),
    (N'simd2012_uw_scotland_decile', 129, N'tinyint', NULL, NULL, 0),
    (N'simd2012_uw_scotland_vigintile', 130, N'tinyint', NULL, NULL, 0),
    (N'simd2016_rank', 131, N'smallint', NULL, NULL, 0),
    (N'simd2016_pw_scotland_decile', 132, N'tinyint', NULL, NULL, 0),
    (N'simd2016_pw_scotland_quintile', 133, N'tinyint', NULL, NULL, 0),
    (N'simd2016_pw_hb_decile', 134, N'tinyint', NULL, NULL, 0),
    (N'simd2016_pw_hb_quintile', 135, N'tinyint', NULL, NULL, 0),
    (N'simd2016_pw_hscp_decile', 136, N'tinyint', NULL, NULL, 0),
    (N'simd2016_pw_hscp_quintile', 137, N'tinyint', NULL, NULL, 0),
    (N'simd2016_pw_ca_decile', 138, N'tinyint', NULL, NULL, 0),
    (N'simd2016_pw_ca_quintile', 139, N'tinyint', NULL, NULL, 0),
    (N'simd2016_most15pc', 140, N'tinyint', NULL, NULL, 0),
    (N'simd2016_least15pc', 141, N'tinyint', NULL, NULL, 0),
    (N'simd2016_uw_scotland_quintile', 142, N'tinyint', NULL, NULL, 0),
    (N'simd2016_uw_scotland_decile', 143, N'tinyint', NULL, NULL, 0),
    (N'simd2016_uw_scotland_vigintile', 144, N'tinyint', NULL, NULL, 0),
    (N'simd2020v2_rank', 145, N'smallint', NULL, NULL, 0),
    (N'simd2020v2_pw_scotland_decile', 146, N'tinyint', NULL, NULL, 0),
    (N'simd2020v2_pw_scotland_quintile', 147, N'tinyint', NULL, NULL, 0),
    (N'simd2020v2_pw_hb_decile', 148, N'tinyint', NULL, NULL, 0),
    (N'simd2020v2_pw_hb_quintile', 149, N'tinyint', NULL, NULL, 0),
    (N'simd2020v2_pw_hscp_decile', 150, N'tinyint', NULL, NULL, 0),
    (N'simd2020v2_pw_hscp_quintile', 151, N'tinyint', NULL, NULL, 0),
    (N'simd2020v2_pw_ca_decile', 152, N'tinyint', NULL, NULL, 0),
    (N'simd2020v2_pw_ca_quintile', 153, N'tinyint', NULL, NULL, 0),
    (N'simd2020v2_most15pc', 154, N'tinyint', NULL, NULL, 0),
    (N'simd2020v2_least15pc', 155, N'tinyint', NULL, NULL, 0),
    (N'simd2020v2_uw_scotland_quintile', 156, N'tinyint', NULL, NULL, 0),
    (N'simd2020v2_uw_scotland_decile', 157, N'tinyint', NULL, NULL, 0),
    (N'simd2020v2_uw_scotland_vigintile', 158, N'tinyint', NULL, NULL, 0),
    (N'urbanrural2003_2004_6fold', 159, N'tinyint', NULL, NULL, 1),
    (N'urbanrural2003_2004_8fold', 160, N'tinyint', NULL, NULL, 1),
    (N'urbanrural2003_2004_status', 161, N'nvarchar', NULL, NULL, 1),
    (N'urbanrural2005_2006_6fold', 162, N'tinyint', NULL, NULL, 1),
    (N'urbanrural2005_2006_8fold', 163, N'tinyint', NULL, NULL, 1),
    (N'urbanrural2005_2006_status', 164, N'nvarchar', NULL, NULL, 1),
    (N'urbanrural2007_2008_6fold', 165, N'tinyint', NULL, NULL, 1),
    (N'urbanrural2007_2008_8fold', 166, N'tinyint', NULL, NULL, 1),
    (N'urbanrural2007_2008_status', 167, N'nvarchar', NULL, NULL, 1),
    (N'urbanrural2009_2010_6fold', 168, N'tinyint', NULL, NULL, 1),
    (N'urbanrural2009_2010_8fold', 169, N'tinyint', NULL, NULL, 1),
    (N'urbanrural2009_2010_status', 170, N'nvarchar', NULL, NULL, 1),
    (N'urbanrural2011_2012_6fold', 171, N'tinyint', NULL, NULL, 1),
    (N'urbanrural2011_2012_8fold', 172, N'tinyint', NULL, NULL, 1),
    (N'urbanrural2011_2012_status', 173, N'nvarchar', NULL, NULL, 1),
    (N'urbanrural2013_2014_6fold', 174, N'tinyint', NULL, NULL, 1),
    (N'urbanrural2013_2014_8fold', 175, N'tinyint', NULL, NULL, 1),
    (N'urbanrural2013_2014_status', 176, N'nvarchar', NULL, NULL, 1),
    (N'urbanrural2016_6fold', 177, N'tinyint', NULL, NULL, 1),
    (N'urbanrural2016_8fold', 178, N'tinyint', NULL, NULL, 1),
    (N'urbanrural2016_status', 179, N'nvarchar', NULL, NULL, 1),
    (N'urbanrural2020_6fold', 180, N'tinyint', NULL, NULL, 1),
    (N'urbanrural2020_8fold', 181, N'tinyint', NULL, NULL, 1),
    (N'urbanrural2020_status', 182, N'nvarchar', NULL, NULL, 1),
    (N'urbanrural2022_6fold', 183, N'tinyint', NULL, NULL, 1),
    (N'urbanrural2022_8fold', 184, N'tinyint', NULL, NULL, 1),
    (N'urbanrural2022_status', 185, N'nvarchar', NULL, NULL, 1),
    (N'simd2004_income_domain_rank', 186, N'decimal', 6, 1, 0),
    (N'simd2004_employment_domain_rank', 187, N'decimal', 6, 1, 0),
    (N'simd2004_health_domain_rank', 188, N'decimal', 6, 1, 0),
    (N'simd2004_education_domain_rank', 189, N'decimal', 6, 1, 0),
    (N'simd2004_access_domain_rank', 190, N'decimal', 6, 1, 0),
    (N'simd2004_housing_domain_rank', 191, N'decimal', 6, 1, 0),
    (N'simd2006_income_domain_rank', 192, N'decimal', 6, 1, 0),
    (N'simd2006_employment_domain_rank', 193, N'decimal', 6, 1, 0),
    (N'simd2006_health_domain_rank', 194, N'decimal', 6, 1, 0),
    (N'simd2006_education_domain_rank', 195, N'decimal', 6, 1, 0),
    (N'simd2006_access_domain_rank', 196, N'decimal', 6, 1, 0),
    (N'simd2006_crime_domain_rank', 197, N'decimal', 6, 1, 0),
    (N'simd2006_housing_domain_rank', 198, N'decimal', 6, 1, 0),
    (N'simd2009v2_income_domain_rank', 199, N'decimal', 6, 1, 0),
    (N'simd2009v2_employment_domain_rank', 200, N'decimal', 6, 1, 0),
    (N'simd2009v2_health_domain_rank', 201, N'decimal', 6, 1, 0),
    (N'simd2009v2_education_domain_rank', 202, N'decimal', 6, 1, 0),
    (N'simd2009v2_access_domain_rank', 203, N'decimal', 6, 1, 0),
    (N'simd2009v2_crime_domain_rank', 204, N'decimal', 6, 1, 0),
    (N'simd2009v2_housing_domain_rank', 205, N'decimal', 6, 1, 0),
    (N'simd2012_income_domain_rank', 206, N'decimal', 6, 1, 0),
    (N'simd2012_employment_domain_rank', 207, N'decimal', 6, 1, 0),
    (N'simd2012_health_domain_rank', 208, N'decimal', 6, 1, 0),
    (N'simd2012_education_domain_rank', 209, N'decimal', 6, 1, 0),
    (N'simd2012_access_domain_rank', 210, N'decimal', 6, 1, 0),
    (N'simd2012_crime_domain_rank', 211, N'decimal', 6, 1, 0),
    (N'simd2012_housing_domain_rank', 212, N'decimal', 6, 1, 0),
    (N'simd2016_income_domain_rank', 213, N'decimal', 6, 1, 0),
    (N'simd2016_employment_domain_rank', 214, N'decimal', 6, 1, 0),
    (N'simd2016_health_domain_rank', 215, N'decimal', 6, 1, 0),
    (N'simd2016_education_domain_rank', 216, N'decimal', 6, 1, 0),
    (N'simd2016_access_domain_rank', 217, N'decimal', 6, 1, 0),
    (N'simd2016_crime_domain_rank', 218, N'decimal', 6, 1, 0),
    (N'simd2016_housing_domain_rank', 219, N'decimal', 6, 1, 0),
    (N'simd2020v2_income_domain_rank', 220, N'decimal', 6, 1, 0),
    (N'simd2020v2_employment_domain_rank', 221, N'decimal', 6, 1, 0),
    (N'simd2020v2_health_domain_rank', 222, N'decimal', 6, 1, 0),
    (N'simd2020v2_education_domain_rank', 223, N'decimal', 6, 1, 0),
    (N'simd2020v2_access_domain_rank', 224, N'decimal', 6, 1, 0),
    (N'simd2020v2_crime_domain_rank', 225, N'decimal', 6, 1, 0),
    (N'simd2020v2_housing_domain_rank', 226, N'decimal', 6, 1, 0),
    (N'simd2004_income_domain_quintile', 227, N'tinyint', NULL, NULL, 0),
    (N'simd2004_income_domain_decile', 228, N'tinyint', NULL, NULL, 0),
    (N'simd2004_income_domain_vigintile', 229, N'tinyint', NULL, NULL, 0),
    (N'simd2004_employment_domain_quintile', 230, N'tinyint', NULL, NULL, 0),
    (N'simd2004_employment_domain_decile', 231, N'tinyint', NULL, NULL, 0),
    (N'simd2004_employment_domain_vigintile', 232, N'tinyint', NULL, NULL, 0),
    (N'simd2004_health_domain_quintile', 233, N'tinyint', NULL, NULL, 0),
    (N'simd2004_health_domain_decile', 234, N'tinyint', NULL, NULL, 0),
    (N'simd2004_health_domain_vigintile', 235, N'tinyint', NULL, NULL, 0),
    (N'simd2004_education_domain_quintile', 236, N'tinyint', NULL, NULL, 0),
    (N'simd2004_education_domain_decile', 237, N'tinyint', NULL, NULL, 0),
    (N'simd2004_education_domain_vigintile', 238, N'tinyint', NULL, NULL, 0),
    (N'simd2004_access_domain_quintile', 239, N'tinyint', NULL, NULL, 0),
    (N'simd2004_access_domain_decile', 240, N'tinyint', NULL, NULL, 0),
    (N'simd2004_access_domain_vigintile', 241, N'tinyint', NULL, NULL, 0),
    (N'simd2004_housing_domain_quintile', 242, N'tinyint', NULL, NULL, 0),
    (N'simd2004_housing_domain_decile', 243, N'tinyint', NULL, NULL, 0),
    (N'simd2004_housing_domain_vigintile', 244, N'tinyint', NULL, NULL, 0),
    (N'simd2006_income_domain_quintile', 245, N'tinyint', NULL, NULL, 0),
    (N'simd2006_income_domain_decile', 246, N'tinyint', NULL, NULL, 0),
    (N'simd2006_income_domain_vigintile', 247, N'tinyint', NULL, NULL, 0),
    (N'simd2006_employment_domain_quintile', 248, N'tinyint', NULL, NULL, 0),
    (N'simd2006_employment_domain_decile', 249, N'tinyint', NULL, NULL, 0),
    (N'simd2006_employment_domain_vigintile', 250, N'tinyint', NULL, NULL, 0),
    (N'simd2006_health_domain_quintile', 251, N'tinyint', NULL, NULL, 0),
    (N'simd2006_health_domain_decile', 252, N'tinyint', NULL, NULL, 0),
    (N'simd2006_health_domain_vigintile', 253, N'tinyint', NULL, NULL, 0),
    (N'simd2006_education_domain_quintile', 254, N'tinyint', NULL, NULL, 0),
    (N'simd2006_education_domain_decile', 255, N'tinyint', NULL, NULL, 0),
    (N'simd2006_education_domain_vigintile', 256, N'tinyint', NULL, NULL, 0),
    (N'simd2006_access_domain_quintile', 257, N'tinyint', NULL, NULL, 0),
    (N'simd2006_access_domain_decile', 258, N'tinyint', NULL, NULL, 0),
    (N'simd2006_access_domain_vigintile', 259, N'tinyint', NULL, NULL, 0),
    (N'simd2006_crime_domain_quintile', 260, N'tinyint', NULL, NULL, 0),
    (N'simd2006_crime_domain_decile', 261, N'tinyint', NULL, NULL, 0),
    (N'simd2006_crime_domain_vigintile', 262, N'tinyint', NULL, NULL, 0),
    (N'simd2006_housing_domain_quintile', 263, N'tinyint', NULL, NULL, 0),
    (N'simd2006_housing_domain_decile', 264, N'tinyint', NULL, NULL, 0),
    (N'simd2006_housing_domain_vigintile', 265, N'tinyint', NULL, NULL, 0),
    (N'simd2009v2_income_domain_quintile', 266, N'tinyint', NULL, NULL, 0),
    (N'simd2009v2_income_domain_decile', 267, N'tinyint', NULL, NULL, 0),
    (N'simd2009v2_income_domain_vigintile', 268, N'tinyint', NULL, NULL, 0),
    (N'simd2009v2_employment_domain_quintile', 269, N'tinyint', NULL, NULL, 0),
    (N'simd2009v2_employment_domain_decile', 270, N'tinyint', NULL, NULL, 0),
    (N'simd2009v2_employment_domain_vigintile', 271, N'tinyint', NULL, NULL, 0),
    (N'simd2009v2_health_domain_quintile', 272, N'tinyint', NULL, NULL, 0),
    (N'simd2009v2_health_domain_decile', 273, N'tinyint', NULL, NULL, 0),
    (N'simd2009v2_health_domain_vigintile', 274, N'tinyint', NULL, NULL, 0),
    (N'simd2009v2_education_domain_quintile', 275, N'tinyint', NULL, NULL, 0),
    (N'simd2009v2_education_domain_decile', 276, N'tinyint', NULL, NULL, 0),
    (N'simd2009v2_education_domain_vigintile', 277, N'tinyint', NULL, NULL, 0),
    (N'simd2009v2_access_domain_quintile', 278, N'tinyint', NULL, NULL, 0),
    (N'simd2009v2_access_domain_decile', 279, N'tinyint', NULL, NULL, 0),
    (N'simd2009v2_access_domain_vigintile', 280, N'tinyint', NULL, NULL, 0),
    (N'simd2009v2_crime_domain_quintile', 281, N'tinyint', NULL, NULL, 0),
    (N'simd2009v2_crime_domain_decile', 282, N'tinyint', NULL, NULL, 0),
    (N'simd2009v2_crime_domain_vigintile', 283, N'tinyint', NULL, NULL, 0),
    (N'simd2009v2_housing_domain_quintile', 284, N'tinyint', NULL, NULL, 0),
    (N'simd2009v2_housing_domain_decile', 285, N'tinyint', NULL, NULL, 0),
    (N'simd2009v2_housing_domain_vigintile', 286, N'tinyint', NULL, NULL, 0),
    (N'simd2012_income_domain_quintile', 287, N'tinyint', NULL, NULL, 0),
    (N'simd2012_income_domain_decile', 288, N'tinyint', NULL, NULL, 0),
    (N'simd2012_income_domain_vigintile', 289, N'tinyint', NULL, NULL, 0),
    (N'simd2012_employment_domain_quintile', 290, N'tinyint', NULL, NULL, 0),
    (N'simd2012_employment_domain_decile', 291, N'tinyint', NULL, NULL, 0),
    (N'simd2012_employment_domain_vigintile', 292, N'tinyint', NULL, NULL, 0),
    (N'simd2012_health_domain_quintile', 293, N'tinyint', NULL, NULL, 0),
    (N'simd2012_health_domain_decile', 294, N'tinyint', NULL, NULL, 0),
    (N'simd2012_health_domain_vigintile', 295, N'tinyint', NULL, NULL, 0),
    (N'simd2012_education_domain_quintile', 296, N'tinyint', NULL, NULL, 0),
    (N'simd2012_education_domain_decile', 297, N'tinyint', NULL, NULL, 0),
    (N'simd2012_education_domain_vigintile', 298, N'tinyint', NULL, NULL, 0),
    (N'simd2012_access_domain_quintile', 299, N'tinyint', NULL, NULL, 0),
    (N'simd2012_access_domain_decile', 300, N'tinyint', NULL, NULL, 0),
    (N'simd2012_access_domain_vigintile', 301, N'tinyint', NULL, NULL, 0),
    (N'simd2012_crime_domain_quintile', 302, N'tinyint', NULL, NULL, 0),
    (N'simd2012_crime_domain_decile', 303, N'tinyint', NULL, NULL, 0),
    (N'simd2012_crime_domain_vigintile', 304, N'tinyint', NULL, NULL, 0),
    (N'simd2012_housing_domain_quintile', 305, N'tinyint', NULL, NULL, 0),
    (N'simd2012_housing_domain_decile', 306, N'tinyint', NULL, NULL, 0),
    (N'simd2012_housing_domain_vigintile', 307, N'tinyint', NULL, NULL, 0),
    (N'simd2016_income_domain_quintile', 308, N'tinyint', NULL, NULL, 0),
    (N'simd2016_income_domain_decile', 309, N'tinyint', NULL, NULL, 0),
    (N'simd2016_income_domain_vigintile', 310, N'tinyint', NULL, NULL, 0),
    (N'simd2016_employment_domain_quintile', 311, N'tinyint', NULL, NULL, 0),
    (N'simd2016_employment_domain_decile', 312, N'tinyint', NULL, NULL, 0),
    (N'simd2016_employment_domain_vigintile', 313, N'tinyint', NULL, NULL, 0),
    (N'simd2016_health_domain_quintile', 314, N'tinyint', NULL, NULL, 0),
    (N'simd2016_health_domain_decile', 315, N'tinyint', NULL, NULL, 0),
    (N'simd2016_health_domain_vigintile', 316, N'tinyint', NULL, NULL, 0),
    (N'simd2016_education_domain_quintile', 317, N'tinyint', NULL, NULL, 0),
    (N'simd2016_education_domain_decile', 318, N'tinyint', NULL, NULL, 0),
    (N'simd2016_education_domain_vigintile', 319, N'tinyint', NULL, NULL, 0),
    (N'simd2016_access_domain_quintile', 320, N'tinyint', NULL, NULL, 0),
    (N'simd2016_access_domain_decile', 321, N'tinyint', NULL, NULL, 0),
    (N'simd2016_access_domain_vigintile', 322, N'tinyint', NULL, NULL, 0),
    (N'simd2016_crime_domain_quintile', 323, N'tinyint', NULL, NULL, 0),
    (N'simd2016_crime_domain_decile', 324, N'tinyint', NULL, NULL, 0),
    (N'simd2016_crime_domain_vigintile', 325, N'tinyint', NULL, NULL, 0),
    (N'simd2016_housing_domain_quintile', 326, N'tinyint', NULL, NULL, 0),
    (N'simd2016_housing_domain_decile', 327, N'tinyint', NULL, NULL, 0),
    (N'simd2016_housing_domain_vigintile', 328, N'tinyint', NULL, NULL, 0),
    (N'simd2020v2_income_domain_quintile', 329, N'tinyint', NULL, NULL, 0),
    (N'simd2020v2_income_domain_decile', 330, N'tinyint', NULL, NULL, 0),
    (N'simd2020v2_income_domain_vigintile', 331, N'tinyint', NULL, NULL, 0),
    (N'simd2020v2_employment_domain_quintile', 332, N'tinyint', NULL, NULL, 0),
    (N'simd2020v2_employment_domain_decile', 333, N'tinyint', NULL, NULL, 0),
    (N'simd2020v2_employment_domain_vigintile', 334, N'tinyint', NULL, NULL, 0),
    (N'simd2020v2_health_domain_quintile', 335, N'tinyint', NULL, NULL, 0),
    (N'simd2020v2_health_domain_decile', 336, N'tinyint', NULL, NULL, 0),
    (N'simd2020v2_health_domain_vigintile', 337, N'tinyint', NULL, NULL, 0),
    (N'simd2020v2_education_domain_quintile', 338, N'tinyint', NULL, NULL, 0),
    (N'simd2020v2_education_domain_decile', 339, N'tinyint', NULL, NULL, 0),
    (N'simd2020v2_education_domain_vigintile', 340, N'tinyint', NULL, NULL, 0),
    (N'simd2020v2_access_domain_quintile', 341, N'tinyint', NULL, NULL, 0),
    (N'simd2020v2_access_domain_decile', 342, N'tinyint', NULL, NULL, 0),
    (N'simd2020v2_access_domain_vigintile', 343, N'tinyint', NULL, NULL, 0),
    (N'simd2020v2_crime_domain_quintile', 344, N'tinyint', NULL, NULL, 0),
    (N'simd2020v2_crime_domain_decile', 345, N'tinyint', NULL, NULL, 0),
    (N'simd2020v2_crime_domain_vigintile', 346, N'tinyint', NULL, NULL, 0),
    (N'simd2020v2_housing_domain_quintile', 347, N'tinyint', NULL, NULL, 0),
    (N'simd2020v2_housing_domain_decile', 348, N'tinyint', NULL, NULL, 0),
    (N'simd2020v2_housing_domain_vigintile', 349, N'tinyint', NULL, NULL, 0),
    (N'simd2020v2_housing_domain_rank_source_status', 350, N'nvarchar', NULL, NULL, 1),
    (N'simd2004_income_domain_pw_scotland_quintile', 351, N'tinyint', NULL, NULL, 0),
    (N'simd2004_income_domain_pw_scotland_decile', 352, N'tinyint', NULL, NULL, 0),
    (N'simd2004_employment_domain_pw_scotland_quintile', 353, N'tinyint', NULL, NULL, 0),
    (N'simd2004_employment_domain_pw_scotland_decile', 354, N'tinyint', NULL, NULL, 0),
    (N'simd2004_health_domain_pw_scotland_quintile', 355, N'tinyint', NULL, NULL, 0),
    (N'simd2004_health_domain_pw_scotland_decile', 356, N'tinyint', NULL, NULL, 0),
    (N'simd2004_education_domain_pw_scotland_quintile', 357, N'tinyint', NULL, NULL, 0),
    (N'simd2004_education_domain_pw_scotland_decile', 358, N'tinyint', NULL, NULL, 0),
    (N'simd2004_access_domain_pw_scotland_quintile', 359, N'tinyint', NULL, NULL, 0),
    (N'simd2004_access_domain_pw_scotland_decile', 360, N'tinyint', NULL, NULL, 0),
    (N'simd2004_housing_domain_pw_scotland_quintile', 361, N'tinyint', NULL, NULL, 0),
    (N'simd2004_housing_domain_pw_scotland_decile', 362, N'tinyint', NULL, NULL, 0),
    (N'simd2006_income_domain_pw_scotland_quintile', 363, N'tinyint', NULL, NULL, 0),
    (N'simd2006_income_domain_pw_scotland_decile', 364, N'tinyint', NULL, NULL, 0),
    (N'simd2006_employment_domain_pw_scotland_quintile', 365, N'tinyint', NULL, NULL, 0),
    (N'simd2006_employment_domain_pw_scotland_decile', 366, N'tinyint', NULL, NULL, 0),
    (N'simd2006_health_domain_pw_scotland_quintile', 367, N'tinyint', NULL, NULL, 0),
    (N'simd2006_health_domain_pw_scotland_decile', 368, N'tinyint', NULL, NULL, 0),
    (N'simd2006_education_domain_pw_scotland_quintile', 369, N'tinyint', NULL, NULL, 0),
    (N'simd2006_education_domain_pw_scotland_decile', 370, N'tinyint', NULL, NULL, 0),
    (N'simd2006_access_domain_pw_scotland_quintile', 371, N'tinyint', NULL, NULL, 0),
    (N'simd2006_access_domain_pw_scotland_decile', 372, N'tinyint', NULL, NULL, 0),
    (N'simd2006_crime_domain_pw_scotland_quintile', 373, N'tinyint', NULL, NULL, 0),
    (N'simd2006_crime_domain_pw_scotland_decile', 374, N'tinyint', NULL, NULL, 0),
    (N'simd2006_housing_domain_pw_scotland_quintile', 375, N'tinyint', NULL, NULL, 0),
    (N'simd2006_housing_domain_pw_scotland_decile', 376, N'tinyint', NULL, NULL, 0),
    (N'simd2009v2_income_domain_pw_scotland_quintile', 377, N'tinyint', NULL, NULL, 0),
    (N'simd2009v2_income_domain_pw_scotland_decile', 378, N'tinyint', NULL, NULL, 0),
    (N'simd2009v2_employment_domain_pw_scotland_quintile', 379, N'tinyint', NULL, NULL, 0),
    (N'simd2009v2_employment_domain_pw_scotland_decile', 380, N'tinyint', NULL, NULL, 0),
    (N'simd2009v2_health_domain_pw_scotland_quintile', 381, N'tinyint', NULL, NULL, 0),
    (N'simd2009v2_health_domain_pw_scotland_decile', 382, N'tinyint', NULL, NULL, 0),
    (N'simd2009v2_education_domain_pw_scotland_quintile', 383, N'tinyint', NULL, NULL, 0),
    (N'simd2009v2_education_domain_pw_scotland_decile', 384, N'tinyint', NULL, NULL, 0),
    (N'simd2009v2_access_domain_pw_scotland_quintile', 385, N'tinyint', NULL, NULL, 0),
    (N'simd2009v2_access_domain_pw_scotland_decile', 386, N'tinyint', NULL, NULL, 0),
    (N'simd2009v2_crime_domain_pw_scotland_quintile', 387, N'tinyint', NULL, NULL, 0),
    (N'simd2009v2_crime_domain_pw_scotland_decile', 388, N'tinyint', NULL, NULL, 0),
    (N'simd2009v2_housing_domain_pw_scotland_quintile', 389, N'tinyint', NULL, NULL, 0),
    (N'simd2009v2_housing_domain_pw_scotland_decile', 390, N'tinyint', NULL, NULL, 0),
    (N'simd2012_income_domain_pw_scotland_quintile', 391, N'tinyint', NULL, NULL, 0),
    (N'simd2012_income_domain_pw_scotland_decile', 392, N'tinyint', NULL, NULL, 0),
    (N'simd2012_employment_domain_pw_scotland_quintile', 393, N'tinyint', NULL, NULL, 0),
    (N'simd2012_employment_domain_pw_scotland_decile', 394, N'tinyint', NULL, NULL, 0),
    (N'simd2012_health_domain_pw_scotland_quintile', 395, N'tinyint', NULL, NULL, 0),
    (N'simd2012_health_domain_pw_scotland_decile', 396, N'tinyint', NULL, NULL, 0),
    (N'simd2012_education_domain_pw_scotland_quintile', 397, N'tinyint', NULL, NULL, 0),
    (N'simd2012_education_domain_pw_scotland_decile', 398, N'tinyint', NULL, NULL, 0),
    (N'simd2012_access_domain_pw_scotland_quintile', 399, N'tinyint', NULL, NULL, 0),
    (N'simd2012_access_domain_pw_scotland_decile', 400, N'tinyint', NULL, NULL, 0),
    (N'simd2012_crime_domain_pw_scotland_quintile', 401, N'tinyint', NULL, NULL, 0),
    (N'simd2012_crime_domain_pw_scotland_decile', 402, N'tinyint', NULL, NULL, 0),
    (N'simd2012_housing_domain_pw_scotland_quintile', 403, N'tinyint', NULL, NULL, 0),
    (N'simd2012_housing_domain_pw_scotland_decile', 404, N'tinyint', NULL, NULL, 0),
    (N'simd2016_income_domain_pw_scotland_quintile', 405, N'tinyint', NULL, NULL, 0),
    (N'simd2016_income_domain_pw_scotland_decile', 406, N'tinyint', NULL, NULL, 0),
    (N'simd2016_employment_domain_pw_scotland_quintile', 407, N'tinyint', NULL, NULL, 0),
    (N'simd2016_employment_domain_pw_scotland_decile', 408, N'tinyint', NULL, NULL, 0),
    (N'simd2016_health_domain_pw_scotland_quintile', 409, N'tinyint', NULL, NULL, 0),
    (N'simd2016_health_domain_pw_scotland_decile', 410, N'tinyint', NULL, NULL, 0),
    (N'simd2016_education_domain_pw_scotland_quintile', 411, N'tinyint', NULL, NULL, 0),
    (N'simd2016_education_domain_pw_scotland_decile', 412, N'tinyint', NULL, NULL, 0),
    (N'simd2016_access_domain_pw_scotland_quintile', 413, N'tinyint', NULL, NULL, 0),
    (N'simd2016_access_domain_pw_scotland_decile', 414, N'tinyint', NULL, NULL, 0),
    (N'simd2016_crime_domain_pw_scotland_quintile', 415, N'tinyint', NULL, NULL, 0),
    (N'simd2016_crime_domain_pw_scotland_decile', 416, N'tinyint', NULL, NULL, 0),
    (N'simd2016_housing_domain_pw_scotland_quintile', 417, N'tinyint', NULL, NULL, 0),
    (N'simd2016_housing_domain_pw_scotland_decile', 418, N'tinyint', NULL, NULL, 0),
    (N'simd2020v2_income_domain_pw_scotland_quintile', 419, N'tinyint', NULL, NULL, 0),
    (N'simd2020v2_income_domain_pw_scotland_decile', 420, N'tinyint', NULL, NULL, 0),
    (N'simd2020v2_employment_domain_pw_scotland_quintile', 421, N'tinyint', NULL, NULL, 0),
    (N'simd2020v2_employment_domain_pw_scotland_decile', 422, N'tinyint', NULL, NULL, 0),
    (N'simd2020v2_health_domain_pw_scotland_quintile', 423, N'tinyint', NULL, NULL, 0),
    (N'simd2020v2_health_domain_pw_scotland_decile', 424, N'tinyint', NULL, NULL, 0),
    (N'simd2020v2_education_domain_pw_scotland_quintile', 425, N'tinyint', NULL, NULL, 0),
    (N'simd2020v2_education_domain_pw_scotland_decile', 426, N'tinyint', NULL, NULL, 0),
    (N'simd2020v2_access_domain_pw_scotland_quintile', 427, N'tinyint', NULL, NULL, 0),
    (N'simd2020v2_access_domain_pw_scotland_decile', 428, N'tinyint', NULL, NULL, 0),
    (N'simd2020v2_crime_domain_pw_scotland_quintile', 429, N'tinyint', NULL, NULL, 0),
    (N'simd2020v2_crime_domain_pw_scotland_decile', 430, N'tinyint', NULL, NULL, 0),
    (N'simd2020v2_housing_domain_pw_scotland_quintile', 431, N'tinyint', NULL, NULL, 0),
    (N'simd2020v2_housing_domain_pw_scotland_decile', 432, N'tinyint', NULL, NULL, 0),
    (N'simd2004_population', 433, N'smallint', NULL, NULL, 0),
    (N'simd2006_population', 434, N'smallint', NULL, NULL, 0),
    (N'simd2009v2_population', 435, N'smallint', NULL, NULL, 0),
    (N'simd2012_population', 436, N'smallint', NULL, NULL, 0),
    (N'simd2016_population', 437, N'smallint', NULL, NULL, 0),
    (N'simd2020v2_population', 438, N'smallint', NULL, NULL, 0);

    INSERT INTO #result
    SELECT N'postcode_simd_history', N'columns declared and in order', N'0 differences',
           CONVERT(nvarchar(200), COUNT(*)) + N' differences', COUNT(*), CASE WHEN COUNT(*) = 0 THEN 1 ELSE 0 END
    FROM (
        SELECT d.name FROM @declared_history d
        LEFT JOIN INFORMATION_SCHEMA.COLUMNS c
               ON c.TABLE_NAME = PARSENAME(N'postcode_simd_history', 1) AND c.COLUMN_NAME = d.name
              AND c.ORDINAL_POSITION = d.ordinal AND c.DATA_TYPE = d.type_name
              AND (d.numeric_precision IS NULL
                   OR (c.NUMERIC_PRECISION = d.numeric_precision AND c.NUMERIC_SCALE = d.numeric_scale))
              AND c.IS_NULLABLE = CASE d.is_nullable WHEN 1 THEN 'YES' ELSE 'NO' END
        WHERE c.COLUMN_NAME IS NULL
        UNION ALL
        SELECT c.COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS c
        WHERE c.TABLE_NAME = PARSENAME(N'postcode_simd_history', 1)
          AND c.COLUMN_NAME NOT IN (SELECT name FROM @declared_history)
    ) AS differences;

    -- 2. The natural key, which the digest also cannot see: it is order independent, so a
    -- duplicated row and a missing one can cancel out.
    INSERT INTO #result
    SELECT N'postcode_simd_history', N'natural key [pc_norm], [introduced_on] unique and not null', N'0 offending rows',
           CONVERT(nvarchar(200), COUNT_BIG(*)) + N' offending rows', COUNT_BIG(*),
           CASE WHEN COUNT_BIG(*) = 0 THEN 1 ELSE 0 END
    FROM (SELECT [pc_norm], [introduced_on] FROM postcode_simd_history GROUP BY [pc_norm], [introduced_on] HAVING COUNT_BIG(*) > 1) AS duplicates;

    -- 3. Reserved characters in the text columns. The digest frames fields with them, so text
    -- containing one could make two different rows hash alike. The export refuses them; so does
    -- this. A rendered null is legitimately NCHAR(0), which is why this reads the columns.
    INSERT INTO #result
    SELECT N'postcode_simd_history', N'no reserved framing characters in text', N'0 rows',
           CONVERT(nvarchar(200), COUNT_BIG(*)) + N' rows', COUNT_BIG(*),
           CASE WHEN COUNT_BIG(*) = 0 THEN 1 ELSE 0 END
    FROM postcode_simd_history
    WHERE DATALENGTH(REPLACE(REPLACE([Postcode] COLLATE Latin1_General_100_BIN2_UTF8, NCHAR(0), N''), NCHAR(31), N'')) <> DATALENGTH([Postcode])
       OR DATALENGTH(REPLACE(REPLACE([PostcodeDistrict] COLLATE Latin1_General_100_BIN2_UTF8, NCHAR(0), N''), NCHAR(31), N'')) <> DATALENGTH([PostcodeDistrict])
       OR DATALENGTH(REPLACE(REPLACE([PostcodeSector] COLLATE Latin1_General_100_BIN2_UTF8, NCHAR(0), N''), NCHAR(31), N'')) <> DATALENGTH([PostcodeSector])
       OR DATALENGTH(REPLACE(REPLACE([DateOfIntroduction] COLLATE Latin1_General_100_BIN2_UTF8, NCHAR(0), N''), NCHAR(31), N'')) <> DATALENGTH([DateOfIntroduction])
       OR DATALENGTH(REPLACE(REPLACE([DateOfDeletion] COLLATE Latin1_General_100_BIN2_UTF8, NCHAR(0), N''), NCHAR(31), N'')) <> DATALENGTH([DateOfDeletion])
       OR DATALENGTH(REPLACE(REPLACE([SplitIndicator] COLLATE Latin1_General_100_BIN2_UTF8, NCHAR(0), N''), NCHAR(31), N'')) <> DATALENGTH([SplitIndicator])
       OR DATALENGTH(REPLACE(REPLACE([CouncilArea2019Code] COLLATE Latin1_General_100_BIN2_UTF8, NCHAR(0), N''), NCHAR(31), N'')) <> DATALENGTH([CouncilArea2019Code])
       OR DATALENGTH(REPLACE(REPLACE([UKParliamentaryConstituency2024Code] COLLATE Latin1_General_100_BIN2_UTF8, NCHAR(0), N''), NCHAR(31), N'')) <> DATALENGTH([UKParliamentaryConstituency2024Code])
       OR DATALENGTH(REPLACE(REPLACE([ScottishParliamentaryRegion2026Code] COLLATE Latin1_General_100_BIN2_UTF8, NCHAR(0), N''), NCHAR(31), N'')) <> DATALENGTH([ScottishParliamentaryRegion2026Code])
       OR DATALENGTH(REPLACE(REPLACE([ScottishParliamentaryConstituency2026Code] COLLATE Latin1_General_100_BIN2_UTF8, NCHAR(0), N''), NCHAR(31), N'')) <> DATALENGTH([ScottishParliamentaryConstituency2026Code])
       OR DATALENGTH(REPLACE(REPLACE([ElectoralWard2022Code] COLLATE Latin1_General_100_BIN2_UTF8, NCHAR(0), N''), NCHAR(31), N'')) <> DATALENGTH([ElectoralWard2022Code])
       OR DATALENGTH(REPLACE(REPLACE([HealthBoardArea2019Code] COLLATE Latin1_General_100_BIN2_UTF8, NCHAR(0), N''), NCHAR(31), N'')) <> DATALENGTH([HealthBoardArea2019Code])
       OR DATALENGTH(REPLACE(REPLACE([HealthBoardArea2006Code] COLLATE Latin1_General_100_BIN2_UTF8, NCHAR(0), N''), NCHAR(31), N'')) <> DATALENGTH([HealthBoardArea2006Code])
       OR DATALENGTH(REPLACE(REPLACE([HealthBoardArea1995Code] COLLATE Latin1_General_100_BIN2_UTF8, NCHAR(0), N''), NCHAR(31), N'')) <> DATALENGTH([HealthBoardArea1995Code])
       OR DATALENGTH(REPLACE(REPLACE([IntegrationAuthority2019Code] COLLATE Latin1_General_100_BIN2_UTF8, NCHAR(0), N''), NCHAR(31), N'')) <> DATALENGTH([IntegrationAuthority2019Code])
       OR DATALENGTH(REPLACE(REPLACE([OutputArea2022Code] COLLATE Latin1_General_100_BIN2_UTF8, NCHAR(0), N''), NCHAR(31), N'')) <> DATALENGTH([OutputArea2022Code])
       OR DATALENGTH(REPLACE(REPLACE([OutputArea2011Code] COLLATE Latin1_General_100_BIN2_UTF8, NCHAR(0), N''), NCHAR(31), N'')) <> DATALENGTH([OutputArea2011Code])
       OR DATALENGTH(REPLACE(REPLACE([OutputArea2001Code] COLLATE Latin1_General_100_BIN2_UTF8, NCHAR(0), N''), NCHAR(31), N'')) <> DATALENGTH([OutputArea2001Code])
       OR DATALENGTH(REPLACE(REPLACE([OutputArea1991Code] COLLATE Latin1_General_100_BIN2_UTF8, NCHAR(0), N''), NCHAR(31), N'')) <> DATALENGTH([OutputArea1991Code])
       OR DATALENGTH(REPLACE(REPLACE([DataZone2022Code] COLLATE Latin1_General_100_BIN2_UTF8, NCHAR(0), N''), NCHAR(31), N'')) <> DATALENGTH([DataZone2022Code])
       OR DATALENGTH(REPLACE(REPLACE([DataZone2011Code] COLLATE Latin1_General_100_BIN2_UTF8, NCHAR(0), N''), NCHAR(31), N'')) <> DATALENGTH([DataZone2011Code])
       OR DATALENGTH(REPLACE(REPLACE([DataZone2001Code] COLLATE Latin1_General_100_BIN2_UTF8, NCHAR(0), N''), NCHAR(31), N'')) <> DATALENGTH([DataZone2001Code])
       OR DATALENGTH(REPLACE(REPLACE([IntermediateZone2022Code] COLLATE Latin1_General_100_BIN2_UTF8, NCHAR(0), N''), NCHAR(31), N'')) <> DATALENGTH([IntermediateZone2022Code])
       OR DATALENGTH(REPLACE(REPLACE([IntermediateZone2011Code] COLLATE Latin1_General_100_BIN2_UTF8, NCHAR(0), N''), NCHAR(31), N'')) <> DATALENGTH([IntermediateZone2011Code])
       OR DATALENGTH(REPLACE(REPLACE([IntermediateZone2001Code] COLLATE Latin1_General_100_BIN2_UTF8, NCHAR(0), N''), NCHAR(31), N'')) <> DATALENGTH([IntermediateZone2001Code])
       OR DATALENGTH(REPLACE(REPLACE([CensusHouseholdCount2022] COLLATE Latin1_General_100_BIN2_UTF8, NCHAR(0), N''), NCHAR(31), N'')) <> DATALENGTH([CensusHouseholdCount2022])
       OR DATALENGTH(REPLACE(REPLACE([CensusPopulationCount2022] COLLATE Latin1_General_100_BIN2_UTF8, NCHAR(0), N''), NCHAR(31), N'')) <> DATALENGTH([CensusPopulationCount2022])
       OR DATALENGTH(REPLACE(REPLACE([CensusHouseholdCount2011] COLLATE Latin1_General_100_BIN2_UTF8, NCHAR(0), N''), NCHAR(31), N'')) <> DATALENGTH([CensusHouseholdCount2011])
       OR DATALENGTH(REPLACE(REPLACE([CensusPopulationCount2011] COLLATE Latin1_General_100_BIN2_UTF8, NCHAR(0), N''), NCHAR(31), N'')) <> DATALENGTH([CensusPopulationCount2011])
       OR DATALENGTH(REPLACE(REPLACE([CensusHouseholdCount2001] COLLATE Latin1_General_100_BIN2_UTF8, NCHAR(0), N''), NCHAR(31), N'')) <> DATALENGTH([CensusHouseholdCount2001])
       OR DATALENGTH(REPLACE(REPLACE([CensusPopulationCount2001] COLLATE Latin1_General_100_BIN2_UTF8, NCHAR(0), N''), NCHAR(31), N'')) <> DATALENGTH([CensusPopulationCount2001])
       OR DATALENGTH(REPLACE(REPLACE([CensusHouseholdCount1991] COLLATE Latin1_General_100_BIN2_UTF8, NCHAR(0), N''), NCHAR(31), N'')) <> DATALENGTH([CensusHouseholdCount1991])
       OR DATALENGTH(REPLACE(REPLACE([CensusPopulationCount1991] COLLATE Latin1_General_100_BIN2_UTF8, NCHAR(0), N''), NCHAR(31), N'')) <> DATALENGTH([CensusPopulationCount1991])
       OR DATALENGTH(REPLACE(REPLACE([ScottishIndexOfMultipleDeprivation2020Rank] COLLATE Latin1_General_100_BIN2_UTF8, NCHAR(0), N''), NCHAR(31), N'')) <> DATALENGTH([ScottishIndexOfMultipleDeprivation2020Rank])
       OR DATALENGTH(REPLACE(REPLACE([LAU2025Level1Code] COLLATE Latin1_General_100_BIN2_UTF8, NCHAR(0), N''), NCHAR(31), N'')) <> DATALENGTH([LAU2025Level1Code])
       OR DATALENGTH(REPLACE(REPLACE([ITL2025Level2Code] COLLATE Latin1_General_100_BIN2_UTF8, NCHAR(0), N''), NCHAR(31), N'')) <> DATALENGTH([ITL2025Level2Code])
       OR DATALENGTH(REPLACE(REPLACE([ITL2025Level3Code] COLLATE Latin1_General_100_BIN2_UTF8, NCHAR(0), N''), NCHAR(31), N'')) <> DATALENGTH([ITL2025Level3Code])
       OR DATALENGTH(REPLACE(REPLACE([Locality2020Code] COLLATE Latin1_General_100_BIN2_UTF8, NCHAR(0), N''), NCHAR(31), N'')) <> DATALENGTH([Locality2020Code])
       OR DATALENGTH(REPLACE(REPLACE([Locality2022Code] COLLATE Latin1_General_100_BIN2_UTF8, NCHAR(0), N''), NCHAR(31), N'')) <> DATALENGTH([Locality2022Code])
       OR DATALENGTH(REPLACE(REPLACE([Locality2001Code] COLLATE Latin1_General_100_BIN2_UTF8, NCHAR(0), N''), NCHAR(31), N'')) <> DATALENGTH([Locality2001Code])
       OR DATALENGTH(REPLACE(REPLACE([Locality1991Code] COLLATE Latin1_General_100_BIN2_UTF8, NCHAR(0), N''), NCHAR(31), N'')) <> DATALENGTH([Locality1991Code])
       OR DATALENGTH(REPLACE(REPLACE([Settlement2020Code] COLLATE Latin1_General_100_BIN2_UTF8, NCHAR(0), N''), NCHAR(31), N'')) <> DATALENGTH([Settlement2020Code])
       OR DATALENGTH(REPLACE(REPLACE([Settlement2022Code] COLLATE Latin1_General_100_BIN2_UTF8, NCHAR(0), N''), NCHAR(31), N'')) <> DATALENGTH([Settlement2022Code])
       OR DATALENGTH(REPLACE(REPLACE([Settlement2001Code] COLLATE Latin1_General_100_BIN2_UTF8, NCHAR(0), N''), NCHAR(31), N'')) <> DATALENGTH([Settlement2001Code])
       OR DATALENGTH(REPLACE(REPLACE([CivilParish1930Code] COLLATE Latin1_General_100_BIN2_UTF8, NCHAR(0), N''), NCHAR(31), N'')) <> DATALENGTH([CivilParish1930Code])
       OR DATALENGTH(REPLACE(REPLACE([EnterpriseRegion2008Code] COLLATE Latin1_General_100_BIN2_UTF8, NCHAR(0), N''), NCHAR(31), N'')) <> DATALENGTH([EnterpriseRegion2008Code])
       OR DATALENGTH(REPLACE(REPLACE([IslandCode] COLLATE Latin1_General_100_BIN2_UTF8, NCHAR(0), N''), NCHAR(31), N'')) <> DATALENGTH([IslandCode])
       OR DATALENGTH(REPLACE(REPLACE([LocalGovernmentDistrict1995Code] COLLATE Latin1_General_100_BIN2_UTF8, NCHAR(0), N''), NCHAR(31), N'')) <> DATALENGTH([LocalGovernmentDistrict1995Code])
       OR DATALENGTH(REPLACE(REPLACE([LocalGovernmentDistrict1991Code] COLLATE Latin1_General_100_BIN2_UTF8, NCHAR(0), N''), NCHAR(31), N'')) <> DATALENGTH([LocalGovernmentDistrict1991Code])
       OR DATALENGTH(REPLACE(REPLACE([NationalPark2010Code] COLLATE Latin1_General_100_BIN2_UTF8, NCHAR(0), N''), NCHAR(31), N'')) <> DATALENGTH([NationalPark2010Code])
       OR DATALENGTH(REPLACE(REPLACE([RegistrationDistrict2007Code] COLLATE Latin1_General_100_BIN2_UTF8, NCHAR(0), N''), NCHAR(31), N'')) <> DATALENGTH([RegistrationDistrict2007Code])
       OR DATALENGTH(REPLACE(REPLACE([ROACommunityPlanningPartnership2006Code] COLLATE Latin1_General_100_BIN2_UTF8, NCHAR(0), N''), NCHAR(31), N'')) <> DATALENGTH([ROACommunityPlanningPartnership2006Code])
       OR DATALENGTH(REPLACE(REPLACE([ROALocal2006Code] COLLATE Latin1_General_100_BIN2_UTF8, NCHAR(0), N''), NCHAR(31), N'')) <> DATALENGTH([ROALocal2006Code])
       OR DATALENGTH(REPLACE(REPLACE([StrategicDevelopmentPlanningArea2013Code] COLLATE Latin1_General_100_BIN2_UTF8, NCHAR(0), N''), NCHAR(31), N'')) <> DATALENGTH([StrategicDevelopmentPlanningArea2013Code])
       OR DATALENGTH(REPLACE(REPLACE([TravelToWorkArea2011Code] COLLATE Latin1_General_100_BIN2_UTF8, NCHAR(0), N''), NCHAR(31), N'')) <> DATALENGTH([TravelToWorkArea2011Code])
       OR DATALENGTH(REPLACE(REPLACE([UrbanRural6Fold2022Code] COLLATE Latin1_General_100_BIN2_UTF8, NCHAR(0), N''), NCHAR(31), N'')) <> DATALENGTH([UrbanRural6Fold2022Code])
       OR DATALENGTH(REPLACE(REPLACE([UrbanRural8Fold2022Code] COLLATE Latin1_General_100_BIN2_UTF8, NCHAR(0), N''), NCHAR(31), N'')) <> DATALENGTH([UrbanRural8Fold2022Code])
       OR DATALENGTH(REPLACE(REPLACE([GridLinkIndicator] COLLATE Latin1_General_100_BIN2_UTF8, NCHAR(0), N''), NCHAR(31), N'')) <> DATALENGTH([GridLinkIndicator])
       OR DATALENGTH(REPLACE(REPLACE([GridLinkPositionalAccuracy] COLLATE Latin1_General_100_BIN2_UTF8, NCHAR(0), N''), NCHAR(31), N'')) <> DATALENGTH([GridLinkPositionalAccuracy])
       OR DATALENGTH(REPLACE(REPLACE([NeverDigitised] COLLATE Latin1_General_100_BIN2_UTF8, NCHAR(0), N''), NCHAR(31), N'')) <> DATALENGTH([NeverDigitised])
       OR DATALENGTH(REPLACE(REPLACE([LinkedSmallUserPostcode] COLLATE Latin1_General_100_BIN2_UTF8, NCHAR(0), N''), NCHAR(31), N'')) <> DATALENGTH([LinkedSmallUserPostcode])
       OR DATALENGTH(REPLACE(REPLACE([pc_norm] COLLATE Latin1_General_100_BIN2_UTF8, NCHAR(0), N''), NCHAR(31), N'')) <> DATALENGTH([pc_norm])
       OR DATALENGTH(REPLACE(REPLACE([pc_base] COLLATE Latin1_General_100_BIN2_UTF8, NCHAR(0), N''), NCHAR(31), N'')) <> DATALENGTH([pc_base])
       OR DATALENGTH(REPLACE(REPLACE([spd_user_type] COLLATE Latin1_General_100_BIN2_UTF8, NCHAR(0), N''), NCHAR(31), N'')) <> DATALENGTH([spd_user_type])
       OR DATALENGTH(REPLACE(REPLACE([spd_release] COLLATE Latin1_General_100_BIN2_UTF8, NCHAR(0), N''), NCHAR(31), N'')) <> DATALENGTH([spd_release])
       OR DATALENGTH(REPLACE(REPLACE([phs_dz2001_hb] COLLATE Latin1_General_100_BIN2_UTF8, NCHAR(0), N''), NCHAR(31), N'')) <> DATALENGTH([phs_dz2001_hb])
       OR DATALENGTH(REPLACE(REPLACE([phs_dz2001_hscp] COLLATE Latin1_General_100_BIN2_UTF8, NCHAR(0), N''), NCHAR(31), N'')) <> DATALENGTH([phs_dz2001_hscp])
       OR DATALENGTH(REPLACE(REPLACE([phs_dz2001_ca] COLLATE Latin1_General_100_BIN2_UTF8, NCHAR(0), N''), NCHAR(31), N'')) <> DATALENGTH([phs_dz2001_ca])
       OR DATALENGTH(REPLACE(REPLACE([phs_dz2011_hb] COLLATE Latin1_General_100_BIN2_UTF8, NCHAR(0), N''), NCHAR(31), N'')) <> DATALENGTH([phs_dz2011_hb])
       OR DATALENGTH(REPLACE(REPLACE([phs_dz2011_hscp] COLLATE Latin1_General_100_BIN2_UTF8, NCHAR(0), N''), NCHAR(31), N'')) <> DATALENGTH([phs_dz2011_hscp])
       OR DATALENGTH(REPLACE(REPLACE([phs_dz2011_ca] COLLATE Latin1_General_100_BIN2_UTF8, NCHAR(0), N''), NCHAR(31), N'')) <> DATALENGTH([phs_dz2011_ca])
       OR DATALENGTH(REPLACE(REPLACE([urbanrural2003_2004_status] COLLATE Latin1_General_100_BIN2_UTF8, NCHAR(0), N''), NCHAR(31), N'')) <> DATALENGTH([urbanrural2003_2004_status])
       OR DATALENGTH(REPLACE(REPLACE([urbanrural2005_2006_status] COLLATE Latin1_General_100_BIN2_UTF8, NCHAR(0), N''), NCHAR(31), N'')) <> DATALENGTH([urbanrural2005_2006_status])
       OR DATALENGTH(REPLACE(REPLACE([urbanrural2007_2008_status] COLLATE Latin1_General_100_BIN2_UTF8, NCHAR(0), N''), NCHAR(31), N'')) <> DATALENGTH([urbanrural2007_2008_status])
       OR DATALENGTH(REPLACE(REPLACE([urbanrural2009_2010_status] COLLATE Latin1_General_100_BIN2_UTF8, NCHAR(0), N''), NCHAR(31), N'')) <> DATALENGTH([urbanrural2009_2010_status])
       OR DATALENGTH(REPLACE(REPLACE([urbanrural2011_2012_status] COLLATE Latin1_General_100_BIN2_UTF8, NCHAR(0), N''), NCHAR(31), N'')) <> DATALENGTH([urbanrural2011_2012_status])
       OR DATALENGTH(REPLACE(REPLACE([urbanrural2013_2014_status] COLLATE Latin1_General_100_BIN2_UTF8, NCHAR(0), N''), NCHAR(31), N'')) <> DATALENGTH([urbanrural2013_2014_status])
       OR DATALENGTH(REPLACE(REPLACE([urbanrural2016_status] COLLATE Latin1_General_100_BIN2_UTF8, NCHAR(0), N''), NCHAR(31), N'')) <> DATALENGTH([urbanrural2016_status])
       OR DATALENGTH(REPLACE(REPLACE([urbanrural2020_status] COLLATE Latin1_General_100_BIN2_UTF8, NCHAR(0), N''), NCHAR(31), N'')) <> DATALENGTH([urbanrural2020_status])
       OR DATALENGTH(REPLACE(REPLACE([urbanrural2022_status] COLLATE Latin1_General_100_BIN2_UTF8, NCHAR(0), N''), NCHAR(31), N'')) <> DATALENGTH([urbanrural2022_status])
       OR DATALENGTH(REPLACE(REPLACE([simd2020v2_housing_domain_rank_source_status] COLLATE Latin1_General_100_BIN2_UTF8, NCHAR(0), N''), NCHAR(31), N'')) <> DATALENGTH([simd2020v2_housing_domain_rank_source_status]);

    -- 4. The digest. Each row is rendered as the CSV renders it, with NCHAR(0) for a null so a
    -- null and a blank differ, the fields joined with NCHAR(31) in contract order, converted to
    -- UTF-8 and hashed. The signed eight-byte prefixes are summed as decimal: converting after
    -- SUM would be too late, because SUM over bigint can overflow.
    INSERT INTO #result
    SELECT N'postcode_simd_history', N'row count', CONVERT(nvarchar(200), e.row_count),
           CONVERT(nvarchar(200), q.n), ABS(q.n - e.row_count),
           CASE WHEN q.n = e.row_count THEN 1 ELSE 0 END
    FROM #expected e CROSS JOIN (SELECT COUNT_BIG(*) AS n FROM postcode_simd_history) AS q
    WHERE e.name = N'postcode_simd_history';

    INSERT INTO #result
    SELECT N'postcode_simd_history', N'row digest, definition version ' + CONVERT(nvarchar(10), e.digest_version),
           CONVERT(nvarchar(200), e.digest_total), CONVERT(nvarchar(200), ISNULL(q.total, 0)),
           CASE WHEN ISNULL(q.total, 0) = e.digest_total THEN 0 ELSE 1 END,
           CASE WHEN ISNULL(q.total, 0) = e.digest_total THEN 1 ELSE 0 END
    FROM #expected e CROSS JOIN (
        SELECT SUM(CONVERT(decimal(38,0), CONVERT(bigint, SUBSTRING(HASHBYTES('SHA2_256',
                   CONVERT(varchar(max), CONCAT_WS(NCHAR(31),
           CONCAT_WS(NCHAR(31),
           CONVERT(nvarchar(max), ISNULL([Postcode], NCHAR(0))),
           ISNULL([PostcodeDistrict], NCHAR(0)),
           ISNULL([PostcodeSector], NCHAR(0)),
           ISNULL([DateOfIntroduction], NCHAR(0)),
           ISNULL([DateOfDeletion], NCHAR(0)),
           ISNULL([SplitIndicator], NCHAR(0)),
           ISNULL([CouncilArea2019Code], NCHAR(0)),
           ISNULL([UKParliamentaryConstituency2024Code], NCHAR(0)),
           ISNULL([ScottishParliamentaryRegion2026Code], NCHAR(0)),
           ISNULL([ScottishParliamentaryConstituency2026Code], NCHAR(0)),
           ISNULL([ElectoralWard2022Code], NCHAR(0)),
           ISNULL([HealthBoardArea2019Code], NCHAR(0)),
           ISNULL([HealthBoardArea2006Code], NCHAR(0)),
           ISNULL([HealthBoardArea1995Code], NCHAR(0)),
           ISNULL([IntegrationAuthority2019Code], NCHAR(0)),
           ISNULL([OutputArea2022Code], NCHAR(0)),
           ISNULL([OutputArea2011Code], NCHAR(0)),
           ISNULL([OutputArea2001Code], NCHAR(0)),
           ISNULL([OutputArea1991Code], NCHAR(0)),
           ISNULL([DataZone2022Code], NCHAR(0)),
           ISNULL([DataZone2011Code], NCHAR(0)),
           ISNULL([DataZone2001Code], NCHAR(0)),
           ISNULL([IntermediateZone2022Code], NCHAR(0)),
           ISNULL([IntermediateZone2011Code], NCHAR(0)),
           ISNULL([IntermediateZone2001Code], NCHAR(0)),
           ISNULL([CensusHouseholdCount2022], NCHAR(0)),
           ISNULL([CensusPopulationCount2022], NCHAR(0)),
           ISNULL([CensusHouseholdCount2011], NCHAR(0)),
           ISNULL([CensusPopulationCount2011], NCHAR(0)),
           ISNULL([CensusHouseholdCount2001], NCHAR(0)),
           ISNULL([CensusPopulationCount2001], NCHAR(0)),
           ISNULL([CensusHouseholdCount1991], NCHAR(0)),
           ISNULL([CensusPopulationCount1991], NCHAR(0)),
           ISNULL([ScottishIndexOfMultipleDeprivation2020Rank], NCHAR(0)),
           ISNULL([LAU2025Level1Code], NCHAR(0)),
           ISNULL([ITL2025Level2Code], NCHAR(0)),
           ISNULL([ITL2025Level3Code], NCHAR(0)),
           ISNULL([Locality2020Code], NCHAR(0)),
           ISNULL([Locality2022Code], NCHAR(0)),
           ISNULL([Locality2001Code], NCHAR(0)),
           ISNULL([Locality1991Code], NCHAR(0)),
           ISNULL([Settlement2020Code], NCHAR(0)),
           ISNULL([Settlement2022Code], NCHAR(0)),
           ISNULL([Settlement2001Code], NCHAR(0)),
           ISNULL([CivilParish1930Code], NCHAR(0)),
           ISNULL([EnterpriseRegion2008Code], NCHAR(0)),
           ISNULL([IslandCode], NCHAR(0)),
           ISNULL([LocalGovernmentDistrict1995Code], NCHAR(0)),
           ISNULL([LocalGovernmentDistrict1991Code], NCHAR(0)),
           ISNULL([NationalPark2010Code], NCHAR(0)),
           ISNULL([RegistrationDistrict2007Code], NCHAR(0)),
           ISNULL([ROACommunityPlanningPartnership2006Code], NCHAR(0)),
           ISNULL([ROALocal2006Code], NCHAR(0)),
           ISNULL([StrategicDevelopmentPlanningArea2013Code], NCHAR(0)),
           ISNULL([TravelToWorkArea2011Code], NCHAR(0)),
           ISNULL([UrbanRural6Fold2022Code], NCHAR(0)),
           ISNULL([UrbanRural8Fold2022Code], NCHAR(0)),
           ISNULL([GridLinkIndicator], NCHAR(0)),
           ISNULL([GridLinkPositionalAccuracy], NCHAR(0)),
           ISNULL([NeverDigitised], NCHAR(0)),
           ISNULL([LinkedSmallUserPostcode], NCHAR(0)),
           ISNULL([pc_norm], NCHAR(0)),
           ISNULL([pc_base], NCHAR(0)),
           ISNULL([spd_user_type], NCHAR(0)),
           ISNULL([spd_release], NCHAR(0)),
           ISNULL(CONVERT(varchar(10), [introduced_on], 23), NCHAR(0)),
           ISNULL(CONVERT(varchar(10), [deleted_on], 23), NCHAR(0)),
           ISNULL(CONVERT(varchar(1), CONVERT(tinyint, [is_current])), NCHAR(0)),
           ISNULL([phs_dz2001_hb], NCHAR(0)),
           ISNULL([phs_dz2001_hscp], NCHAR(0)),
           ISNULL([phs_dz2001_ca], NCHAR(0)),
           ISNULL([phs_dz2011_hb], NCHAR(0)),
           ISNULL([phs_dz2011_hscp], NCHAR(0)),
           ISNULL([phs_dz2011_ca], NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2004_rank]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2004_pw_scotland_decile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2004_pw_scotland_quintile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2004_pw_hb_decile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2004_pw_hb_quintile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2004_pw_hscp_decile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2004_pw_hscp_quintile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2004_pw_ca_decile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2004_pw_ca_quintile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2004_most15pc]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2004_least15pc]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2004_uw_scotland_quintile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2004_uw_scotland_decile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2004_uw_scotland_vigintile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2006_rank]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2006_pw_scotland_decile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2006_pw_scotland_quintile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2006_pw_hb_decile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2006_pw_hb_quintile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2006_pw_hscp_decile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2006_pw_hscp_quintile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2006_pw_ca_decile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2006_pw_ca_quintile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2006_most15pc]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2006_least15pc]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2006_uw_scotland_quintile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2006_uw_scotland_decile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2006_uw_scotland_vigintile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2009v2_rank]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2009v2_pw_scotland_decile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2009v2_pw_scotland_quintile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2009v2_pw_hb_decile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2009v2_pw_hb_quintile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2009v2_pw_hscp_decile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2009v2_pw_hscp_quintile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2009v2_pw_ca_decile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2009v2_pw_ca_quintile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2009v2_most15pc]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2009v2_least15pc]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2009v2_uw_scotland_quintile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2009v2_uw_scotland_decile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2009v2_uw_scotland_vigintile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2012_rank]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2012_pw_scotland_decile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2012_pw_scotland_quintile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2012_pw_hb_decile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2012_pw_hb_quintile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2012_pw_hscp_decile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2012_pw_hscp_quintile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2012_pw_ca_decile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2012_pw_ca_quintile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2012_most15pc]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2012_least15pc]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2012_uw_scotland_quintile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2012_uw_scotland_decile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2012_uw_scotland_vigintile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2016_rank]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2016_pw_scotland_decile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2016_pw_scotland_quintile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2016_pw_hb_decile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2016_pw_hb_quintile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2016_pw_hscp_decile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2016_pw_hscp_quintile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2016_pw_ca_decile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2016_pw_ca_quintile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2016_most15pc]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2016_least15pc]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2016_uw_scotland_quintile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2016_uw_scotland_decile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2016_uw_scotland_vigintile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2020v2_rank]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2020v2_pw_scotland_decile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2020v2_pw_scotland_quintile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2020v2_pw_hb_decile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2020v2_pw_hb_quintile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2020v2_pw_hscp_decile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2020v2_pw_hscp_quintile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2020v2_pw_ca_decile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2020v2_pw_ca_quintile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2020v2_most15pc]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2020v2_least15pc]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2020v2_uw_scotland_quintile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2020v2_uw_scotland_decile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2020v2_uw_scotland_vigintile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [urbanrural2003_2004_6fold]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [urbanrural2003_2004_8fold]), NCHAR(0)),
           ISNULL([urbanrural2003_2004_status], NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [urbanrural2005_2006_6fold]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [urbanrural2005_2006_8fold]), NCHAR(0)),
           ISNULL([urbanrural2005_2006_status], NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [urbanrural2007_2008_6fold]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [urbanrural2007_2008_8fold]), NCHAR(0)),
           ISNULL([urbanrural2007_2008_status], NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [urbanrural2009_2010_6fold]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [urbanrural2009_2010_8fold]), NCHAR(0)),
           ISNULL([urbanrural2009_2010_status], NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [urbanrural2011_2012_6fold]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [urbanrural2011_2012_8fold]), NCHAR(0)),
           ISNULL([urbanrural2011_2012_status], NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [urbanrural2013_2014_6fold]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [urbanrural2013_2014_8fold]), NCHAR(0)),
           ISNULL([urbanrural2013_2014_status], NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [urbanrural2016_6fold]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [urbanrural2016_8fold]), NCHAR(0)),
           ISNULL([urbanrural2016_status], NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [urbanrural2020_6fold]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [urbanrural2020_8fold]), NCHAR(0)),
           ISNULL([urbanrural2020_status], NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [urbanrural2022_6fold]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [urbanrural2022_8fold]), NCHAR(0)),
           ISNULL([urbanrural2022_status], NCHAR(0)),
           ISNULL(CONVERT(varchar(12), [simd2004_income_domain_rank]), NCHAR(0)),
           ISNULL(CONVERT(varchar(12), [simd2004_employment_domain_rank]), NCHAR(0)),
           ISNULL(CONVERT(varchar(12), [simd2004_health_domain_rank]), NCHAR(0)),
           ISNULL(CONVERT(varchar(12), [simd2004_education_domain_rank]), NCHAR(0)),
           ISNULL(CONVERT(varchar(12), [simd2004_access_domain_rank]), NCHAR(0)),
           ISNULL(CONVERT(varchar(12), [simd2004_housing_domain_rank]), NCHAR(0)),
           ISNULL(CONVERT(varchar(12), [simd2006_income_domain_rank]), NCHAR(0)),
           ISNULL(CONVERT(varchar(12), [simd2006_employment_domain_rank]), NCHAR(0)),
           ISNULL(CONVERT(varchar(12), [simd2006_health_domain_rank]), NCHAR(0)),
           ISNULL(CONVERT(varchar(12), [simd2006_education_domain_rank]), NCHAR(0)),
           ISNULL(CONVERT(varchar(12), [simd2006_access_domain_rank]), NCHAR(0)),
           ISNULL(CONVERT(varchar(12), [simd2006_crime_domain_rank]), NCHAR(0)),
           ISNULL(CONVERT(varchar(12), [simd2006_housing_domain_rank]), NCHAR(0)),
           ISNULL(CONVERT(varchar(12), [simd2009v2_income_domain_rank]), NCHAR(0)),
           ISNULL(CONVERT(varchar(12), [simd2009v2_employment_domain_rank]), NCHAR(0))),
           CONCAT_WS(NCHAR(31),
           CONVERT(nvarchar(max), ISNULL(CONVERT(varchar(12), [simd2009v2_health_domain_rank]), NCHAR(0))),
           ISNULL(CONVERT(varchar(12), [simd2009v2_education_domain_rank]), NCHAR(0)),
           ISNULL(CONVERT(varchar(12), [simd2009v2_access_domain_rank]), NCHAR(0)),
           ISNULL(CONVERT(varchar(12), [simd2009v2_crime_domain_rank]), NCHAR(0)),
           ISNULL(CONVERT(varchar(12), [simd2009v2_housing_domain_rank]), NCHAR(0)),
           ISNULL(CONVERT(varchar(12), [simd2012_income_domain_rank]), NCHAR(0)),
           ISNULL(CONVERT(varchar(12), [simd2012_employment_domain_rank]), NCHAR(0)),
           ISNULL(CONVERT(varchar(12), [simd2012_health_domain_rank]), NCHAR(0)),
           ISNULL(CONVERT(varchar(12), [simd2012_education_domain_rank]), NCHAR(0)),
           ISNULL(CONVERT(varchar(12), [simd2012_access_domain_rank]), NCHAR(0)),
           ISNULL(CONVERT(varchar(12), [simd2012_crime_domain_rank]), NCHAR(0)),
           ISNULL(CONVERT(varchar(12), [simd2012_housing_domain_rank]), NCHAR(0)),
           ISNULL(CONVERT(varchar(12), [simd2016_income_domain_rank]), NCHAR(0)),
           ISNULL(CONVERT(varchar(12), [simd2016_employment_domain_rank]), NCHAR(0)),
           ISNULL(CONVERT(varchar(12), [simd2016_health_domain_rank]), NCHAR(0)),
           ISNULL(CONVERT(varchar(12), [simd2016_education_domain_rank]), NCHAR(0)),
           ISNULL(CONVERT(varchar(12), [simd2016_access_domain_rank]), NCHAR(0)),
           ISNULL(CONVERT(varchar(12), [simd2016_crime_domain_rank]), NCHAR(0)),
           ISNULL(CONVERT(varchar(12), [simd2016_housing_domain_rank]), NCHAR(0)),
           ISNULL(CONVERT(varchar(12), [simd2020v2_income_domain_rank]), NCHAR(0)),
           ISNULL(CONVERT(varchar(12), [simd2020v2_employment_domain_rank]), NCHAR(0)),
           ISNULL(CONVERT(varchar(12), [simd2020v2_health_domain_rank]), NCHAR(0)),
           ISNULL(CONVERT(varchar(12), [simd2020v2_education_domain_rank]), NCHAR(0)),
           ISNULL(CONVERT(varchar(12), [simd2020v2_access_domain_rank]), NCHAR(0)),
           ISNULL(CONVERT(varchar(12), [simd2020v2_crime_domain_rank]), NCHAR(0)),
           ISNULL(CONVERT(varchar(12), [simd2020v2_housing_domain_rank]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2004_income_domain_quintile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2004_income_domain_decile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2004_income_domain_vigintile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2004_employment_domain_quintile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2004_employment_domain_decile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2004_employment_domain_vigintile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2004_health_domain_quintile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2004_health_domain_decile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2004_health_domain_vigintile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2004_education_domain_quintile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2004_education_domain_decile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2004_education_domain_vigintile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2004_access_domain_quintile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2004_access_domain_decile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2004_access_domain_vigintile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2004_housing_domain_quintile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2004_housing_domain_decile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2004_housing_domain_vigintile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2006_income_domain_quintile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2006_income_domain_decile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2006_income_domain_vigintile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2006_employment_domain_quintile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2006_employment_domain_decile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2006_employment_domain_vigintile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2006_health_domain_quintile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2006_health_domain_decile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2006_health_domain_vigintile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2006_education_domain_quintile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2006_education_domain_decile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2006_education_domain_vigintile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2006_access_domain_quintile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2006_access_domain_decile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2006_access_domain_vigintile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2006_crime_domain_quintile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2006_crime_domain_decile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2006_crime_domain_vigintile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2006_housing_domain_quintile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2006_housing_domain_decile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2006_housing_domain_vigintile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2009v2_income_domain_quintile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2009v2_income_domain_decile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2009v2_income_domain_vigintile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2009v2_employment_domain_quintile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2009v2_employment_domain_decile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2009v2_employment_domain_vigintile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2009v2_health_domain_quintile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2009v2_health_domain_decile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2009v2_health_domain_vigintile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2009v2_education_domain_quintile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2009v2_education_domain_decile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2009v2_education_domain_vigintile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2009v2_access_domain_quintile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2009v2_access_domain_decile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2009v2_access_domain_vigintile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2009v2_crime_domain_quintile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2009v2_crime_domain_decile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2009v2_crime_domain_vigintile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2009v2_housing_domain_quintile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2009v2_housing_domain_decile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2009v2_housing_domain_vigintile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2012_income_domain_quintile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2012_income_domain_decile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2012_income_domain_vigintile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2012_employment_domain_quintile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2012_employment_domain_decile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2012_employment_domain_vigintile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2012_health_domain_quintile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2012_health_domain_decile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2012_health_domain_vigintile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2012_education_domain_quintile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2012_education_domain_decile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2012_education_domain_vigintile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2012_access_domain_quintile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2012_access_domain_decile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2012_access_domain_vigintile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2012_crime_domain_quintile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2012_crime_domain_decile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2012_crime_domain_vigintile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2012_housing_domain_quintile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2012_housing_domain_decile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2012_housing_domain_vigintile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2016_income_domain_quintile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2016_income_domain_decile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2016_income_domain_vigintile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2016_employment_domain_quintile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2016_employment_domain_decile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2016_employment_domain_vigintile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2016_health_domain_quintile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2016_health_domain_decile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2016_health_domain_vigintile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2016_education_domain_quintile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2016_education_domain_decile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2016_education_domain_vigintile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2016_access_domain_quintile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2016_access_domain_decile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2016_access_domain_vigintile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2016_crime_domain_quintile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2016_crime_domain_decile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2016_crime_domain_vigintile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2016_housing_domain_quintile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2016_housing_domain_decile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2016_housing_domain_vigintile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2020v2_income_domain_quintile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2020v2_income_domain_decile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2020v2_income_domain_vigintile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2020v2_employment_domain_quintile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2020v2_employment_domain_decile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2020v2_employment_domain_vigintile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2020v2_health_domain_quintile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2020v2_health_domain_decile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2020v2_health_domain_vigintile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2020v2_education_domain_quintile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2020v2_education_domain_decile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2020v2_education_domain_vigintile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2020v2_access_domain_quintile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2020v2_access_domain_decile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2020v2_access_domain_vigintile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2020v2_crime_domain_quintile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2020v2_crime_domain_decile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2020v2_crime_domain_vigintile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2020v2_housing_domain_quintile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2020v2_housing_domain_decile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2020v2_housing_domain_vigintile]), NCHAR(0)),
           ISNULL([simd2020v2_housing_domain_rank_source_status], NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2004_income_domain_pw_scotland_quintile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2004_income_domain_pw_scotland_decile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2004_employment_domain_pw_scotland_quintile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2004_employment_domain_pw_scotland_decile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2004_health_domain_pw_scotland_quintile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2004_health_domain_pw_scotland_decile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2004_education_domain_pw_scotland_quintile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2004_education_domain_pw_scotland_decile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2004_access_domain_pw_scotland_quintile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2004_access_domain_pw_scotland_decile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2004_housing_domain_pw_scotland_quintile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2004_housing_domain_pw_scotland_decile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2006_income_domain_pw_scotland_quintile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2006_income_domain_pw_scotland_decile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2006_employment_domain_pw_scotland_quintile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2006_employment_domain_pw_scotland_decile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2006_health_domain_pw_scotland_quintile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2006_health_domain_pw_scotland_decile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2006_education_domain_pw_scotland_quintile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2006_education_domain_pw_scotland_decile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2006_access_domain_pw_scotland_quintile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2006_access_domain_pw_scotland_decile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2006_crime_domain_pw_scotland_quintile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2006_crime_domain_pw_scotland_decile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2006_housing_domain_pw_scotland_quintile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2006_housing_domain_pw_scotland_decile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2009v2_income_domain_pw_scotland_quintile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2009v2_income_domain_pw_scotland_decile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2009v2_employment_domain_pw_scotland_quintile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2009v2_employment_domain_pw_scotland_decile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2009v2_health_domain_pw_scotland_quintile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2009v2_health_domain_pw_scotland_decile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2009v2_education_domain_pw_scotland_quintile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2009v2_education_domain_pw_scotland_decile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2009v2_access_domain_pw_scotland_quintile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2009v2_access_domain_pw_scotland_decile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2009v2_crime_domain_pw_scotland_quintile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2009v2_crime_domain_pw_scotland_decile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2009v2_housing_domain_pw_scotland_quintile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2009v2_housing_domain_pw_scotland_decile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2012_income_domain_pw_scotland_quintile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2012_income_domain_pw_scotland_decile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2012_employment_domain_pw_scotland_quintile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2012_employment_domain_pw_scotland_decile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2012_health_domain_pw_scotland_quintile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2012_health_domain_pw_scotland_decile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2012_education_domain_pw_scotland_quintile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2012_education_domain_pw_scotland_decile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2012_access_domain_pw_scotland_quintile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2012_access_domain_pw_scotland_decile]), NCHAR(0))),
           CONCAT_WS(NCHAR(31),
           CONVERT(nvarchar(max), ISNULL(CONVERT(varchar(11), [simd2012_crime_domain_pw_scotland_quintile]), NCHAR(0))),
           ISNULL(CONVERT(varchar(11), [simd2012_crime_domain_pw_scotland_decile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2012_housing_domain_pw_scotland_quintile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2012_housing_domain_pw_scotland_decile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2016_income_domain_pw_scotland_quintile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2016_income_domain_pw_scotland_decile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2016_employment_domain_pw_scotland_quintile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2016_employment_domain_pw_scotland_decile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2016_health_domain_pw_scotland_quintile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2016_health_domain_pw_scotland_decile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2016_education_domain_pw_scotland_quintile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2016_education_domain_pw_scotland_decile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2016_access_domain_pw_scotland_quintile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2016_access_domain_pw_scotland_decile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2016_crime_domain_pw_scotland_quintile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2016_crime_domain_pw_scotland_decile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2016_housing_domain_pw_scotland_quintile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2016_housing_domain_pw_scotland_decile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2020v2_income_domain_pw_scotland_quintile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2020v2_income_domain_pw_scotland_decile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2020v2_employment_domain_pw_scotland_quintile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2020v2_employment_domain_pw_scotland_decile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2020v2_health_domain_pw_scotland_quintile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2020v2_health_domain_pw_scotland_decile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2020v2_education_domain_pw_scotland_quintile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2020v2_education_domain_pw_scotland_decile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2020v2_access_domain_pw_scotland_quintile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2020v2_access_domain_pw_scotland_decile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2020v2_crime_domain_pw_scotland_quintile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2020v2_crime_domain_pw_scotland_decile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2020v2_housing_domain_pw_scotland_quintile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2020v2_housing_domain_pw_scotland_decile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2004_population]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2006_population]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2009v2_population]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2012_population]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2016_population]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2020v2_population]), NCHAR(0)))) COLLATE Latin1_General_100_BIN2_UTF8)), 1, 8)))) AS total
        FROM postcode_simd_history) AS q
    WHERE e.name = N'postcode_simd_history';
END

-- ============================================================================================
-- postcode_simd: structure, key, then the digest
-- ============================================================================================
IF OBJECT_ID(N'postcode_simd', N'U') IS NULL
    INSERT INTO #result VALUES (N'postcode_simd', N'table exists', N'a table', N'not found', NULL, 0);
ELSE
BEGIN
    -- 1. Structure. A digest cannot establish any of this: a missing column, a wrong type, a
    -- column that should not accept NULL, or a changed order would all be invisible to it.
    DECLARE @declared_main TABLE (name sysname, ordinal int, type_name sysname,
                                     numeric_precision int, numeric_scale int, is_nullable bit);
    INSERT INTO @declared_main (name, ordinal, type_name, numeric_precision, numeric_scale, is_nullable) VALUES
    (N'Postcode', 1, N'nvarchar', NULL, NULL, 0),
    (N'PostcodeDistrict', 2, N'nvarchar', NULL, NULL, 0),
    (N'PostcodeSector', 3, N'nvarchar', NULL, NULL, 0),
    (N'SplitIndicator', 4, N'nvarchar', NULL, NULL, 0),
    (N'LinkedSmallUserPostcode', 5, N'nvarchar', NULL, NULL, 0),
    (N'DateOfIntroduction', 6, N'nvarchar', NULL, NULL, 0),
    (N'DateOfDeletion', 7, N'nvarchar', NULL, NULL, 0),
    (N'PostcodeType', 8, N'nvarchar', NULL, NULL, 0),
    (N'OutputArea2022Code', 9, N'nvarchar', NULL, NULL, 0),
    (N'OutputArea2011Code', 10, N'nvarchar', NULL, NULL, 0),
    (N'DataZone2022Code', 11, N'nvarchar', NULL, NULL, 0),
    (N'DataZone2011Code', 12, N'nvarchar', NULL, NULL, 0),
    (N'IntermediateZone2022Code', 13, N'nvarchar', NULL, NULL, 0),
    (N'IntermediateZone2011Code', 14, N'nvarchar', NULL, NULL, 0),
    (N'OutputArea2001Code', 15, N'nvarchar', NULL, NULL, 0),
    (N'DataZone2001Code', 16, N'nvarchar', NULL, NULL, 0),
    (N'IntermediateZone2001Code', 17, N'nvarchar', NULL, NULL, 0),
    (N'OutputArea1991Code', 18, N'nvarchar', NULL, NULL, 0),
    (N'CouncilArea2019Code', 19, N'nvarchar', NULL, NULL, 0),
    (N'RegistrationDistrict2007Code', 20, N'nvarchar', NULL, NULL, 0),
    (N'LocalGovernmentDistrict1995Code', 21, N'nvarchar', NULL, NULL, 0),
    (N'LocalGovernmentDistrict1991Code', 22, N'nvarchar', NULL, NULL, 0),
    (N'ElectoralWard2022Code', 23, N'nvarchar', NULL, NULL, 0),
    (N'ScottishParliamentaryRegion2026Code', 24, N'nvarchar', NULL, NULL, 0),
    (N'ScottishParliamentaryConstituency2026Code', 25, N'nvarchar', NULL, NULL, 0),
    (N'UKParliamentaryConstituency2024Code', 26, N'nvarchar', NULL, NULL, 0),
    (N'HealthBoardArea2019Code', 27, N'nvarchar', NULL, NULL, 0),
    (N'HealthBoardArea2006Code', 28, N'nvarchar', NULL, NULL, 0),
    (N'HealthBoardArea1995Code', 29, N'nvarchar', NULL, NULL, 0),
    (N'IntegrationAuthority2019Code', 30, N'nvarchar', NULL, NULL, 0),
    (N'StrategicDevelopmentPlanningArea2013Code', 31, N'nvarchar', NULL, NULL, 0),
    (N'CivilParish1930Code', 32, N'nvarchar', NULL, NULL, 0),
    (N'TravelToWorkArea2011Code', 33, N'nvarchar', NULL, NULL, 0),
    (N'IslandCode', 34, N'nvarchar', NULL, NULL, 0),
    (N'UrbanRural6Fold2022Code', 35, N'nvarchar', NULL, NULL, 0),
    (N'UrbanRural8Fold2022Code', 36, N'nvarchar', NULL, NULL, 0),
    (N'LAU2025Level1Code', 37, N'nvarchar', NULL, NULL, 0),
    (N'ITL2025Level2Code', 38, N'nvarchar', NULL, NULL, 0),
    (N'ITL2025Level3Code', 39, N'nvarchar', NULL, NULL, 0),
    (N'CensusHouseholdCount2022', 40, N'nvarchar', NULL, NULL, 0),
    (N'CensusPopulationCount2022', 41, N'nvarchar', NULL, NULL, 0),
    (N'CensusHouseholdCount2011', 42, N'nvarchar', NULL, NULL, 0),
    (N'CensusPopulationCount2011', 43, N'nvarchar', NULL, NULL, 0),
    (N'CensusHouseholdCount2001', 44, N'nvarchar', NULL, NULL, 0),
    (N'CensusPopulationCount2001', 45, N'nvarchar', NULL, NULL, 0),
    (N'CensusHouseholdCount1991', 46, N'nvarchar', NULL, NULL, 0),
    (N'CensusPopulationCount1991', 47, N'nvarchar', NULL, NULL, 0),
    (N'ScottishIndexOfMultipleDeprivation2020Rank', 48, N'nvarchar', NULL, NULL, 0),
    (N'pc_norm', 49, N'nvarchar', NULL, NULL, 0),
    (N'spd_user_type', 50, N'nvarchar', NULL, NULL, 0),
    (N'sspl_release', 51, N'nvarchar', NULL, NULL, 0),
    (N'introduced_on', 52, N'date', NULL, NULL, 0),
    (N'deleted_on', 53, N'date', NULL, NULL, 1),
    (N'is_current', 54, N'bit', NULL, NULL, 0),
    (N'phs_dz2001_hb', 55, N'nvarchar', NULL, NULL, 0),
    (N'phs_dz2001_hscp', 56, N'nvarchar', NULL, NULL, 0),
    (N'phs_dz2001_ca', 57, N'nvarchar', NULL, NULL, 0),
    (N'phs_dz2011_hb', 58, N'nvarchar', NULL, NULL, 0),
    (N'phs_dz2011_hscp', 59, N'nvarchar', NULL, NULL, 0),
    (N'phs_dz2011_ca', 60, N'nvarchar', NULL, NULL, 0),
    (N'simd2004_rank', 61, N'smallint', NULL, NULL, 0),
    (N'simd2004_pw_scotland_decile', 62, N'tinyint', NULL, NULL, 0),
    (N'simd2004_pw_scotland_quintile', 63, N'tinyint', NULL, NULL, 0),
    (N'simd2004_pw_hb_decile', 64, N'tinyint', NULL, NULL, 0),
    (N'simd2004_pw_hb_quintile', 65, N'tinyint', NULL, NULL, 0),
    (N'simd2004_pw_hscp_decile', 66, N'tinyint', NULL, NULL, 0),
    (N'simd2004_pw_hscp_quintile', 67, N'tinyint', NULL, NULL, 0),
    (N'simd2004_pw_ca_decile', 68, N'tinyint', NULL, NULL, 0),
    (N'simd2004_pw_ca_quintile', 69, N'tinyint', NULL, NULL, 0),
    (N'simd2004_most15pc', 70, N'tinyint', NULL, NULL, 0),
    (N'simd2004_least15pc', 71, N'tinyint', NULL, NULL, 0),
    (N'simd2004_uw_scotland_quintile', 72, N'tinyint', NULL, NULL, 0),
    (N'simd2004_uw_scotland_decile', 73, N'tinyint', NULL, NULL, 0),
    (N'simd2004_uw_scotland_vigintile', 74, N'tinyint', NULL, NULL, 0),
    (N'simd2006_rank', 75, N'smallint', NULL, NULL, 0),
    (N'simd2006_pw_scotland_decile', 76, N'tinyint', NULL, NULL, 0),
    (N'simd2006_pw_scotland_quintile', 77, N'tinyint', NULL, NULL, 0),
    (N'simd2006_pw_hb_decile', 78, N'tinyint', NULL, NULL, 0),
    (N'simd2006_pw_hb_quintile', 79, N'tinyint', NULL, NULL, 0),
    (N'simd2006_pw_hscp_decile', 80, N'tinyint', NULL, NULL, 0),
    (N'simd2006_pw_hscp_quintile', 81, N'tinyint', NULL, NULL, 0),
    (N'simd2006_pw_ca_decile', 82, N'tinyint', NULL, NULL, 0),
    (N'simd2006_pw_ca_quintile', 83, N'tinyint', NULL, NULL, 0),
    (N'simd2006_most15pc', 84, N'tinyint', NULL, NULL, 0),
    (N'simd2006_least15pc', 85, N'tinyint', NULL, NULL, 0),
    (N'simd2006_uw_scotland_quintile', 86, N'tinyint', NULL, NULL, 0),
    (N'simd2006_uw_scotland_decile', 87, N'tinyint', NULL, NULL, 0),
    (N'simd2006_uw_scotland_vigintile', 88, N'tinyint', NULL, NULL, 0),
    (N'simd2009v2_rank', 89, N'smallint', NULL, NULL, 0),
    (N'simd2009v2_pw_scotland_decile', 90, N'tinyint', NULL, NULL, 0),
    (N'simd2009v2_pw_scotland_quintile', 91, N'tinyint', NULL, NULL, 0),
    (N'simd2009v2_pw_hb_decile', 92, N'tinyint', NULL, NULL, 0),
    (N'simd2009v2_pw_hb_quintile', 93, N'tinyint', NULL, NULL, 0),
    (N'simd2009v2_pw_hscp_decile', 94, N'tinyint', NULL, NULL, 0),
    (N'simd2009v2_pw_hscp_quintile', 95, N'tinyint', NULL, NULL, 0),
    (N'simd2009v2_pw_ca_decile', 96, N'tinyint', NULL, NULL, 0),
    (N'simd2009v2_pw_ca_quintile', 97, N'tinyint', NULL, NULL, 0),
    (N'simd2009v2_most15pc', 98, N'tinyint', NULL, NULL, 0),
    (N'simd2009v2_least15pc', 99, N'tinyint', NULL, NULL, 0),
    (N'simd2009v2_uw_scotland_quintile', 100, N'tinyint', NULL, NULL, 0),
    (N'simd2009v2_uw_scotland_decile', 101, N'tinyint', NULL, NULL, 0),
    (N'simd2009v2_uw_scotland_vigintile', 102, N'tinyint', NULL, NULL, 0),
    (N'simd2012_rank', 103, N'smallint', NULL, NULL, 0),
    (N'simd2012_pw_scotland_decile', 104, N'tinyint', NULL, NULL, 0),
    (N'simd2012_pw_scotland_quintile', 105, N'tinyint', NULL, NULL, 0),
    (N'simd2012_pw_hb_decile', 106, N'tinyint', NULL, NULL, 0),
    (N'simd2012_pw_hb_quintile', 107, N'tinyint', NULL, NULL, 0),
    (N'simd2012_pw_hscp_decile', 108, N'tinyint', NULL, NULL, 0),
    (N'simd2012_pw_hscp_quintile', 109, N'tinyint', NULL, NULL, 0),
    (N'simd2012_pw_ca_decile', 110, N'tinyint', NULL, NULL, 0),
    (N'simd2012_pw_ca_quintile', 111, N'tinyint', NULL, NULL, 0),
    (N'simd2012_most15pc', 112, N'tinyint', NULL, NULL, 0),
    (N'simd2012_least15pc', 113, N'tinyint', NULL, NULL, 0),
    (N'simd2012_uw_scotland_quintile', 114, N'tinyint', NULL, NULL, 0),
    (N'simd2012_uw_scotland_decile', 115, N'tinyint', NULL, NULL, 0),
    (N'simd2012_uw_scotland_vigintile', 116, N'tinyint', NULL, NULL, 0),
    (N'simd2016_rank', 117, N'smallint', NULL, NULL, 0),
    (N'simd2016_pw_scotland_decile', 118, N'tinyint', NULL, NULL, 0),
    (N'simd2016_pw_scotland_quintile', 119, N'tinyint', NULL, NULL, 0),
    (N'simd2016_pw_hb_decile', 120, N'tinyint', NULL, NULL, 0),
    (N'simd2016_pw_hb_quintile', 121, N'tinyint', NULL, NULL, 0),
    (N'simd2016_pw_hscp_decile', 122, N'tinyint', NULL, NULL, 0),
    (N'simd2016_pw_hscp_quintile', 123, N'tinyint', NULL, NULL, 0),
    (N'simd2016_pw_ca_decile', 124, N'tinyint', NULL, NULL, 0),
    (N'simd2016_pw_ca_quintile', 125, N'tinyint', NULL, NULL, 0),
    (N'simd2016_most15pc', 126, N'tinyint', NULL, NULL, 0),
    (N'simd2016_least15pc', 127, N'tinyint', NULL, NULL, 0),
    (N'simd2016_uw_scotland_quintile', 128, N'tinyint', NULL, NULL, 0),
    (N'simd2016_uw_scotland_decile', 129, N'tinyint', NULL, NULL, 0),
    (N'simd2016_uw_scotland_vigintile', 130, N'tinyint', NULL, NULL, 0),
    (N'simd2020v2_rank', 131, N'smallint', NULL, NULL, 0),
    (N'simd2020v2_pw_scotland_decile', 132, N'tinyint', NULL, NULL, 0),
    (N'simd2020v2_pw_scotland_quintile', 133, N'tinyint', NULL, NULL, 0),
    (N'simd2020v2_pw_hb_decile', 134, N'tinyint', NULL, NULL, 0),
    (N'simd2020v2_pw_hb_quintile', 135, N'tinyint', NULL, NULL, 0),
    (N'simd2020v2_pw_hscp_decile', 136, N'tinyint', NULL, NULL, 0),
    (N'simd2020v2_pw_hscp_quintile', 137, N'tinyint', NULL, NULL, 0),
    (N'simd2020v2_pw_ca_decile', 138, N'tinyint', NULL, NULL, 0),
    (N'simd2020v2_pw_ca_quintile', 139, N'tinyint', NULL, NULL, 0),
    (N'simd2020v2_most15pc', 140, N'tinyint', NULL, NULL, 0),
    (N'simd2020v2_least15pc', 141, N'tinyint', NULL, NULL, 0),
    (N'simd2020v2_uw_scotland_quintile', 142, N'tinyint', NULL, NULL, 0),
    (N'simd2020v2_uw_scotland_decile', 143, N'tinyint', NULL, NULL, 0),
    (N'simd2020v2_uw_scotland_vigintile', 144, N'tinyint', NULL, NULL, 0),
    (N'simd2004_income_domain_rank', 145, N'decimal', 6, 1, 0),
    (N'simd2004_employment_domain_rank', 146, N'decimal', 6, 1, 0),
    (N'simd2004_health_domain_rank', 147, N'decimal', 6, 1, 0),
    (N'simd2004_education_domain_rank', 148, N'decimal', 6, 1, 0),
    (N'simd2004_access_domain_rank', 149, N'decimal', 6, 1, 0),
    (N'simd2004_housing_domain_rank', 150, N'decimal', 6, 1, 0),
    (N'simd2006_income_domain_rank', 151, N'decimal', 6, 1, 0),
    (N'simd2006_employment_domain_rank', 152, N'decimal', 6, 1, 0),
    (N'simd2006_health_domain_rank', 153, N'decimal', 6, 1, 0),
    (N'simd2006_education_domain_rank', 154, N'decimal', 6, 1, 0),
    (N'simd2006_access_domain_rank', 155, N'decimal', 6, 1, 0),
    (N'simd2006_crime_domain_rank', 156, N'decimal', 6, 1, 0),
    (N'simd2006_housing_domain_rank', 157, N'decimal', 6, 1, 0),
    (N'simd2009v2_income_domain_rank', 158, N'decimal', 6, 1, 0),
    (N'simd2009v2_employment_domain_rank', 159, N'decimal', 6, 1, 0),
    (N'simd2009v2_health_domain_rank', 160, N'decimal', 6, 1, 0),
    (N'simd2009v2_education_domain_rank', 161, N'decimal', 6, 1, 0),
    (N'simd2009v2_access_domain_rank', 162, N'decimal', 6, 1, 0),
    (N'simd2009v2_crime_domain_rank', 163, N'decimal', 6, 1, 0),
    (N'simd2009v2_housing_domain_rank', 164, N'decimal', 6, 1, 0),
    (N'simd2012_income_domain_rank', 165, N'decimal', 6, 1, 0),
    (N'simd2012_employment_domain_rank', 166, N'decimal', 6, 1, 0),
    (N'simd2012_health_domain_rank', 167, N'decimal', 6, 1, 0),
    (N'simd2012_education_domain_rank', 168, N'decimal', 6, 1, 0),
    (N'simd2012_access_domain_rank', 169, N'decimal', 6, 1, 0),
    (N'simd2012_crime_domain_rank', 170, N'decimal', 6, 1, 0),
    (N'simd2012_housing_domain_rank', 171, N'decimal', 6, 1, 0),
    (N'simd2016_income_domain_rank', 172, N'decimal', 6, 1, 0),
    (N'simd2016_employment_domain_rank', 173, N'decimal', 6, 1, 0),
    (N'simd2016_health_domain_rank', 174, N'decimal', 6, 1, 0),
    (N'simd2016_education_domain_rank', 175, N'decimal', 6, 1, 0),
    (N'simd2016_access_domain_rank', 176, N'decimal', 6, 1, 0),
    (N'simd2016_crime_domain_rank', 177, N'decimal', 6, 1, 0),
    (N'simd2016_housing_domain_rank', 178, N'decimal', 6, 1, 0),
    (N'simd2020v2_income_domain_rank', 179, N'decimal', 6, 1, 0),
    (N'simd2020v2_employment_domain_rank', 180, N'decimal', 6, 1, 0),
    (N'simd2020v2_health_domain_rank', 181, N'decimal', 6, 1, 0),
    (N'simd2020v2_education_domain_rank', 182, N'decimal', 6, 1, 0),
    (N'simd2020v2_access_domain_rank', 183, N'decimal', 6, 1, 0),
    (N'simd2020v2_crime_domain_rank', 184, N'decimal', 6, 1, 0),
    (N'simd2020v2_housing_domain_rank', 185, N'decimal', 6, 1, 0),
    (N'simd2004_income_domain_quintile', 186, N'tinyint', NULL, NULL, 0),
    (N'simd2004_income_domain_decile', 187, N'tinyint', NULL, NULL, 0),
    (N'simd2004_income_domain_vigintile', 188, N'tinyint', NULL, NULL, 0),
    (N'simd2004_employment_domain_quintile', 189, N'tinyint', NULL, NULL, 0),
    (N'simd2004_employment_domain_decile', 190, N'tinyint', NULL, NULL, 0),
    (N'simd2004_employment_domain_vigintile', 191, N'tinyint', NULL, NULL, 0),
    (N'simd2004_health_domain_quintile', 192, N'tinyint', NULL, NULL, 0),
    (N'simd2004_health_domain_decile', 193, N'tinyint', NULL, NULL, 0),
    (N'simd2004_health_domain_vigintile', 194, N'tinyint', NULL, NULL, 0),
    (N'simd2004_education_domain_quintile', 195, N'tinyint', NULL, NULL, 0),
    (N'simd2004_education_domain_decile', 196, N'tinyint', NULL, NULL, 0),
    (N'simd2004_education_domain_vigintile', 197, N'tinyint', NULL, NULL, 0),
    (N'simd2004_access_domain_quintile', 198, N'tinyint', NULL, NULL, 0),
    (N'simd2004_access_domain_decile', 199, N'tinyint', NULL, NULL, 0),
    (N'simd2004_access_domain_vigintile', 200, N'tinyint', NULL, NULL, 0),
    (N'simd2004_housing_domain_quintile', 201, N'tinyint', NULL, NULL, 0),
    (N'simd2004_housing_domain_decile', 202, N'tinyint', NULL, NULL, 0),
    (N'simd2004_housing_domain_vigintile', 203, N'tinyint', NULL, NULL, 0),
    (N'simd2006_income_domain_quintile', 204, N'tinyint', NULL, NULL, 0),
    (N'simd2006_income_domain_decile', 205, N'tinyint', NULL, NULL, 0),
    (N'simd2006_income_domain_vigintile', 206, N'tinyint', NULL, NULL, 0),
    (N'simd2006_employment_domain_quintile', 207, N'tinyint', NULL, NULL, 0),
    (N'simd2006_employment_domain_decile', 208, N'tinyint', NULL, NULL, 0),
    (N'simd2006_employment_domain_vigintile', 209, N'tinyint', NULL, NULL, 0),
    (N'simd2006_health_domain_quintile', 210, N'tinyint', NULL, NULL, 0),
    (N'simd2006_health_domain_decile', 211, N'tinyint', NULL, NULL, 0),
    (N'simd2006_health_domain_vigintile', 212, N'tinyint', NULL, NULL, 0),
    (N'simd2006_education_domain_quintile', 213, N'tinyint', NULL, NULL, 0),
    (N'simd2006_education_domain_decile', 214, N'tinyint', NULL, NULL, 0),
    (N'simd2006_education_domain_vigintile', 215, N'tinyint', NULL, NULL, 0),
    (N'simd2006_access_domain_quintile', 216, N'tinyint', NULL, NULL, 0),
    (N'simd2006_access_domain_decile', 217, N'tinyint', NULL, NULL, 0),
    (N'simd2006_access_domain_vigintile', 218, N'tinyint', NULL, NULL, 0),
    (N'simd2006_crime_domain_quintile', 219, N'tinyint', NULL, NULL, 0),
    (N'simd2006_crime_domain_decile', 220, N'tinyint', NULL, NULL, 0),
    (N'simd2006_crime_domain_vigintile', 221, N'tinyint', NULL, NULL, 0),
    (N'simd2006_housing_domain_quintile', 222, N'tinyint', NULL, NULL, 0),
    (N'simd2006_housing_domain_decile', 223, N'tinyint', NULL, NULL, 0),
    (N'simd2006_housing_domain_vigintile', 224, N'tinyint', NULL, NULL, 0),
    (N'simd2009v2_income_domain_quintile', 225, N'tinyint', NULL, NULL, 0),
    (N'simd2009v2_income_domain_decile', 226, N'tinyint', NULL, NULL, 0),
    (N'simd2009v2_income_domain_vigintile', 227, N'tinyint', NULL, NULL, 0),
    (N'simd2009v2_employment_domain_quintile', 228, N'tinyint', NULL, NULL, 0),
    (N'simd2009v2_employment_domain_decile', 229, N'tinyint', NULL, NULL, 0),
    (N'simd2009v2_employment_domain_vigintile', 230, N'tinyint', NULL, NULL, 0),
    (N'simd2009v2_health_domain_quintile', 231, N'tinyint', NULL, NULL, 0),
    (N'simd2009v2_health_domain_decile', 232, N'tinyint', NULL, NULL, 0),
    (N'simd2009v2_health_domain_vigintile', 233, N'tinyint', NULL, NULL, 0),
    (N'simd2009v2_education_domain_quintile', 234, N'tinyint', NULL, NULL, 0),
    (N'simd2009v2_education_domain_decile', 235, N'tinyint', NULL, NULL, 0),
    (N'simd2009v2_education_domain_vigintile', 236, N'tinyint', NULL, NULL, 0),
    (N'simd2009v2_access_domain_quintile', 237, N'tinyint', NULL, NULL, 0),
    (N'simd2009v2_access_domain_decile', 238, N'tinyint', NULL, NULL, 0),
    (N'simd2009v2_access_domain_vigintile', 239, N'tinyint', NULL, NULL, 0),
    (N'simd2009v2_crime_domain_quintile', 240, N'tinyint', NULL, NULL, 0),
    (N'simd2009v2_crime_domain_decile', 241, N'tinyint', NULL, NULL, 0),
    (N'simd2009v2_crime_domain_vigintile', 242, N'tinyint', NULL, NULL, 0),
    (N'simd2009v2_housing_domain_quintile', 243, N'tinyint', NULL, NULL, 0),
    (N'simd2009v2_housing_domain_decile', 244, N'tinyint', NULL, NULL, 0),
    (N'simd2009v2_housing_domain_vigintile', 245, N'tinyint', NULL, NULL, 0),
    (N'simd2012_income_domain_quintile', 246, N'tinyint', NULL, NULL, 0),
    (N'simd2012_income_domain_decile', 247, N'tinyint', NULL, NULL, 0),
    (N'simd2012_income_domain_vigintile', 248, N'tinyint', NULL, NULL, 0),
    (N'simd2012_employment_domain_quintile', 249, N'tinyint', NULL, NULL, 0),
    (N'simd2012_employment_domain_decile', 250, N'tinyint', NULL, NULL, 0),
    (N'simd2012_employment_domain_vigintile', 251, N'tinyint', NULL, NULL, 0),
    (N'simd2012_health_domain_quintile', 252, N'tinyint', NULL, NULL, 0),
    (N'simd2012_health_domain_decile', 253, N'tinyint', NULL, NULL, 0),
    (N'simd2012_health_domain_vigintile', 254, N'tinyint', NULL, NULL, 0),
    (N'simd2012_education_domain_quintile', 255, N'tinyint', NULL, NULL, 0),
    (N'simd2012_education_domain_decile', 256, N'tinyint', NULL, NULL, 0),
    (N'simd2012_education_domain_vigintile', 257, N'tinyint', NULL, NULL, 0),
    (N'simd2012_access_domain_quintile', 258, N'tinyint', NULL, NULL, 0),
    (N'simd2012_access_domain_decile', 259, N'tinyint', NULL, NULL, 0),
    (N'simd2012_access_domain_vigintile', 260, N'tinyint', NULL, NULL, 0),
    (N'simd2012_crime_domain_quintile', 261, N'tinyint', NULL, NULL, 0),
    (N'simd2012_crime_domain_decile', 262, N'tinyint', NULL, NULL, 0),
    (N'simd2012_crime_domain_vigintile', 263, N'tinyint', NULL, NULL, 0),
    (N'simd2012_housing_domain_quintile', 264, N'tinyint', NULL, NULL, 0),
    (N'simd2012_housing_domain_decile', 265, N'tinyint', NULL, NULL, 0),
    (N'simd2012_housing_domain_vigintile', 266, N'tinyint', NULL, NULL, 0),
    (N'simd2016_income_domain_quintile', 267, N'tinyint', NULL, NULL, 0),
    (N'simd2016_income_domain_decile', 268, N'tinyint', NULL, NULL, 0),
    (N'simd2016_income_domain_vigintile', 269, N'tinyint', NULL, NULL, 0),
    (N'simd2016_employment_domain_quintile', 270, N'tinyint', NULL, NULL, 0),
    (N'simd2016_employment_domain_decile', 271, N'tinyint', NULL, NULL, 0),
    (N'simd2016_employment_domain_vigintile', 272, N'tinyint', NULL, NULL, 0),
    (N'simd2016_health_domain_quintile', 273, N'tinyint', NULL, NULL, 0),
    (N'simd2016_health_domain_decile', 274, N'tinyint', NULL, NULL, 0),
    (N'simd2016_health_domain_vigintile', 275, N'tinyint', NULL, NULL, 0),
    (N'simd2016_education_domain_quintile', 276, N'tinyint', NULL, NULL, 0),
    (N'simd2016_education_domain_decile', 277, N'tinyint', NULL, NULL, 0),
    (N'simd2016_education_domain_vigintile', 278, N'tinyint', NULL, NULL, 0),
    (N'simd2016_access_domain_quintile', 279, N'tinyint', NULL, NULL, 0),
    (N'simd2016_access_domain_decile', 280, N'tinyint', NULL, NULL, 0),
    (N'simd2016_access_domain_vigintile', 281, N'tinyint', NULL, NULL, 0),
    (N'simd2016_crime_domain_quintile', 282, N'tinyint', NULL, NULL, 0),
    (N'simd2016_crime_domain_decile', 283, N'tinyint', NULL, NULL, 0),
    (N'simd2016_crime_domain_vigintile', 284, N'tinyint', NULL, NULL, 0),
    (N'simd2016_housing_domain_quintile', 285, N'tinyint', NULL, NULL, 0),
    (N'simd2016_housing_domain_decile', 286, N'tinyint', NULL, NULL, 0),
    (N'simd2016_housing_domain_vigintile', 287, N'tinyint', NULL, NULL, 0),
    (N'simd2020v2_income_domain_quintile', 288, N'tinyint', NULL, NULL, 0),
    (N'simd2020v2_income_domain_decile', 289, N'tinyint', NULL, NULL, 0),
    (N'simd2020v2_income_domain_vigintile', 290, N'tinyint', NULL, NULL, 0),
    (N'simd2020v2_employment_domain_quintile', 291, N'tinyint', NULL, NULL, 0),
    (N'simd2020v2_employment_domain_decile', 292, N'tinyint', NULL, NULL, 0),
    (N'simd2020v2_employment_domain_vigintile', 293, N'tinyint', NULL, NULL, 0),
    (N'simd2020v2_health_domain_quintile', 294, N'tinyint', NULL, NULL, 0),
    (N'simd2020v2_health_domain_decile', 295, N'tinyint', NULL, NULL, 0),
    (N'simd2020v2_health_domain_vigintile', 296, N'tinyint', NULL, NULL, 0),
    (N'simd2020v2_education_domain_quintile', 297, N'tinyint', NULL, NULL, 0),
    (N'simd2020v2_education_domain_decile', 298, N'tinyint', NULL, NULL, 0),
    (N'simd2020v2_education_domain_vigintile', 299, N'tinyint', NULL, NULL, 0),
    (N'simd2020v2_access_domain_quintile', 300, N'tinyint', NULL, NULL, 0),
    (N'simd2020v2_access_domain_decile', 301, N'tinyint', NULL, NULL, 0),
    (N'simd2020v2_access_domain_vigintile', 302, N'tinyint', NULL, NULL, 0),
    (N'simd2020v2_crime_domain_quintile', 303, N'tinyint', NULL, NULL, 0),
    (N'simd2020v2_crime_domain_decile', 304, N'tinyint', NULL, NULL, 0),
    (N'simd2020v2_crime_domain_vigintile', 305, N'tinyint', NULL, NULL, 0),
    (N'simd2020v2_housing_domain_quintile', 306, N'tinyint', NULL, NULL, 0),
    (N'simd2020v2_housing_domain_decile', 307, N'tinyint', NULL, NULL, 0),
    (N'simd2020v2_housing_domain_vigintile', 308, N'tinyint', NULL, NULL, 0),
    (N'simd2020v2_housing_domain_rank_source_status', 309, N'nvarchar', NULL, NULL, 1),
    (N'simd2004_income_domain_pw_scotland_quintile', 310, N'tinyint', NULL, NULL, 0),
    (N'simd2004_income_domain_pw_scotland_decile', 311, N'tinyint', NULL, NULL, 0),
    (N'simd2004_employment_domain_pw_scotland_quintile', 312, N'tinyint', NULL, NULL, 0),
    (N'simd2004_employment_domain_pw_scotland_decile', 313, N'tinyint', NULL, NULL, 0),
    (N'simd2004_health_domain_pw_scotland_quintile', 314, N'tinyint', NULL, NULL, 0),
    (N'simd2004_health_domain_pw_scotland_decile', 315, N'tinyint', NULL, NULL, 0),
    (N'simd2004_education_domain_pw_scotland_quintile', 316, N'tinyint', NULL, NULL, 0),
    (N'simd2004_education_domain_pw_scotland_decile', 317, N'tinyint', NULL, NULL, 0),
    (N'simd2004_access_domain_pw_scotland_quintile', 318, N'tinyint', NULL, NULL, 0),
    (N'simd2004_access_domain_pw_scotland_decile', 319, N'tinyint', NULL, NULL, 0),
    (N'simd2004_housing_domain_pw_scotland_quintile', 320, N'tinyint', NULL, NULL, 0),
    (N'simd2004_housing_domain_pw_scotland_decile', 321, N'tinyint', NULL, NULL, 0),
    (N'simd2006_income_domain_pw_scotland_quintile', 322, N'tinyint', NULL, NULL, 0),
    (N'simd2006_income_domain_pw_scotland_decile', 323, N'tinyint', NULL, NULL, 0),
    (N'simd2006_employment_domain_pw_scotland_quintile', 324, N'tinyint', NULL, NULL, 0),
    (N'simd2006_employment_domain_pw_scotland_decile', 325, N'tinyint', NULL, NULL, 0),
    (N'simd2006_health_domain_pw_scotland_quintile', 326, N'tinyint', NULL, NULL, 0),
    (N'simd2006_health_domain_pw_scotland_decile', 327, N'tinyint', NULL, NULL, 0),
    (N'simd2006_education_domain_pw_scotland_quintile', 328, N'tinyint', NULL, NULL, 0),
    (N'simd2006_education_domain_pw_scotland_decile', 329, N'tinyint', NULL, NULL, 0),
    (N'simd2006_access_domain_pw_scotland_quintile', 330, N'tinyint', NULL, NULL, 0),
    (N'simd2006_access_domain_pw_scotland_decile', 331, N'tinyint', NULL, NULL, 0),
    (N'simd2006_crime_domain_pw_scotland_quintile', 332, N'tinyint', NULL, NULL, 0),
    (N'simd2006_crime_domain_pw_scotland_decile', 333, N'tinyint', NULL, NULL, 0),
    (N'simd2006_housing_domain_pw_scotland_quintile', 334, N'tinyint', NULL, NULL, 0),
    (N'simd2006_housing_domain_pw_scotland_decile', 335, N'tinyint', NULL, NULL, 0),
    (N'simd2009v2_income_domain_pw_scotland_quintile', 336, N'tinyint', NULL, NULL, 0),
    (N'simd2009v2_income_domain_pw_scotland_decile', 337, N'tinyint', NULL, NULL, 0),
    (N'simd2009v2_employment_domain_pw_scotland_quintile', 338, N'tinyint', NULL, NULL, 0),
    (N'simd2009v2_employment_domain_pw_scotland_decile', 339, N'tinyint', NULL, NULL, 0),
    (N'simd2009v2_health_domain_pw_scotland_quintile', 340, N'tinyint', NULL, NULL, 0),
    (N'simd2009v2_health_domain_pw_scotland_decile', 341, N'tinyint', NULL, NULL, 0),
    (N'simd2009v2_education_domain_pw_scotland_quintile', 342, N'tinyint', NULL, NULL, 0),
    (N'simd2009v2_education_domain_pw_scotland_decile', 343, N'tinyint', NULL, NULL, 0),
    (N'simd2009v2_access_domain_pw_scotland_quintile', 344, N'tinyint', NULL, NULL, 0),
    (N'simd2009v2_access_domain_pw_scotland_decile', 345, N'tinyint', NULL, NULL, 0),
    (N'simd2009v2_crime_domain_pw_scotland_quintile', 346, N'tinyint', NULL, NULL, 0),
    (N'simd2009v2_crime_domain_pw_scotland_decile', 347, N'tinyint', NULL, NULL, 0),
    (N'simd2009v2_housing_domain_pw_scotland_quintile', 348, N'tinyint', NULL, NULL, 0),
    (N'simd2009v2_housing_domain_pw_scotland_decile', 349, N'tinyint', NULL, NULL, 0),
    (N'simd2012_income_domain_pw_scotland_quintile', 350, N'tinyint', NULL, NULL, 0),
    (N'simd2012_income_domain_pw_scotland_decile', 351, N'tinyint', NULL, NULL, 0),
    (N'simd2012_employment_domain_pw_scotland_quintile', 352, N'tinyint', NULL, NULL, 0),
    (N'simd2012_employment_domain_pw_scotland_decile', 353, N'tinyint', NULL, NULL, 0),
    (N'simd2012_health_domain_pw_scotland_quintile', 354, N'tinyint', NULL, NULL, 0),
    (N'simd2012_health_domain_pw_scotland_decile', 355, N'tinyint', NULL, NULL, 0),
    (N'simd2012_education_domain_pw_scotland_quintile', 356, N'tinyint', NULL, NULL, 0),
    (N'simd2012_education_domain_pw_scotland_decile', 357, N'tinyint', NULL, NULL, 0),
    (N'simd2012_access_domain_pw_scotland_quintile', 358, N'tinyint', NULL, NULL, 0),
    (N'simd2012_access_domain_pw_scotland_decile', 359, N'tinyint', NULL, NULL, 0),
    (N'simd2012_crime_domain_pw_scotland_quintile', 360, N'tinyint', NULL, NULL, 0),
    (N'simd2012_crime_domain_pw_scotland_decile', 361, N'tinyint', NULL, NULL, 0),
    (N'simd2012_housing_domain_pw_scotland_quintile', 362, N'tinyint', NULL, NULL, 0),
    (N'simd2012_housing_domain_pw_scotland_decile', 363, N'tinyint', NULL, NULL, 0),
    (N'simd2016_income_domain_pw_scotland_quintile', 364, N'tinyint', NULL, NULL, 0),
    (N'simd2016_income_domain_pw_scotland_decile', 365, N'tinyint', NULL, NULL, 0),
    (N'simd2016_employment_domain_pw_scotland_quintile', 366, N'tinyint', NULL, NULL, 0),
    (N'simd2016_employment_domain_pw_scotland_decile', 367, N'tinyint', NULL, NULL, 0),
    (N'simd2016_health_domain_pw_scotland_quintile', 368, N'tinyint', NULL, NULL, 0),
    (N'simd2016_health_domain_pw_scotland_decile', 369, N'tinyint', NULL, NULL, 0),
    (N'simd2016_education_domain_pw_scotland_quintile', 370, N'tinyint', NULL, NULL, 0),
    (N'simd2016_education_domain_pw_scotland_decile', 371, N'tinyint', NULL, NULL, 0),
    (N'simd2016_access_domain_pw_scotland_quintile', 372, N'tinyint', NULL, NULL, 0),
    (N'simd2016_access_domain_pw_scotland_decile', 373, N'tinyint', NULL, NULL, 0),
    (N'simd2016_crime_domain_pw_scotland_quintile', 374, N'tinyint', NULL, NULL, 0),
    (N'simd2016_crime_domain_pw_scotland_decile', 375, N'tinyint', NULL, NULL, 0),
    (N'simd2016_housing_domain_pw_scotland_quintile', 376, N'tinyint', NULL, NULL, 0),
    (N'simd2016_housing_domain_pw_scotland_decile', 377, N'tinyint', NULL, NULL, 0),
    (N'simd2020v2_income_domain_pw_scotland_quintile', 378, N'tinyint', NULL, NULL, 0),
    (N'simd2020v2_income_domain_pw_scotland_decile', 379, N'tinyint', NULL, NULL, 0),
    (N'simd2020v2_employment_domain_pw_scotland_quintile', 380, N'tinyint', NULL, NULL, 0),
    (N'simd2020v2_employment_domain_pw_scotland_decile', 381, N'tinyint', NULL, NULL, 0),
    (N'simd2020v2_health_domain_pw_scotland_quintile', 382, N'tinyint', NULL, NULL, 0),
    (N'simd2020v2_health_domain_pw_scotland_decile', 383, N'tinyint', NULL, NULL, 0),
    (N'simd2020v2_education_domain_pw_scotland_quintile', 384, N'tinyint', NULL, NULL, 0),
    (N'simd2020v2_education_domain_pw_scotland_decile', 385, N'tinyint', NULL, NULL, 0),
    (N'simd2020v2_access_domain_pw_scotland_quintile', 386, N'tinyint', NULL, NULL, 0),
    (N'simd2020v2_access_domain_pw_scotland_decile', 387, N'tinyint', NULL, NULL, 0),
    (N'simd2020v2_crime_domain_pw_scotland_quintile', 388, N'tinyint', NULL, NULL, 0),
    (N'simd2020v2_crime_domain_pw_scotland_decile', 389, N'tinyint', NULL, NULL, 0),
    (N'simd2020v2_housing_domain_pw_scotland_quintile', 390, N'tinyint', NULL, NULL, 0),
    (N'simd2020v2_housing_domain_pw_scotland_decile', 391, N'tinyint', NULL, NULL, 0),
    (N'simd2004_population', 392, N'smallint', NULL, NULL, 0),
    (N'simd2006_population', 393, N'smallint', NULL, NULL, 0),
    (N'simd2009v2_population', 394, N'smallint', NULL, NULL, 0),
    (N'simd2012_population', 395, N'smallint', NULL, NULL, 0),
    (N'simd2016_population', 396, N'smallint', NULL, NULL, 0),
    (N'simd2020v2_population', 397, N'smallint', NULL, NULL, 0);

    INSERT INTO #result
    SELECT N'postcode_simd', N'columns declared and in order', N'0 differences',
           CONVERT(nvarchar(200), COUNT(*)) + N' differences', COUNT(*), CASE WHEN COUNT(*) = 0 THEN 1 ELSE 0 END
    FROM (
        SELECT d.name FROM @declared_main d
        LEFT JOIN INFORMATION_SCHEMA.COLUMNS c
               ON c.TABLE_NAME = PARSENAME(N'postcode_simd', 1) AND c.COLUMN_NAME = d.name
              AND c.ORDINAL_POSITION = d.ordinal AND c.DATA_TYPE = d.type_name
              AND (d.numeric_precision IS NULL
                   OR (c.NUMERIC_PRECISION = d.numeric_precision AND c.NUMERIC_SCALE = d.numeric_scale))
              AND c.IS_NULLABLE = CASE d.is_nullable WHEN 1 THEN 'YES' ELSE 'NO' END
        WHERE c.COLUMN_NAME IS NULL
        UNION ALL
        SELECT c.COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS c
        WHERE c.TABLE_NAME = PARSENAME(N'postcode_simd', 1)
          AND c.COLUMN_NAME NOT IN (SELECT name FROM @declared_main)
    ) AS differences;

    -- 2. The natural key, which the digest also cannot see: it is order independent, so a
    -- duplicated row and a missing one can cancel out.
    INSERT INTO #result
    SELECT N'postcode_simd', N'natural key [pc_norm] unique and not null', N'0 offending rows',
           CONVERT(nvarchar(200), COUNT_BIG(*)) + N' offending rows', COUNT_BIG(*),
           CASE WHEN COUNT_BIG(*) = 0 THEN 1 ELSE 0 END
    FROM (SELECT [pc_norm] FROM postcode_simd GROUP BY [pc_norm] HAVING COUNT_BIG(*) > 1) AS duplicates;

    -- 3. Reserved characters in the text columns. The digest frames fields with them, so text
    -- containing one could make two different rows hash alike. The export refuses them; so does
    -- this. A rendered null is legitimately NCHAR(0), which is why this reads the columns.
    INSERT INTO #result
    SELECT N'postcode_simd', N'no reserved framing characters in text', N'0 rows',
           CONVERT(nvarchar(200), COUNT_BIG(*)) + N' rows', COUNT_BIG(*),
           CASE WHEN COUNT_BIG(*) = 0 THEN 1 ELSE 0 END
    FROM postcode_simd
    WHERE DATALENGTH(REPLACE(REPLACE([Postcode] COLLATE Latin1_General_100_BIN2_UTF8, NCHAR(0), N''), NCHAR(31), N'')) <> DATALENGTH([Postcode])
       OR DATALENGTH(REPLACE(REPLACE([PostcodeDistrict] COLLATE Latin1_General_100_BIN2_UTF8, NCHAR(0), N''), NCHAR(31), N'')) <> DATALENGTH([PostcodeDistrict])
       OR DATALENGTH(REPLACE(REPLACE([PostcodeSector] COLLATE Latin1_General_100_BIN2_UTF8, NCHAR(0), N''), NCHAR(31), N'')) <> DATALENGTH([PostcodeSector])
       OR DATALENGTH(REPLACE(REPLACE([SplitIndicator] COLLATE Latin1_General_100_BIN2_UTF8, NCHAR(0), N''), NCHAR(31), N'')) <> DATALENGTH([SplitIndicator])
       OR DATALENGTH(REPLACE(REPLACE([LinkedSmallUserPostcode] COLLATE Latin1_General_100_BIN2_UTF8, NCHAR(0), N''), NCHAR(31), N'')) <> DATALENGTH([LinkedSmallUserPostcode])
       OR DATALENGTH(REPLACE(REPLACE([DateOfIntroduction] COLLATE Latin1_General_100_BIN2_UTF8, NCHAR(0), N''), NCHAR(31), N'')) <> DATALENGTH([DateOfIntroduction])
       OR DATALENGTH(REPLACE(REPLACE([DateOfDeletion] COLLATE Latin1_General_100_BIN2_UTF8, NCHAR(0), N''), NCHAR(31), N'')) <> DATALENGTH([DateOfDeletion])
       OR DATALENGTH(REPLACE(REPLACE([PostcodeType] COLLATE Latin1_General_100_BIN2_UTF8, NCHAR(0), N''), NCHAR(31), N'')) <> DATALENGTH([PostcodeType])
       OR DATALENGTH(REPLACE(REPLACE([OutputArea2022Code] COLLATE Latin1_General_100_BIN2_UTF8, NCHAR(0), N''), NCHAR(31), N'')) <> DATALENGTH([OutputArea2022Code])
       OR DATALENGTH(REPLACE(REPLACE([OutputArea2011Code] COLLATE Latin1_General_100_BIN2_UTF8, NCHAR(0), N''), NCHAR(31), N'')) <> DATALENGTH([OutputArea2011Code])
       OR DATALENGTH(REPLACE(REPLACE([DataZone2022Code] COLLATE Latin1_General_100_BIN2_UTF8, NCHAR(0), N''), NCHAR(31), N'')) <> DATALENGTH([DataZone2022Code])
       OR DATALENGTH(REPLACE(REPLACE([DataZone2011Code] COLLATE Latin1_General_100_BIN2_UTF8, NCHAR(0), N''), NCHAR(31), N'')) <> DATALENGTH([DataZone2011Code])
       OR DATALENGTH(REPLACE(REPLACE([IntermediateZone2022Code] COLLATE Latin1_General_100_BIN2_UTF8, NCHAR(0), N''), NCHAR(31), N'')) <> DATALENGTH([IntermediateZone2022Code])
       OR DATALENGTH(REPLACE(REPLACE([IntermediateZone2011Code] COLLATE Latin1_General_100_BIN2_UTF8, NCHAR(0), N''), NCHAR(31), N'')) <> DATALENGTH([IntermediateZone2011Code])
       OR DATALENGTH(REPLACE(REPLACE([OutputArea2001Code] COLLATE Latin1_General_100_BIN2_UTF8, NCHAR(0), N''), NCHAR(31), N'')) <> DATALENGTH([OutputArea2001Code])
       OR DATALENGTH(REPLACE(REPLACE([DataZone2001Code] COLLATE Latin1_General_100_BIN2_UTF8, NCHAR(0), N''), NCHAR(31), N'')) <> DATALENGTH([DataZone2001Code])
       OR DATALENGTH(REPLACE(REPLACE([IntermediateZone2001Code] COLLATE Latin1_General_100_BIN2_UTF8, NCHAR(0), N''), NCHAR(31), N'')) <> DATALENGTH([IntermediateZone2001Code])
       OR DATALENGTH(REPLACE(REPLACE([OutputArea1991Code] COLLATE Latin1_General_100_BIN2_UTF8, NCHAR(0), N''), NCHAR(31), N'')) <> DATALENGTH([OutputArea1991Code])
       OR DATALENGTH(REPLACE(REPLACE([CouncilArea2019Code] COLLATE Latin1_General_100_BIN2_UTF8, NCHAR(0), N''), NCHAR(31), N'')) <> DATALENGTH([CouncilArea2019Code])
       OR DATALENGTH(REPLACE(REPLACE([RegistrationDistrict2007Code] COLLATE Latin1_General_100_BIN2_UTF8, NCHAR(0), N''), NCHAR(31), N'')) <> DATALENGTH([RegistrationDistrict2007Code])
       OR DATALENGTH(REPLACE(REPLACE([LocalGovernmentDistrict1995Code] COLLATE Latin1_General_100_BIN2_UTF8, NCHAR(0), N''), NCHAR(31), N'')) <> DATALENGTH([LocalGovernmentDistrict1995Code])
       OR DATALENGTH(REPLACE(REPLACE([LocalGovernmentDistrict1991Code] COLLATE Latin1_General_100_BIN2_UTF8, NCHAR(0), N''), NCHAR(31), N'')) <> DATALENGTH([LocalGovernmentDistrict1991Code])
       OR DATALENGTH(REPLACE(REPLACE([ElectoralWard2022Code] COLLATE Latin1_General_100_BIN2_UTF8, NCHAR(0), N''), NCHAR(31), N'')) <> DATALENGTH([ElectoralWard2022Code])
       OR DATALENGTH(REPLACE(REPLACE([ScottishParliamentaryRegion2026Code] COLLATE Latin1_General_100_BIN2_UTF8, NCHAR(0), N''), NCHAR(31), N'')) <> DATALENGTH([ScottishParliamentaryRegion2026Code])
       OR DATALENGTH(REPLACE(REPLACE([ScottishParliamentaryConstituency2026Code] COLLATE Latin1_General_100_BIN2_UTF8, NCHAR(0), N''), NCHAR(31), N'')) <> DATALENGTH([ScottishParliamentaryConstituency2026Code])
       OR DATALENGTH(REPLACE(REPLACE([UKParliamentaryConstituency2024Code] COLLATE Latin1_General_100_BIN2_UTF8, NCHAR(0), N''), NCHAR(31), N'')) <> DATALENGTH([UKParliamentaryConstituency2024Code])
       OR DATALENGTH(REPLACE(REPLACE([HealthBoardArea2019Code] COLLATE Latin1_General_100_BIN2_UTF8, NCHAR(0), N''), NCHAR(31), N'')) <> DATALENGTH([HealthBoardArea2019Code])
       OR DATALENGTH(REPLACE(REPLACE([HealthBoardArea2006Code] COLLATE Latin1_General_100_BIN2_UTF8, NCHAR(0), N''), NCHAR(31), N'')) <> DATALENGTH([HealthBoardArea2006Code])
       OR DATALENGTH(REPLACE(REPLACE([HealthBoardArea1995Code] COLLATE Latin1_General_100_BIN2_UTF8, NCHAR(0), N''), NCHAR(31), N'')) <> DATALENGTH([HealthBoardArea1995Code])
       OR DATALENGTH(REPLACE(REPLACE([IntegrationAuthority2019Code] COLLATE Latin1_General_100_BIN2_UTF8, NCHAR(0), N''), NCHAR(31), N'')) <> DATALENGTH([IntegrationAuthority2019Code])
       OR DATALENGTH(REPLACE(REPLACE([StrategicDevelopmentPlanningArea2013Code] COLLATE Latin1_General_100_BIN2_UTF8, NCHAR(0), N''), NCHAR(31), N'')) <> DATALENGTH([StrategicDevelopmentPlanningArea2013Code])
       OR DATALENGTH(REPLACE(REPLACE([CivilParish1930Code] COLLATE Latin1_General_100_BIN2_UTF8, NCHAR(0), N''), NCHAR(31), N'')) <> DATALENGTH([CivilParish1930Code])
       OR DATALENGTH(REPLACE(REPLACE([TravelToWorkArea2011Code] COLLATE Latin1_General_100_BIN2_UTF8, NCHAR(0), N''), NCHAR(31), N'')) <> DATALENGTH([TravelToWorkArea2011Code])
       OR DATALENGTH(REPLACE(REPLACE([IslandCode] COLLATE Latin1_General_100_BIN2_UTF8, NCHAR(0), N''), NCHAR(31), N'')) <> DATALENGTH([IslandCode])
       OR DATALENGTH(REPLACE(REPLACE([UrbanRural6Fold2022Code] COLLATE Latin1_General_100_BIN2_UTF8, NCHAR(0), N''), NCHAR(31), N'')) <> DATALENGTH([UrbanRural6Fold2022Code])
       OR DATALENGTH(REPLACE(REPLACE([UrbanRural8Fold2022Code] COLLATE Latin1_General_100_BIN2_UTF8, NCHAR(0), N''), NCHAR(31), N'')) <> DATALENGTH([UrbanRural8Fold2022Code])
       OR DATALENGTH(REPLACE(REPLACE([LAU2025Level1Code] COLLATE Latin1_General_100_BIN2_UTF8, NCHAR(0), N''), NCHAR(31), N'')) <> DATALENGTH([LAU2025Level1Code])
       OR DATALENGTH(REPLACE(REPLACE([ITL2025Level2Code] COLLATE Latin1_General_100_BIN2_UTF8, NCHAR(0), N''), NCHAR(31), N'')) <> DATALENGTH([ITL2025Level2Code])
       OR DATALENGTH(REPLACE(REPLACE([ITL2025Level3Code] COLLATE Latin1_General_100_BIN2_UTF8, NCHAR(0), N''), NCHAR(31), N'')) <> DATALENGTH([ITL2025Level3Code])
       OR DATALENGTH(REPLACE(REPLACE([CensusHouseholdCount2022] COLLATE Latin1_General_100_BIN2_UTF8, NCHAR(0), N''), NCHAR(31), N'')) <> DATALENGTH([CensusHouseholdCount2022])
       OR DATALENGTH(REPLACE(REPLACE([CensusPopulationCount2022] COLLATE Latin1_General_100_BIN2_UTF8, NCHAR(0), N''), NCHAR(31), N'')) <> DATALENGTH([CensusPopulationCount2022])
       OR DATALENGTH(REPLACE(REPLACE([CensusHouseholdCount2011] COLLATE Latin1_General_100_BIN2_UTF8, NCHAR(0), N''), NCHAR(31), N'')) <> DATALENGTH([CensusHouseholdCount2011])
       OR DATALENGTH(REPLACE(REPLACE([CensusPopulationCount2011] COLLATE Latin1_General_100_BIN2_UTF8, NCHAR(0), N''), NCHAR(31), N'')) <> DATALENGTH([CensusPopulationCount2011])
       OR DATALENGTH(REPLACE(REPLACE([CensusHouseholdCount2001] COLLATE Latin1_General_100_BIN2_UTF8, NCHAR(0), N''), NCHAR(31), N'')) <> DATALENGTH([CensusHouseholdCount2001])
       OR DATALENGTH(REPLACE(REPLACE([CensusPopulationCount2001] COLLATE Latin1_General_100_BIN2_UTF8, NCHAR(0), N''), NCHAR(31), N'')) <> DATALENGTH([CensusPopulationCount2001])
       OR DATALENGTH(REPLACE(REPLACE([CensusHouseholdCount1991] COLLATE Latin1_General_100_BIN2_UTF8, NCHAR(0), N''), NCHAR(31), N'')) <> DATALENGTH([CensusHouseholdCount1991])
       OR DATALENGTH(REPLACE(REPLACE([CensusPopulationCount1991] COLLATE Latin1_General_100_BIN2_UTF8, NCHAR(0), N''), NCHAR(31), N'')) <> DATALENGTH([CensusPopulationCount1991])
       OR DATALENGTH(REPLACE(REPLACE([ScottishIndexOfMultipleDeprivation2020Rank] COLLATE Latin1_General_100_BIN2_UTF8, NCHAR(0), N''), NCHAR(31), N'')) <> DATALENGTH([ScottishIndexOfMultipleDeprivation2020Rank])
       OR DATALENGTH(REPLACE(REPLACE([pc_norm] COLLATE Latin1_General_100_BIN2_UTF8, NCHAR(0), N''), NCHAR(31), N'')) <> DATALENGTH([pc_norm])
       OR DATALENGTH(REPLACE(REPLACE([spd_user_type] COLLATE Latin1_General_100_BIN2_UTF8, NCHAR(0), N''), NCHAR(31), N'')) <> DATALENGTH([spd_user_type])
       OR DATALENGTH(REPLACE(REPLACE([sspl_release] COLLATE Latin1_General_100_BIN2_UTF8, NCHAR(0), N''), NCHAR(31), N'')) <> DATALENGTH([sspl_release])
       OR DATALENGTH(REPLACE(REPLACE([phs_dz2001_hb] COLLATE Latin1_General_100_BIN2_UTF8, NCHAR(0), N''), NCHAR(31), N'')) <> DATALENGTH([phs_dz2001_hb])
       OR DATALENGTH(REPLACE(REPLACE([phs_dz2001_hscp] COLLATE Latin1_General_100_BIN2_UTF8, NCHAR(0), N''), NCHAR(31), N'')) <> DATALENGTH([phs_dz2001_hscp])
       OR DATALENGTH(REPLACE(REPLACE([phs_dz2001_ca] COLLATE Latin1_General_100_BIN2_UTF8, NCHAR(0), N''), NCHAR(31), N'')) <> DATALENGTH([phs_dz2001_ca])
       OR DATALENGTH(REPLACE(REPLACE([phs_dz2011_hb] COLLATE Latin1_General_100_BIN2_UTF8, NCHAR(0), N''), NCHAR(31), N'')) <> DATALENGTH([phs_dz2011_hb])
       OR DATALENGTH(REPLACE(REPLACE([phs_dz2011_hscp] COLLATE Latin1_General_100_BIN2_UTF8, NCHAR(0), N''), NCHAR(31), N'')) <> DATALENGTH([phs_dz2011_hscp])
       OR DATALENGTH(REPLACE(REPLACE([phs_dz2011_ca] COLLATE Latin1_General_100_BIN2_UTF8, NCHAR(0), N''), NCHAR(31), N'')) <> DATALENGTH([phs_dz2011_ca])
       OR DATALENGTH(REPLACE(REPLACE([simd2020v2_housing_domain_rank_source_status] COLLATE Latin1_General_100_BIN2_UTF8, NCHAR(0), N''), NCHAR(31), N'')) <> DATALENGTH([simd2020v2_housing_domain_rank_source_status]);

    -- 4. The digest. Each row is rendered as the CSV renders it, with NCHAR(0) for a null so a
    -- null and a blank differ, the fields joined with NCHAR(31) in contract order, converted to
    -- UTF-8 and hashed. The signed eight-byte prefixes are summed as decimal: converting after
    -- SUM would be too late, because SUM over bigint can overflow.
    INSERT INTO #result
    SELECT N'postcode_simd', N'row count', CONVERT(nvarchar(200), e.row_count),
           CONVERT(nvarchar(200), q.n), ABS(q.n - e.row_count),
           CASE WHEN q.n = e.row_count THEN 1 ELSE 0 END
    FROM #expected e CROSS JOIN (SELECT COUNT_BIG(*) AS n FROM postcode_simd) AS q
    WHERE e.name = N'postcode_simd';

    INSERT INTO #result
    SELECT N'postcode_simd', N'row digest, definition version ' + CONVERT(nvarchar(10), e.digest_version),
           CONVERT(nvarchar(200), e.digest_total), CONVERT(nvarchar(200), ISNULL(q.total, 0)),
           CASE WHEN ISNULL(q.total, 0) = e.digest_total THEN 0 ELSE 1 END,
           CASE WHEN ISNULL(q.total, 0) = e.digest_total THEN 1 ELSE 0 END
    FROM #expected e CROSS JOIN (
        SELECT SUM(CONVERT(decimal(38,0), CONVERT(bigint, SUBSTRING(HASHBYTES('SHA2_256',
                   CONVERT(varchar(max), CONCAT_WS(NCHAR(31),
           CONCAT_WS(NCHAR(31),
           CONVERT(nvarchar(max), ISNULL([Postcode], NCHAR(0))),
           ISNULL([PostcodeDistrict], NCHAR(0)),
           ISNULL([PostcodeSector], NCHAR(0)),
           ISNULL([SplitIndicator], NCHAR(0)),
           ISNULL([LinkedSmallUserPostcode], NCHAR(0)),
           ISNULL([DateOfIntroduction], NCHAR(0)),
           ISNULL([DateOfDeletion], NCHAR(0)),
           ISNULL([PostcodeType], NCHAR(0)),
           ISNULL([OutputArea2022Code], NCHAR(0)),
           ISNULL([OutputArea2011Code], NCHAR(0)),
           ISNULL([DataZone2022Code], NCHAR(0)),
           ISNULL([DataZone2011Code], NCHAR(0)),
           ISNULL([IntermediateZone2022Code], NCHAR(0)),
           ISNULL([IntermediateZone2011Code], NCHAR(0)),
           ISNULL([OutputArea2001Code], NCHAR(0)),
           ISNULL([DataZone2001Code], NCHAR(0)),
           ISNULL([IntermediateZone2001Code], NCHAR(0)),
           ISNULL([OutputArea1991Code], NCHAR(0)),
           ISNULL([CouncilArea2019Code], NCHAR(0)),
           ISNULL([RegistrationDistrict2007Code], NCHAR(0)),
           ISNULL([LocalGovernmentDistrict1995Code], NCHAR(0)),
           ISNULL([LocalGovernmentDistrict1991Code], NCHAR(0)),
           ISNULL([ElectoralWard2022Code], NCHAR(0)),
           ISNULL([ScottishParliamentaryRegion2026Code], NCHAR(0)),
           ISNULL([ScottishParliamentaryConstituency2026Code], NCHAR(0)),
           ISNULL([UKParliamentaryConstituency2024Code], NCHAR(0)),
           ISNULL([HealthBoardArea2019Code], NCHAR(0)),
           ISNULL([HealthBoardArea2006Code], NCHAR(0)),
           ISNULL([HealthBoardArea1995Code], NCHAR(0)),
           ISNULL([IntegrationAuthority2019Code], NCHAR(0)),
           ISNULL([StrategicDevelopmentPlanningArea2013Code], NCHAR(0)),
           ISNULL([CivilParish1930Code], NCHAR(0)),
           ISNULL([TravelToWorkArea2011Code], NCHAR(0)),
           ISNULL([IslandCode], NCHAR(0)),
           ISNULL([UrbanRural6Fold2022Code], NCHAR(0)),
           ISNULL([UrbanRural8Fold2022Code], NCHAR(0)),
           ISNULL([LAU2025Level1Code], NCHAR(0)),
           ISNULL([ITL2025Level2Code], NCHAR(0)),
           ISNULL([ITL2025Level3Code], NCHAR(0)),
           ISNULL([CensusHouseholdCount2022], NCHAR(0)),
           ISNULL([CensusPopulationCount2022], NCHAR(0)),
           ISNULL([CensusHouseholdCount2011], NCHAR(0)),
           ISNULL([CensusPopulationCount2011], NCHAR(0)),
           ISNULL([CensusHouseholdCount2001], NCHAR(0)),
           ISNULL([CensusPopulationCount2001], NCHAR(0)),
           ISNULL([CensusHouseholdCount1991], NCHAR(0)),
           ISNULL([CensusPopulationCount1991], NCHAR(0)),
           ISNULL([ScottishIndexOfMultipleDeprivation2020Rank], NCHAR(0)),
           ISNULL([pc_norm], NCHAR(0)),
           ISNULL([spd_user_type], NCHAR(0)),
           ISNULL([sspl_release], NCHAR(0)),
           ISNULL(CONVERT(varchar(10), [introduced_on], 23), NCHAR(0)),
           ISNULL(CONVERT(varchar(10), [deleted_on], 23), NCHAR(0)),
           ISNULL(CONVERT(varchar(1), CONVERT(tinyint, [is_current])), NCHAR(0)),
           ISNULL([phs_dz2001_hb], NCHAR(0)),
           ISNULL([phs_dz2001_hscp], NCHAR(0)),
           ISNULL([phs_dz2001_ca], NCHAR(0)),
           ISNULL([phs_dz2011_hb], NCHAR(0)),
           ISNULL([phs_dz2011_hscp], NCHAR(0)),
           ISNULL([phs_dz2011_ca], NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2004_rank]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2004_pw_scotland_decile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2004_pw_scotland_quintile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2004_pw_hb_decile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2004_pw_hb_quintile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2004_pw_hscp_decile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2004_pw_hscp_quintile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2004_pw_ca_decile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2004_pw_ca_quintile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2004_most15pc]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2004_least15pc]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2004_uw_scotland_quintile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2004_uw_scotland_decile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2004_uw_scotland_vigintile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2006_rank]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2006_pw_scotland_decile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2006_pw_scotland_quintile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2006_pw_hb_decile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2006_pw_hb_quintile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2006_pw_hscp_decile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2006_pw_hscp_quintile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2006_pw_ca_decile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2006_pw_ca_quintile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2006_most15pc]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2006_least15pc]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2006_uw_scotland_quintile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2006_uw_scotland_decile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2006_uw_scotland_vigintile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2009v2_rank]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2009v2_pw_scotland_decile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2009v2_pw_scotland_quintile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2009v2_pw_hb_decile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2009v2_pw_hb_quintile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2009v2_pw_hscp_decile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2009v2_pw_hscp_quintile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2009v2_pw_ca_decile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2009v2_pw_ca_quintile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2009v2_most15pc]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2009v2_least15pc]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2009v2_uw_scotland_quintile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2009v2_uw_scotland_decile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2009v2_uw_scotland_vigintile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2012_rank]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2012_pw_scotland_decile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2012_pw_scotland_quintile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2012_pw_hb_decile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2012_pw_hb_quintile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2012_pw_hscp_decile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2012_pw_hscp_quintile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2012_pw_ca_decile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2012_pw_ca_quintile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2012_most15pc]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2012_least15pc]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2012_uw_scotland_quintile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2012_uw_scotland_decile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2012_uw_scotland_vigintile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2016_rank]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2016_pw_scotland_decile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2016_pw_scotland_quintile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2016_pw_hb_decile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2016_pw_hb_quintile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2016_pw_hscp_decile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2016_pw_hscp_quintile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2016_pw_ca_decile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2016_pw_ca_quintile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2016_most15pc]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2016_least15pc]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2016_uw_scotland_quintile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2016_uw_scotland_decile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2016_uw_scotland_vigintile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2020v2_rank]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2020v2_pw_scotland_decile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2020v2_pw_scotland_quintile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2020v2_pw_hb_decile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2020v2_pw_hb_quintile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2020v2_pw_hscp_decile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2020v2_pw_hscp_quintile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2020v2_pw_ca_decile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2020v2_pw_ca_quintile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2020v2_most15pc]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2020v2_least15pc]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2020v2_uw_scotland_quintile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2020v2_uw_scotland_decile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2020v2_uw_scotland_vigintile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(12), [simd2004_income_domain_rank]), NCHAR(0)),
           ISNULL(CONVERT(varchar(12), [simd2004_employment_domain_rank]), NCHAR(0)),
           ISNULL(CONVERT(varchar(12), [simd2004_health_domain_rank]), NCHAR(0)),
           ISNULL(CONVERT(varchar(12), [simd2004_education_domain_rank]), NCHAR(0)),
           ISNULL(CONVERT(varchar(12), [simd2004_access_domain_rank]), NCHAR(0)),
           ISNULL(CONVERT(varchar(12), [simd2004_housing_domain_rank]), NCHAR(0)),
           ISNULL(CONVERT(varchar(12), [simd2006_income_domain_rank]), NCHAR(0)),
           ISNULL(CONVERT(varchar(12), [simd2006_employment_domain_rank]), NCHAR(0)),
           ISNULL(CONVERT(varchar(12), [simd2006_health_domain_rank]), NCHAR(0)),
           ISNULL(CONVERT(varchar(12), [simd2006_education_domain_rank]), NCHAR(0)),
           ISNULL(CONVERT(varchar(12), [simd2006_access_domain_rank]), NCHAR(0)),
           ISNULL(CONVERT(varchar(12), [simd2006_crime_domain_rank]), NCHAR(0)),
           ISNULL(CONVERT(varchar(12), [simd2006_housing_domain_rank]), NCHAR(0)),
           ISNULL(CONVERT(varchar(12), [simd2009v2_income_domain_rank]), NCHAR(0)),
           ISNULL(CONVERT(varchar(12), [simd2009v2_employment_domain_rank]), NCHAR(0)),
           ISNULL(CONVERT(varchar(12), [simd2009v2_health_domain_rank]), NCHAR(0)),
           ISNULL(CONVERT(varchar(12), [simd2009v2_education_domain_rank]), NCHAR(0)),
           ISNULL(CONVERT(varchar(12), [simd2009v2_access_domain_rank]), NCHAR(0)),
           ISNULL(CONVERT(varchar(12), [simd2009v2_crime_domain_rank]), NCHAR(0)),
           ISNULL(CONVERT(varchar(12), [simd2009v2_housing_domain_rank]), NCHAR(0)),
           ISNULL(CONVERT(varchar(12), [simd2012_income_domain_rank]), NCHAR(0)),
           ISNULL(CONVERT(varchar(12), [simd2012_employment_domain_rank]), NCHAR(0)),
           ISNULL(CONVERT(varchar(12), [simd2012_health_domain_rank]), NCHAR(0)),
           ISNULL(CONVERT(varchar(12), [simd2012_education_domain_rank]), NCHAR(0)),
           ISNULL(CONVERT(varchar(12), [simd2012_access_domain_rank]), NCHAR(0)),
           ISNULL(CONVERT(varchar(12), [simd2012_crime_domain_rank]), NCHAR(0)),
           ISNULL(CONVERT(varchar(12), [simd2012_housing_domain_rank]), NCHAR(0)),
           ISNULL(CONVERT(varchar(12), [simd2016_income_domain_rank]), NCHAR(0)),
           ISNULL(CONVERT(varchar(12), [simd2016_employment_domain_rank]), NCHAR(0)),
           ISNULL(CONVERT(varchar(12), [simd2016_health_domain_rank]), NCHAR(0)),
           ISNULL(CONVERT(varchar(12), [simd2016_education_domain_rank]), NCHAR(0)),
           ISNULL(CONVERT(varchar(12), [simd2016_access_domain_rank]), NCHAR(0)),
           ISNULL(CONVERT(varchar(12), [simd2016_crime_domain_rank]), NCHAR(0)),
           ISNULL(CONVERT(varchar(12), [simd2016_housing_domain_rank]), NCHAR(0)),
           ISNULL(CONVERT(varchar(12), [simd2020v2_income_domain_rank]), NCHAR(0)),
           ISNULL(CONVERT(varchar(12), [simd2020v2_employment_domain_rank]), NCHAR(0)),
           ISNULL(CONVERT(varchar(12), [simd2020v2_health_domain_rank]), NCHAR(0)),
           ISNULL(CONVERT(varchar(12), [simd2020v2_education_domain_rank]), NCHAR(0)),
           ISNULL(CONVERT(varchar(12), [simd2020v2_access_domain_rank]), NCHAR(0)),
           ISNULL(CONVERT(varchar(12), [simd2020v2_crime_domain_rank]), NCHAR(0)),
           ISNULL(CONVERT(varchar(12), [simd2020v2_housing_domain_rank]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2004_income_domain_quintile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2004_income_domain_decile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2004_income_domain_vigintile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2004_employment_domain_quintile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2004_employment_domain_decile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2004_employment_domain_vigintile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2004_health_domain_quintile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2004_health_domain_decile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2004_health_domain_vigintile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2004_education_domain_quintile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2004_education_domain_decile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2004_education_domain_vigintile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2004_access_domain_quintile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2004_access_domain_decile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2004_access_domain_vigintile]), NCHAR(0))),
           CONCAT_WS(NCHAR(31),
           CONVERT(nvarchar(max), ISNULL(CONVERT(varchar(11), [simd2004_housing_domain_quintile]), NCHAR(0))),
           ISNULL(CONVERT(varchar(11), [simd2004_housing_domain_decile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2004_housing_domain_vigintile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2006_income_domain_quintile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2006_income_domain_decile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2006_income_domain_vigintile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2006_employment_domain_quintile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2006_employment_domain_decile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2006_employment_domain_vigintile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2006_health_domain_quintile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2006_health_domain_decile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2006_health_domain_vigintile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2006_education_domain_quintile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2006_education_domain_decile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2006_education_domain_vigintile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2006_access_domain_quintile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2006_access_domain_decile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2006_access_domain_vigintile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2006_crime_domain_quintile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2006_crime_domain_decile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2006_crime_domain_vigintile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2006_housing_domain_quintile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2006_housing_domain_decile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2006_housing_domain_vigintile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2009v2_income_domain_quintile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2009v2_income_domain_decile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2009v2_income_domain_vigintile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2009v2_employment_domain_quintile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2009v2_employment_domain_decile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2009v2_employment_domain_vigintile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2009v2_health_domain_quintile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2009v2_health_domain_decile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2009v2_health_domain_vigintile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2009v2_education_domain_quintile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2009v2_education_domain_decile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2009v2_education_domain_vigintile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2009v2_access_domain_quintile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2009v2_access_domain_decile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2009v2_access_domain_vigintile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2009v2_crime_domain_quintile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2009v2_crime_domain_decile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2009v2_crime_domain_vigintile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2009v2_housing_domain_quintile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2009v2_housing_domain_decile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2009v2_housing_domain_vigintile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2012_income_domain_quintile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2012_income_domain_decile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2012_income_domain_vigintile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2012_employment_domain_quintile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2012_employment_domain_decile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2012_employment_domain_vigintile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2012_health_domain_quintile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2012_health_domain_decile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2012_health_domain_vigintile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2012_education_domain_quintile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2012_education_domain_decile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2012_education_domain_vigintile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2012_access_domain_quintile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2012_access_domain_decile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2012_access_domain_vigintile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2012_crime_domain_quintile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2012_crime_domain_decile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2012_crime_domain_vigintile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2012_housing_domain_quintile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2012_housing_domain_decile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2012_housing_domain_vigintile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2016_income_domain_quintile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2016_income_domain_decile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2016_income_domain_vigintile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2016_employment_domain_quintile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2016_employment_domain_decile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2016_employment_domain_vigintile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2016_health_domain_quintile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2016_health_domain_decile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2016_health_domain_vigintile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2016_education_domain_quintile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2016_education_domain_decile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2016_education_domain_vigintile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2016_access_domain_quintile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2016_access_domain_decile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2016_access_domain_vigintile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2016_crime_domain_quintile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2016_crime_domain_decile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2016_crime_domain_vigintile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2016_housing_domain_quintile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2016_housing_domain_decile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2016_housing_domain_vigintile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2020v2_income_domain_quintile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2020v2_income_domain_decile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2020v2_income_domain_vigintile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2020v2_employment_domain_quintile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2020v2_employment_domain_decile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2020v2_employment_domain_vigintile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2020v2_health_domain_quintile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2020v2_health_domain_decile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2020v2_health_domain_vigintile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2020v2_education_domain_quintile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2020v2_education_domain_decile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2020v2_education_domain_vigintile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2020v2_access_domain_quintile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2020v2_access_domain_decile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2020v2_access_domain_vigintile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2020v2_crime_domain_quintile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2020v2_crime_domain_decile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2020v2_crime_domain_vigintile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2020v2_housing_domain_quintile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2020v2_housing_domain_decile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2020v2_housing_domain_vigintile]), NCHAR(0)),
           ISNULL([simd2020v2_housing_domain_rank_source_status], NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2004_income_domain_pw_scotland_quintile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2004_income_domain_pw_scotland_decile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2004_employment_domain_pw_scotland_quintile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2004_employment_domain_pw_scotland_decile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2004_health_domain_pw_scotland_quintile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2004_health_domain_pw_scotland_decile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2004_education_domain_pw_scotland_quintile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2004_education_domain_pw_scotland_decile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2004_access_domain_pw_scotland_quintile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2004_access_domain_pw_scotland_decile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2004_housing_domain_pw_scotland_quintile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2004_housing_domain_pw_scotland_decile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2006_income_domain_pw_scotland_quintile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2006_income_domain_pw_scotland_decile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2006_employment_domain_pw_scotland_quintile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2006_employment_domain_pw_scotland_decile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2006_health_domain_pw_scotland_quintile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2006_health_domain_pw_scotland_decile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2006_education_domain_pw_scotland_quintile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2006_education_domain_pw_scotland_decile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2006_access_domain_pw_scotland_quintile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2006_access_domain_pw_scotland_decile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2006_crime_domain_pw_scotland_quintile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2006_crime_domain_pw_scotland_decile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2006_housing_domain_pw_scotland_quintile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2006_housing_domain_pw_scotland_decile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2009v2_income_domain_pw_scotland_quintile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2009v2_income_domain_pw_scotland_decile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2009v2_employment_domain_pw_scotland_quintile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2009v2_employment_domain_pw_scotland_decile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2009v2_health_domain_pw_scotland_quintile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2009v2_health_domain_pw_scotland_decile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2009v2_education_domain_pw_scotland_quintile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2009v2_education_domain_pw_scotland_decile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2009v2_access_domain_pw_scotland_quintile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2009v2_access_domain_pw_scotland_decile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2009v2_crime_domain_pw_scotland_quintile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2009v2_crime_domain_pw_scotland_decile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2009v2_housing_domain_pw_scotland_quintile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2009v2_housing_domain_pw_scotland_decile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2012_income_domain_pw_scotland_quintile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2012_income_domain_pw_scotland_decile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2012_employment_domain_pw_scotland_quintile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2012_employment_domain_pw_scotland_decile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2012_health_domain_pw_scotland_quintile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2012_health_domain_pw_scotland_decile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2012_education_domain_pw_scotland_quintile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2012_education_domain_pw_scotland_decile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2012_access_domain_pw_scotland_quintile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2012_access_domain_pw_scotland_decile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2012_crime_domain_pw_scotland_quintile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2012_crime_domain_pw_scotland_decile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2012_housing_domain_pw_scotland_quintile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2012_housing_domain_pw_scotland_decile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2016_income_domain_pw_scotland_quintile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2016_income_domain_pw_scotland_decile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2016_employment_domain_pw_scotland_quintile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2016_employment_domain_pw_scotland_decile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2016_health_domain_pw_scotland_quintile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2016_health_domain_pw_scotland_decile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2016_education_domain_pw_scotland_quintile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2016_education_domain_pw_scotland_decile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2016_access_domain_pw_scotland_quintile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2016_access_domain_pw_scotland_decile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2016_crime_domain_pw_scotland_quintile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2016_crime_domain_pw_scotland_decile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2016_housing_domain_pw_scotland_quintile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2016_housing_domain_pw_scotland_decile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2020v2_income_domain_pw_scotland_quintile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2020v2_income_domain_pw_scotland_decile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2020v2_employment_domain_pw_scotland_quintile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2020v2_employment_domain_pw_scotland_decile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2020v2_health_domain_pw_scotland_quintile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2020v2_health_domain_pw_scotland_decile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2020v2_education_domain_pw_scotland_quintile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2020v2_education_domain_pw_scotland_decile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2020v2_access_domain_pw_scotland_quintile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2020v2_access_domain_pw_scotland_decile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2020v2_crime_domain_pw_scotland_quintile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2020v2_crime_domain_pw_scotland_decile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2020v2_housing_domain_pw_scotland_quintile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2020v2_housing_domain_pw_scotland_decile]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2004_population]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2006_population]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2009v2_population]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2012_population]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2016_population]), NCHAR(0)),
           ISNULL(CONVERT(varchar(11), [simd2020v2_population]), NCHAR(0)))) COLLATE Latin1_General_100_BIN2_UTF8)), 1, 8)))) AS total
        FROM postcode_simd) AS q
    WHERE e.name = N'postcode_simd';
END

-- ============================================================================================
-- The result. A skipped check is not a pass, so an absent row fails the count below.
-- ============================================================================================
SELECT table_name, check_name, expected, actual, difference,
       CASE passed WHEN 1 THEN 'pass' ELSE 'FAIL' END AS outcome
FROM #result ORDER BY table_name, check_name;

DECLARE @failed int = (SELECT COUNT(*) FROM #result WHERE passed = 0);
DECLARE @ran int = (SELECT COUNT(*) FROM #result);
IF @failed > 0 OR @ran <> 10
    THROW 51000, 'The imported tables do not match the build: see the result above.', 1;