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

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

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

Brent Ozar tackles a common error.

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

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.

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?

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 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.

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.

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.

“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.

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.

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...

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.

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.

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.

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 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 changes to the way it will be rolling out feature updates for Windows 10 starting with the 19H1/1903 release.

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.

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.

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.

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.

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.

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.

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.

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

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...

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.

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

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.

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.

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.

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.

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.

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...

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...