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

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

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.

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,

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.

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.

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

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.

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.

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 the SQL Server ODBC Driver names and platform (32 bit|64 bit), to check application compatibility .

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.

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.

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

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

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

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

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

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

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.

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

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.

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.

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.

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.

Connect, click, boom, too much data!

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?

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

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

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.

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

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

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!

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

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.

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

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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