SQLServerCentral - www.sqlservercentral.com

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

Featured Contents

Featured Script

The Voice of the DBA

Production Subsets

This editorial was originally published on Aug 22, 2014. It is being republished for today's US holiday.

Continuous delivery recommends developers never use production data. It's too big, too cumbersome, and slows the process too much. Developers should have enough data to determine if their solutions work as they build them. Testing should have enough to do some tuning, but unless you plan on full performance/load tests (which you should), then you don't need the full set of production data.

It's an interesting idea, and overall I agree. A subset of data, hundreds of rows, can usually tell you if you're writing code that works if you profile the code and look for inefficiencies. Note that profiling code doesn't mean use Profiler. It means examining the resource used by your code in terms of CPU, I/O, memory, etc. There are tools to help you, and at some point in your development process, you should be using them.

However it can be time consuming and cumbersome to build small development data sets. There are lots of choices in how you might do this, and I thought this would make an interesting poll. For those of you that deal with development, whether that's T-SQL, .NET, or something else, what do you think?

Should we have a subset of production data, a custom data set, or perhaps deal with complete production data? 

Some of this depends on the size of your production data, and I hope, it's contents. I would not want any PII, PCI, medical, etc. in any development area. However if that's not the case, then what do you prefer?

Whether you have  custom data set or a subset of production, it can be cumbersome to keep this up to date. Your data may evolve over time and there's overhead in maintaining some scripts that would produce the data you need. Perhaps that's the cost of writing good software, but I'm curious how many of you feel.

Steve Jones from SQLServerCentral.com

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

SQL Source Control

How to track every change to your SQL Server database

See who’s changing your database, alongside affected objects, date, time, and reason for the change with SQL Source Control. Get a full change history in your source control system. Learn more

SQL in the City

Sign up for more free training from Redgate

Redgate has committed to hosting a free virtual event in every quarter of 2018, and will be kicking this off on February 28 with a livestream themed around data privacy and protection. The agenda has now been released, so you can see who will be presenting, what they will be presenting, and how you can tune in to watch. Find out more about the sessions and register your place

Featured Contents


Implementing John Conway's Game of Life in Microsoft SQL Server

Paul Chang from SQLServerCentral.com

A fun exercise, using CTEs to implement John Conway's Game of Life cellular automata simulation More »


How to reformat a database in one operation

Inherited a database from another team? Changed your team policy on the way that you format SQL? What’s to stop you formatting the code of an entire database nicely, when you’re developing it? It can be done, but the process can take longer than you expect. This article will demonstrate a simple 3-step approach to reformatting a whole database to your standard, in a single operation, using SQL Compare and SQL Prompt. More »


Questions about Data Governance that You Were Too Shy to Ask

Additional Articles from SimpleTalk

A company’s data is one of its most valuable and important resources. Managing and protecting that data are big responsibilities, and a data governance processes must be put into place to avoid misuse and to meet regulations. In this article, William Brewer answers questions you may have about data governance but were too shy to ask. More »


From the SQLServerCentral Blogs - Connecting to a SQL Server instance – Back to Basics

Wayne Sheffield from SQLServerCentral Blogs

Back to the SQL Server Basics with Wayne Connecting to a SQL Server instance is one of the first things that... More »


From the SQLServerCentral Blogs - Moving Lookup Data with ReadyRoll

Steve Jones from SQLServerCentral Blogs

I’ve been using ReadyRoll for a small project and wanted to move some lookup data. There are some tables where... More »

Question of the Day

Today's Question (by Steve Jones):

If I run this code, what is returned?


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: SET Options.

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


Pro Power BI Desktop

This book shows how to deliver eye-catching Business Intelligence with Microsoft Power BI Desktop. You can now take data from virtually any source and use it to produce stunning dashboards and compelling reports that will seize your audience’s attention. Slice and dice the data with remarkable ease then add metrics and KPIs to project the insights that create your competitive advantage.

Yesterday's Question of the Day

Yesterday's Question (by Steve Jones):

I see I have a guest user in my database, but in SSMS the user has a red x next to the name. How do I enable this user in the database for others to map to if they don't have a user account?

Answer: GRANT CONNECT TO guest


The guest user exists in all databases and cannot be added or dropped. Instead, the user is enabled by granting the CONNECT permissin. The syntax is


Ref: Guest user account in SQL Server - click here

» Discuss this question and answer on the forums

Featured Script

HTML Table Results For Email

M Higgins from SQLServerCentral.com

This stored procedure will take a pre-defined view, table or temp table and return the results as an HTML table formatted string.  This can then be used with email to send better formatted emails.

Example usage

EXEC sp_GenerateHTMLTableResults @ResultsTableName = N'<TableName>' ,-- Can be a tablename, temp table or view
                                  @GeneratedHTML = @GeneratedHTML OUTPUT 
EXEC msdb.dbo.sp_send_dbmail @profile_name = NULL ,
                             @recipients = 'someone@domain.com' ,
                             @blind_copy_recipients = '' ,
                             @subject = 'HTML Table Results' ,
                             @body = @GeneratedHTML ,
                             @body_format = 'HTML'

More »

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

Convert a query to a View - First and foremost I am not a db programmer, Dispatcher by trade. So if I do not belong here do...

Average number of records per hour per datetime range - Hi, I have a DB with PATIENT_ID, ARRIVAL_DATE, DEPARTURE_DATE, and DEPARTMENT. I need to find the average number of patients in...

SQL Server 2016 : SQL Server 2016 - Administration

Transaction Log Growth - Hi, I am currently using Ola Hallengren's backup solution for my backups on a SQL Server 2016 Standard instance. I have...

Installing multiple versions of SQL server on Windows 2016 cluster - Hi team, We have a two node Windows 2016  cluster with single SQL server 2014 instance. Now I want to add...

Showplan - Started a new role, have limited (read) rights to the server, I have asked for showplan rights but have been...

Training? - I'm not sure if this is the proper place to post this but I'm sure someone will tell me if...

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

Show results based on String Search - Hi Everyone,                      I have a complex requirement. There is a ASP.NET page where an Agent logs in to run a...

Need experts Advice - HI guys,    I need expects advice to do below things with great performance.     A Table has 10 million ID's records. My...

SQL Server 2014 : Administration - SQL Server 2014

Adding multiple triggers to same table - Recommendations/Best Practices - Hi Folks, I have a requirement to add additional triggers(INSERT/UPDATE) for a table which already have INSERT/UPDATE/DELETE triggers. This is for...

SQL Server 2014 : Development - SQL Server 2014

Stored procedure not executing in Linked Server - Have a problem in which stored procedure is not getting executing it through a SQL Server agent job .Executing the...

SQL Server 2012 : SQL Server 2012 - T-SQL

Need to get consecutive duplicate rows - I need a query that can get me (the ID of) consecutive duplicate rows, where I am grouping on some...

This email has been sent to {user_email}. 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.
This transmission is ©2018 Redgate Software Ltd, Newnham House, Cambridge Business Park, Cambridge, CB4 0WZ, United Kingdom. All rights reserved.
Contact: webmaster@sqlservercentral.com