SQL Prompt

Avoid use of the MONEY and SMALLMONEY datatypes

The MONEY data type confuses the storage of data values with their display, though its name clearly suggests the sort of data it holds. It is proprietary to SQL Server and allows you to specify monetary values preceded by a currency symbol, but SQL Server doesn’t store any currency information at all with the actual Read more

SQL Source Control

A strategy for implementing database source control

Much has been written on the benefits of having a database under source control though many articles are clear on “why” but conspicuously vague on “how”. Prior to our organization’s decision to embrace Linux and other open-source technologies, one of our development teams had notable success using Redgate SQL Source Control for a data mart Read more

SQL Monitor

Custom Metrics for Detecting Problems with Ad-hoc Queries

Whatever development methodology you use, you must check on the quality of the code before releasing a version of a database. This isn’t just a general check for ‘technical debt’ or code smells; you must also make sure that queries are not hogging resources on the server. A common crime is the unnecessary overuse of Read more

SQL Toolbelt

Effective Database Testing with SQL Test and SQL Cover

A well-established technique for improving application code quality, during software development, is to run unit tests, in conjunction with a code coverage tool. The aim is not only to test that your software components behave as you would expect, but also that your suite of tests gives your code a thorough workout. Errors encountered within Read more

Data Masker

Approaches to masking email addresses

A recent Data Governance Survey conducted by Redgate of over 500 SQL Server professionals showed that 61% of respondents were using Production data for non-production workflows; a process that is often seen as necessary for the reliability of development, testing and other similar workflows. However, with data privacy legislation such as the GDPR, POPI and Read more

SQL Monitor

Using SQL Monitor to Detect Problems on Databases that use Snapshot-based Transaction Isolation

If you’re using SQL Server’s Read Committed Snapshot Isolation level (RCSI), to avoid long waits for a blocked resource, caused by transactions being held open for too long, then you’ll want to monitor for possible side effects. Usually, the overhead of using RCSI is not significant compared to the performance benefits of alleviating blocking, but Read more

SQL Prompt

When to use the SELECT…INTO statement

We can use SELECT…INTO in SQL Server to create a new table from a table source. SQL Server uses the attributes of the expressions in the SELECT list to define the structure of the new table. Prior to SQL Server 2005, use of SELECT…INTO in production code was a performance ‘code smell’ because it acquired Read more

SQL Prompt

Beyond Formatting: Improving SQL Code using SQL Prompt Actions

In this article, I’ll discuss how I use the SQL Prompt actions that you can apply as part of the Format SQL command. These actions are designed to help improve the overall quality of your SQL code, in various subtle but meaningful ways, such as qualifying object names, standardizing the use of aliases, adding semicolons Read more

SQL Monitor

Monitoring and Troubleshooting Deadlocks with SQL Monitor

Deadlocks can occur when two or more sessions inside of the database engine are waiting for access to locked resources held by each other. Technically, a deadlock can be viewed as a circular locking chain, because every process (SPID) in the blocking chain will be waiting for one or more other processes in that same Read more

SQL Prompt

Driving up database coding standards using SQL Prompt

Most of us in the data management industry will have learned to adapt, in recent years, to ‘agile’ development and deployment practices. Many organizations have invested heavily in the tools and processes they hope will allow them to deliver new functionality to users more frequently and reliably, while also maintaining quality standards. To achieve this, Read more