SQL Server 2017

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE [dbo].[tCat](
    [CatID] [int] IDENTITY(1,1) NOT NULL,
    [Cat] [nchar](100) NOT NULL,
    [CatBreedID] [int] NULL,
    [Fluffiness] [float] NOT NULL,
 CONSTRAINT [PK_tCat] PRIMARY KEY CLUSTERED 
(
    [CatID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
-- batch abort
SET XACT_ABORT ON;
BEGIN TRAN
    INSERT INTO tCat(Cat, Fluffiness) VALUES('Sir Pounce A Lot', 8.0)
    INSERT INTO tCat(Cat)             VALUES('Violet')
COMMIT 
Msg 515 Level 16 State 2 Line 5
Cannot insert the value NULL into column 'Fluffiness', table 'fiddle_920b1041f5944f16bc8a2ce954835490.dbo.tCat'; column does not allow nulls. INSERT fails.

SELECT @@TRANCOUNT


(No column name)


0


SELECT * FROM dbo.tCat


CatID
Cat
CatBreedID
Fluffiness


-- statement abort
SET XACT_ABORT OFF;
BEGIN TRAN
    INSERT INTO tCat(Cat, Fluffiness) VALUES('Sir Pounce A Lot', 8.0)
    INSERT INTO tCat(Cat)             VALUES('Violet')
COMMIT 
Msg 515 Level 16 State 2 Line 5
Cannot insert the value NULL into column 'Fluffiness', table 'fiddle_920b1041f5944f16bc8a2ce954835490.dbo.tCat'; column does not allow nulls. INSERT fails.
Msg 3621 Level 0 State 0 Line 5
The statement has been terminated.

SELECT @@TRANCOUNT


(No column name)


0


SELECT * FROM dbo.tCat


CatID
Cat
CatBreedID
Fluffiness


3
Sir Pounce A Lot
null
8