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 – Get Numeric Value From Alpha Numeric String – Get Numbers Only
Reader Christofer improved my logic to get a numeric value from alpha numeric string data. His stored procedure uses REVERSE and PATINDEX.
SQL SERVER – Outer Join Not Allowed in Indexed Views – Limitation of the View 8
Why is an outer join not allowed in indexed views? Rows can logically disappear when you insert into a base table. Limitation of the view 8 shows an example.
SQL SERVER – Cross Database Queries Not Allowed in Indexed View – Limitation of the View 7
An indexed view needs WITH SCHEMABINDING, and that rules out cross database queries. Limitation of the view 7 walks through it with two test databases.
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.
SQL SERVER – How to Stop Growing Log File Too Big
A client kept truncating the transaction log because of a growing log file. I explain why that is risky and share two better options, starting with Simple recovery.