SQL Provision

Building Better Test Data with SQL Provision

Development teams make software available for release once they are confident that it behaves consistently, as it was designed to behave, under as many different user workflows as they can test. Unfortunately, their test cells often don’t reflect the harsher reality of the live environments, where their software will encounter large volumes of real data, Read more

SQL Change Automation

SQL Change Automation with PowerShell Scripts: getting up-and-running

In this article, I’ll demonstrate how to use SQL Change Automation, a PowerShell script, to take the contents of a source control directory for a database, check that it is possible to build from it, document it and then create a NuGet package and place the code in it, along with the documentation. Finally, I’ll Read more

SQL Prompt

Improving legacy code using SQL Prompt code analysis

Code analysis is a useful technique for identifying potential issues in your code base, and ensuring that it adheres to your pre-defined standards, so that you can release your software product with more confidence. SQL Prompt 9.2 and later also allows you to view a whole list of code issues detected in a SQL script, Read more

SQL Change Automation

SQL Code Analysis from a PowerShell Deployment Script

Database code analysis becomes more important as the team doing the database development gets bigger and more diverse in skills. Hard-working database developers sometimes check-in ‘temporary’ development code, by mistake, so it is always good to have a way of flagging up SQL Code issues and ‘smells’ that are agreed to be incompatible with ‘production’ Read more

SQL Change Automation

Moving from application automation to true DevOps by including the database

There is a growing motivation, in many organizations, to integrate database changes into a DevOps process. The recent State of Database DevOps Report revealed that within two years, 82% of companies will adopt some aspects DevOps, and that 76% of companies already have developers in their team who work across both applications and databases. The Read more

SQL Change Automation

What is SQL Change Automation?

It is easy to deliver a small SQL Server test database without extra software tools, but once you edge into the real world you will soon need a consistent and repeatable way of automating as much as possible of the build, test, and deployment processes. SQL Change Automation (SCA) is a software framework that uses Read more

SQL Monitor

Troubleshooting Blocking in SQL Server using SQL Monitor

A call comes in to the DBA from Help Desk. There is an urgent problem with a reporting application; unhappy users say that their screens are “frozen”, and not because of an overactive A/C vent. Some have reported seeing a timeout issue related to SQL. Is it a blocking problem? As a DBA, if blocking Read more

SQL Prompt

SQL Prompt Hidden Gems: The SSMS Results Pane

Within SSMS, when executing a query, we can view the query results in a grid, or tabular, format (Ctrl-D), as plain text (Ctrl-T), or we can write them to a plain-text reporting (.rpt) file using Ctrl-Shift-F. However, over my 20 plus years as a SQL programmer, I’ve often needed to take these results and use Read more

SQL Prompt

Finding code smells using SQL Prompt: Asterisk in SELECT list (BP005)

Using SELECT * FROM within IF EXISTS statements are fine, but in other contexts it causes several problems. For example, INSERT INTO…SELECT * FROM statement assumes the columns are in a particular order, and that order may not last. Also, selecting all the columns in a table when you don’t need them all can cause Read more

SQL Prompt

SQL Prompt Safety Net Features for Developers

Mistakes happen, occasionally .You accidentally close an SSMS query tab without saving it, before realizing it contained an essential bit of code. You’re working late, switching between test and development servers, and accidentally execute code against the wrong server. SSMS conspires against you and crashes unexpectedly, and you lose all your currently open query tabs, Read more