If you declare a variable-length string , or coerce a string, without specifying its length, you can fall foul of ‘silent’ string truncation. Some developers resort to using the (MAX) specification, which is a mistake too. Phil Factor explains the dangers and then offers a workaround for the problem, when you're importing text and... Read more
Phil Factor suggests a philosophy of "the SQL query optimizer knows best" when it comes to choosing the right execution plan. Use hints as a last resort, and evaluate them carefully whenever SQL Prompt warns you of their presence in your SQL code. Read more
If SQL Prompt alerts you to a table without a clustered index, investigate the reason for its absence carefully. It is rare indeed to find a table where data retrieval is faster without one. Read more
Data in relational tables is never guaranteed to be returned in a particular order, so adding an ORDER BY to an INSERT INTO statement is meaningless and, pre-SQL Server 2012, could cause performance issues. If you really need to impose a particular order on rows in the table, use a Row_Number() window clause instead. Read more
If you declare a column alias using equals SQL Prompt will raise a violation of a style rule (ST002). It's best to follow the ANSI-SQL92 standard of assigning aliases using the AS keyword. If the alias is not a standard identifier, delimit it with double quotes. using single quotes has long been deprecated (DEP021). Read more
If you want to use TOP with an expression or subquery, or in INSERT, UPDATE, MERGE, and DELETE statements, then use of brackets is required, so it's a good habit to adopt everywhere. Read more
Phil Factor demonstrates the use of temporary tables and table variables, and offers a few simple rules to decide if a table variable will give better performance than a temp table (ST011), or vice-versa (ST012). Read more
