Problems displaying this newsletter? View online.
Database Weekly
The Complete Weekly Roundup of SQL Server News
Hand-picked content to sharpen your professional edge

Why Getting Data Right Matters

An InfoWorld article from 2017 suggests that 80 percent of a data scientist’s job is cleaning and transforming data, and I believe this is probably only true for organizations that spend at least an average effort in designing and implementing their data storage. These persons who have trained to analyze data using complex math formulas could be spending their time producing important insights into how to make their company more profitable. Yet instead, assuming they don't have other meetings and administrative tasks during a week, they are spending on average 32 hours each week mucking through data trying to figure out what data is useful, what it means, and then reorganizing it for analyzing. They have only eight hours a week left to provide insight into the data, assuming they were right in what they thought the data meant.

We can do better. Organizations can reduce the amount of time deciphering data by paying more attention to getting database structures designed as right as reasonably possible the first time. It is crucial when you start a project that involves data, no matter whether a completely new system or altering an existing system, to understand the reason a company stores data. Clearly, the first reason is the obvious one: to manage operations. Take money in, ship products out.

What follows is generally where the power of data comes in. Why did we get that money? How quickly did we ship the product? For people who received their product quickly, were they more likely to purchase more? Did the offer that was included on the receipt help to bring in more sales? How did we not know that people who bought peanut butter on a Tuesday ordered more milk on Friday?

Structure data properly for whatever data platform you are employing. Name attributes the same from version to version of a system, so you know what the ProductStatus means in v1, and v10, even though structures and even platform may have changed. Perhaps even more importantly, that when you needed to store the ProductStatus, you didn't use the LastName attribute in v3-3.2 because it was "easier" than adding a new column.

Just getting structure right is just the beginning. Too many databases are like basic buckets. They will allow any data in that the customer wants. This leads to situations like having a product order appear to be shipped years before it was even ordered; phone calls that appear to take less than 0 seconds; invoices that were paid in 3020. I could go on listing issues I have seen before, but the problem is that when computer systems allow bad data, bad data creeps in. Analyzing data with more than a smattering of instances with poor quality affects the results of analysis.

Discovering insights by analyzing data is what’s truly important in the long run. Data is useful operationally for minutes, perhaps days, but for analysis for years to come. It all starts with the boring, somewhat time-consuming basics of following proper design patterns.

Louis Davidson (@drsql)

Join the debate, and respond to the editorial on the forums

Redgate Database Devops
The Weekly News
All the headlines and interesting SQL Server information that we've collected over the past week, and sometimes even a few repeats if we think they fit.
Vendors/3rd Party Products

Do It Live! A First Look at Redgate’s Hybrid Model for SQL Source Control and SQL Change Automation (video)

In this 70 minute livestream recording, I kick the tires of a fresh new Azure DevOps demo environment showing Redgate’s Hybrid Model for SQL Source Control and SQL Change...

Monitoring SQL Server with Splunk and SQL Monitor

Splunk is a search engine for collecting and analyzing all sorts of "machine data", including log data and metrics for SQL Server. SQL Monitor gives you the detailed diagnostic view of all your SQL Server instances, and databases. If you have Splunk to monitor your applications and server infrastructure, and SQL Monitor to help you understand the behavior of a complex database system such as SQL Server, then you have a powerful and capable monitoring tool set.

Removing the Square Bracket Decorations with SQL Prompt

If you avoid illegal characters and reserved words in your identifiers, you'll rarely need delimiters. Sadly, SSMS applies square bracket delimiters indiscriminately, as a precaution, when generating build scripts. Phil Factor provides a handy function that adds quoted delimiters only where they are really needed and then sits back and lets SQL Prompt strip out any extraneous square brackets, in a flash.

Using Aliases in SQL Prompt

Phil Factor explores the role of table aliases, explaining when they are required, and their general purpose otherwise, the need for sensible naming of aliases, and how SQL Prompt handles them.

Achieve CI/CD for your Oracle Databases on Linux and Windows

We’ve added new capabilities to the Deployment Suite for Oracle, so it now supports automated migrations-based and state-based Oracle deployments on both Linux and Windows. We've introduced Redgate Change Control, a new capability for understanding development changes, generating migration scripts for version control, and getting precise control over the migration path during automated deployments. Our Schema Compare and Data Compare for Oracle command line tools have been extended to Linux as well as Windows, and we’ve added static code analysis that encourages teams to follow good coding practices on Windows or Linux.


Trace Query in Extended Events

Tracing a query is a common task for a DBA. The methods employed are pretty varied.

How To Move TempDB on SQL Server

This post is going to walk through the quick steps to move TempDB through TSQL and the Windows Server UI.

sys.xp_delete_files and ‘allow filesystem enumeration’: two new undocumented items in SQL Server 2019

SQL Server 2019 introduced, among other things, two new filesystem-related items:

Extended Events Misperceptions: Profiler is Easier, Part 2

I wrote a short blog post about the misperception that Profiler was easier than Extended Events when it came to the core concept of “click, connect, BOOM, too much...

Chaos Engineering and SQL Server

Recently I’ve been delving into Chaos Engineerin...

Your SQL Server's Screaming But Is Anyone Listening? – High Severity Alert Notifications

As usual, the scripts in this post can be found HERE on our Git Hub repo If your SQL Server comes across something that it’s not happy about, it... The...

Accelerated Database Recovery in SQL Server 2019

Total: 0 Average: 0An Overview of Traditional Recovery As with all relational database systems, SQL Server guarantees the durability of data by implementing crash recovery. Durability in the acronym...

Redgate SQL Provision
Azure Synapse (SQL Data Warehouse and Data Lake)

Moving Data Around in Azure Synapse Analytics

Niko Neugebauer looks at some techniques for copyi...

Big Data Cluster

How to Deploy a Big Data Cluster to a Multi Node Kubeadm Cluster

In my previous post, I talked about deploying a Big Data Cluster on a single node Kubernetes cluster.

Computing in the Cloud (Azure, Google, AWS)

Hybrid Solutions are the Hot Topic for 2020

Click to learn more about author Lex Boost. In 2019, global hybrid solutions became the ‘hot topic’ for businesses. Industry experts anticipate this trend to continue this year as...

Conferences, Classes, Events, and Webinars

Key findings from the 2020 State of Database DevOps report

Free webinar February 12th 4-5pm GMT - The results are in! Join our expert panel, Kendra Little, Kellyn Pot’Vin Gorman and Grant Fritchey, to dig into the key findings from Redgate’s fourth annual State of Database DevOps report and learn how to use these insights to drive improvements in your own software development process.

Introducing the Hybrid Model to SQL Source Control

We’re excited to announce our new ‘hybrid model’ workflow! The model combines the state-based development experience implemented in SQL Source Control and Source Control for Oracle with the customizable power of migrations-based deployments. Join this session on February 20th 4-5pm to find out more.

Data Privacy, Compliance, and GDPR

What is the California Consumer Privacy Act and how will it affect your business? Part 1

Remember all that talk in 2018 about the introduction of the GDPR across Europe? For many of us in the US, it didn’t seem real because it wasn’t in...

Data Science

Do data scientists need evangelists?

Data science can be a bit of a lonely job. It’s ...

Database Design, Theory and Development

Finding Foreign Key Child Records In SQL Server

Foreign keys help to ensure referential integrity ...

ETL/SSIS/Azure Data Factory/Biml

Announcing SSIS Catalog Browser v0.9.1.0

I am excited to announce a fairly substantial update to SSIS Catalog Browser! What’s New? In a word, speed. The #1 complaint I received from customers using previous versions...

Continuous integration and delivery (CI/CD) in Azure Data Factory using DevOps and GitHub - Part 2

(2020-Jan-28) This blog post is a followup to my ...

Loading data with umlauts from SQL server into Oracle using SSIS and OLE DB driver

Recently, I was developing a simple SSIS package. The purpose of the SSIS package was extraction of a data from MS SQL server into Oracle database. Extraction data flow...

HA/DR/Always On/Clustering

SQL Server High availability: Install SQL Server failover clustered instance Part 2

Total: 2 Average: 4.5In my previous article, I have explained the step-by-step process of installing SQL Server Failover Clustered Instance. In this article, I will explain how to add...

Performance Tuning SQL Server

What Tables Are Being Written To The Most?

You have excessive WRITELOG waits (or HADR_SYNC_COMMIT waits) and among other things, you want to understand where.

Signs Your Execution Plan Is Lying To You

Polygraph Unless you’re looking at an actual execution plan, one must never ever ever ever ever believe what one is…

Using Erik Darling’s sp_pressure_detector to resolve CPU issues

Last Friday I spent my lunch break going through s...

Sorting in Stored Procedures – Food for Thought

We know that sorting can be one of the most expensive things in an execution plan as shown below. However,  we continue to do ORDER BYs repeatedly. Yes, I...


Intro into Power BI Premium

I was chatting to my good friend Treb Gatte and one of the things we both discovered is that there was not a good blog post with regards to an introduction into Power BI Premium

Creating an Income Statement with DAX in Power BI – Part 1

This post is part 1 in my series on creating financial statements in Power BI!

Power BI API – Usage Monitoring – Activity Log (Audit Log) Activity Event Data

When using the cloud-based Power BI Service,, every action that is taken while logged into the portal — whether it is viewing or publishing a report, creating a new workspace, or even signing up for a pro trial license, that activity is logged within the Microsoft servers as part of the Office 365 audit logs.

Compare Period Over Period Breakdown in Power BI Using the Waterfall Chart

The Waterfall chart is a good visualization to show you changes on value over a sequence, The sequence can be time, or date or workflow steps, etc. There is...

Sending Power BI Alerts To Users Via Teams Using Power Automate And Adaptive Cards

Send Power BI alerts to users via Teams using Power Automate and format the data using Adaptive Cards

Do you know what a Power BI seed file is??? (Roundup | Jan 27, 2020)

Thanks for watching this week's Power BI news roundup! Last weeks roundup: 2 Minute Tuesday: Patrick's tech video: Adam's tech video: Sources Easily...

SQL Server Security and Auditing

Auditing Login Events with Service Broker

Perhaps one of the most important aspects of running a secure SQL Server is auditing login events – essentially capturing who’s logging in, and who’s attempting to login. Being aware of who’s logging in, and who’s trying to login, can help troubleshoot a variety of problems.

Security News and Issues

What is a strong password anyway?

Background Fellow Microsoft MVP Troy Hunt (blog | ...


The transitive Property

You may recall the transitive property from elementary school math class. It states:

What are Different Methods to Know the Row Count of Table? – Interview Question of the Week #261

Question: What are Different Methods to Know the R...


If you read the title to this post and your first ...

How to Remove Times from Dates in SQL Server

Say that you’ve got a table with a datetime column in it, like the LastAccessDate column in the Stack Overflow Users table: And let’s say you wanna find the...

T-SQL 101: #53 Date and time data types in SQL Server T-SQL

SQL Server has a rich set of data types. Some of them were older data types and a number of new ones were introduced in SQL Server 2008. One...

RSS FeedTwitter
This email has been sent to {email}. To be removed from this list, please click here. If you have any problems leaving the list, please contact the This newsletter was sent to you because you signed up at Note: This is not the daily newsletter list, and unsubscribing to this newsletter will not stop you receiving the SQL Server Central daily newsletters. If you want to be removed from that list, you can follow the instructions on the daily newsletter.
©2019 Redgate Software Ltd, Newnham House, Cambridge Business Park, Cambridge, CB4 0WZ, United Kingdom. All rights reserved.


- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -