By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE [dbo].[LastNames](
[LastNameID] [int] IDENTITY(1,1) NOT NULL,
[LastName] [varchar](50) NOT NULL
) ON [PRIMARY];
CREATE UNIQUE CLUSTERED INDEX [CIX_LastNames_LastName] ON [dbo].[LastNames]
(
[LastName] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY];
CREATE TABLE [dbo].[FirstNames](
[FirstNameID] [int] IDENTITY(1,1) NOT NULL,
[FirstName] [varchar](50) NOT NULL
) ON [PRIMARY];
CREATE UNIQUE CLUSTERED INDEX [CIX_FirstNames_FirstName] ON [dbo].[FirstNames]
(
[FirstName] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY];
CREATE TABLE [dbo].[PersonsAnon](
[PersonID] [int] IDENTITY(1,1) NOT NULL,
[LastNameID] [int] NOT NULL,
[FirstNameID] [int] NOT NULL,
[Info1] [bit] NULL,
[Info2] [char](1) NULL,
[Info3] [nchar](50) NULL,
[AdressID] [int] NULL
) ON [PRIMARY];
CREATE UNIQUE CLUSTERED INDEX [CIX_PersonsAnon_PersonID_LastNameID_FirstNameID] ON [dbo].[PersonsAnon]
(
[PersonID] ASC,
[LastNameID] ASC,
[FirstNameID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY];
CREATE NONCLUSTERED INDEX [NIX_PersonsAnon_LastNameID_FirstNameID] ON [dbo].[PersonsAnon]
(
[LastNameID] ASC,
[FirstNameID] ASC
)
INCLUDE([PersonID]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY];
INSERT INTO dbo.LastNames (LastName) SELECT DISTINCT app.LastName FROM Person.Person AS app;
1206 rows affected
INSERT INTO dbo.FirstNames (FirstName) SELECT DISTINCT app.FirstName FROM Person.Person AS app;
1018 rows affected
INSERT INTO dbo.PersonsAnon (LastNameID, FirstNameID)
SELECT ln.LastNameID, fn.FirstNameID FROM LastNames ln CROSS APPLY FirstNames fn;
Msg 1101 Level 17 State 12 Line 1
Could not allocate a new page for database 'fiddle_18db0a5382d849b2a8cf9f9addf716f8' because of insufficient disk space in filegroup 'PRIMARY'. Create the necessary space by dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup.
SELECT pa.PersonID,
fn.FirstName,
ln.LastName,
pa.Info2
FROM PersonsAnon AS pa
JOIN LastNames AS ln
ON pa.LastNameID = ln.LastNameID
JOIN FirstNames AS fn
ON pa.FirstNameID = fn.FirstNameID
WHERE ln.LastName LIKE 'Pete%'
AND fn.FirstName LIKE 'John%'
ORDER BY
ln.LastName,
fn.FirstName;
| PersonID | FirstName | LastName | Info2 |
|---|