create table Parent (
    id int not null, 
    constraint PK_Parent primary key clustered (id)
); 

create table Child (
    id int not null, 
    Parent_id int not null foreign key references Parent(id), 
    constraint PK_Child primary key clustered (id)
);

-- OK 
select * from Parent inner join Child on Child.Parent_ID = Parent.ID
-- Error
select * from Parent inner join Child on Child.ID = Parent.ID

declare @tableWithNulls table (val int null);
declare @tableWithoutNulls table (val int not null);

-- OK
insert into @tableWithNulls
select val from @tableWithoutNulls;

-- TypeError
insert into @tableWithoutNulls
select val from @tableWithNulls;

-- OK
insert into @tableWithoutNulls
select COALESCE(val, 0) from @tableWithNulls;

-- when declaring a variable and immediately setting it to a non-null
-- TSqlStrong knows the value is not null.
declare @someInt int = 10;

-- OK. The value was initialized to not null.
insert into @tableWithoutNulls values (@someInt);

declare @someOtherInt int;

-- ERROR. TSqlStrong saw that @someInt became null.
insert into @tableWithoutNulls values (@someOtherInt);

if (@someOtherInt is not null)
begin
    -- OK. TSqlStrong knows that @someOtherInt is not null at this point thanks to the check.
    insert into @tableWithoutNulls values (@someInt);
end;

create table Person
(
    gender varchar(40) 
        constraint ck_Person_Gender
        check (
            (gender = 'male')
            or (gender = 'female')
            or (gender = 'other') 
            or (gender = 'unknown')
        )
)

-- OK
insert into Person (gender)
values 
    ('female'),
    ('other'),
    ('male');

-- Type Error
insert into Person (gender)
values ('hello');

create table Person
(
    gender varchar(40) 
        constraint ck_Person_Gender
        check (
            (gender = 'male')
            or (gender = 'female')
            or (gender = 'other') 
            or (gender = 'unknown')
        )
)
GO

declare @someInt int;

-- OK
insert into Person (gender)
values
    (case 
        when @someInt is null then 'unknown'
        when @someInt > 0 then 'female'
        else 'male'                
    end);

-- Type Error due to 'apple' 
insert into Person (gender)
values
    (case 
        when @someInt is null then 'unknown'
        when @someInt > 0 then 'apple'
        else 'male'                
    end);

-- Type Error: 
insert into TableOfManyColumns (A, B, C, D /*,...Z*/)
select
    1 as A,
    'Hi' as B,
    13 as C,
    'Bye' as E, -- Error: Expected D in this position    
    /* ...Z */
from
    SomeOtherWideTable

create procedure dbo.SelectFirstAndLastNameFromTemp 
as
begin
    select firstName, lastName from #Temp;
end;
GO

create procedure dbo.CallsSelectFirstAndLastNameFromTemp
as
begin
    exec dbo.SelectFirstAndLastNameFromTemp; 
end;
GO

create procedure dbo.CreateWithFullNameAndExecuteIndirect
as
begin
    create table #Temp (fullName varchar(200));

    -- ERROR: When called from here the temp table does not have the correct structure.
    exec dbo.CallsSelectFirstAndLastNameFromTemp; 
end;
GO

create procedure dbo.CreateWithFirstAndLastNameAndExecuteIndirect
as
begin
    create table #Temp (firstName varchar(200), lastName varchar(200));
    
    -- OK: When called from here it is just fine.
    exec dbo.CallsSelectFirstAndLastNameFromTemp; 
end;
GO

with Recurse (Num)
as
(
    select 0

    union all

    -- ERROR: Num is an Int from the base case
    select Num + 'apples' 
    from Recurse 
)
select top 10 Num from Recurse;

with Recurse (Num)
as
(
    select 0

    union all

    -- ERROR: NumX is not in the list of Recurse
    select NumX
    from Recurse 
)
select top 10 Num from Recurse;

-- ERROR: Column count mismatch between CTE and union.
with Recurse (Num)
as
(
    select 0, 1

    union all

    select Num, 20
    from Recurse 
)
select top 10 Num from Recurse;

-- GOOD!
with Recurse (Num)
as
(
    select 0

    union all

    select Num + 1
    from Recurse 
)
select top 10 Num from Recurse;

with NonRecursive (Num)
as
(
    select 0
    union all
    select 1
    union all
    select 2
)
select * 
from 
    NonRecursive 
where 
    -- ERROR: TSqlStrong figured out Num can only be 0, 1, or 2 so this comparison check makes no sense.
    Num = 4;

-- GOOD
with NonRecursive (Num)
as
(
    select 0
    union all
    select 1
    union all
    select 2
)
select * 
from 
    NonRecursive 
where 
    Num = 2;

create table Person (weight int not null)
GO

declare @someInt int; -- NOTE: All variables are nullable by default.

insert into Person select @someInt; -- ERROR: Possible nullable assignment to non-null.
insert into Person select Coalesce(@someInt, 1); -- OK. 
insert into Person select IsNull(@someInt, 1); -- OK. 

if (@someInt is not null)
begin
    insert into Person select @someInt; -- OK
end
else 
begin
    insert into Person select @someInt; -- TYPE ERROR
end;

create table Person
(
    gender varchar(40) 
        not null
        constraint ck_Person_Gender
        check (
            (gender = 'male')
            or (gender = 'female')
            or (gender = 'other') 
            or (gender = 'unknown')
        )
)
GO

declare @randomText varchar(100);

insert into Person select coalesce(@randomText, 'unknown'); -- TYPE ERROR

if (@randomText = 'male') or (@randomText = 'female') or (@randomText = 'other')
begin
    -- OK. TSqlStrong knows randomText must be 'male', 'female', 'other'.
    insert into Person select @randomText; 
end;