In SQL, if you declare a string in any of the four formats, CHAR, NCHAR, VARCHAR or NVARCHAR, without specifying its length, the string is given the length of one character. If you coerce a string, using CAST or CONVERT, and make the same mistake, it gets a length of thirty characters. Why one and Read more
Using dynamic SQL is unavoidable at times, but it is reckless to execute dynamic SQL that is created directly from strings that include values that change at execution time. It can allow SQL Injection and it is also inefficient. SQL Prompt’s code analysis rule, BP013 will alert you to use of Execute(<string>), to execute a Read more
I use SQL Prompt to format my code; I’ve defined my standard style that lays out the code just the way I like it, for most cases. I also have a couple of alternative styles for specific tasks, such as comparing two versions of the same code. Occasionally, however, the standard formatting just won’t work Read more
Having untrusted or disabled FOREIGN KEY or CHECK constraints isn’t nearly as bad a fault as not having defined any in the first place. However, it does cause issues with data consistency and integrity, since you can’t be certain that ever row of data complies with the conditions of the constraint. Moreover, neither can the Read more
“Because the SQL Server query optimizer typically selects the best execution plan, we recommend that hints be used only as a last resort by experienced developers and database administrators” — Microsoft SQL Server Documentation Really, there should be no discussion about this, because the above warning, in the documentation, summarizes it so well. However, I Read more
With a few exceptions, every table should have a clustered index. However, they are not always essential for performance. The value of a clustered index depends on the way a table is used, the typical pattern of queries, and how it the table is updated. More important for a table is that it should have Read more
Here is some SQL code to reveal the highest-performing salespeople at AdventureWorks and listing their managers. It produces the following result… So, the code works, but it is ugly. If I need to understand and improve the code, I’ll first need to get it into a readable form. I have SQL Prompt, so I can Read more
This article is all about using a SQL Prompt snippet to create and run a test harness on a batch of SQL code, to provide performance data. Behind the scenes, the snippet creates and starts an Extended Events event session, inserts and runs the text of the batch of SQL you’ve selected in SSMS, and Read more
Avoid using the IsNumeric() function, because it can often lead to data type conversion errors, when importing data. SQL Prompt Code Analysis rules include an Execution rule, E1029, which will alert you to use of this function, in your T-SQL. If you’re working on SQL Server 2012 or later, it’s much better to use the Try_Convert()or Read more
The SQL query that is used to produce the result that is inserted into the permanent table has its order specified by an ORDER BY statement. Relational tables are not ordered, so the ORDER BY is meaningless. Use a Row_Number() window clause instead, if you need to impose a particular order on rows in the Read more
Compliant Database DevOps
