{"id":7555,"date":"2026-09-03T13:06:28","date_gmt":"2026-09-03T20:06:28","guid":{"rendered":"https:\/\/devblogs.microsoft.com\/azure-sql\/?p=7555"},"modified":"2026-09-03T13:06:28","modified_gmt":"2026-09-03T20:06:28","slug":"sql-decomposition","status":"publish","type":"post","link":"https:\/\/devblogs.microsoft.com\/azure-sql\/sql-decomposition\/","title":{"rendered":"SQL Decomposition in a Nutshell"},"content":{"rendered":"

Application developers<\/em> already know what happens when one method does everything: it becomes difficult to read, test, reason over, and safely change. We use patterns like decomposition, encapsulation, and explicit dependencies because they solve those problems.<\/p>\n

T-SQL does not give us classes, inheritance, interfaces, or polymorphism in the same way C# does, but that does not mean good software practices stop applying when logic moves into the database.<\/p>\n

Decomposition is a good example.<\/strong> Breaking complex database logic into sensible, well-defined components can reduce complexity, improve readability and maintainability, and make individual pieces easier to test. These are established, respected, and proven techniques for building great software, whether the code runs in an application or inside the database.<\/p>\n

For example: Hybrid search<\/h2>\n

<\/i>In case you don't know<\/strong><\/p>Hybrid search combines multiple retrieval techniques, usually full-text search and vector search, to improve the quality of search results. Rather than trusting a single ranking strategy, it retrieves candidates in different ways and then combines those results into a final ranking.<\/div><\/p>\n

Learn more about Hybrid Search<\/a><\/div><\/p>\n

A complete hybrid search solution may involve query rewriting, embedding generation, full-text search, vector search, fusion, reranking, and response generation. Each step contributes to the overall result, but each is also naturally separable. Full-text search should be able to run without vector search. Vector search should be testable without fusion. Fusion should operate on results without needing to understand how those results were produced.<\/p>\n

\"Hybrid<\/p>\n

Introducing separate components adds a little structural sophistication<\/strong>, but it can reduce the complexity of the system as a whole. That sounds contradictory, but it is not. We can measure the benefit through smaller units of code, clearer responsibilities, simpler tests, easier diagnostics, and safer changes. Decomposition adds boundaries so each part becomes easier to reason over.<\/p>\n

What decomposition solves<\/h2>\n

Complex database logic becomes difficult to reason over when too many responsibilities accumulate in one place. A single stored procedure may begin as a straightforward query and slowly grow to validate inputs, search, rank, transform results, handle errors, and orchestrate other operations. At some point, the procedure simply understands too much.<\/p>\n

<\/i>Decomposition introduces boundaries.<\/strong><\/p>Instead of asking one procedure to understand the entire architecture, divide the work into smaller units with narrower responsibilities. A procedure such as ProductSearch_FullText<\/code> can focus only on full-text retrieval while another handles vector search and another handles fusion.<\/div><\/p>\n

The result is not less capability.<\/strong> It is less complexity per component. Smaller units are easier to read, easier to test, easier to change, and easier to reason over. Good decomposition simplifies the design without simplifying what the system can do.<\/p>\n

EXEC dbo.ProductSearch_FullText\r\n    @Query = @Query,\r\n    @TopN = @TopN;\r\n\r\nEXEC dbo.ProductSearch_Vector\r\n    @QueryVector = @QueryVector,\r\n    @TopN = @TopN;<\/code><\/pre>\n
These components may eventually participate in the same hybrid search operation, but neither needs to understand how the other works.<\/p>\n
What encapsulation solves<\/h2>\n
Decomposition creates boundaries; encapsulation makes those boundaries useful.\u00a0If you are coming from application development, think about calling a method. You care about its inputs, its output, and its expected behavior. You should not need to understand every line inside it.<\/p>\n
The same principle applies here.<\/strong> The caller of ProductSearch_FullText<\/code> should not need to understand every FREETEXTTABLE<\/code> operation, validation rule, ranking calculation, or internal implementation detail. It should understand the contract.<\/p>\n
CREATE PROC dbo.ProductSearch_FullText\r\n    @Query nvarchar(4000),\r\n    @TopN int = 20\r\nAS\r\nBEGIN\r\n    SELECT TOP (@TopN)\r\n        ft.[KEY] AS ProductId,\r\n        CAST(ROW_NUMBER() OVER (\r\n            ORDER BY ft.[RANK] DESC\r\n        ) AS int) AS Rank\r\n    FROM FREETEXTTABLE(dbo.Product, *, @Query) AS ft\r\n    ORDER BY ft.[RANK] DESC;\r\nEND;<\/code><\/pre>\n
A query goes in. Ranked products come out. How those products were found stays behind the boundary.\u00a0That separation improves readability and maintainability<\/strong> because changes inside the boundary do not necessarily require changes outside it. We can improve the full-text implementation, add validation, or change its internal query without forcing the orchestrating procedure to change.<\/p>\n
What statelessness solves<\/h2>\n
Statelessness makes those components easier to isolate.\u00a0Here, stateless does not mean ignoring database state. Reading data is the entire point. It means avoiding hidden execution state: global temporary tables, session context, values established by a previous call, or assumptions about what another component already did.<\/p>\n
Instead, relevant state crosses the boundary explicitly as parameters.<\/p>\n
CREATE PROC dbo.ProductSearch_Vector\r\n    @QueryVector vector(1536),\r\n    @TopN int = 20\r\nAS\r\nBEGIN\r\n    SELECT TOP (@TopN)\r\n        ProductId,\r\n        CAST(ROW_NUMBER() OVER (\r\n            ORDER BY d.Distance\r\n        ) AS int) AS Rank\r\n    FROM dbo.ProductEmbedding\r\n    CROSS APPLY\r\n    (\r\n        VALUES (\r\n            VECTOR_DISTANCE(\r\n                'cosine',\r\n                Embedding,\r\n                @QueryVector\r\n            )\r\n        )\r\n    ) AS d(Distance)\r\n    ORDER BY d.Distance;\r\nEND;<\/code><\/pre>\n
Everything specific to this search is explicit: the query vector and the number of candidates to return.\u00a0This starts to feel a little like dependency injection<\/strong> in application development. Instead of a component reaching outward to discover everything it needs, its dependencies are supplied across the boundary.\u00a0The benefit is not statelessness for its own sake. It is less hidden context. Dependencies become visible, executions become reproducible, failures become easier to investigate, and components become easier to test and reuse.<\/p>\n
Notice that embedding generation is not hidden inside ProductSearch_Vector. Creating that vector can be another database component or happen in the application. Either way, vector search has a simple starting contract: give it a vector.<\/div><\/p>\n
What does a SQL developer have?<\/h2>\n
SQL developers do not have exactly the same building blocks as application developers, but they are not without architectural tools.\u00a0Stored procedures provide executable boundaries. User-defined table types provide reusable data shapes. Functions provide reusable logic. Schemas provide namespaces and security boundaries. THROW<\/code> lets components define intentional failures.<\/p>\n
Different tools, same design principles.<\/p>\n
User-defined table types<\/h3>\n
As our hybrid search pipeline becomes decomposed, each component needs a predictable way to exchange data with the next. A user-defined table type gives that data a named, reusable shape.\u00a0If you are coming from C#, think of it roughly like a small DTO<\/strong> for tabular data.<\/p>\n
CREATE TYPE dbo.ProductSearchResult AS TABLE\r\n(\r\n    ProductId int NOT NULL,\r\n    Rank      int NOT NULL\r\n);<\/code><\/pre>\n
Now different parts of the pipeline can work with the same understood shape:<\/p>\n
DECLARE @FullText dbo.ProductSearchResult;\r\nDECLARE @Vector   dbo.ProductSearchResult;\r\n\r\nINSERT INTO @FullText\r\nEXEC dbo.ProductSearch_FullText @Query, @TopN;\r\n\r\nINSERT INTO @Vector\r\nEXEC dbo.ProductSearch_Vector @QueryVector, @TopN;<\/code><\/pre>\n
Full-text and vector search have completely different implementations, but the next stage can reason over their results in exactly the same way.<\/p>\n
There is another subtle benefit here. FREETEXTTABLE<\/code> produces its own ranking score, while vector search produces a distance. Those values mean different things and cannot sensibly be compared directly. Reciprocal Rank Fusion needs position, not the raw score, so both retrievers expose a simple 1..N<\/code> rank instead.<\/p>\n
<\/i>One important distinction<\/strong><\/p>A user-defined table type does not formally type a stored procedure’s result set. The procedures still need to return compatible columns. The type gives table variables and table-valued parameters a real, reusable contract inside the architecture.<\/div><\/p>\n
Table-valued parameters are also READONLY<\/code>, and user-defined table types are best treated as relatively stable contracts because changing the type later is more involved than altering a table.<\/p>\n
User-defined functions<\/h3>\n
As the pipeline is decomposed, some logic will naturally appear in more than one component. A user-defined function lets us give that logic a name and reuse it instead of copying the same expression throughout the solution.\u00a0Reciprocal Rank Fusion, for example, repeatedly calculates a score from a rank:<\/p>\n
CREATE FUNCTION dbo.ProductSearch_RrfScore ( @Rank int )\r\nRETURNS TABLE AS\r\nRETURN\r\n(\r\n    SELECT 1.0 \/ (60 + @Rank) AS Score\r\n);<\/code><\/pre>\n
Now any component can reuse that calculation:<\/p>\n
SELECT\r\n    r.ProductId,\r\n    s.Score\r\nFROM @FullText AS r\r\nCROSS APPLY dbo.ProductSearch_RrfScore(r.Rank) AS s;<\/code><\/pre>\n
Instead of duplicating the calculation, we define it once behind a meaningful name and boundary.<\/p>\n
The goal is not to move every expression into a function. Use a function when the logic has meaning, reuse, or enough complexity to deserve its own boundary. Inline table-valued functions are particularly useful for set-based logic because SQL Server can incorporate them into the surrounding query plan.<\/p>\n
Schemas<\/h3>\n
Application developers use namespaces to organize related code. SQL Server schemas can serve a similar organizational purpose, but they also provide a security boundary.\u00a0For example, a search architecture might eventually expose objects under a search<\/code> schema. Permissions can then be granted to those capabilities without granting callers direct access to every underlying table.\u00a0That is another form of encapsulation: expose what callers need while keeping implementation details behind the boundary.<\/p>\n
Errors are part of the contract<\/h3>\n
A component’s contract includes failure too.<\/p>\n
IF @TopN < 1\r\n    THROW 51001, 'TopN must be greater than zero.', 1;<\/code><\/pre>\n
Custom error numbers and messages let a component fail intentionally instead of leaking an obscure error from somewhere deep inside its implementation.\u00a0For an application developer, the idea should feel familiar: callers should know not only what success looks like, but which failures they are expected to handle.<\/p>\n
Putting the pieces together<\/h2>\n
So far, each component has been independently useful. Now we can compose them.<\/p>\n
Fusion receives the two result sets without knowing how either was generated:<\/p>\n
CREATE PROC dbo.ProductSearch_Fuse\r\n    @FullText dbo.ProductSearchResult READONLY,\r\n    @Vector   dbo.ProductSearchResult READONLY,\r\n    @TopN     int = 20\r\nAS\r\nBEGIN\r\n    WITH Scores AS\r\n    (\r\n        SELECT r.ProductId, s.Score\r\n        FROM @FullText AS r\r\n        CROSS APPLY dbo.ProductSearch_RrfScore(r.Rank) AS s\r\n\r\n        UNION ALL\r\n\r\n        SELECT r.ProductId, s.Score\r\n        FROM @Vector AS r\r\n        CROSS APPLY dbo.ProductSearch_RrfScore(r.Rank) AS s\r\n    ),\r\n    Fused AS\r\n    (\r\n        SELECT\r\n            ProductId,\r\n            SUM(Score) AS FusionScore\r\n        FROM Scores\r\n        GROUP BY ProductId\r\n    )\r\n    SELECT TOP (@TopN)\r\n        ProductId,\r\n        FusionScore\r\n    FROM Fused\r\n    ORDER BY FusionScore DESC;\r\nEND;<\/code><\/pre>\n
UNION ALL<\/code> is intentional.<\/strong> If a product appears in only one retriever, it still participates in fusion. If it appears in both, its two reciprocal-rank contributions are added together.\u00a0Fusion is the terminal stage in this small example, so it returns FusionScore<\/code> rather than the intermediate ProductSearchResult<\/code> shape.\u00a0Then a thin orchestration procedure describes the workflow:<\/p>\n
CREATE PROC dbo.ProductSearch\r\n    @Query nvarchar(4000),\r\n    @QueryVector vector(1536),\r\n    @TopN int = 20\r\nAS\r\nBEGIN\r\n    DECLARE @FullText dbo.ProductSearchResult;\r\n    DECLARE @Vector   dbo.ProductSearchResult;\r\n\r\n    INSERT INTO @FullText\r\n    EXEC dbo.ProductSearch_FullText @Query, @TopN;\r\n\r\n    INSERT INTO @Vector\r\n    EXEC dbo.ProductSearch_Vector @QueryVector, @TopN;\r\n\r\n    EXEC dbo.ProductSearch_Fuse\r\n        @FullText = @FullText,\r\n        @Vector   = @Vector,\r\n        @TopN     = @TopN;\r\nEND;<\/code><\/pre>\n
This is where the value of decomposition becomes visible.\u00a0<\/strong>The orchestrator orchestrates. Full-text search handles full-text search. Vector search handles vector search. Fusion handles fusion.\u00a0Each component can evolve without requiring every other component to understand how it changed.<\/p>\n
SQL still has SQL-specific costs<\/h2>\n
This is where application patterns and database development part ways a little.<\/strong> A stored procedure call is not simply a C# method call, and database boundaries have engine-level costs and limitations.<\/p>\n
INSERT...EXEC<\/code>, for example, cannot be nested. If ProductSearch_FullText<\/code> itself used INSERT...EXEC<\/code>, the orchestrator above could not capture its result the same way. One alternative for more composable pipelines is to implement suitable leaf operations as inline table-valued functions instead of procedures.<\/p>\n
Table variables and table-valued parameters also do not provide the same column statistics as temporary tables. For the small candidate sets common in search fusion, that may be perfectly reasonable. For much larger intermediate sets, a #temp table may produce better execution plans.<\/div><\/p>\n
The vector example uses VECTOR_DISTANCE<\/code> because exact distance makes the example easy to understand. On larger datasets, VECTOR_SEARCH<\/code> with a vector index may be the better production implementation when approximate search is acceptable.<\/p>\n
And that is precisely why the boundary matters.<\/strong> We can change the implementation of vector retrieval without redesigning fusion or the rest of the pipeline.\u00a0These are not arguments against decomposition. They are reminders that good software design still has to respect the database engine.<\/p>\n
Testing becomes simpler<\/h2>\n
The payoff becomes obvious when something goes wrong.<\/p>\n
We can execute full-text search by itself:<\/p>\n
EXEC dbo.ProductSearch_FullText\r\n    @Query = N'running shoes',\r\n    @TopN = 10;<\/code><\/pre>\n
No embedding generation. No vector search. No fusion. No final response generation.\u00a0We can test each capability independently, which also makes debugging easier. When a hybrid result looks wrong, inspect the full-text results, inspect the vector results, test fusion independently, and find the boundary where behavior stopped matching expectations.\u00a0That is much easier than reasoning over one giant stored procedure.<\/p>\n
Don’t decompose everything<\/h2>\n
Decomposition has a cost. More components mean more objects, more contracts, and more architecture to understand.\u00a0A three-line lookup does not need three stored procedures, a table type, and a function.\u00a0The objective is not more components. The objective is meaningful boundaries.\u00a0Decompose where responsibilities are genuinely independent, where logic deserves reuse, where testing benefits from isolation, or where one component should be free to evolve without forcing changes throughout the system.<\/p>\n
\u2b50 Keep simple things simple.<\/strong><\/p><\/blockquote>\n
The point is simpler code<\/h2>\n
Good database design is not about making SQL look like C#. It is about applying the same proven engineering principles where they make sense.\u00a0Decomposition gives complex logic meaningful boundaries. Encapsulation keeps implementation details behind those boundaries. Statelessness makes dependencies explicit. SQL Server gives us stored procedures, types, functions, schemas<\/em>, and intentional errors<\/em> to put those ideas into practice.<\/p>\n
The architecture may become a little more sophisticated, but each individual component becomes less complex. That is the trade<\/strong>.\u00a0Great software is easier to read, easier to test, easier to maintain, and safer to change, whether it runs in an application or inside the database.<\/p>\n","protected":false},"excerpt":{"rendered":"
Good software design principles apply inside the database too. See how decomposition, encapsulation, statelessness, and clear contracts can make complex T-SQL easier to read, test, reuse, and maintain.<\/p>\n","protected":false},"author":96788,"featured_media":4136,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[1,619],"tags":[449],"class_list":["post-7555","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-azure-sql","category-t-sql","tag-development"],"acf":[],"blog_post_summary":"
Good software design principles apply inside the database too. See how decomposition, encapsulation, statelessness, and clear contracts can make complex T-SQL easier to read, test, reuse, and maintain.<\/p>\n","_links":{"self":[{"href":"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-json\/wp\/v2\/posts\/7555","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-json\/wp\/v2\/users\/96788"}],"replies":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-json\/wp\/v2\/comments?post=7555"}],"version-history":[{"count":1,"href":"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-json\/wp\/v2\/posts\/7555\/revisions"}],"predecessor-version":[{"id":7569,"href":"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-json\/wp\/v2\/posts\/7555\/revisions\/7569"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-json\/wp\/v2\/media\/4136"}],"wp:attachment":[{"href":"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-json\/wp\/v2\/media?parent=7555"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-json\/wp\/v2\/categories?post=7555"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-json\/wp\/v2\/tags?post=7555"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}