SQL Prompt

Choosing Between Table Variables and Temporary Tables

People can, and do, argue a great deal about the relative merits of table variables and temporary tables. Sometimes, as when writing functions, you have no choice; but when you do you’ll find that both have their uses, and it’s easy to find examples where either one is quicker. In this article, I’ll explain the Read more

SQL Monitor

Monitoring Distributed SQL Servers using SQL Monitor

Many organizations are experiencing rapid expansion and diversification of their SQL Server estate to include Cloud, VMWare and other platforms, alongside traditional on-premise servers. This article explains the basic architecture and components of SQL Monitor, and then how to set up a distributed monitoring solution. It will allow a single administrative team to manage and Read more

SQL Monitor

The State of SQL Server Monitoring 2018

Over 600 technology professionals who work in organizations that use SQL Server recently responded to our survey to discover the current state of SQL Server monitoring. We asked people across a range of sectors, in organizations of every size around the globe, about how they monitor SQL Server, the technologies they work with, and what Read more

SQL Data Generator

Pseudonymizing your data with SQL Data Generator

Imagine that you are the CIO of AdventureWorks. Out of a blue sky comes an order from Taxman that you supply details of all your sales, along with the tax charged to your customers. They want to be able to check that the total tax you pass on to them is correct, and is the Read more

SQL Provision

Automatic Provisioning of Developer Databases with SQL Provision

The GDPR, and other regulations, requires that we be careful in how we handle sensitive data. One of the easiest ways to avoid a data breach incident, and any accompanying fine, is to limit the sensitive data your organization collects, and then restrict the ‘exposure’ of that data, within your organization. Many high-profile incidents in Read more

SQL Compare

SQL Compare Snapshots: a lightweight database version control and rollback mechanism

I’m a big fan of using SQL Compare during proof-of-concept (POC) development. During the very early stages, I’m often unsure of the value of my current coding efforts and am not ready to commit unstable changes to a version control system (VCS). However, I do need a lightweight way to keep track of my changes. Read more

SQL Provision

Masking Data in Practice

Even small extracts of data need to be created with caution, if they are for public consumption. Sensitive data can 'hide' in unexpected places, and apparently innocuous data can be combined with other information to expose information about identifiable individuals. If we need to deliver an entire database in obfuscated form, the problems can get harder. Phil Factor examines some of the basic data masking techniques, and the challenges inherent in masking certain types of sensitive and personal data, while ensuring it still looks like the real data, and preserving its referential integrity, and distribution characteristics. Read more

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