The Complete Weekly Roundup of SQL Server News

Hand-picked content to sharpen your professional edge

A good IT all-rounder can usually write clearly in a logical sequence, and can do it in such a way as to prevent the reader going into a system shutdown. It is as important a skill in any team as being able to place a sticky note on a whiteboard, or write an application installer.

While writing isn't a much-celebrated skill in IT, it is a valuable one that comes into play in a variety of teamwork. Systems that are clearly and reasonably documented are easier to maintain. The better you can explain a technology, the more likely others will adopt it, or appreciate the effort you took to master it.

I spend a lot of time these days reading IT documentation and articles and have noticed the recurring faults that always seem trip up the writer, and then subsequently confuse and frustrate the reader

Getting the sequence muddled up. Your train of thought must be reasonably obvious, without leaving steps out. Your statements must be in the right order.

Lapsing into jargon. When you are working closely in a team, everyone tends to lapse into a shared private language that is intelligible to team members but baffling to outsiders. In larger groups we gravitate towards Three-letter Acronyms (TLAs) that, if you use them to devoted relatives, or friends in the pub, will result in you talking to yourself. You must never assume that readers will be familiar with your private language. Managers are unlikely to be

It's all about 'me' or ('we'). Alas, if you have read too many literary narratives about one person's struggle towards spiritual growth, or trans-continental road-trips, you will involve yourself in the narrative; "I/we struggled with the interface, and spent many sleepless nights understanding the technology." No. In moderation, perhaps, but you must stand back and just write about the technology, rather than your personal interaction with it.

Using formal 'textbook' language. Written language is a compromise with spoken language, but when writing technically, many writers lapse into an oddly formal brand of textbook language, where everything is expressed in the passive tense. Cats don't sit on mats, but rather mats often contain a sedentary cat. The English language offers several ways of expressing a thought. We can use the clear subject-verb-object sentence of middle English when speaking to others, choose instead the flowery Norman-French if we need to sound dignified and we even sometimes adopt the Latin if we need to give the impression of spiritual intelligence. It is best to write simply, in plain English, but in a conversational way that sounds natural if read aloud.

Using Industry clichés. Clichés often seem to be the correct way of saying things, simply because they are used so frequently. To this, we add the industrial patois, based on military slang, with excruciating phrases such as 'heads-up', 'ameliorating concerns', 'the learning curve', 'pushing the envelope', 'rocket science', 'going forward', 'zero-sum game', 'the chicken-and-egg situation', and 'win-win situation'. Again, stick to plain English.

ThingammyJigs. In IT we often run short of a suitable vocabulary because the technology is so new. Coopers, for example, aren't lost for words for describing how casks and barrels are made. All such long-established technologies have a technical word for everything. We use words like 'environment', 'situation' or 'scenario' when we are lost for words. Some good IT glossaries are maintained, including one for SQL Server, and it is best to use the right terms where possible.

Phil Factor.

Phil Factor from SQLServerCentral.com

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

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. These headlines are gathered throughout the week and are posted in real time at the website. Check there for information throughout the week or enjoy this weekly summary of the SQL Server world.

T-SQL

When Stored Procedures say they Alter Schemas of the Target Table - INSERT EXEC failed because the stored procedure altered the schema of the target table. Shane O'Neil investigates....(more)

Adaptive Queries in Standard Edition: Interleaved Exec for Multi-Statement TVFs - My memory told me that the new Adaptive Joins feature in SQL Server 2017 was Enterprise Edition only… and that’s correct, but I didn’t realize that the fancy new feature to make Multi-Statement TVFs smarter has much wider licensing....(more)

Shortcut: Navigate as you type in sorted SSMS Object Explorer Details pane - Greg Low shows a simple way to navigate to the right table in the Object Explorer Details panel of SSMS....(more)

Are There Any System Generated Constraint Names Lurking In Your Database? - Names for constraints are optional meaning that if you don’t provide a name when it’s created or cannot afford one, one will be appointed to you by the system. These system provided names are messy things but how do you know whether you have any?...(more)

The Uni-Code: The Search for the True List of Valid Characters for T-SQL Regular Identifiers, Part 1 - Why do some characters work for parameter names and others do not? Solomon Rutzky wants a definitive list of “letters” and “decimal numbers” that you can use....(more)

Dealing with date and time instead of datetime - Data professionals don’t always get to use databases that have an optimal design. For example, when your datetime (or datetime2, or better still, datetimeoffset) column is actually two columns – one for the date, and one for the time....(more)

Just What Exactly Will Cast to a Bit Value? - Which dataypes and values will cast to a bit? More than you might imagine. The numeric values that will cast to a bit are voluminous (even some that are in string format). ...(more)

Sort SQL Server tables into similarly sized buckets - You need to perform some form of maintenance on all of the tables in SQL Server and one table at a time is going to take too long. Bill Fellows suggests parallel tasks, each one working on a set of tables with a similar total row count....(more)

Tech News : General Interest

Facebook: Personal Data of 87 Million People Exposed - Mike Schroepfer, Facebook's chief technology officer, dropped a bombshell yesterday: Facebook might have "improperly shared" the data of up to 87 million people with Cambridge Analytica, the U.K.-based political consulting firm that worked for President Donald Trump's 2016 election campaign. ...(more)

SQL Server Security

Encrypting SQL Server Database Backups - Sometimes we go to great lengths to make sure that our databases are secure, but then neglect the security of our backups....(more)

PowerPivot/PowerQuery/PowerBI

M vs DAX: Chopping Broccoli vs Planning a Menu - One of the challenges with learning Power BI, is that you have to learn not 1, but 2 new data manipulations languages. And it’s not always clear what they are good for, especially if you come from the SQL world....(more)

Performance Tuning SQL Server

Column Store Deadlocks: Missing Information - While poking around with things recently, I created a pretty typical deadlock on a table, but this one had a clustered column store index on it. Of course, that makes things different....(more)

sp_updatestats2 - SQL Server has a problem with the way statistics are sampled. Indexes in which the lead column is not unique can be adversely affected. Joe Change's version of the sp_updatestats procedure marks indexes which are either not unique or has more than one key column for full scan statistics....(more)

Troubleshooting SQL Server Scheduling and Yielding - Simon Su needed to find out how long a SQL server worker thread has been running on a scheduler without yielding, and why....(more)

Filtered Stats and CE Model Variation - A model variation is a new concept in the cardinality estimation framework 2014, that allows us to easily turn on and off some model assumptions and cardinality estimation algorithms. ...(more)

Sys.dm_db_tuning_recommendations Makes Suggestions if Automatic Tuning isn’t Enabled - I had assumed that recommendations would only show up in sys.dm_db_tuning_recommendations if I’d enabled automatic tuning for the database. I hadn’t even thought to test looking at the DMV if Query Store was set up but Automatic Tuning was disabled....(more)

Building SQL ConstantCare - Brent Ozar shares some interesting insights gleaned from the data collected by customers who use SQLConstantCare and opted in to public sharing....(more)

Obituaries

Goodbye, SQL Soldier - We lost Robert L Davis this week, unexpectedly. I’m far too short on words today, so I’m going to borrow from Kendra Little:...(more)

The Generous DBA - The conversation came out of nowhere, lasted only a few seconds, but impacted me deeply....(more)

Remembering Robert Davis, aka @SQLSoldier - I woke up early on Tuesday with a hundred things to do and plenty of energy to match my task list. By noon, I’d made a loaf of bread, helped a friend solve a tech mystery, and had various professional adventures....(more)

HA/DR/Always On/Clustering

Lesson learned from an Availability Group performance case - One of my customers implemented a very high workload synchronous AG (Availability Group) solution using the in-memory technology, but they found a very strange behavior in transaction processing of SQL Server....(more)

Graph Databases

How to support your organisation with Azure Cosmos DB Graph (Gremlin)? - The HR department need to query the mentor/mentee structure from the organization while the resourcing department, need to find the most suitable and available consultant to start a new project next week. In this blog I’ll demonstrate how to achieve both requirements using Azure Cosmos DB Graph....(more)

Data Access / ORMs

Spinlocks and You - Spinlocks are a building block of concurrent programs. As long as you have more than one actor in your system, you’re going to need to be able to control access. We use spinlocks to maintain mutual exclusion – if process 1 is changing something in memory, we want to prevent all other processes from doing so....(more)

Computing in the Cloud (Azure, Google , AWS)

Changing the port for SQL Server in Azure Container Services - Running SQL Server in Azure Container Services (AKS) does mean exposing a port to the internet to allow connections, so leaving SQL Server listening on the default port can be risky....(more)

Backup and Recovery

SQLskills SQL101: Why does repair invalidate replication subscriptions? - There are two ways that replication can be affected: repairs on replication metadata tables, and repairs on anything else to do with a subscription....(more)

Azure SQL Database

Automatic Index Management in Azure SQL Database - Automatic Index Management will manage indexes in your Azure SQL database. Specifically, it can create indexes that are missing, and it can remove indexes that are not used, and those that are duplicates. Let’s take a look at how this occurs....(more)

Azure SQL Database – Failed to Delete Database Nightmare - Arun Sirpal confesses to an errant response to a "failed to delete database" error, then explains why the error occurred and the right way to deal with it....(more)

A new better way to buy Azure SQL DB - Microsoft has announced that there is a new way to buy Azure SQL DB. If DTUs aren’t making sense to you, you’ll be happy to know that you can now simply select how many vCores you want for your SQL DB workload. ...(more)

Monitoring Azure SQL Database with Azure SQL Analytics - Esat Erkeç walks through the basics of SQL Analytics to measure and monitor Azure SQL databases and elastic pools....(more)

Administration of SQL Server

Procedure to Create New Filegroups and Files - A stored procedure that offers six different ways to create files and filegroups, based on your needs. ...(more)

Central Management Server Folder List - We use a CMS server for each domain and I can't imagine life without it. The real magic of a CMS comes from being able to push jobs, or evaluate policies, on any server (targets) you want....(more)