The relational engine receives requests from SQL Server’s protocol layer and turns SQL into a normalized query tree. It parses syntax, resolves object names to object IDs through algebraization, and passes the result to the Query Optimizer. section 1.2.1
Mistake: treating the relational engine as a simple pass-through. SQL is descriptive, so the engine must determine how to produce the requested results; the optimizer uses object types, data types, statistics, and other metadata to compare plans. Better code still matters, because optimization cannot repair every T-SQL design problem. section 1.2.1 section 1.5
A product-stock query
A request for one product can return “In Stock,” “Out of stock,” or an exact remaining quantity through a CASE expression. The request travels through the application protocol and protocol layer before reaching the relational engine. The example also warns that adding NOLOCK is a mistake rather than a harmless performance shortcut. section 1.2.1
SELECT
ProductName
, ProductDescription AS Description
, CASE
WHEN StockQty >= 10
THEN N'In Stock'
WHEN StockQty > 0 AND StockQty < 10
THEN CAST(StockQty AS NVARCHAR) + ' left in stock'
ELSE 'Out of stock'
END
, ProductImage
FROM dbo.Products WITH (NOLOCK)
WHERE ProductID = @ProductID
Inspect the product-stock SELECT statement, including its CASE expression, parameterized filter, and NOLOCK hint. section 1.2.1
