Interview Questions and Answers Part 3 opens with NOLOCK: how the hint skips read locks, why that means dirty reads, and what it does for concurrency.
SQL SERVER – UDF – User Defined Function to Extract Only Numbers From String
A simple UDF, ExtractInteger, to extract only numbers from string input. I test it with a phone number that also has letters in it.
SQL SERVER – Search Text Field – CHARINDEX vs PATINDEX
CHARINDEX and PATINDEX both return the start position of a pattern in a text field. PATINDEX allows wildcards, CHARINDEX does not. Examples use AdventureWorks.
SQL SERVER – Difference between DISTINCT and GROUP BY – Distinct vs Group By
What is the difference between DISTINCT and GROUP BY? They usually produce the same plan. Use GROUP BY for aggregates and DISTINCT to remove duplicates.
SQL SERVER – @@IDENTITY vs SCOPE_IDENTITY() vs IDENT_CURRENT – Retrieve Last Inserted Identity of Record
@@IDENTITY vs SCOPE_IDENTITY() vs IDENT_CURRENT: how each one returns the last identity value, and why a trigger can make @@IDENTITY give the wrong one.
SQL SERVER – SQL Commandments – Suggestions, Tips, Tricks
My 20 SQL Commandments for SQL Server, rewritten from an Oracle article: know your data, test with realistic data, write identical statements, and more tips.
SQL SERVER – Script to Determine Which Version of SQL Server 2000-2005 is Running
A quick way to see which version of SQL Server 2000 or 2005 is running: SERVERPROPERTY returns the product version, the product level and the edition.