Everyone writes about AI generating queries. Here are nine unusual ways my clients use AI with SQL Server, and I helped build every one.
EXISTS vs IN vs JOIN: Writing Semi-Joins
Compare EXISTS, IN, and JOIN for semi-joins, prevent duplicate customer rows, and handle NULL safely in exclusion queries.
SQL SERVER – Unique Indexes on Nullable Columns
As the server allows only one null value in a column if a unique constraint is applied. Let us learn about Unique Indexes on Nullable Columns.
SQL SERVER – Difference Between ISNULL and COALESCE
SQL Server provides two commonly used functions, ISNULL and COALESCE, to address this requirement. Difference Between ISNULL and COALESCE
IGNORE NULLS in SQL Server 2022: Carrying the Last Known Value Forward
Use IGNORE NULLS for forward-filled readings, find the next known value, and compare the older running-count grouping method carefully.
A Unique Constraint That Allows Many NULLs
Allow many NULLs while enforcing unique non-NULL values with a filtered SQL Server index, required SET options, and tested queries.
Convert Empty String to NULL Date in SQL Server: Use NULLIF
To convert empty string to NULL in a date column, wrap the text in NULLIF before you cast it. A plain CAST returns 1900-01-01 and hides the problem. This post tests five expressions on six text values, including a real 1900 date, a run of spaces and a tab, and shows which ones stay correct.