CHARINDEX and PATINDEX both return the start position of a pattern in a text field. PATINDEX allows wildcards, CHARINDEX does not. Examples use AdventureWorks.
CHOOSE: Handle Indexes Outside the List
CHOOSE returns an item from a positional list. Check unsupported indexes and compare a consistently typed CASE expression with an explicit fallback.
SQL SERVER – Fix: HResult 0x274D, SQLCMD Level 16, State 1 Error: Microsoft SQL Native Client : Login timeout expired
SQLCMD error HResult 0x274D, Microsoft SQL Native Client: Login timeout expired? TCP/IP is off by default in SQL Server 2005; enable it in Configuration Manager.
SQL SERVER – TempDB is Full. Move TempDB from one drive to another drive.
When TempDB is Full, I investigate capacity and usage before moving its files. A move requires a planned restart.
SQL SERVER – Index Seek Vs. Index Scan (Table Scan)
Index Scan retrieves all the rows from the table. Index Seek retrieves selective rows from the table.
SQL SERVER – Fix : Error 8101 An explicit value for the identity column in table can only be specified when a column list is used and IDENTITY_INSERT is ON
Error 8101 means you gave a value for an identity column. What IDENTITY_INSERT does, why there are two different errors, and why TRY CATCH misses it.
SQL SERVER – Stored Procedure – Clean Cache and Clean Buffer
Clean Cache commands affect different parts of SQL Server memory. I separate buffer-pool testing from execution-plan testing.