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
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
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
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
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
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
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
You’ve been working on a query, function or procedure and you want to store the results of executing it in a table; maybe a table variable, or a temporary table. These all require column definitions, and such things can be tiresome to do. Consider the following AdventureWorks query. Listing 1 You need to store the Read more
One way to offer a better experience to our application users is to anticipate likely mistakes they could make, such as when filling in a web form, and provide them with a meaningful error message that allows them to continue quickly with their work. One way to do this in SQL Server to is to Read more
Using TOP in a SELECT statement without a subsequent ORDER BY clause is legal in SQL Server, but meaningless because asking for the TOP 10 rows implies that the data is guaranteed to be in a certain order, and tables have no implicit logical order. You must specify the order. In a SELECT statement, you Read more
Compliant Database DevOps
