A quick reference of the keywords a view definition must not contain if you want an indexed view in SQL Server, part 10 of my views series.
SQL SERVER – SELF JOIN Not Allowed in Indexed View – Limitation of the View 9
Self Join is handy, but it is not allowed in indexed view definitions. Limitation of the view 9 reuses my employee and manager example to show it.
SQL SERVER – UNION Not Allowed but OR Allowed in Index View – Limitation of the View 6
UNION not allowed in an indexed view can surprise you. Limitation of the view 6 shows the script that fails and how the OR operator is allowed instead.
SQL SERVER – COUNT(*) Not Allowed but COUNT_BIG(*) Allowed – Limitation of the View 5
A view with COUNT(*) cannot get a clustered index, but switch to COUNT_BIG and it works. Limitation of the view 5, shown with a simple tempdb example.
Finding the Nearest Location With a Spatial Index
A nearest location query in SQL Server needs TOP, STDistance in WHERE and ORDER BY, matching SRIDs, and a checked spatial plan.
SQL SERVER – Disabled Index and Index Levels and B-Tree
For T-SQL Tuesday I write about my mentor Vinod Kumar and his interesting question, which leads to a disabled index, its index levels and the B-Tree.
SQL SERVER – Index Levels and Delete Operations – Page Level Observation
When you delete data from a table, does SQL Server reduce its size? A page level look at index levels and delete operations, corrected with Hrvoje’s help.