Problems displaying this newsletter? View online.
Database Weekly
The Complete Weekly Roundup of SQL Server News
Hand-picked content to sharpen your professional edge

Towards an efficient interface between application and database

I've noticed that SQL Server's JSON support has changed the way I use SQL Server, particularly where the database is working closely with applications. It happened so slowly that I hadn't really been aware of it. First, I suppose, was the use of a single JSON document as an output from a procedure, giving me the ability to pass several results, along with errors, warnings and so on, in a single NVARCHAR(MAX) variable. It is all very liberating. This is great for returning data to a JavaScript-based application, as it is so much easier for them and loosens the coupling between application and database.

Then, of course, it is rather good to be able to pass a JSON document from an application to a stored procedure. Naturally, there can be all sorts of lists, arrays, tables and atomic data in this document. You'd be amazed how much easier it is to create a robust interface with an application this way.

This all works fine, as long as you don't get mistakes, such as using the wrong JSON document as a parameter. You can test out a document pretty well, but it takes away some of the new-found economy of code. You want to validate that document rigorously, check out the datatypes, and check the data, as you would with a CHECK constraint in a relational table, but do it easily without bulking out your code or having something else to test.

Once you've hit this sort of wall, you understand why JSON Schema is so good. It means that you can, from the specifications within a JSON document, validate your input, enumerate the valid alternatives, the legitimate range of a number, check whether the data is sensitive, or whether it matches a regex. It will check whether array items are unique in value and so on. All these checks are done by making a single call to a method, to validate the document to the schema.

Once you have the technology, you can provide a JSON document from the stored procedure that allows the application to validate your output. This provides a sort of data document for the data to send to the application, so that there is less need to develop database and application in close step.

JSON Schema is now becoming more mainstream, with support in all the major languages such as Python, Go, Java, PHP, .NET. Even MongoDB now has it. Does SQL Server? From the way that it supports, and used to promote, XML Schema, you'd expect it to; but it doesn't. I can test a JSON file as being appropriate for a stored procedure from within PowerShell or Python, but I want to, and need to, test it at the point of consumption, within the batch or routine that has to use the JSON parameter.

OK. The paint isn't entirely dry on the JSON Schema standard. It is in draft 7, submitted to the IETF, but then the .NET implementation is very robust. I reckon it is for Microsoft to get stuck in with a SQL function that validates a JSON document, using a JSON Schema document.

Phil Factor

Join the debate, and respond to the editorial on the forums

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.
AI/Machine Learning/Cognitive Services

Text Classification using AI Builder inside Power Automate (Microsoft Flow)

Text classification is one of the important tasks for classifying the texts based on the allocated tags. In this Blog Post, you will see how to use the Text classification model in the Power Automate (Microsoft Flow).


Common commands and tasks to make dealing with Windows Core easier

You aren’t supposed to be logging into your servers all that often so why have the extra overhead of Windows? Windows Core removes all of that and comes back with something that looks and feels remarkably like DOS from when I started with computers. That said, most of us, myself included, aren’t used to just having DOS commands any more so here are some helpful tips.

Database Scoped Configurations in the year 2019

The DMV sys.database_scoped_configurations allows us to configure the most important details that govern the way our databases function. Niko Neugebauer reviews a few of the newer ones,

Analysis Services / BI on the MS Stack

SSAS Tabular – Process Add memory consumption

Roman Lánský takes a closer look into the memory consumption of the "Process Add" command, often the best choice for incremental data load to SSAS Tabular.

Azure DevOps

Controlling who commits code to a given database schema in Git with Azure DevOps

After consideration, Kendra Little suggest keeping all objects in the database in a single project and use Pull Requests (PRs) with automatic reviewers to ensure the right changes are being made.

Azure SQL Managed Instance

Bring Your Own Key to Azure SQL Database Managed Instance TDE

Last year Azure SQL Database Managed Instance saw the introduction of bring your own key (BYOK) functionality for transparent data encryption (TDE).

Big Data

Installing the SQL Server Big Data Cluster Tools in a Docker Container

Running a SQL Server Big Data Cluster is quite easy, because you can deploy it with a simple command into Azure Kubernetes Services, but you need to have a few command line tools installed on your local machine, or in a Docker Container.

Computing in the Cloud (Azure, Google, AWS)

A useful script for AWS credentials

If your work involves elevated access to computers, including Amazon Web Services (AWS) accounts, you'll need multi-factor authentication (MFA) for elevated access. Dev Nambi shares a quick utility script called sessioner that grants AWS security credentials that are valid for a few hours.

Migrating Oracle Exadata Workloads to Azure

As Exadata backup hardware like power units and cell disks need to replaced, many organisations are instead opting to migrate those workloads to Azure, but you need to know what to identify and address before the move to the cloud.


How to get ODBC drivers using Powershell

How to get the SQL Server ODBC Driver names and platform (32 bit|64 bit), to check application compatibility .

Redgate SQL Monitor
Data Mining / Data Analysis

Selecting Categorical Features in Customer Attrition Prediction Using Python

Illustrating two data analytics approaches that can be used to reduce the number of input categorical features in a data set and hence identify and select subsets of predictor features that are most relevant to predicting customer attrition.

Data Visualisation

a small (multiple) makeover for a big range problem

Employing a small multiple view to split data apart certainly isn’t the only solution to the big range problem, but it’s one that I find myself using frequently.

Database Design, Theory and Development

In Memory Table Indexes

Monica Rathbun explores memory optimized indexes, and just how different they are from indexes on regular tables.

HA/DR/Always On/Clustering

How to Deploy a Big Data Cluster to a Single Node Kubeadm Cluster

This blog post will walk you through deploying a SQL Server Big Data Cluster on a single node Kubernetes cluster.

SQL Server AlwaysOn Availability Groups: Installation and configuration, Part 2

A step-by-step deployment process for AlwaysOn availability groups, using the "New Availability Group Wizard", in SSMS.

Keyset Does Not Exist – Add-ClusterNode

Jonathan Kehayias investigates the cause of a strange error while attempting to add a new node to one of his Windows Server 2016 clusters.


Storage 101: The Language of Storage

Robert Sheldon continues his series on storage. This article covers some of the basics of performance metrics, HDDs, and SSDs.


Computing the future value of an investment based on compound growth in DAX

How to write efficient DAX expressions that compute the compound interest of incremental investments made throughout the holding period.

A small change in DAX Formatter and in DAX formatting rules

Marco Russo proposes a change to a formatting rule in their DAX Formatter, a tool designed to help users format their code based on the established rules.

Performance Tuning SQL Server

TVPs vs Memory Optimized TVPs

Will switching your table types to in memory table types help your application perform better? Possibly, but you need to test it for yourself.

Strongly Type Those Table-Valued Parameters

By their very definition, table-valued parameters are strongly-typed to a user-defined table type that must exist within the database where the call is being made. However, strongly-typed isn't really strictly "strongly-typed" as you would expect, as this article is going to demonstrate, and performance could be impacted as a result.

Why Waits Alone Are Not Enough

If you are dependent on a waits-focused tool for monitoring and tuning your workloads, there's a high likelihood that you are focusing on the wrong queries and missing those responsible for most of the query duration and resource consumption on a system.

Watching SQL Server Stuff From Performance Monitor

A quick query that uses the User Settable counter to monitor something that’s not already exposed as a performance counter, like the progress of a custom task.

SQLskills SQL101: Why are Statistics so Important?

While some aspects of statistics are handled automatically; they’re not perfect, and SQL Server often needs some with certain data patterns and data sets.

Extended Events Misperceptions: Profiler is Easier

Connect, click, boom, too much data!

Where Should You Tune Queries: Production, Staging, or Development?

The #1 fastest way to tune queries is in the production database, on the production server, but it's dangerous, so what are the next best options?

Changing MaxDop For Resumable Index Create Operations

While you can adjust the MAXDOP value during a REBUILD operation, you cannot do the same with a resumable CREATE operation.

Database Compatibility Level and Query Store

What compatibility level does the database need in order to use Query Store? The quick answer: it doesn’t matter.

What Events are in a Trace?

For those still using Profiler and server-side traces, Erin Stellato provides a query to show how the traces are configured, what events are in it, what filters and if the traces are writing to a file or rowset provider.

When Can You Get Away With Non-SARGable predicates?

Short Answer: If you have selective predicates earlier in the index that filter a lot of rows, the SARGability of trailing predicates matters less.


Sort a Column with a Custom Order in Power BI

Sorting a column by another column, using a Sort Order table.

Additional reporting insights using the Get-PowerBIActivityEvent audit logs

On how the new PowerShell script Get-PowerBIActivityEvent allows you to provide additional reporting insights, such as which reports, and dashboards are consumed via an App!

Copy a measure in the desktop – Why it doesn’t always work

To avoid frustration when copying and pasting a measure, use CTRL + A to select all and then press CTRL + C to copy the formula.

The “Visual Has Exceeded The Available Resources” Error In Power BI

Chris Webb explains why occasionally you may find that a report that renders successfully, but slowly, in Power BI Desktop shows the error “Visual has exceeded the available resources” for some visuals when published to the Power BI Service.

R Language

Generate synthetic data using R

If you are building data science applications and need some data to demonstrate the prototype to a potential client, you will most likely need synthetic data. In this article, we discuss the steps to generating synthetic data using the R package ‘conjurer’.

Reporting Services

CRM Data Source Connection Error

This article takes us to the edge with a couple of CRM related errors after changing the service account to a more secure Managed Service Account. Despite the CRM reports working properly within Report Manager (via SSRS), the reports would fail in CRM.

SQL Server Security and Auditing

Changing the owner of a schema removes all direct permissions of objects owned by the schema

Not a lot of people use multiple schemas, even fewer change their owners, most use database level permissions (db_datareader, EXECUTE at the db level etc). So while this is an uncommon issue, it's still one that you should keep in the back of your mind in case it comes up and bites.

SQL Server on Linux

How to Create an Ubuntu PowerShell Development Environment – Part 1

Since SQL Server now runs in Linux, more database professionals will begin to learn this operating system. Robert Cain demonstrates how to set up an Ubuntu virtual machine for learning.


The Dangers of using Float or Real Datatypes

Floating point datatypes accommodate very big numbers but sacrifice precision. They are handy for some types of scientific calculations, but are dangerous when used more widely, because they can introduce big rounding errors.

UnmatchedIndexes – Bug in SSMS

SSMS showplan properties have two attributes to indicate when a filtered index can not be used during a parameterized query. One is ‘UnmatchedIndexes’, and the other one is under ‘Warnings’ attribute. Due to a bug in SSMS, the attributes are also showing positive results with filtered index and non-parameterized queries.

Improper SET Option Errors

SQL Server backwards compatibility SET options are hidden land mines that explode when one tries to use a feature that requires proper session settings, such as a filtered index, indexed view, and others.

Cool Query Hints

While query hints should be avoided as a general rule, Brent Ozar proposes a few that you ought to consider when your query tuning situations get really dire.

OPENROWSET and Quoted Data

When using OPENROWSET for "querying" data in source data files that are external to a SQL Server instance, one of the main challenges is dealing with values enclosed in double quotes.

An Order By Oddity

Asking for ordered data can change a lot of things about a query; indexes used, joins and aggregates chosen, parallelism, and also less obvious things, like memory grants, the type of prefetch used and so on.

An Unfortunate Side Effect Of OUTPUT

I see people using OUTPUT to audit modifications from time to time, often because “triggers are bad” or “triggers are slow”. Well, sometimes, sure. But using OUTPUT can be a downer, too.

Efficient Solutions to Gaps and Islands Challenges

Window functions are useful for solving many SQL queries. In this article, Ed Pollack demonstrates how they can be used to analyse baseball winning streaks.

Changing SET options in a Procedure or Trigger leads to Recompilation

Phil Factor delves into the use of the SET statements within a stored procedure or trigger, which might cause unnecessary recompilations, though the issue extends to other types of batches.

Virtualization and Containers/Kubernetes

Managing SQL Server Docker containers in macOS

Carlos Robles explains continues his series on managing Docker images and containers, exploring in detail the Docker command line client.

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.


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