Data type traps

In one line: some of the worst bugs in SQL Server don’t raise an error. A total is a fraction off, a report is missing a day, a date is read as the 2nd of January, a name is stored as ??. Each is a data type doing exactly what it’s documented to do.

Run it yourself: the scripts that reproduce everything on this page are in sqlserver-demos/data-type-traps on GitHub.

What you’ll see

what you wrote	what came back	what you meant
`AVG(Stars)` over 5, 4, 4 (`int`)	4	4.33
`SUM` of a million `float` 0.1s	100000.0000000133	100000
`decimal(38,18) * decimal(38,18)`	6 decimal places	12
`money`: `100 / 3 * 3`	99.9999 (displayed as 100.00)	100.00
`DECLARE @s varchar = 'Hello, world'`	H	Hello, world
`'東京'` into `nvarchar`, no `N`	??	東京
`BETWEEN '20250101' AND '20250131 23:59:59.999'` (`datetime`)	1,441 rows, including 1 February	1,440
`CAST('2025-02-01' AS datetime)` under British English	2 January	1 February
`'ABC'` and `'ABC '` in a `UNIQUE` column	duplicate key error	two codes

Why it happens

int / int is an int, and AVG of an int column is an int. The fraction is discarded, not rounded.
float has no exact 0.1. Tiny errors add up, and equal-looking floats compare unequal.
Decimal multiplication needs precision p1 + p2 + 1. Over 38, the scale is cut, as low as 6, with no warning.
money rounds every intermediate step to four places, and converting it to a string shows two.
varchar with no length means varchar(1) in a declaration and varchar(30) in CAST. A literal with no N is converted to the database’s code page before it reaches the nvarchar column.
datetime stores time in 1/300-second steps, so .999 rounds up to the next day. 'YYYY-MM-DD' converted to datetime follows the session’s date order.
Comparisons ignore trailing spaces, and so does LEN.

What to do

Use decimal for money and quantities, sized for what each column holds.
Give every string type a length and every Unicode literal an N.
Use date or datetime2, write literals as 'YYYYMMDD', and query time with half-open ranges: >= '20250101' AND < '20250201'.
Multiply by 1.0 (or cast) before dividing integers.
Choose bigint for identities that might run for a long time.

What doesn’t help

Declaring every decimal as (38, x) “to be safe”. That’s exactly what makes multiplication drop digits.
BETWEEN with 23:59:59.999. It’s wrong for datetime and fragile for every other type.

Check your server

-- Identity columns in this database, and how much of their type's range is used.
-- Failed and rolled-back inserts burn values too.
SELECT OBJECT_SCHEMA_NAME(ic.object_id) AS schema_name,
       OBJECT_NAME(ic.object_id)        AS table_name,
       ic.name                          AS column_name,
       TYPE_NAME(ic.system_type_id)     AS data_type,
       LastValue = CAST(ic.last_value AS bigint),
       PctUsed   = CAST(100.0 * CAST(ic.last_value AS bigint) /
                        CASE TYPE_NAME(ic.system_type_id)
                             WHEN 'tinyint'  THEN 255
                             WHEN 'smallint' THEN 32767
                             WHEN 'int'      THEN 2147483647
                             ELSE 9223372036854775807 END AS decimal(5, 2))
FROM sys.identity_columns AS ic
WHERE ic.last_value IS NOT NULL
  AND TYPE_NAME(ic.system_type_id) IN ('tinyint', 'smallint', 'int', 'bigint')
ORDER BY PctUsed DESC;

An int identity stops at 2,147,483,647, and every insert after that fails with Msg 8115.

See it happen

The data-type-traps demo runs nine traps and prints what SQL Server returned next to what was wanted, with the fix beside each. The traps cover integer arithmetic, float, decimal × decimal, money, strings, datetime, date literals, trailing spaces and identity overflow.

Measured on SQL Server 2022 CU25 (16.0.4255.1). Your numbers will differ; the behaviour shouldn’t.

Part of SQL Server Demos: short explanations of SQL Server problems, each backed by a runnable demo in MarlonRibunal/sqlserver-demos on GitHub. Written with the help of AI; every number comes from running the demo on SQL Server. How this was made.