Neglecting to Use, or Misusing, the RETURN Keyword (BP016)

Phil Factor demonstrates the correct use of the RETURN keyword in stored procedures or batches, to pass back a non-zero RETURN code to the calling process, informing it of the error. He also explains some of its misuses. Read more

Problems with adding NOT NULL columns or making nullable columns NOT NULL (EI028)

Phil Factor explains the problems you might encounter when adding a non-nullable column to an existing table or altering a column that contains NULL values to be non-nullable. He demos a migration script that can deploy such changes safely. You might also learn that in an archaic form of the Scots language, used in Cumberland, the number 17 is "tiny bumfit"; I think the tiny bumfit bus goes from Penrith to Carlisle. Read more

Why you Should Avoid Using the @@IDENTITY Function

Phil Factor demonstrates why SQL Prompt has a 'Best Practice' rule (BP010) that checks for use of the @@IDENTITY function, and suggests less error-prone ways to get the latest identity values used in a table. Read more

Problems Caused by Use of the SQL_VARIANT Datatype

Phil Factor illustrates the 'quirks' of the SQL_VARIANT datatype and why it's best to investigate when SQL Prompt alerts you to its use. It is only safe to store data as a SQL_VARIANT, if you explicitly convert it to its true type before you use it. Read more

The ‘= NULL’ Mistake and other SQL NULL Heresies

The SQL Prompt Best Practice rule checks whether a comparison or expression includes a NULL literal ('NULL'), which in SQL Server, rather than result in an error, will simply always produce a NULL result. Phil Factor explains how to avoid this, and other SQL NULL-related calamities. Read more

What’s new in SQL Prompt 9.5: SSMS 18 and VS 2019 support, improved suggestions and more

Faris Hilmi presents the highlights of the latest SQL Prompt 9.5 release, including SSMS 18 and VS 2019 support, schema filtering for code completion suggestions, new code analysis auto-fixes and more. Read more

Refactoring Databases with SQL Prompt

Louis Davidson demonstrates how SQL Prompt can significantly lessen the pain involved in occasional, 'heavyweight' database refactoring processes, such as renaming modules, tables and columns (Smart Rename), or splitting tables (Split Table). Read more

Insert Statement Without Column List (BP004)

Many production databases have failed embarrassingly as a result of INSERT code that omits a column list, usually in mysterious ways and often without generating errors. Phil Factor demonstrates the problem, and advocates a 'defense-in-depth' approach to writing SQL, in order to avoid it. Read more

The Sins of SELECT * (BP005)

If Prompt warns you of use of the asterisk, or 'star' (*), in SELECT statements, consider replacing it with an explicit column list. It will prevent unnecessary network load and query performance problems, and avoid problems if the column order changes, when inserting into a table. Read more

Refactoring a Batch or Object with SQL Prompt

Louis Davidson reveals some useful Prompt features for refactoring individual code blocks or modules during development, which will improve code quality, reduce tedium, make testing simpler, or sometimes all three. Read more

OVERVIEW

BY NEED

BY ROLE

BY INDUSTRY

Product articles SQL Prompt