[SQL Server] Simple experiment - Insert blank into the numeric column

[SQL Server] Simple experiment - Insert blank into the numeric column

What will happen if you insert blank value to the numric type columns?

Let's try it.
Create a table.

CREATE TABLE dbo.tbl_NumberTest
(
	SEQ           INT IDENTITY(1, 1) NOT NULL, 
	DATATYPE      VARCHAR(200) NOT NULL, 
	ColBigInt     BIGINT, 
	ColBit        BIT, 
	ColDecimal    DECIMAL(5, 2), 
	ColInt        INT, 
	ColMoney      MONEY, 
	ColNumeric    NUMERIC(5, 2), 
	ColSmallInt   SMALLINT, 
	ColSmallMoney SMALLMONEY, 
	ColTinyInt    TINYINT, 
	ColFloat      FLOAT, 
	ColReal       REAL
);
GO



Insert some data

INSERT INTO dbo.tbl_NumberTest (DATATYPE, ColBigInt) VALUES ('ColBigInt', '');
(1 row affected)
 
INSERT INTO dbo.tbl_NumberTest (DATATYPE, ColBit) VALUES ('ColBit', '');
(1 row affected)
 
INSERT INTO dbo.tbl_NumberTest (DATATYPE, ColDecimal) VALUES ('ColDecimal', '');
Msg 8114, Level 16, State 5, Line 27
Error converting data type varchar to numeric.
 
INSERT INTO dbo.tbl_NumberTest (DATATYPE, ColInt) VALUES ('ColInt', '');
(1 row affected)
 
INSERT INTO dbo.tbl_NumberTest (DATATYPE, ColMoney) VALUES ('ColMoney', '');
(1 row affected)
 
INSERT INTO dbo.tbl_NumberTest (DATATYPE, ColNumeric) VALUES ('ColNumeric', '');
Msg 8114, Level 16, State 5, Line 37
Error converting data type varchar to numeric.
 
INSERT INTO dbo.tbl_NumberTest (DATATYPE, ColSmallInt) VALUES ('ColSmallInt', '');
(1 row affected)
 
INSERT INTO dbo.tbl_NumberTest (DATATYPE, ColSmallMoney) VALUES ('ColSmallMoney', '');
(1 row affected)
 
INSERT INTO dbo.tbl_NumberTest (DATATYPE, ColTinyInt) VALUES ('ColTinyInt', '');
(1 row affected)
 
INSERT INTO dbo.tbl_NumberTest (DATATYPE, ColFloat) VALUES ('ColFloat', '');
(1 row affected)
 
INSERT INTO dbo.tbl_NumberTest (DATATYPE, ColReal) VALUES ('ColReal', '');
(1 row affected)



You can see that some of them had failed.

Select data

SELECT * FROM dbo.tbl_NumberTest;

Conclusion

As you can see, by default, they had been inserted as "0" without errors except NUMERIC type and DECIMAL Type.
- DECIMAL type = NUMERIC Type