Types

witsqltypesreferenceddl

Three things are worth keeping apart: the type you write in CREATE TABLE, the type the row is stored as, and the type a value has when a query hands it back.

The first two agree closely. The third collapses: every width of integer arrives as long, and every kind of string arrives as string.

Integers

Declare Aliases Stored as Comes back as
TINYINT INT8 1 byte, fixed long
UTINYINT UINT8, BYTE 1 byte, fixed long
SMALLINT INT16, SHORT 2 bytes, fixed long
USMALLINT UINT16, USHORT 2 bytes, fixed long
INT INTEGER, INT32 1 to 5 bytes, variable long
UINT UINT32 1 to 5 bytes, variable long
BIGINT INT64, LONG 1 to 10 bytes, variable long
UBIGINT UINT64, ULONG 1 to 10 bytes, variable long

All of them read back as long.

The storage is worth a moment. The two narrow types are fixed width, so declaring TINYINT for something that holds a percentage genuinely saves bytes. INT and BIGINT are variable width, so a small value in a BIGINT column costs the same as the same value in an INT column, and choosing INT over BIGINT to save space saves nothing. Choose them for the range you need instead, and choose BIGINT for a key you expect to grow.

A value outside a column's declared range is refused rather than wrapped.

Real and exact numbers

Declare Aliases Stored as Comes back as
FLOAT16 HALF 2 bytes double
FLOAT FLOAT32, REAL 4 bytes double
DOUBLE FLOAT64 8 bytes double
DECIMAL(p,s) NUMERIC, MONEY 16 bytes decimal

Use DECIMAL for money and anything else where a rounding error would be a bug rather than a nuisance.

Text

Declare Aliases Stored as
CHAR(n) NCHAR(n) Fixed, n bytes
VARCHAR(n) NVARCHAR(n) Variable, length prefix and bytes
TEXT NTEXT, STRING Variable, unbounded

Everything is UTF-8, so the N prefixed names are aliases rather than a different encoding. All three come back as string.

Binary

Declare Stored as Comes back as
BINARY(n) Fixed, n bytes byte[]
VARBINARY(n) Variable, bounded byte[]
BLOB Variable, unbounded byte[]

Dates and times

Declare Aliases Stored as Comes back as
DATE DATEONLY 4 bytes DateOnly
TIME TIMEONLY 8 bytes TimeOnly
DATETIME TIMESTAMP, DATETIME2 8 bytes DateTime
DATETIMEOFFSET 10 bytes DateTimeOffset
INTERVAL TIMESPAN, DURATION 8 bytes TimeSpan

These map onto the .NET types of the same shape, so a DATE column is a DateOnly rather than a DateTime at midnight.

Write a temporal literal with the type in front of it, which is what keeps its type through a round trip. A bare quoted string loses it.

sql
INSERT INTO Events (On, At, Stamp, Offset) VALUES (
    DATE '2026-08-11',
    TIME '14:30:00',
    TIMESTAMP '2026-08-11 14:30:00',
    DATETIMEOFFSET '2026-08-11 14:30:00 +03:00'
);

DATETIME works in place of TIMESTAMP.

The rest

Declare Aliases Stored as Comes back as
BOOLEAN BOOL, BIT 1 byte bool
GUID UUID, UNIQUEIDENTIFIER 16 bytes Guid
JSON JSONB Text JsonDocument
ROWVERSION 8 bytes ulong

ROWVERSION is maintained by the engine and increments whenever the row changes, which is what optimistic concurrency checks against. EF Core's IsRowVersion() maps onto it.

JSON and JSONB are the same type here. Both store text, and the JSON functions parse it. See functions.

An unknown type name becomes text

A type name the engine does not recognise is stored as a variable-length string rather than refused. CREATE TABLE t (x INTEGERR) produces a text column, and nothing says so until a value comes back the wrong shape.

Check the spelling of anything unusual, and prefer the canonical names in the left column of the tables above over the aliases, since a mistyped alias is exactly the case this hides.

ROWID

Every table has a ROWID, an internal 64-bit identifier the engine places rows by. It can be selected like a column. An INTEGER PRIMARY KEY AUTOINCREMENT column is backed by the same counter, which is why auto-increment works on a single-column key and not on a composite one.

Declared sizes are enforced

VARCHAR(n) and CHAR(n) mean what they say. A longer string is refused rather than truncated, on insert and on update, because silently losing the end of a value is the outcome nobody can want.

DECIMAL(p,s) behaves as it does in PostgreSQL: a value with more decimals than the scale is rounded to it, so 123.456 into a DECIMAL(5,2) is stored as 123.46 and that is not an error. A value whose integer part does not fit in p - s digits is refused, since no rounding saves it.

A value outside an integer column's range is refused rather than wrapped.

CAST

CAST converts a value and does not constrain it:

sql
SELECT CAST(x AS SMALLINT) FROM t;

The result is an integer, not an integer wrapped to two bytes. Casting 100000 to SMALLINT returns 100000. SQLite behaves the same way. A cast expresses how you want the value read, and a column type expresses what may be stored.

A cast that cannot read its input answers with a default rather than an error. CAST('not a number' AS BIGINT) is 0, and CAST('abc' AS DATETIME) is the zero date. Check the input first where that matters, with TYPEOF or a pattern.

That is deliberate, and the boundary is worth knowing: ALTER COLUMN ... TYPE does not behave this way. A stored value that will not read as the new type is refused, naming the value, before anything is written, because a rewrite of stored data that silently zeroes what it cannot parse is a different thing from an expression that returns a default.

NULL

NULL is a value of its own, distinct from zero and from the empty string. Comparisons with it give NULL rather than true or false, which is why WHERE x = NULL matches nothing and WHERE x IS NULL is what you want.

The functions for working with it are COALESCE, IFNULL, NULLIF and IS [NOT] NULL; see functions.

Where to go next