Transact-SQL (SQL Server)

This assessment applies the coupling account — coupling as access to a shared resource, and the coupling floor [1], [2] — and the knowledge-embodiment theorem [3]; the language is assessed against its official reference [4].

1 Assessment

This page assesses Transact-SQL in five parts: core coupling — what the language’s own constructs force; ecosystem coupling — what the surrounding platform, tooling, and community cost; engineering readiness — whether the artifact can be reproduced, verified, observed, worked in quickly, connected to other languages, and governed openly; the profile — the unscored facts a score cannot express; and the scoring detail — the mechanism marks and the arithmetic behind every score. A ✅ bullet marks a construct that helps. A ⚠️ or ❌ bullet marks a cost; the scoring model does not price every cost named here.

Core coupling 58/100 · Ecosystem coupling 69/100 · Engineering readiness 61/100

1.1 Core coupling

1.1.1 Type

  • ✅ Static typing over SQL data types: Every variable, parameter, and column has a declared type, and a block that violates it fails at parse or bind time
  • ✅ Table variables and table-valued parameters carry a declared row type: DECLARE @t TABLE (...) and a user-defined table type pass a typed row set between units
  • ✅ A procedure parameter declares its type and direction: @p int OUTPUT fixes the type and the direction, so a caller binds to a signature
  • ⚠️ DECLARE @name defaults to NULL (T10): A declared variable starts as NULL, so an unassigned variable is valid and propagates NULL into the expression
  • ⚠️ NULL comparison needs three-valued logic: With ANSI_NULLS ON (the default, and the only supported setting since SQL Server 2017), @x = NULL is unknown, so a check needs IS NULL
  • ⚠️ There is no type beyond a SQL data type: No user-defined sum type or generic exists, so a variant is modelled with nullable columns or a discriminator

1.1.2 Construction

  • ✅ A stored procedure provides modularity with a declared interface: CREATE PROCEDURE names the unit, its parameters, and its result, and can return a status value and OUTPUT parameters
  • ✅ A view or an inline table-valued function separates consumers from the query: The exposed relation is stable while its defining query can change
  • ✅ A table-valued parameter passes a row set without a shared temporary table: A user-defined table type plus a TVP parameter removes the need for a global temporary table
  • ✅ Set-based statements compose without a cursor: INSERT ... SELECT, UPDATE ... FROM, and MERGE express the whole transformation in one statement
  • ⚠️ Triggers fire implicitly: An AFTER or INSTEAD OF trigger runs in response to a data change, so the writer of a statement cannot see from the statement which code will run
  • ⚠️ Session SET options change the meaning of a statement: ANSI_NULLS and QUOTED_IDENTIFIER are captured when a stored procedure is created, while DATEFORMAT applies when the statement runs, so the same text can parse or convert differently elsewhere

1.1.3 Memory

  • ✅ No manual memory management: The SQL Server buffer pool and the query workspace own the memory, and a batch never names an address
  • ✅ The server grants memory to a query: The query’s memory grant comes from the server’s calculator or the Resource Governor, so the batch does not request bytes
  • ✅ Buffer pool and tempdb behaviour are server configuration: max server memory, the tempdb files, and a Resource Governor pool are settings, not batch properties
  • ⚠️ A sort or hash can spill to tempdb: An operation whose grant is too small writes to tempdb, which costs I/O even though the language hides allocation
  • ⚠️ A cursor allocates per fetch: A row-by-row cursor uses workspace per fetch, so the memory pattern follows the loop

1.1.4 Error

  • ✅ TRY ... CATCH gives a structured path: The CATCH block runs on error and exposes ERROR_NUMBER, ERROR_MESSAGE, ERROR_SEVERITY, and ERROR_LINE
  • ✅ THROW and RAISERROR raise with a number and a message: THROW 50001, 'msg', 1 carries a number, so a handler can branch on it
  • ✅ XACT_STATE() and @@TRANCOUNT tell the handler whether the transaction is committable: A doomed transaction is detectable inside CATCH
  • ⚠️ Errors ignored or non-exhaustive (Er3): A CATCH block that swallows the error, or a skipped @@ERROR check, lets the batch continue past the failure
  • ⚠️ Errors do not abort the batch by default: Without SET XACT_ABORT ON, a statement error rolls back that statement and the batch continues
  • ⚠️ Severity decides whether the caller sees the error: A low-severity error is informational, so the same statement text can pass a caller by

1.1.5 Concurrency

  • ✅ Transactions group statements atomically: BEGIN TRANSACTION, COMMIT, and ROLLBACK make a unit of work commit or abort as one
  • ✅ Isolation levels are selectable and versioned: READ UNCOMMITTED, READ COMMITTED (default), REPEATABLE READ, SNAPSHOT, and SERIALIZABLE, while the database options READ_COMMITTED_SNAPSHOT and ALLOW_SNAPSHOT_ISOLATION switch READ COMMITTED to row versioning and enable SNAPSHOT
  • ✅ Snapshot isolation removes reader–writer blocking: With READ_COMMITTED_SNAPSHOT ON, a reader uses row versions and does not block a writer
  • ✅ Set-based statements reduce explicit locking: One UPDATE moves many rows without a row-by-row lock protocol in application code
  • ❌ Shared mutable state by default (K2): SET options, CONTEXT_INFO, and a global temporary table ##t are visible to code the caller did not write
  • ⚠️ A locking hint couples the query to the engine’s concurrency model: WITH (NOLOCK), UPDLOCK, and READPAST are engine-shaped and must agree with the application’s lock ordering

1.1.6 Platform

  • ✅ Client interfaces are standard: ODBC, JDBC, OLE DB, and ADO.NET reach the engine, so a client is not bound to one binary protocol
  • ✅ Bulk interfaces are documented: bcp and BULK INSERT use a documented format, so data exchange is not tied to an application API
  • ⚠️ Two implementations exist under separate vendors: Microsoft SQL Server and SAP ASE share a lineage and a core syntax, but the two diverge, and Microsoft and SAP document them separately
  • ⚠️ Azure SQL is the same vendor with a different deployment: The managed service changes availability, administration, and some features, so code targets a specific surface
  • ❌ Cannot function outside one vendor (P1): The language needs SQL Server, Azure SQL, or SAP ASE
  • ❌ The T-SQL surface is engine-specific: TOP, IDENTITY, GO, the sys catalog views, and the sp_ procedures are SQL Server features, so a port to another engine is a rewrite

1.1.7 Trade-offs (not coupling)

  • Logic beside the data is deliberate: Keeping a rule in the database guarantees it for every writer
  • A cursor is a deliberate trade for row-by-row work: It makes a per-row algorithm expressible, at the cost of the set-based model
  • Dynamic SQL is deliberate for DDL and for a flexible query: sp_executesql and EXEC reach constructs a static statement cannot name
  • NOLOCK trades consistency for concurrency: A reader that accepts dirty reads avoids blocking
  • The engine is proprietary by design: The licence buys one integrated stack of engine, tooling, and support
  • T-SQL is not a general-purpose language: The assumption of a SQL Server database is a scope decision

1.2 Ecosystem coupling

1.2.1 Performance

  • ✅ A cost-based optimiser with a plan cache executes a statement: A procedure’s plan can be reused, so the caller does not choose an access path
  • ✅ A columnstore index and batch mode scan analytically: CREATE COLUMNSTORE INDEX plus batch mode processes a large scan in vectorised batches
  • ✅ Query Store records plans and regressions: An operator can see a plan change and force a previous plan
  • ✅ In-memory OLTP tables are lock-free and memory-optimised: MEMORY_OPTIMIZED = ON tables and natively compiled procedures remove latch contention
  • ⚠️ Parameter sniffing can choose a plan for the wrong input: A cached plan built for one parameter value can be poor for another
  • ⚠️ A plan can regress without a query change: New statistics, a version upgrade, or a changed data volume can select a worse plan

1.2.2 Ecosystem

  • ✅ Tooling is mature and first-party: SQL Server Management Studio, Azure Data Studio, and sqlcmd cover administration and query work
  • ✅ The market and the hiring pool are large: SQL Server is widely deployed in Microsoft environments, so documentation, training, and consultants are abundant
  • ✅ Cloud deployment is a first-party service: Azure SQL Database and Managed Instance run the same engine with a managed administration model
  • ❌ Proprietary (Ec4): The engine, the tooling, and the support come from Microsoft (and, for ASE, from SAP) under licence
  • ⚠️ The ASE toolchain is separate from the SQL Server toolchain: SAP maintains its own documentation, tools, and support, so knowledge does not transfer freely

1.2.3 Evolution

  • ✅ The release cadence is regular and active: SQL Server ships a major release periodically, and Azure SQL receives continuous updates
  • ✅ A compatibility level keeps old behaviour: A database at an older compatibility level keeps the older optimiser and syntax behaviour while the engine is upgraded
  • ✅ The engine leads the standard: TRY ... CATCH, MERGE, window functions, and JSON support arrived as engine features, so users get them without waiting for the standard
  • ⚠️ A deprecated feature is warned and then removed: The documentation lists deprecated features, and an upgrade can require edits
  • ⚠️ Feature availability differs by edition and deployment: An Enterprise feature or an Azure-only feature may not exist on a local Standard edition

1.2.4 Domain

  • ✅ Excellent fit for transactional and analytical logic in the database: A stored procedure runs beside the data with a plan and a transaction
  • ✅ One language spans OLTP and analytics: Set-based DML, window functions, and a columnstore scan all live in the same language
  • ✅ The surrounding pipeline is first-party: Integration Services, the Agent scheduler, and Reporting Services cover the rest of a data workflow
  • ❌ Domain and vendor lock-in (D1): The language exists inside SQL Server or SAP ASE, so both the domain and the vendor are fixed
  • ⚠️ Procedural row logic reads poorly: A row-by-row algorithm in a cursor is verbose and slow beside its set-based equivalent
  • ⚠️ It is not a runtime for arbitrary programs: The language assumes a SQL Server database and a set-based workload

1.2.5 Learning

  • ✅ Gentle for a SQL developer: A procedure with BEGIN ... END, TRY ... CATCH, and SELECT ... INTO is readable to anyone who knows SQL
  • ✅ Documentation and examples are complete: The Microsoft T-SQL reference documents every statement with examples
  • ⚠️ The procedural and the set-based model coexist: A developer must choose between a cursor and a set-based statement, and the wrong choice shows up as a performance problem
  • ⚠️ NULL handling must be learned: With ANSI_NULLS ON, = NULL never matches, and a comparison with NULL yields unknown
  • ⚠️ Plan and locking behaviour is a separate body of knowledge: Performance work means reading plans and lock waits rather than syntax
  • ⚠️ The SAP ASE dialect diverges in details: Differences in GO, IDENTITY, and built-in functions mean knowledge transfers only in part

1.2.6 Syntax

  • ✅ Keyword-based and readable: SELECT ... FROM ... WHERE and BEGIN ... END read close to English
  • ✅ A procedure is a readable unit: The parameter list, the body, and the RETURN value are declared in one CREATE PROCEDURE statement
  • ✅ GO marks a batch boundary in a script: The client tool sends the batch, so the boundary is visible in the source
  • ⚠️ SET options prefix every script: SET NOCOUNT ON, ANSI_NULLS ON, and QUOTED_IDENTIFIER ON are usually written at the top, so the boilerplate grows
  • ⚠️ @ and @@ distinguish kinds of variable by convention: A local variable, a parameter, and a global function share the sigil, so a reader distinguishes them by spelling
  • ⚠️ Identifier quoting depends on a setting: [name] and "name" both quote an identifier, and the second form needs QUOTED_IDENTIFIER ON

1.2.7 Testing

  • ✅ tSQLt is the established framework: An open-source unit-test framework runs test classes with mocked tables and assertions
  • ✅ TRY ... CATCH makes a unit test expressible in the language: A test procedure asserts and THROWs on a mismatch
  • ✅ A transaction wraps a test and rolls it back: The database is unchanged after the test
  • ✅ Error metadata supports an assertion on a failure path: ERROR_NUMBER and ERROR_MESSAGE inside CATCH let a test check the rejection
  • ⚠️ The base product ships no test framework: tSQLt is a third-party install, so a suite depends on deployment and permissions
  • ⚠️ A server-side suite needs a server: The harness needs a SQL Server instance, so the tests couple to infrastructure

1.2.8 Security

  • ✅ sp_executesql with parameters removes the injection surface: EXEC sp_executesql N'SELECT ... WHERE x = @p', N'@p int', @p = 1 passes the value out of band
  • ✅ GRANT, DENY, and REVOKE with roles express least privilege: Access is declared per object, per schema, and per role
  • ✅ Row-level security limits what a statement can see: A SECURITY POLICY with a predicate function filters rows by the caller’s identity
  • ✅ EXECUTE AS makes the effective identity explicit: A procedure can run as its owner or as a named user
  • ❌ Known vulnerability/injection (Q3): A statement built with EXEC (@sql) from concatenated input lets the input become code
  • ⚠️ Dynamic SQL and owner-rights execution re-open the risk: EXEC on a built string, sp_OA* procedures, and xp_cmdshell where enabled defeat parameterisation

1.3 Engineering readiness

1.3.1 Reproducibility

  • ✅ No charged mechanism in this dimension.

1.3.2 Supply chain

  • ⚠️ Published packages carry no signed provenance by default.

1.3.3 Observability

  • ⚠️ No stable tracing and metrics standard.
  • ⚠️ The standard library has no structured logging.

1.3.4 Feedback

  • ✅ No charged mechanism in this dimension.

1.3.5 Interop

  • ⚠️ No maintained portable target.

1.3.6 Governance

  • ⚠️ One vendor decides the language’s direction.
  • ⚠️ The specification is not public.

1.4 Profile

Unscored, and kept separate from every score on purpose. The profile records what a mechanism price cannot express: what a running program costs, how well a coding model and an agent work in the language, and whether a safety-critical path exists.

1.4.1 Energy and cost

What this axis asks: what does a running program cost in energy and time?

  • ⚠️ Runs inside a database engine: energy and time are the engine’s, not the program’s.

1.4.2 AI and agent readiness

What this axis asks: how well does a coding model and an agent work in this language?

  • ❌ Sparse training data: models write it unreliably [5].

1.4.3 Safety certification

What this axis asks: is there a certified or standards-qualified path to a safety-critical system?

  • ⚠️ No certified or standards-qualified path for safety-critical systems.

1.5 Scoring detail

1.5.1 Core coupling Dimensions

Dimension Mechanism Description Present Cost
Type T1 dynamic no static check — 0
T2 weak static — 0
T3 dynamic/duck dispatch — 0
T4 no sum types — 0
T5 no generics — 0
T6 no namespaces — 0
T7 type erasure/reflection — 0
T8 operator overloading — 0
T9 inheritance required for polymorphism — 0
T10 no null-safety ✅ −8
T11 no module boundary — 0
T12 no effect tracking ✅ −8
T13 async/sync colouring — 0
T14 unchecked foreign boundary ✅ −4
Type subtotal −15 (−20)
Construction C1 global mutable state by default — 0
C2 no immutability by default — 0
C3 constructors coupled to allocation — 0
C4 no constructor/validation — 0
Construction subtotal 0
Memory M1 manual deallocation — 0
M2 GC non-deterministic pauses — 0
M3 no RAII — 0
M4 no ownership model — 0
Memory subtotal 0
Error Er1 no structured path — 0
Er2 unchecked exceptions only — 0
Er3 errors ignored/non-exhaustive ✅ −4
Er4 no composable result type — 0
Error subtotal −4
Concurrency K1 GIL/no parallel — 0
K2 shared mutable state default ✅ −8
K3 manual sync no race detection — 0
K4 no structured concurrency — 0
K5 no primitives — 0
Concurrency subtotal −8
Platform P1 cannot function outside one vendor/domain ✅ −15
P2 single-vendor but other uses — 0
P3 legacy/discontinued platform — 0
Platform subtotal −15

Core Dimensions score: 58/100 — total cost = −42 (per-dimension subtotals are capped at −15; an uncapped cost is shown in parentheses where the cap bites).

1.5.2 Ecosystem coupling Dimensions

Dimension Mechanism Description Present Cost
Performance R1 interpreted no AOT/JIT — 0
R2 GC pauses — 0
R3 JIT warmup — 0
Performance subtotal 0
Ecosystem Ec1 no package manager — 0
Ec2 fragmented tooling — 0
Ec3 small/niche/discontinued — 0
Ec4 proprietary ✅ −8
Ec5 platform-locked tooling — 0
Ec6 monoculture — 0
Ec7 legacy burden — 0
Ecosystem subtotal −8
Evolution V1 breaking changes — 0
V2 frozen — 0
V3 discontinued — 0
V4 glacial standards — 0
Evolution subtotal 0
Domain D1 domain/vendor lock-in ✅ −15
D2 poor domain fit — 0
D3 framework forces architecture — 0
Domain subtotal −15
Learning L1 steep — 0
L2 large surface/multi-paradigm — 0
L3 sparse docs — 0
Learning subtotal 0
Syntax S1 verbose — 0
S2 inconsistent — 0
S3 punctuation-heavy — 0
Syntax subtotal 0
Testing G1 weak tooling — 0
G2 poor debugging — 0
G3 runtime-only errors — 0
Testing subtotal 0
Security Q1 memory-unsafe — 0
Q2 dynamic/weak typing risk — 0
Q3 known vulnerability/injection ✅ −8
Security subtotal −8

Meta Dimensions score: 69/100 — total cost = −31 (per-dimension subtotals are capped at −15; an uncapped cost is shown in parentheses where the cap bites).

1.5.3 Engineering readiness Dimensions

Dimension Mechanism Description Present Cost
Reproducibility Y1 build not reproducible — 0
Y2 dependencies not pinned — 0
Reproducibility subtotal 0
Supply chain Y3 no signed provenance ✅ −8
Y4 registry without namespacing — 0
Supply chain subtotal −8
Observability Y5 no stable tracing standard ✅ −8
Y6 no structured logging ✅ −4
Observability subtotal −12
Feedback Y7 slow build — 0
Y8 no fast in-editor analysis — 0
Feedback subtotal 0
Interop Y9 no foreign-function interface — 0
Y10 no portable target ✅ −4
Interop subtotal −4
Governance Y11 single-vendor governance ✅ −8
Y12 closed specification ✅ −8
Governance subtotal −15 (−16)

Readiness Dimensions score: 61/100 — total cost = −39 (per-dimension subtotals are capped at −15; an uncapped cost is shown in parentheses where the cap bites).

Verdict: Transact-SQL’s coupling follows its host engine. Stored procedures, views, and table-valued parameters give real modularity, TRY ... CATCH gives a structured failure path, and the optimiser, Query Store, columnstore, and in-memory OLTP carry performance work. Main strengths: a mature first-party toolchain, a regular release cadence, and standard client interfaces. Main weaknesses: the language only runs on SQL Server or SAP ASE, the useful surface is engine-specific, NULL starts in every declared variable, an unhandled CATCH continues the batch, cursor code paths are slow, and a string-built statement is a standing injection hazard.

The formal development. The definitions, theorems, and proofs behind this page are published in:

All published papers

References

[1]
Y. Loth, “On the nature of coupling: All coupling as shared resource access.” Zenodo, Aug. 2026. doi: 10.5281/zenodo.21925122.
[2]
Y. Loth, “Coupling as a consequence of cohesion: Decomposition, the coupling floor, and domain-imposed coupling.” Zenodo, Aug. 2026. doi: 10.5281/zenodo.21925201.
[3]
Y. Loth, “On the nature of cohesion: Cohesion as correctness relative to a partitioning rule.” Zenodo, Jul. 2026. doi: 10.5281/zenodo.21703193.
[4]
Microsoft, “Transact-SQL reference (SQL server).” Microsoft, 2024. Available: https://learn.microsoft.com/en-us/sql/t-sql/language-reference
[5]
S. Joel, J. J. Wu, and F. H. Fard, “A survey on LLM-based code generation for low-resource and domain-specific programming languages,” ACM Transactions on Software Engineering and Methodology, 2025, doi: 10.1145/3770084.