Today's question (by Steve Jones - SSC Editor):

The Tightly Linked View

CREATE OR ALTER VIEW OrderShipping
AS
SELECT cl.CityNameID,
       cl.CityName,
       o.OrderID,
       o.Customer,
       o.OrderDate,
       o.CustomerID,
       o.cityId
 FROM dbo.CityList AS cl
 INNER JOIN dbo.[Order] AS o ON o.cityId = cl.CityNameID
GO
CREATE OR ALTER FUNCTION GetShipCityForOrder
(
    @OrderID INT
)
RETURNS VARCHAR(50)
WITH SCHEMABINDING
AS
BEGIN
    DECLARE @city VARCHAR(50);
    SELECT @city = os.CityName
    FROM dbo.OrderShipping AS os
    WHERE os.OrderID = @OrderID;
    RETURN @city;
END;
go

Think you know the answer? Click here, and find out if you are right.

SQL Server, Heaps and Fragmentation

A table without a clustered index (heap) will NOT suffer from fragmentation during frequent updates or deletes.

True or False?

Answer: False

Explanation: The answer is FALSE. In SQL Server,  heaps (non-clustered indexes) can become heavily fragmented with frequent updates/deletes, especially when forwarding pointers are introduced.  With modern storage technology advances, this is often remediated, but it's important when performance impacts are reported to identify if full scans of tables are occurring, identify the type of workload, (OLTP especially) and if a missing clustered index might not be needed. Ref:

• Optimize index maintenance to improve query performance and reduce resource consumption - https://learn.microsoft.com/en-us/sql/relational-databases/indexes/reorganize-and-rebuild-indexes?view=sql-server-ver17 ("for both rowstore and columnstore indexes, review index or heap fragmentation and page density after a large number of rows is deleted or updated. For heaps, if there are frequent updates, review fragmentation periodically to avoid proliferation of forwarding records.")
• Heaps - https://learn.microsoft.com/en-us/sql/relational-databases/indexes/heaps-tables-without-clustered-indexes?view=sql-server-ver17#heap-structures  ("Do not use a heap if the data is frequently updated. If you update a record and the update uses more space in the data pages than they are currently using, the record has to be moved to a data page that has enough free space. This creates a forwarded record pointing to the new location of the data, and forwarding pointer has to be written in the page that held that data previously, to indicate the new physical location. This introduces fragmentation in the heap.")

Discuss this question and answer on the forums

Here's a few of the new posts today on the forums. To see more, visit the forums.