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 should SQL Server 2019 excite developers?

Although I’m sure there are plenty of fine things in SQL Server 2019, I must take it on trust because few are likely to affect my work as a database developer. I feel sure that DBAs will manage to work themselves into excitement but, unless I’ve missed something, as a developer I must content myself with the bone of Scalar UDF in-lining. This doesn’t allow you to do more with a scalar UDF. It just runs faster. I’d now rather like it to do more. Please don’t think I’m sulking; I just wish that some of the existing, but undistinguished, features would get some of the attention.

This isn’t a release like 2012 or 2017, where, after years of neglect, database developers got some love. In fact, it was as much as we could do to keep up. We got the full Windowing functions after all, though it was some time after other leading RDBMS got them. We loved STRING_AGG (2017). We thought OPENJSON (2016) was cool, even if we only use it as a way of passing ‘mutable’ lists, or tables, between routines. It was handy to be able to define indexes within the table’s CREATE statement. TRY_PARSE solved a lot of problems.

There were some damp squibs, though. STRING_ESCAPE could only do JSON, but not XML or HTML. STRING_SPLIT (2016) could only cope with single-character separators. TRANSLATE (2017), a curious single-purpose function. This isn’t helped by the documentation which puzzlingly, says ‘TRANSLATE does not, however, replace a character more than once‘, though it seems to in practice.

FORMAT is fine for short runs, if you really want to get involved with presentation stuff but is slow with large result sets. CONCAT_WS (2017) allows you to do things that are already easy, such as joining column values into strings. One could go on and on, but the point is that currently in SQL Server 2019, the energy isn’t going into keeping SQL Server in parity with, say, PostgreSQL as a developer tool.

Nowadays, SQL Server developers are increasingly having to tough it out in an industry that has plenty of alternative development stacks. Sometimes a team can appear to make much more rapid initial progress with a MEAN (MongoDB, Express.js, AngularJS and Node.js.) stack or even the old LAMP (Linux, Apache, MySQL/MongoDB and PHP) stack, while on SQL Server (the M could stand for MSSQL these days, after all), we still, for example, wrestle with the basics of producing the JSON documents that application developers need, with such things as arrays.

The industry is changing rapidly, and SQL Server needs to make sure that it is there in the forefront, actively supporting all the changing architectures and data document formats that, like it or not, are being increasingly adopted. They need to give developers the warm feeling that Microsoft are continuing to think carefully how they can support them and proving to us that SQL Server is still the obvious choice, by providing all the extra features we need.

Phil Factor

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.

SSIS, Docker, and Windows Containers, Part 3 – Adding the SSIS Catalog, Attempt 1

In this post, Andy Leonard show his first steps at adding an SSIS Catalog to a container. Spoilers: this attempt fails.

Managing Kubernetes Resources

Vincent-Philippe Lauzon takes us through some thoughts on how to optimize Kubernetes resource allocation.


How to fix the error “String or binary data would be truncated”

Brent Ozar tackles a common error.

Finding Tables with Few Dependencies

This blog post talks about how to find small sets of tables that are joined to each other, but nothing else.

Testing Databases: What’s Required?

Phil Factor reviews the various types of database test that need to run during development work, what sort of test data they require, and the challenges with managing this data, and in keeping the test cell stocked with the correct database, and data, in a way that allows rapid cycles of database testing.

Are Differential Backups Necessary?

SQL Server natively supports 3 types of backups: Full, Differential and Log. Full backups take a complete backup of the entire database, while Log backups take a backup of the database’s transaction log. So, What are Differential backups? Are they really necessary?

Using Column Sets for Sparsely Populated Columns

SQL Server has a little-known feature called Column Sets that is very useful when dealing with large numbers of sparse columns. Sparse columns are useful when you need a table with many columns where the rows are sparsely populated with data.

The Collapse Operator

The Collapse operator is typically used to optimize update processing, usually in combination with Split and Sort operators. This operator combines combinations of rows that represent a delete and an insert for the same key value (or set of key values in the case of a multi-column key) into a single row that represents an update. Other rows are passed unchanged.

SQL For Cosmos DB – Tips and Tricks

Building on the introduction to the SQL used by Cosmos DB in the first article in the series, here you will learn how to handle some of the more classic challenges that you may face when querying JSON documents. Adam Aspin shows you some of the workarounds that are useful in practice when finding and shaping output data ready for further analysis.

Exporting Data to Flat File with BCP Utility and Importing data with Bulk Insert

The BCP (Bulk Copy Program) utility in SQL Server allows database administrators to import data into a table and export data from a table into a flat file. The BCP utility also supports various features that facilitate the process of exporting and importing of the bulk data.

The Difference Between Primary Key And Unique Key

“What is the difference between a primary key constraint and a unique key constraint?” This is probably the most frequently-asked job interview question for database developers. In this article, Ben Richardson will try to answer it.


What is Azure Cost Management?

If you’re using Azure, one key piece is effectively planning and controlling the costs involved in running your business. In this post, I’ll discuss what I believe to be one of the most critical functions in Azure that will help you properly deploy and manage your environment.

Conferences and Events

Group By Conference is Back April 11th!

I have joined forces with Blyther Morrow & Daniel Hutmacher to keep the GroupBy Virtual Conference going.  We’re aiming to host the conference at least twice this year, and... The...

Database Design and Implementation

The Cost of Useless Surrogate Keys in Relationship Tables

What’s a good natural key? This is a very difficult question for most entities when you design your schema. In some rare cases, there seems to be an “obvious” candidate, such as a variety of ISO standards.

Devops and Continuous Delivery (CI/CD)

Musings of a DevOps Data Pro

Continuous delivery and automation are a couple of core concepts of DevOps. As a Data Professional, Derik Hammer has spent countless hours pushing through the resistance of people who feel the data tier should be left out of source control, automated deployments, and who feel the need to make the data tier a separate work stream from applications.


Overcoming the Top Barriers to Data Encryption

While IT security seeks to protect physical assets like networked computers, databases, and servers, encryption is a must-have element in any security strategy – it protects the data that lives on and between those assets.


SQL in the City Summits – UK, US & Down Under

Redgate are inviting senior data professionals to attend one of the upcoming SQL in the City Summit events taking place in April, May and June. If you’re interested in learning how your business can benefit from implementing Compliant Database DevOps this event is for you. Find out who’s presenting and register for a Summit near you today.

Microsoft News

SQL Server 2008 and 2008R2 End of Life – coming soon

Microsoft has announced that as of July 2019 SQL Server 2008 and SQL Server 2008R2 will be at the end of life support. What does this mean?

Microsoft is making big Windows 10 update changes starting with the May 2019 release

Microsoft is making changes to the way it will be rolling out feature updates for Windows 10 starting with the 19H1/1903 release.

Performance Tuning

IGNORE_DUP_KEY slower on clustered indexes

The IGNORE_DUP_KEY option for unique indexes specifies how SQL Server responds to an attempt to INSERT duplicate values: It only applies to tables (not views) and only to inserts. Any insert portion of a MERGE statement ignores any IGNORE_DUP_KEY index setting.

Indexed Views – Performance Panacea or Plight

Far too often, an indexed view is created without consideration for the costs of the indexed view. In this article, Jason Brimhall covers some of the more important costs that are frequently overlooked when considering an indexed view as a performance panacea.

WhoIsActive In SSMS

Kevin Feasel provides three SSMS keyboard shortcuts for the sp_whoisactive calls he uses most often in day-to-day database development and query tuning work.

Redgate University
Performance and Tuning

A Brief History of Deadlock and Modern Approach of Resolution

Deadlocks have been constant troublemakers for database professionals for years. They seem to show up from nowhere and keep on happening again and again. Pinal Dave provides some oversight.


Different types of many-to-many relationships in Power BI

The introduction of the weak relationships in Power BI composite models enables new data modeling techniques. However, not all of the many-to-many relationships can be managed by using weak relationship.

Affordable Power BI Premium for Small Business (Part 2)

The second in Matt Allington's series of articles explaining how to use Microsoft Flow, the Azure APIs, the Power BI APIs and SharePoint Online to help make Power BI Premium more affordable for small to medium sized enterprises.

Easily get an access token for the Power BI Embedded Playground

You need an Embed Token for the Power BI Embedded Playground, but you don't want to write code. Adam shows you how to easily get an access token.


Debugging Type Binding in PowerShell

Why does this PowerShell function’s default argument change value based on the use of . or & to invoke a command within it?


How Political Campaigns Use Personal Data

Really interesting report from Tactical Tech. Data-driven technologies are an inevitable feature of modern political campaigning. Some argue that they are a welcome addition to politics as normal and...

SQL Server 2019

New Page Functions in SQL Server 2019

Two new tools will be making an appearance in SQL Server 2019, a dynamic management function (DMF) and the other a function, will be making an appearance, namely sys.dm_db_page_info and sys.fn_pagerescracker respectively.

SQL Server on Linux

Upgrading SQL Server on Linux

This quick blog posting shows how easy it is to upgrade SQL Server on Linux.


Finding a character in every character string by elimination

Using a number’s table, you can break down every value in a column into one character per row, and then do some analysis. In our case, I want to find a bad character, by eliminating known acceptable characters.

Vendors/3rd-party Products

Reporting on the Status of Clones During Database Development

Phil Factor uses SQL Clone, PowerShell and Visio to build a live 'clone network' diagram showing when there was last activity on each clone, and the number of object changes made to each one, alongside useful metadata such as the clone and image sizes, who created them and when.

Azure SQL Elastic Pools and SQL Monitor

This article explains the Azure-tailored metrics and alerts in SQL Monitor that track use of the limited compute resources available to an Azure SQL Elastic Pool, and to each of the databases in it.

How monitoring can help keep your SQL Server estate secure

Using broad SQL Server monitoring can instantly alert you to unusual activity or patterns across your entire estate, letting you can act fast to resolve problems and keep your systems online, secure, and compliant. Read our new SQL Server security white paper today and start using your monitoring to its fullest.


Speaking at 24 Hours of PASS: Past Learnings and Future Visions

Cathrine Wilhelmsen is presenting a brand new session called Pipelines and Packages: Introduction to Azure Data Factory. If Azure Data Factory is not really your thing, don’t worry! You can choose from 23 other topics, including SQL Server, Power BI, AI, Containers, and even Professional Development.

Extending DevOps to the Database: Development Best Practice

Wednesday April 10, 4PM-5PM BST / 10AM-11AM CDT - Discover how you can improve your database development and deployment processes. You'll see a demo of Redgate tools and how...

Extending DevOps to the Database: Development Best Practice

Wednesday April 17, 4PM-5PM BST / 10AM-11AM CDT - The many benefits of having globally, or even locally distributed teams do not come without a trade-off. So how can...

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.


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