It would be wrong to portray SQL Change Automation (SCA) as being suitable only for epic project deployments, of the sort described in my previous article. It can do smaller tasks as well. To demonstrate, I’ll show how you can use SCA to check that the source code builds a database, and to provide a 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
Data protection and privacy regulations, ranging from GDPR to HIPPAA to PCI, among many others, put strict compliance requirements on the storage and use of personal and sensitive data, in any of your systems. There is no distinction between development, test or production databases, in the event of a data breach. If such data is Read more
Sometimes developer teams need access to a copy of the database containing live data. However, if that database contains sensitive or personal data, then it cannot be used for testing and development work, unless all appropriate security measures are in place. The data protection regulations make no distinction between development and production databases, in the Read more
This article uses the PowerShell cmdlets of SQL Change Automation (SCA), formerly DLM-Automation, to take the source code of a database from a directory, validate it, document it and then create a NuGet package of it. Up to this point, the script uses a very similar approach to that described in SQL Change Automation with Read more
There was a time when column aliases in SQL Server and Sybase were only declared by an assignment expression, and some people are still fond of the old way of doing it, in much the same way as some of us still like line-dancing, or singing madrigals dressed in tights. Nowadays, the other relational database Read more
The right to be forgotten is one of the main features of new data protection legislation across the globe. Under Article 17 of one such piece of legislation in Europe, the GDPR, individuals have the right to have personal data erased from all systems and data the company may be storing about them. Similarly, in Read more
Most organizations are finding that the size and number of databases that need to be monitored is increasing, unlike the number of people available to do the work. Now that it is possible for the organization to scale out to cloud-hosted databases, the constraints of the available infrastructure have ceased to govern the growth of Read more
The syntax of the TOP clause insists on a bracketed, or parenthesized, expression as its first argument, so that TOP 34 should, strictly speaking be TOP (34), although if you are just supplying a numeric constant, such as 34, as an argument then either style will work. Early incarnations of TOP could use only a Read more
Developers, when working on databases rather than the application code, often find they have less freedom to experiment than they are used to. It’s not necessarily easy to set up a database for testing, especially if the process isn’t automated. They’ll need to dig around in source control, build the database at the correct version, Read more
