Exact/Approximate Numerics, Strings, Unicode, Dates, Times, GUIDs, Binary, XML, and JSON

Learning outcomes

ServiceHub’s schema already contains integers, text, datetime2, bit, and rowversion. A new integration now proposes storing money in float, multilingual technician notes in varchar, local appointment times without offsets, GUIDs as 36-character text, and request payloads in whatever JSON feature happens to be newest. Those choices can work syntactically while still create precision, collation, storage, interoperability, or upgrade problems.

1. Exact numerics model quantities; approximate numerics model ranges

tinyint, smallint, int, and bigint are exact integral types. decimal(p,s)/numeric(p,s) are exact fixed-precision types where precision p is the total digit count and scale s is digits to the right of the decimal point. float and real are approximate binary floating-point types.

Approximate does not mean “bad.” It is appropriate for scientific/measurement workloads where range and floating-point semantics are accepted. It is usually the wrong default for money, invoice totals, quotas, or identifiers where equality and exact decimal arithmetic are business requirements.

USE ServiceHubLab;GODECLARE @exact decimal(19,4) = 0.1,        @approx float = 0.1;SELECT    @exact * 10 AS exact_times_ten,    @approx * 10 AS approx_times_ten,    SQL_VARIANT_PROPERTY(@exact, 'BaseType') AS exact_type,    SQL_VARIANT_PROPERTY(@approx, 'BaseType') AS approx_type;GOSELECT    CAST(0.1 AS float) + CAST(0.2 AS float) AS approximate_sum,    CAST(0.1 AS decimal(10,4)) + CAST(0.2 AS decimal(10,4)) AS exact_sum;GO

Do not turn a single displayed result into a universal floating-point demonstration; client formatting can hide binary representation differences. The contractual lesson is that float is approximate by definition. Choose the type from required arithmetic/rounding behavior, then test boundary values.

2. varchar and nvarchar carry different encoding/collation assumptions

varchar stores non-Unicode character data according to collation/code-page rules; UTF-8 collations can change that storage model. nvarchar stores Unicode data using UTF-16 encoding semantics. The N'...' literal prefix creates a Unicode string literal. Choosing a string type is therefore not merely a maximum-character-count question—it affects representation, implicit conversion, collation, indexes, and driver parameter metadata.

USE ServiceHubLab;GODECLARE @v varchar(20) = 'ServiceHub',        @n nvarchar(20) = N'ServiceHub';SELECT    LEN(@v) AS varchar_chars,    DATALENGTH(@v) AS varchar_bytes,    LEN(@n) AS nvarchar_chars,    DATALENGTH(@n) AS nvarchar_bytes;GOSELECT    c.name AS column_name,    TYPE_NAME(c.user_type_id) AS data_type,    c.max_length,    c.collation_nameFROM sys.columns AS cWHERE c.object_id = OBJECT_ID(N'ops.Technician')ORDER BY c.column_id;GO

Notice that max_length is stored in bytes in catalog metadata, so an nvarchar(100) column commonly reports 200. Chapter 02 already established that collation is a comparison/sort contract; data type and collation must be considered together.

3. Date/time types preserve different information

Use date for calendar dates, time for time-of-day, datetime2 for a date/time value without an offset, and datetimeoffset when the offset is part of the stored value. Legacy datetime and smalldatetime remain supported but have older ranges/precision semantics; new designs should justify them rather than choose them by habit.

A timestamp that means “the instant this work order was opened” is often normalized to UTC and stored in datetime2, with the application’s business time-zone context stored separately. If the original offset matters (for example, an appointment entered as 09:00 at +04:00), datetimeoffset can preserve that offset. It still does not store a named time zone or daylight-saving rule set.

SELECT    CAST('2026-08-22T13:30:00' AS datetime2(0)) AS no_offset_value,    CAST('2026-08-22T13:30:00+04:00' AS datetimeoffset(0)) AS offset_value,    SWITCHOFFSET(CAST('2026-08-22T13:30:00+04:00' AS datetimeoffset(0)), '+00:00') AS same_instant_utc;GO

4. GUID, binary and XML are first-class types—not text conventions

uniqueidentifier stores a 16-byte GUID value with SQL Server comparison rules. It avoids wasting space on the textual punctuation and prevents invalid “GUID-shaped” strings from entering the column. It can still be a poor clustered-key choice under some insertion patterns; key/index design is Chapter 9 material, not a reason to encode GUIDs as strings.

varbinary stores bytes: hashes, compressed payloads, encrypted blobs, or small binary artifacts. xml stores XML with XML-aware validation/query/index capabilities. The rule is not “use special types for everything”; it is “do not erase semantics into strings unless the system deliberately wants opaque text.”

USE ServiceHubLab;GOSELECT    NEWID() AS sample_guid,    HASHBYTES('SHA2_256', CONVERT(varbinary(max), N'ServiceHub')) AS sample_hash,    CAST(N'<workOrder id="1001"><status>assigned</status></workOrder>' AS xml) AS sample_xml;GO

5. JSON in SQL Server 2025: stable functions, preview native type

SQL Server has supported JSON functions over string storage since SQL Server 2016. A robust stable pattern is nvarchar(max) plus validation such as CHECK (ISJSON(payload)=1), then JSON_VALUE/OPENJSON for access. SQL Server 2025 adds a native binary json type, but Microsoft currently labels that type preview for SQL Server 2025 itself. A course that requires free reproducible labs should not silently require preview features.

USE ServiceHubLab;GOIF SCHEMA_ID(N'lab03') IS NULL EXEC(N'CREATE SCHEMA lab03 AUTHORIZATION dbo;');GODROP TABLE IF EXISTS lab03.PayloadProbe;GOCREATE TABLE lab03.PayloadProbe(    payload_id int IDENTITY PRIMARY KEY,    payload nvarchar(max) NOT NULL,    CONSTRAINT CK_PayloadProbe_IsJson CHECK (ISJSON(payload) = 1));GOINSERT lab03.PayloadProbe(payload)VALUES (N'{"workOrderId":1001,"source":"mobile","ok":true}');GOSELECT    payload_id,    JSON_VALUE(payload, '$.workOrderId') AS work_order_id_text,    JSON_VALUE(payload, '$.source') AS source_nameFROM lab03.PayloadProbe;GO

The scalar return from classic JSON_VALUE without a SQL Server 2025 RETURNING clause is text. Convert deliberately when the downstream value is numeric/date typed. If you later adopt the native json type, re-check its preview/GA status, driver support, conversion restrictions, indexing options, and deployment prerequisites first.

6. Deliberately wrong approach: “store everything as nvarchar(max)”

A hurried integration stores amount, timestamp, work-order ID, boolean, GUID, and JSON payload all as nvarchar(max). The schema now accepts 'twelve dollars', malformed timestamps, arbitrary GUID text, and inconsistent boolean spellings. Every query must parse values repeatedly, parameter types are easy to mismatch, and useful constraints/indexes become harder to express.

7. Hands-on lab: build a type-contract probe

USE ServiceHubLab;GODROP TABLE IF EXISTS lab03.TypeContract;GOCREATE TABLE lab03.TypeContract(    event_id uniqueidentifier NOT NULL CONSTRAINT DF_TypeContract_Id DEFAULT NEWID(),    work_order_id bigint NOT NULL,    billable_amount decimal(19,4) NULL,    measurement float NULL,    event_time_utc datetime2(3) NOT NULL,    source_time datetimeoffset(3) NULL,    is_verified bit NOT NULL,    note nvarchar(200) NULL,    payload nvarchar(max) NULL,    payload_hash varbinary(32) NULL,    metadata xml NULL,    CONSTRAINT PK_TypeContract PRIMARY KEY (event_id),    CONSTRAINT CK_TypeContract_Payload CHECK (payload IS NULL OR ISJSON(payload)=1));GOINSERT lab03.TypeContract(work_order_id, billable_amount, measurement, event_time_utc, source_time, is_verified, note, payload, payload_hash, metadata)VALUES(1001, 125.7500, 0.125, '2026-08-22T09:30:00.000', '2026-08-22T13:30:00.000+04:00', 1, N'Unicode note: café', N'{"source":"mobile","retry":false}', HASHBYTES('SHA2_256', CONVERT(varbinary(max), N'1001|mobile')), CAST(N'<source name="mobile" />' AS xml));GOSELECT * FROM lab03.TypeContract;GOSELECT    c.name, TYPE_NAME(c.user_type_id) AS type_name,    c.max_length, c.precision, c.scale, c.is_nullable, c.collation_nameFROM sys.columns AS cWHERE c.object_id = OBJECT_ID(N'lab03.TypeContract')ORDER BY c.column_id;GO

Verification checklist

• Money-like data uses an exact decimal type.
• The Unicode note round-trips correctly.
• You can explain what information datetimeoffset preserves beyond datetime2.
• Invalid JSON text is rejected by the check constraint on the stable path.
• You did not enable a preview feature merely to finish the mandatory lab.

USE ServiceHubLab;GODROP TABLE IF EXISTS lab03.TypeContract;DROP TABLE IF EXISTS lab03.PayloadProbe;GO

8. Production judgment and next bridge

Type design is an application contract. Record precision/scale/length, Unicode needs, collation, time-zone policy, identifier semantics, binary size, nullability, and feature status in schema/API documentation. “It fits today” is not enough; types influence constraints, parameter metadata, index keys, sorting, hashing, serialization, and future migrations.

Lesson 3 shows why type choices also become performance choices. When operands differ, SQL Server follows data-type precedence and may introduce implicit conversions. A conversion on the wrong side of an indexed predicate can change an access path from a seek into a scan.

Authoritative references

• Data types (Transact-SQL) — SQL Server type families and language reference
• decimal and numeric — precision and scale for exact numerics
• float and real — approximate numeric semantics
• char and varchar — non-Unicode/UTF-8-collation string behavior
• nchar and nvarchar — Unicode string semantics
• Date and time types — datetime2/datetimeoffset and temporal type choices
• uniqueidentifier — GUID storage and conversion semantics
• xml data type — native XML semantics
• JSON data type — SQL Server 2025 native JSON feature status and limitations
• Store JSON documents — stable string-based JSON storage and relational alternatives