A quick tip to remove bookmark lookup, RID lookup or key lookup: why it happens when a query needs columns missing from the non-clustered index.
SQL SERVER – Interesting Observation – Query Hint – FORCE ORDER
A junior developer used the FORCE ORDER query hint without knowing what it does. Here is how it works and how much this one hint can change performance.
geography Point: Latitude Comes Before Longitude
geography Point uses latitude, longitude and SRID arguments. Its coordinate order differs from POINT text, so inspect both returned properties.
ALLOW_PAGE_LOCKS OFF Stops REORGANIZE
ALLOW_PAGE_LOCKS OFF prevents a rowstore index from being reorganized. A controlled example shows the maintenance error, checks the index setting and verifies that enabling page locks allows REORGANIZE without changing the stored rows.
SQL SERVER – Interesting Observation – Index on Index View Used in Similar Query
A query on the base table used an index it did not own. It was the index on index view of the same table, an interesting observation from an optimization project.
SQL SERVER – Insert Values of Stored Procedure in Table – Use Table Valued Function
How do you insert values of stored procedure output into a table? The traditional temp table way, and an alternate method with a Table Valued Function.
Integer Division: Keep Quotient and Remainder Together
Integer division and modulo can reconstruct the original signed dividend. These read-only cases keep both operands, quotient, remainder and reconstructed value together.