Skip to main content

SQL Server type support

Foundations JDBC supports SQL Server data types, including geography, geometry, hierarchyid, and Unicode types.

Key differences​

  • TINYINT is UNSIGNED in SQL Server (0-255), unlike most other databases
  • Separate Unicode types (NCHAR, NVARCHAR, NTEXT) vs non-Unicode
  • DATETIMEOFFSET for timezone-aware timestamps
  • UNIQUEIDENTIFIER for UUIDs/GUIDs
  • No native array support - use table-valued parameters instead

Integer types​

SQL Server TypeJava TypeRangeNotes
TINYINTUint10-255Unsigned!
SMALLINTShort-32,768 to 32,767
INTInteger-2^31 to 2^31-1
BIGINTLong-2^63 to 2^63-1
val tinyType: SqlServerType<Uint1> = SqlServerTypes.tinyint   // Note: unsigned!
val intType: SqlServerType<Int> = SqlServerTypes.int_
val bigType: SqlServerType<Long> = SqlServerTypes.bigint

Fixed-point types​

SQL Server TypeJava TypeNotes
DECIMAL(p,s)BigDecimalExact numeric
NUMERIC(p,s)BigDecimalAlias for DECIMAL
MONEYBigDecimalCurrency (4 decimal places)
SMALLMONEYBigDecimalSmaller currency range
val decimalType: SqlServerType<BigDecimal> = SqlServerTypes.decimal
val precise: SqlServerType<BigDecimal> = SqlServerTypes.decimalOf(18, 4)
val moneyType: SqlServerType<BigDecimal> = SqlServerTypes.money

Floating-point types​

SQL Server TypeJava TypeNotes
REALFloat32-bit IEEE 754
FLOATDouble64-bit IEEE 754
val realType: SqlServerType<Float> = SqlServerTypes.real
val floatType: SqlServerType<Double> = SqlServerTypes.float_

Boolean type​

SQL Server TypeJava Type
BITBoolean
val bitType: SqlServerType<Boolean> = SqlServerTypes.bit

String types (non-Unicode)​

SQL Server TypeJava TypeMax LengthNotes
CHAR(n)String8,000 charsFixed-length
VARCHAR(n)String8,000 charsVariable-length
VARCHAR(MAX)String2 GBLarge variable-length
TEXTString2 GBDeprecated, use VARCHAR(MAX)
val charType: SqlServerType<String> = SqlServerTypes.char_Of(10)
val varcharType: SqlServerType<String> = SqlServerTypes.varcharOf(255)
val varcharMax: SqlServerType<String> = SqlServerTypes.varcharMax

String types (Unicode)​

SQL Server TypeJava TypeMax LengthNotes
NCHAR(n)String4,000 charsFixed-length Unicode
NVARCHAR(n)String4,000 charsVariable-length Unicode
NVARCHAR(MAX)String2 GBLarge Unicode
NTEXTString2 GBDeprecated
val ncharType: SqlServerType<String> = SqlServerTypes.ncharOf(10)
val nvarcharType: SqlServerType<String> = SqlServerTypes.nvarcharOf(255)
val nvarcharMax: SqlServerType<String> = SqlServerTypes.nvarcharMax

Binary types​

SQL Server TypeJava TypeMax Length
BINARY(n)byte[]8,000 bytes
VARBINARY(n)byte[]8,000 bytes
VARBINARY(MAX)byte[]2 GB
IMAGEbyte[]2 GB (deprecated)
val binaryType: SqlServerType<ByteArray> = SqlServerTypes.binaryOf(16)
val varbinaryType: SqlServerType<ByteArray> = SqlServerTypes.varbinaryOf(255)
val varbinaryMax: SqlServerType<ByteArray> = SqlServerTypes.varbinaryMax

Date/time types​

SQL Server TypeJava TypePrecisionNotes
DATELocalDateDayNaive date, no zone
TIMELocalTime100nsNaive time, no zone
DATETIMELocalDateTime3.33msLegacy naive timestamp
SMALLDATETIMELocalDateTimeMinuteLegacy naive timestamp
DATETIME2LocalDateTime100nsModern naive timestamp
DATETIMEOFFSETOffsetDateTime100nsPreserves offset — see note below
val dateType: SqlServerType<LocalDate> = SqlServerTypes.date
val timeType: SqlServerType<LocalTime> = SqlServerTypes.time
val time3: SqlServerType<LocalTime> = SqlServerTypes.timeOf(3)  // TIME(3)

// Legacy types
val datetimeType: SqlServerType<LocalDateTime> = SqlServerTypes.datetime
val smalldtType: SqlServerType<LocalDateTime> = SqlServerTypes.smalldatetime

// Modern types (recommended)
val datetime2Type: SqlServerType<LocalDateTime> = SqlServerTypes.datetime2
val datetime2_3: SqlServerType<LocalDateTime> = SqlServerTypes.datetime2Of(3)

// Timezone-aware
val dtoType: SqlServerType<OffsetDateTime> = SqlServerTypes.datetimeoffset
val dto3: SqlServerType<OffsetDateTime> = SqlServerTypes.datetimeoffsetOf(3)
DATETIMEOFFSET → OffsetDateTime (genuinely stores the offset)

Unlike PostgreSQL's timestamptz or DuckDB's TIMESTAMPTZ, SQL Server's DATETIMEOFFSET really does store the offset value byte-for-byte (-14:00 to +14:00). From Microsoft's docs: "Time zone offset aware and preservation: Yes… The time zone offset is preserved in the database for retrieval."

OffsetDateTime is the matching Java type — a timestamp plus a fixed numeric offset, no DST awareness (SQL Server's own docs: "Daylight saving aware: No"). Since SQL Server can only store an offset (not a named zone like America/Los_Angeles), OffsetDateTime captures exactly what the column holds — using ZonedDateTime would suggest the library can preserve zone regions, which the storage cannot.

If you need UTC-only "instant" semantics instead, use DATETIME2 + a separate offset column, or normalize client-side before insert.

UNIQUEIDENTIFIER (UUID/GUID)​

SQL Server TypeJava Type
UNIQUEIDENTIFIERjava.util.UUID
val uuidType: SqlServerType<UUID> = SqlServerTypes.uniqueidentifier

XML type​

SQL Server TypeJava Type
XMLXml
val xmlType: SqlServerType<Xml> = SqlServerTypes.xml

JSON type​

SQL Server 2016+ stores JSON as NVARCHAR(MAX):

SQL Server TypeJava TypeNotes
NVARCHAR(MAX)JsonJSON stored as Unicode string
val jsonType: SqlServerType<Json> = SqlServerTypes.json

Spatial types​

SQL Server spatial types use the JDBC driver's native classes:

SQL Server TypeJava TypeNotes
GEOGRAPHYGeographyGeodetic (round earth)
GEOMETRYGeometryPlanar (flat earth)
val geoType: SqlServerType<Geography> = SqlServerTypes.geography
val geomType: SqlServerType<Geometry> = SqlServerTypes.geometry

HIERARCHYID​

For hierarchical tree structures:

SQL Server TypeJava TypeNotes
HIERARCHYIDHierarchyIdPath notation like /1/2/3/
val hierarchyType: SqlServerType<HierarchyId> = SqlServerTypes.hierarchyid

ROWVERSION / TIMESTAMP​

SQL Server TypeJava TypeNotes
ROWVERSIONbyte[]8-byte version number
TIMESTAMPbyte[]Alias for ROWVERSION
val rowversionType: SqlServerType<ByteArray> = SqlServerTypes.rowversion

SQL_VARIANT​

SQL Server TypeJava TypeNotes
SQL_VARIANTObjectCan store various types
val variantType: SqlServerType<Any> = SqlServerTypes.sqlVariant

VECTOR (SQL Server 2025)​

SQL Server TypeJava TypeNotes
VECTORbyte[]For embeddings/ML
val vectorType: SqlServerType<ByteArray> = SqlServerTypes.vector

Nullable types​

Any type can be made nullable using .opt():

val notNull: SqlServerType<Int> = SqlServerTypes.int_
val nullable: SqlServerType<Int?> = SqlServerTypes.int_.opt()

Custom domain types​

Wrap base types with custom Java types using transform:

// Wrapper type
data class OrderId(val value: Int)

// Create SqlServerType from INT
val orderIdType: SqlServerType<OrderId> =
    SqlServerTypes.int_.transform(::OrderId, OrderId::value)