Skip to main content

SQL Server Type Support

Foundations JDBC provides comprehensive support for 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.decimal(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_(10)
val varcharType: SqlServerType<String> = SqlServerTypes.varchar(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.nchar(10)
val nvarcharType: SqlServerType<String> = SqlServerTypes.nvarchar(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.binary(16)
val varbinaryType: SqlServerType<ByteArray> = SqlServerTypes.varbinary(255)
val varbinaryMax: SqlServerType<ByteArray> = SqlServerTypes.varbinaryMax

Date/Time Types​

SQL Server TypeJava TypePrecisionNotes
DATELocalDateDayDate only
TIMELocalTime100nsTime only
DATETIMELocalDateTime3.33msLegacy
SMALLDATETIMELocalDateTimeMinuteLegacy
DATETIME2LocalDateTime100nsModern
DATETIMEOFFSETOffsetDateTime100nsWith timezone
val dateType: SqlServerType<LocalDate> = SqlServerTypes.date
val timeType: SqlServerType<LocalTime> = SqlServerTypes.time
val time3: SqlServerType<LocalTime> = SqlServerTypes.time(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.datetime2(3)

// Timezone-aware
val dtoType: SqlServerType<OffsetDateTime> = SqlServerTypes.datetimeoffset
val dto3: SqlServerType<OffsetDateTime> = SqlServerTypes.datetimeoffset(3)

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)