SQL Compare

Using SQL Data Compare to Synchronize Custom Error Messages

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

SQL Prompt

Finding code smells using SQL Prompt: TOP without ORDER BY in a SELECT statement

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

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