SQL批量生成测试数据时随机查询国家名称异常问题及解决

业务需求 #

向User表批量插入数百万行测试数据，插入时需要从共249条记录的Country表中随机选取1个国家名称，填入User表的Location字段，无需使用国家ID。

表结构定义 #

User表结构 #

CREATE TABLE dbo.[User] (
    UserId BIGINT IDENTITY(1,1) NOT NULL,
    FirstName NVARCHAR(50) NOT NULL, 
    LastName NVARCHAR(50) NOT NULL,
    CreatedDate DATETIME2(2) NOT NULL,
    Reputation INT NOT NULL,
    [Location] NVARCHAR(100) NOT NULL,
    Bio NVARCHAR(4000) NULL,
    CONSTRAINT PK_User PRIMARY KEY CLUSTERED(UserId)
);

Nums辅助表结构及初始化 #

Nums表用于辅助生成大批量测试行：

CREATE TABLE dbo.Nums(n INT NOT NULL PRIMARY KEY)

INSERT INTO dbo.Nums(n)
VALUES(0), (1), (2), (3), (4), (5), (6), (7), (8), (9)
GO

Country表初始化语句 #

共249个国家/地区数据：

INSERT INTO dbo.[Country] ([Name])
    VALUES ('Afghanistan'),
    ('Åland Islands'),
    ('Albania'),
    ('Algeria'),
    ('American Samoa'),
    ('Andorra'),
    ('Angola'),
    ('Anguilla'),
    ('Antarctica'),
    ('Antigua and Barbuda'),
    ('Argentina'),
    ('Armenia'),
    ('Aruba'),
    ('Australia'),
    ('Austria'),
    ('Azerbaijan'),
    ('Bahrain'),
    ('Bahamas'),
    ('Bangladesh'),
    ('Barbados'),
    ('Belarus'),
    ('Belgium'),
    ('Belize'),
    ('Benin'),
    ('Bermuda'),
    ('Bhutan'),
    ('Bolivia, Plurinational State of'),
    ('Bonaire, Sint Eustatius and Saba'),
    ('Bosnia and Herzegovina'),
    ('Botswana'),
    ('Bouvet Island'),
    ('Brazil'),
    ('British Indian Ocean Territory'),
    ('Brunei Darussalam'),
    ('Bulgaria'),
    ('Burkina Faso'),
    ('Burundi'),
    ('Cambodia'),
    ('Cameroon'),
    ('Canada'),
    ('Cape Verde'),
    ('Cayman Islands'),
    ('Central African Republic'),
    ('Chad'),
    ('Chile'),
    ('China'),
    ('Christmas Island'),
    ('Cocos (Keeling) Islands'),
    ('Colombia'),
    ('Comoros'),
    ('Congo'),
    ('Congo, the Democratic Republic of the'),
    ('Cook Islands'),
    ('Costa Rica'),
    ('Côte d''Ivoire'),
    ('Croatia'),
    ('Cuba'),
    ('Curaçao'),
    ('Cyprus'),
    ('Czech Republic'),
    ('Denmark'),
    ('Djibouti'),
    ('Dominica'),
    ('Dominican Republic'),
    ('Ecuador'),
    ('Egypt'),
    ('El Salvador'),
    ('Equatorial Guinea'),
    ('Eritrea'),
    ('Estonia'),
    ('Ethiopia'),
    ('Falkland Islands (Malvinas)'),
    ('Faroe Islands'),
    ('Fiji'),
    ('Finland'),
    ('France'),
    ('French Guiana'),
    ('French Polynesia'),
    ('French Southern Territories'),
    ('Gabon'),
    ('Gambia'),
    ('Georgia'),
    ('Germany'),
    ('Ghana'),
    ('Gibraltar'),
    ('Greece'),
    ('Greenland'),
    ('Grenada'),
    ('Guadeloupe'),
    ('Guam'),
    ('Guatemala'),
    ('Guernsey'),
    ('Guinea'),
    ('Guinea-Bissau'),
    ('Guyana'),
    ('Haiti'),
    ('Heard Island and McDonald Islands'),
    ('Holy See (Vatican City State)'),
    ('Honduras'),
    ('Hong Kong'),
    ('Hungary'),
    ('Iceland'),
    ('India'),
    ('Indonesia'),
    ('Iran, Islamic Republic of'),
    ('Iraq'),
    ('Ireland'),
    ('Isle of Man'),
    ('Israel'),
    ('Italy'),
    ('Jamaica'),
    ('Japan'),
    ('Jersey'),
    ('Jordan'),
    ('Kazakhstan'),
    ('Kenya'),
    ('Kiribati'),
    ('Korea, Democratic People''s Republic of'),
    ('Korea, Republic of'),
    ('Kuwait'),
    ('Kyrgyzstan'),
    ('Lao People''s Democratic Republic'),
    ('Latvia'),
    ('Lebanon'),
    ('Lesotho'),
    ('Liberia'),
    ('Libya'),
    ('Liechtenstein'),
    ('Lithuania'),
    ('Luxembourg'),
    ('Macao'),
    ('Macedonia, the Former Yugoslav Republic of'),
    ('Madagascar'),
    ('Malawi'),
    ('Malaysia'),
    ('Maldives'),
    ('Mali'),
    ('Malta'),
    ('Marshall Islands'),
    ('Martinique'),
    ('Mauritania'),
    ('Mauritius'),
    ('Mayotte'),
    ('Mexico'),
    ('Micronesia, Federated States of'),
    ('Moldova, Republic of'),
    ('Monaco'),
    ('Mongolia'),
    ('Montenegro'),
    ('Montserrat'),
    ('Morocco'),
    ('Mozambique'),
    ('Myanmar'),
    ('Namibia'),
    ('Nauru'),
    ('Nepal'),
    ('Netherlands'),
    ('New Caledonia'),
    ('New Zealand'),
    ('Nicaragua'),
    ('Niger'),
    ('Nigeria'),
    ('Niue'),
    ('Norfolk Island'),
    ('Northern Mariana Islands'),
    ('Norway'),
    ('Oman'),
    ('Pakistan'),
    ('Palau'),
    ('Palestine, State of'),
    ('Panama'),
    ('Papua New Guinea'),
    ('Paraguay'),
    ('Peru'),
    ('Philippines'),
    ('Pitcairn'),
    ('Poland'),
    ('Portugal'),
    ('Puerto Rico'),
    ('Qatar'),
    ('Réunion'),
    ('Romania'),
    ('Russian Federation'),
    ('Rwanda'),
    ('Saint Barthélemy'),
    ('Saint Helena, Ascension and Tristan da Cunha'),
    ('Saint Kitts and Nevis'),
    ('Saint Lucia'),
    ('Saint Martin (French part)'),
    ('Saint Pierre and Miquelon'),
    ('Saint Vincent and the Grenadines'),
    ('Samoa'),
    ('San Marino'),
    ('Sao Tome and Principe'),
    ('Saudi Arabia'),
    ('Senegal'),
    ('Serbia'),
    ('Seychelles'),
    ('Sierra Leone'),
    ('Singapore'),
    ('Sint Maarten (Dutch part)'),
    ('Slovakia'),
    ('Slovenia'),
    ('Solomon Islands'),
    ('Somalia'),
    ('South Africa'),
    ('South Georgia and the South Sandwich Islands'),
    ('South Sudan'),
    ('Spain'),
    ('Sri Lanka'),
    ('Sudan'),
    ('Suriname'),
    ('Svalbard and Jan Mayen'),
    ('Swaziland'),
    ('Sweden'),
    ('Switzerland'),
    ('Syrian Arab Republic'),
    ('Taiwan, Province of China'),
    ('Tajikistan'),
    ('Tanzania, United Republic of'),
    ('Thailand'),
    ('Timor-Leste'),
    ('Togo'),
    ('Tokelau'),
    ('Tonga'),
    ('Trinidad and Tobago'),
    ('Tunisia'),
    ('Turkey'),
    ('Turkmenistan'),
    ('Turks and Caicos Islands'),
    ('Tuvalu'),
    ('Uganda'),
    ('Ukraine'),
    ('United Arab Emirates'),
    ('United Kingdom'),
    ('United States'),
    ('United States Minor Outlying Islands'),
    ('Uruguay'),
    ('Uzbekistan'),
    ('Vanuatu'),
    ('Venezuela, Bolivarian Republic of'),
    ('Viet Nam'),
    ('Virgin Islands, British'),
    ('Virgin Islands, U.S.'),
    ('Wallis and Futuna'),
    ('Western Sahara'),
    ('Yemen'),
    ('Zambia'),
    ('Zimbabwe')

问题实现及表现 #

最初的错误插入语句 #

随机取国家名称的逻辑运行异常：

INSERT INTO dbo.[User] (FirstName, LastName, CreatedDate, Reputation, [Location], Bio) 
SELECT 'FirstName' + CAST(ROUND(RAND(CHECKSUM(NEWID())) * (100000), 0) AS VARCHAR(10)), 
'LastName' +  CAST(ROUND(RAND(CHECKSUM(NEWID())) * (100000), 0) AS VARCHAR(10)),
DATEADD(DAY, RAND(CHECKSUM(NEWID()))*(1+DATEDIFF(DAY, '2015-01-01', '2021-06-08')), '2015-01-01'), 
ROUND(RAND(CHECKSUM(NEWID())) * (99999) + 1, 0),
-- 以下行逻辑异常
(SELECT TOP 1 [Name] FROM Country WHERE CountryId = ROUND(RAND(CHECKSUM(NEWID()))*(249-1)+1, 0) ),
N'Lorem ipsum dolor sit amet, consectetur adipiscing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua. Ut enim ad minim veniam, quis nostrud exercitation ullamco laboris nisi ut aliquip ex ea commodo consequat. Duis aute irure dolor in reprehenderit in voluptate velit esse cillum dolore eu fugiat nulla pariatur. Excepteur sint occaecat cupidatat non proident, sunt in culpa qui officia deserunt mollit anim id est laborum.'
FROM dbo.Nums AS D1
CROSS JOIN dbo.Nums AS D2
CROSS JOIN dbo.Nums AS D3
CROSS JOIN dbo.Nums AS D4
CROSS JOIN dbo.Nums AS D5
CROSS JOIN dbo.Nums AS D6
ORDER BY D1.n, D2.n, D3.n, D4.n, D5.n, D6.n

异常表现 #

单独执行测试语句SELECT * FROM Country WHERE CountryId = ROUND(RAND(CHECKSUM(NEWID()))*(249-1)+1, 0)，会随机出现返回空结果、返回多条结果、返回单条结果三种情况，不符合每次仅返回1条有效国家名称的预期。

错误尝试记录 #

曾尝试使用SELECT TOP 1 [Name] FROM Country ORDER BY NEWID()的写法实现随机取国家，但实际统计插入结果后发现仅能返回4个不同的国家名称，随机性完全不满足要求，对应错误代码如下：

INSERT INTO dbo.[User] (FirstName, LastName, CreatedDate, Reputation, [Location], Bio) 
SELECT 'FirstName' + CAST(ROUND(RAND(CHECKSUM(NEWID())) * (100000), 0) AS VARCHAR(10)), 
'LastName' +  CAST(ROUND(RAND(CHECKSUM(NEWID())) * (100000), 0) AS VARCHAR(10)),
DATEADD(DAY, RAND(CHECKSUM(NEWID()))*(1+DATEDIFF(DAY, '2015-01-01', '2021-06-08')), '2015-01-01'), 
ROUND(RAND(CHECKSUM(NEWID())) * (99999) + 1, 0),
(SELECT TOP 1 [Name] FROM Country ORDER BY NEWID()),
N'Lorem ipsum dolor sit amet, consectetur adipiscing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua. Ut enim ad minim veniam, quis nostrud exercitation ullamco laboris nisi ut aliquip ex ea commodo consequat. Duis aute irure dolor in reprehenderit in voluptate velit esse cillum dolore eu fugiat nulla pariatur. Excepteur sint occaecat cupidatat non proident, sunt in culpa qui officia deserunt mollit anim id est laborum.'
FROM dbo.Nums AS D1
CROSS JOIN dbo.Nums AS D2
CROSS JOIN dbo.Nums AS D3
CROSS JOIN dbo.Nums AS D4
CROSS JOIN dbo.Nums AS D5
CROSS JOIN dbo.Nums AS D6
ORDER BY D1.n, D2.n, D3.n, D4.n, D5.n, D6.n

最终可用解决方案 #

以下方案可实现国家名称均匀随机分布，完全符合需求：

INSERT INTO dbo.[User] (FirstName, LastName, CreatedDate, Reputation, [Location], Bio) 
SELECT 'FirstName' + CAST(ROUND(RAND(CHECKSUM(NEWID())) * (100000), 0) AS VARCHAR(10)), 
'LastName' +  CAST(ROUND(RAND(CHECKSUM(NEWID())) * (100000), 0) AS VARCHAR(10)),
DATEADD(DAY, RAND(CHECKSUM(NEWID()))*(1+DATEDIFF(DAY, '2015-01-01', '2021-06-08')), '2015-01-01'), 
ROUND(RAND(CHECKSUM(NEWID())) * (99999) + 1, 0),
C.[Name],
N'Lorem ipsum dolor sit amet, consectetur adipiscing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua. Ut enim ad minim veniam, quis nostrud exercitation ullamco laboris nisi ut aliquip ex ea commodo consequat. Duis aute irure dolor in reprehenderit in voluptate velit esse cillum dolore eu fugiat nulla pariatur. Excepteur sint occaecat cupidatat non proident, sunt in culpa qui officia deserunt mollit anim id est laborum.'
FROM dbo.Nums AS D1
CROSS JOIN dbo.Nums AS D2
CROSS JOIN dbo.Nums AS D3
CROSS JOIN dbo.Nums AS D4
CROSS JOIN dbo.Nums AS D5
CROSS JOIN dbo.Nums AS D6
CROSS APPLY (values((ABS(CHECKSUM(NEWID())) % 249)+1)) AS RND(R)
CROSS APPLY (
    SELECT TOP 1 [Name]
    FROM Country AS C
    WHERE C.CountryId = R
) AS C
ORDER BY D1.n, D2.n, D3.n, D4.n, D5.n, D6.n

内容的提问来源于stack exchange，提问作者Andrew