A community of more than 1,600,000 database professionals and growing

The Voice of the DBA

Common Data Challenges

Working with data has proven to be a challenge for most of my career. It's been fun, and certainly fulfilling, but there are constant challenges involved. Let's take away the hardware and admin challenges of keeping systems running, backed up, and performing well. I'm thinking today of the struggles of just data.

There is a short article that talks about three common data issues, and these are some of the same ones I've struggled with for most of my career. Are these challenges any different than they were 20 or 30 years ago? I'm not sure, and I was working with databases and software nearly 30 years ago.

ETL is a constant challenge, even today with tools like SSIS and Biml that make it much easier to build flows that migrate data from one database to another. ETL is such a challenge that many people make a very comfortable living helping organizations meet their every changing needs to move and prepare information for end users.

The other challenges noted in the article are getting a complete data picture because of missing data and not trusting or believing in data. The latter hasn't been as much of a problem for me. I might describe it differently as more often we aren't sure what weight to place on certain data. The world is messy, and often we collect data that we think might be valuable only to realize later that it doesn't mean what we thought or our our hypothesis was incorrect in the first place.

I think the challenges are part of what makes this work interesting. Our employers and clients might view the effort and time involved as frustrating, and I wish I had solutions to make our process quicker and smoother. Actually, I do think that advances like SSIS have made things quicker, but the world has grown more complex. We deal with more data from more systems, in the still chaotic, messy formats of the world.

What are your challenges with data today? Are they getting better, worse, or still the same as they always were?

Steve Jones from SQLServerCentral.com

Join the debate, and respond to today's editorial on the forums

The Voice of the DBA Podcast

Listen to the MP3 Audio ( 3.4MB) podcast or subscribe to the feed at iTunes and Libsyn.

The Voice of the DBA podcast features music by Everyday Jones. No relation, but I stumbled on to them and really like the music.

Redgate University

Self-paced online training courses with easy to follow classes on getting started, exploring advanced features, and making the most of Redgate products. Learn the fundamentals, best practices, and top tips from the experts - Redgate’s Microsoft Data Platform MVPs and engineers.
Start Learning

SQL Provision: Create, protect, & manage SQL Server database copies for compliant DevOps

With SQL Provisions virtual cloning technology, databases can be created in seconds using just 1MB of storage, and sensitive data can be anonymized or replaced with realistic data to ensure it is protected as it moves between environments. Download your free trial

Featured Contents

Introducing SQL+ Dot Net

Alan Hyneman from SQLServerCentral.com

SQL+.net is the first real innovation in data access for quite some time, and was built exclusively for SQL developers. More »

Back to Basics; Masking Address Data

Additional Articles from Redgate

Protecting Personable Identifiable Information is central to the compliance of numerous regulations which your organization may be subject to. In this article Grant Fritchey starts with the basics of obfuscating address data with the data masking technology in SQL Provision. More »

The Performance of Window Aggregates Revisited with SQL Server 2019

Additional Articles from SimpleTalk

T-SQL window functions have been a fantastic addition to the T-SQL language. In this article, Kathi Kellenberger reviews how optimizations available in SQL Server 2019 can improve the performance of these functions. More »

From the SQLServerCentral Blogs - Finding single use plans in the plan cache

Arthur Daniels from SQLServerCentral Blogs

A single use plan is an entry in the SQL Server plan cache that was only used once. When a... More »

From the SQLServerCentral Blogs - Creating custom SQL Server Helm charts

Andrew Pruski from SQLServerCentral Blogs

In my previous post I went through how to deploy SQL Server to Kubernetes using Helm in which I used... More »

Question of the Day

Today's Question (by Steve Jones):

I create a new empty database in SQL Server 2017. I then run this code:

 CREATE PROCEDURE Sometest AS BEGIN SELECT * FROM WeLikeBeer END GO

What happens?

Think you know the answer? Click here, and find out if you are right.

We keep track of your score to give you bragging rights against your peers.
This question is worth 1 point in this category: Stored Procedures.

We'd love to give you credit for your own question and answer.
To submit a QOTD, simply log in to the Contribution Center.

T-SQL Querying (Developer Reference)

Squeeze maximum performance and efficiency from every T-SQL query you write or tune. Four leading experts take an in-depth look at T-SQL’s internal architecture and offer advanced practical techniques for optimizing response time and resource usage. Get your copy from Amazon today.

Yesterday's Question of the Day

Yesterday's Question (by Steve Jones):

I want to get a set of the odd numbers less than 100. Which of these produces that set?

Answer: a = {x for x in range(100) if x % 2 == 1}

Explanation:

The correct answer is a comprehension that gets all numbers in the range(100) and looks for a modulus of 1. A modulus of 0 is an even number.

A set uses curly braces to return the unique set.

Ref: set comprehension - click here

» Discuss this question and answer on the forums

Database Pros Who Need Your Help

Here's a few of the new posts today on the forums. To see more, visit the forums.

SQL Server 2017 : SQL Server 2017 - Development

Help with OUTPUT INSERTED syntax needed - I want to collect a value from the source table of a SELECT statement used in an INSERT statement, that...

HOW TO USE EXISTS FUNCTION WITH 3 TABLES - Can you Please show me that how can we use EXISTS FUNCTION WITH 3 OR MORE TABLE. Please show me...

Retaining values between date range - Good afternoon I need to calculate rolling 12 month count I.e. 1/1/2018 - 12/31/2018 is period 1 , 2/1/2018 - 1/30/2019 is period...

TSQL Help with a Stored Procedure. - I need help with my below mentioned Stored Procedure - specific to the field in my dataset. Per my clients specifications,...

SQL Server 2016 : SQL Server 2016 - Administration

Deadlock troubleshooting - My boss is asking me about how I would end up resolving this issue that is causing deadlocks and most...

Find New SQL Servers - I am trying to find all new SQL Servers and DBs being created and grant appropriate access.Very often we learn...

High Availabiltiy Role Change Alerting - I've created a SQL Alert to email an operator when the High Availability Group fails over. I've set it to...

SQL Server 2016 : SQL Server 2016 - Development and T-SQL

How does a cursor affect the optimizer (and the execution plan) ? - Hi, This is Part 2 of Slow in the Application, Fast in SSMS. How to force the execution plan generated by...

Query runs slow when executing under the context of a different DB - I am trying to understand this behavior but when I try to use the below code and execute it in...

Slow in the Application, Fast in SSMS. How to force the execution plan generated by SMSS - Hi, This is my second case this week where the same query is fast in SSMS and slow in the application....

datetime - with 01/01/1000 date - Am I on the right track here when a vendor sends me data that includes a "begin date" of 01/01/1000...

SQL Server 2014 : Development - SQL Server 2014

sent SQL query as formatted Excel .xlsx via email - I already have a working stored procedure that exports sql query data set into a .csv file and emails it. Works great. Now I...

SQL Server 2012 : SQL 2012 - General

Can I set TDE Encryption off while my db actively in use? - We have a DB about 45GB (32gb compressed-backed up) that has TDE setup, I need to remove encryption so I...

Turn on Scalar Permissions - We have a production database and also a reporting database, that is essentially just a copy of the original DB....

How does the target table size impact the insert performance - I have a target table which is incrementing with 2 Million records every day from the past 30 days. Earlier...

SQL Server 2012 : SQL Server 2012 - T-SQL

Parse string based on a list of values - Rather that use a bunch of replace functions, is there a better way to parse these values, CH,HC,MA,NH,OA,OTH,PSY,SNF, out of...

Save the actual execution plan of a SQL statement in a script - I have a script that runs in SSIS from an "Execute SQL Task". One statement that is taking about 1...

SQL Server 2008 : SQL Server 2008 - General

nvarchar to smalldatetime - Im currently working on updating company database in which on one of the tables the 'CreateDt' is in the nvarchar...

Query to group and count data elements from a single column - In records 1, 2 and 3 (plus 3000 more rows), we have the following sample data from table SAMPLE_DATA, COL01: The...

Microsoft Access : Microsoft Access

Export memo field to Excel truncates values - I am using MS Access 2007. There are two tables each having a memo column. I have written a IIF statement to...

This email has been sent to newsletter@newslettercollector.com. To be removed from this list, please click here.
If you have any problems leaving the list, please contact the webmaster@sqlservercentral.com.

This newsletter was sent to you because you signed up at SQLServerCentral.com.
Feel free to forward this to any colleagues that you think might be interested.
If you have received this email from a colleague, you can register to receive it here.