Problems displaying this newsletter? View online.
SQL Server Central
Featured Contents
Question of the Day
Redgate SQL Source Control
The Voice of the DBA

Creating Test Data

Test data is hard to come by, and I agree with Brent Ozar: " I get so frustrated when I hear trainers/presenters/bloggers/idealists talk about how developers should be using purpose-built-from-scratch data sets with no real customer data in ’em. The real world just rarely works that way."

I agree, and I know this is a hard problem. I'm one of those that tries to get people to use non-production data. I know there isn't an easy solution, and there isn't a one-off solution here. Really, my frustration is with the fact that many development people just don't see the problem as one that requires ongoing effort. However, as I've seen unit testing grow and become commonplace, I do expect that eventually we will start to understand that it is worth some effort to produce test data.

Often we want a lot of things. We want large data sets, similar to production. We need some randomness in there because we won't think of every edge case. We need some consistent values because we will use them to initially evaluate our logic and finding new values all the time is frustrating.

Is there a good way to do this? I've seen some good, creative ways of building test data, such as Andy Mallon's post from last year. I do think routines like this are good, but they need to be a) shared with other developers, b) easy for other developers to find, and c) incorporated into some developer database creation process.

To me, this ought to be a process that is maintained by all developers and DBAs, and one that ensures a new development environment can be created in seconds, or possibly minutes. This often means some pre-staging work to ensure developers can quickly and easily hydrate or reset a new database. Certainly products like SQL Provision can help, but these still require some work to ensure that developers have the test data that is useful for them.

Ultimately, I think maintaining test data is part of being a developer. Having a central, consistent set (or two), is a part of working in a software team. When we start to accept this as part of our work, we can build more consistent and reliable processes.

Steve Jones - SSC Editor

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

Redgate University
  Featured Contents

Use Polybase to consume Oracle data from SQL Server on Linux 2019

pabechevb from SQLServerCentral

Learn how to set up Polybase in containers to access an Oracle database.

Replace Model SQL Server Database with a User Database

Additional Articles from

In this tip we look at what happens if we replace the SQL Server model database with a regular user database

From the SQL Server Central Blogs - T-SQL Basics: using OUTPUT

epivaral from SQL Guatemala

You are working on a database development project, and you need to obtain the records before/after an INSERT, DELETE, UPGRADE or MERGE Statement to present it to the user...

From the SQL Server Central Blogs - Installing PowerShell for SQL Server – #SQLNewBlogger

Steve Jones - SSC Editor from The Voice of the DBA

Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers. PowerShell is the hot new scripting language for...


  Question of the Day

Today's question (by Steve Jones - SSC Editor):


More Delays

What happens when I run this code?
WAITFOR TIME '2020-01-10 1:13p'

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



  Yesterday's Question of the Day (by Steve Jones - SSC Editor)

Cleaning Up NA Values

I have a data frame that has data from the first week of our fantasy football playoffs.

> fantasy.playoffs
                Team  Score           Opp
1 Way0utwest Cowboys 135.40          
2         SSC Ravens  66.26 Green Machine
3   Orange Engineers  85.20          
4          Mexicanos 111.52     Mexicanos

I want to extract out the rows with in them. What function can I use?

Answer: > na.omit(fantasy.playoffs)

Explanation: The na.omit function will clean rows out that have NA values in them. Ref: na.omit() -

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 - Administration
Audit Login Usage - I'm wondering what methods others have used to trace for logons being used.  Some of the thoughts I had and saw were: Turn on success and failure auditing for logons -- and try to leverage something like this post: Attempt to use a logon trigger to capture logons as they come in, but really […]
SQL Server 2017 - Development
SQL difference between SQL Management studio and VS sqlproj - I have a project "fred.sqlproj" it includes this file, atable.sql which contains the following: CREATE PARTITION FUNCTION [Ehouse_TP_Function](smallint) AS RANGE RIGHT FOR VALUES (N'30', N'100', N'140') GO CREATE PARTITION SCHEME [Ehouse_TP_Scheme] AS PARTITION [Ehouse_TP_Function] TO ([FG1], [FG2], [FG3], [FG4]) GO CREATE TABLE [dbo].[Ehouse] ( [Id] BIGINT IDENTITY (1, 1) NOT NULL, [AnimalTypeId] SMALLINT NOT NULL, […]
SQL Server 2016 - Administration
Creating a data and log drive per CPU - Happy New Years everyone. Have just had a conversation with a client where they are creating a separate drive per CPU inside their SQL Server.  They then partition out the data/log files across these (for this case eight) drives.  I have never seen that before.  Currently at where I work we do have a separate […]
Attach read only DBs - I am testing a DR scenario but I am running into an issue and wondering if I can get some help. We use DPM tool to backup DBs. DPM only runs on secondary replicas so there is no impact on primary server. DPM takes the snapshot of a data and log file so the only […]
Kerberos / SQL Service Accounts - Cluster - Hi, In single server setups I have always used a single domain account for SQL services and set up the relevant SPNs, specifically for IIS "double-hop" issues. I'm now supporting an existing 2016 cluster which has used different domain accounts for Agent and DB Engine services. Interestingly SPNs have only been setup for the domain […]
Listener timeout - Hello folks and happy new year! . I have a lab here at work and i ran into this issue regarding the Listener. This is an Always On Availability group lab i have and everything is going fine but there's an issue when connecting through the Listener. These are 2 SQL server 2016 EE […]
SQL Server 2016 - Development and T-SQL
Error converting data type varchar to numeric. - , a.sku + p1.SHORT_DESC + ' ' + p1.LONG_DESC1 AS short_description sku is numeric the descriptions are nvarchar It works until I add the sku to the front then I get Error converting data type varchar to numeric. Any help is appreciated.
Development - SQL Server 2014
Help required creating an SQL PIVOT - Hi, Im struggling to PIVOT SQL data in a single table which links to additional tables via the column\key TraceableItemID. In the attached example you can see TraceableItemID has the same value for the different attributes but the table stores the attributes on different rows. What I would like to do is have all the […]
SQL Server 2019 - Administration
i can't find ssms - hi   after install sql server 2019 ican't find ssms, shouldn't it be install while install sql server 2019?
Setting flexible security for reporting - I have an increasing number of reports distributed to users. Managing the security is providing challenge. Reporting code is stored in a repository database on the same server the production DB. A user needs to have EXECUTE on stored procedures in the repository DB, though stored procedures that call SP's in the production DB, and the […]
SQL Server 2008 - General
Can't open Alert in SSMS - Cannot show requested dialog. - When I try to view the properties of an existing alert of try to use the ssms to add a new alert, I get the following error; Cannot show requested dialog. Additional information: Cannot show the requested dialog (SqlMgmt) --Failed to create/initialize Agent Alert dialog. (SqlManagerUI) ----Cannot create/initialize Response page. (SqlmanagerUI) --------Object reference not set […]
Code to extract data - Hi I'm trying to add some code into a query I have written which extracts the data and saves it in a certain file path on my network. Does anyone know a)if any code exists and b)what the structure of that code is?? Thanks in advance
SSRS 2016
Using Visual Studio instead of Report Designer - When I'm on the SSRS server using a browser to browse all of the reports we have, I can click on the corner of a report and choose "Edit in Report Builder". It will open the report using the the Designer and I don't have to save the file anywhere to make any edits.  When […]
Strategies and Ideas
An international challenge - Please indulge me for asking a rather vague, 10,000 foot type question. I hope to get some feedback from anyone who faced a similar situation. Thanks! A manufacturing company uses three separate instances of the same ERP (Enterprise Resource Planning) software in three different countries (U.S., Sweden and China).  The ERP system uses sql server […]
Star Schema for Data Modelling (Beginner) - I'm trying to convert scenario into database tables using Star Schema...To do that, I would need dimension tables and fact tables. I'm confused as to how many fact tables are allowed for the star schema..since in the examples that I read, only fact table is used....Are 2 or more fact tables allowed for star schema? […]


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
©2019 Redgate Software Ltd, Newnham House, Cambridge Business Park, Cambridge, CB4 0WZ, United Kingdom. All rights reserved.


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