The Complete Weekly Roundup of SQL Server News

Hand-picked content to sharpen your professional edge

I once asked a Microsoft employee, in some exasperation at the absence from T-SQL of a much-needed function, how the decision was made to add one. He described a byzantine struggle to convince a panel of sage panjandrums within Microsoft, long on academic status but, I imagine, short on experience in the stark reality of developing database applications under pressure.

I suspect that success in swaying the "judgement of the wise" depended more on your persuasive powers in extolling the virtues of the feature, than on the its real value to the end user. The rich variety of ObjectPropertyEx(), for example, seems to be more about the convenience of 'internal' use rather than for the benefit to us.

By contrast, it has taken twelve years of persuasion to get the expedient String_Agg() aggregate function and Translate() into TSQL (SQL Server 2017). We get a trickle of useful functions. The SQL Server 2012 functions for converting between datatypes, Try_Convert(), Try_Cast() and Try_Parse() were very handy, and everyone loves the String_Split() function in SQL Server 2016. All these have been easy to implement, but have taken a long time to get through the decision-making process.

When you look through the new crop of functions that appear in every release, it typically seems to be a mixture of the sublime and gorblimey. I’m sure that some of you use the IIF function rather than the CASE construct it is translated into, but it isn’t exactly exciting. The Choose() function seems to be a lifebelt thrown to a struggling procedural programmer. The Trim() function still doesn’t behave the same way as in any other language.

Who are the sage mandarins of Microsoft that for so long frowned on String_Agg() yet smiled on IIF? I sometimes wonder if they look at the world through some distorting mirror, as they gaze on the misbegotten implementation of JSON (‘where are the lists?’), and smile at a job well done.

We have, in PASS, a professional association of users of SQL Server, with a mission to spread knowledge about the use of the Microsoft Data Platform. Surely, amongst the worthy dignitaries of this august society, there are many who are uniquely qualified to assign a value to a proposed extension to TSQL. To be sure, we aren’t all professors, but we are mostly graduates of the school of hard knocks, with a life at the hard end of SQL Server development, with impossible deadlines, implacable mangers and improbable developers. There is, surely, the necessary talent that we can elect to listen to a pitch for a new function or feature in Transact SQL, and to ace the decision every time.

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.

Virtualization and Containers

Persisting data in docker containers – Part Three - How to persist data using data volume containers, where you create a volume in a container and then mount that volume into another container....(more)

Vendors/3rd Party Products

Extending DevOps practices to SQL Server databases – Branching & Merging with VSTS - Join Microsoft Data Platform MVP Steve Jones for the next Database DevOps demo webinar. He’ll be plugging Redgate’s tools into Visual Studio Team Services and showing how you can branch and merge your database changes. Sign up now....(more)

Free prototype tool to audit SQL Server permissions - Redgate have just released SQL Census, a prototype tool from the Foundry division that makes auditing SQL Server user access permissions much easier. In this post Ally Parker shows how it works, tells us what's up next in the tool's development development, and explains how you can download it for free....(more)

T-SQL

SQL SERVER – ALTER Column from INT to BIGINT – Error and Solutions - Sometime ago I wrote a blog on identity column value jump issue, you can read it from here SQL SERVER – Jump in Identity column after restart! In this blog post we will learn how to resolve the error when we alter column from int to bigint. One of my ......(more)

Converting from DateTime to Ticks using SQL Server - If you do want to convert between datetime2 and ticks inside SQL Server instead of letting the application handle it, then you need to do some date math. It can get a bit tricky avoiding arithmetic overflows and keeping things accurate....(more)

Temporal Tables As SCD2 Tables - Kevin Feasel explains why temporal tables do not make for great slowly changing dimension implementations. ...(more)

SQL Graph Objects in SQL Server 2017: the Good and the Bad - Graph databases are useful for certain types of database tasks that involve representing and traversing complex relationships between entities. These can be difficult to do in relational databases and even trickier to report on. Until now, we have had the choice of doing it awkwardly in SQL Server or having an ancillary database to tackle this type of task. SQL Server 2017 will be bringing graph capabilities to the product but will these features prove to be good enough to allow us to dispense with specialised Graph databases? Dennes Torres decided to find out....(more)

Statistics in SQL: The Mann–Whitney U test - Imagine you have some data - maybe you’ve got a website, and you want to do some A/B testing. Is the new design significantly better than the old one? You need to disprove the ‘null hypothesis’ that the two samples, from those who got the new design and those who got the old one are from the same population. There are several ways that you can test this, but nobody is going to argue with you if you use a Mann–Whitney U test to test whether two samples come from the same distribution, as Phil Factor explains....(more)

The Lighter Side

HASSP Testing Diary #2 - Final testing, a lesson in barometric pressure, and learning to accept potential failure. Here's the second HASSP testing diary. ...(more)

Testing Software

Writing Dynamic and Random Tests Cases for Pester - I have written a module SQLDiagAPI for consuming the SQL Server Diagnostics API with PowerShell. I blogged about how I used Pester to develop one of the functions . Whilst writing Get-SQLDiagFix I wrote some Pester Tests to make sure that the output ......(more)

Tech News : General Interest

Amazon, Apple, Alphabet stock prices go haywire - A routine test of the Nasdaq exchange system, ahead of the US Independence Day holiday, appears to have been performed at a different time on Tuesday and data sent to the likes of Bloomberg, Reuters and Google Finance was accidentally interpreted into real time prices....(more)

SQL Server on Linux

SSoL–Exiting Root - I don’t think most of us need to know Linux, but if you end up managing a system, it’s good to have a little idea of how to get around. This is a short series of posts as I remember the skills I used to have back in university. There are times you need ......(more)

How the SQLCAT Customer Lab is Monitoring SQL on Linux - SQL CAT team explain how they are using InfluxDB, collectd and Grafana to monitor SQL performance in Linux....(more)

HASSP Software: Linux and SQL Server and More - Having looked at all the hardware that runs the HASSP module, we’ll take a tour of our system installation, SQL Server instance, and custom code that makes this all work....(more)

SQL Server News

Microsoft JDBC Driver 6.2 for SQL Server released - This post was authored by Andrea Lam, Program Manager, SQL Server We are happy to announce the full release of the Microsoft JDBC Driver 6.2 for SQL Server. The JDBC Driver provides Java database connectivity from any Java application, application server ......(more)

Software Development

Perf results on scaling Git on VSTS with GVFS - A little over a month ago, I gave an update on our progress of moving the Windows team to Git in the largest Git repo on the planet. At the time, I described the scale of the Windows Git repo (over 3.5M files and ~4,000 engineers), the current performance ......(more)

I Love Entity Framework - I love Entity Framework. I also like (not love) nHibernate. That’s right, as a DBA and data professional, I’m telling you I love Object/Relational Mapping tools (ORM). I think this is a technology set that the DBA needs to more tightly embrace. Let me ......(more)

Morphing the Monolith - Microservices can certainly be made to work well for particular types of applications, but is it relevant to the mainstream? Can it replace the traditional architectures of database-driven applications?...(more)

Security news and thoughts

Password Strength Indicators Help People Make Ill-Informed Choices - Sponsored by: Cybercriminals want to interrupt your business. Be unstoppable with Symantec Complete Website Security. Learn howI watched a discussion unfold on Twitter recently which started like so many of the security related ones I see: This was ......(more)

The 5 Stages of Data Breach Grief - Sponsored by: Cybercriminals want to interrupt your business. Be unstoppable with Symantec Complete Website Security. Learn howWhen you see something play out enough times, you start to notice patterns. I was reflecting on this today as I watched The ......(more)

GoldenEye Malware - I don't have anything to say -- mostly because I'm otherwise busy -- about the malware known as GoldenEye, NotPetya, or ExPetr. But I wanted a post to park links. Please add any good relevant links in the comments. ...(more)

Now It's Easier than Ever to Steal Someone's Keys - The website key.me will make a duplicate key from a digital photo. If a friend or coworker leaves their keys unattended for a few seconds, you know what to do. ...(more)

Reporting Services

SQL SERVER – FIX: Rule “Reporting Services Catalog Database File Existence” Failed - Disasters are always bad. Recovering from disaster is a skill and needs a lot of planning and practice. One of my clients had a disaster and built a new machine to restore the backups. They tried a few attempts to install SQL Server but were having issues. ...(more)

R Language

Using R to illustrate relationships from graph objects - Dennes Torres explores the possibility of using Graph databases in conjunction with R....(more)

PowerShell

Running dbatools commands with VS Code tasks - I’ve started to look at the excellent dbatools.io to automate some of the checks that I routinely perform on my SQL instances. But before I go into this post, I want to say a thank you to Cláudio Silva (t). The powershell commands that are below are ......(more)

Check Instant File Initialization with PowerShell - In this article I show how to use the Where method with the Split option to quickly assign two variables. As an example we will check if our SQL Service Accounts have the Instant File Initialization Privilege....(more)

PowerPivot/PowerQuery/PowerBI

Power BI Custom Visuals Class (Module 58 – Drilldown Choropleth) - In this module you will learn how to use the Drilldown Choropleth Custom Visual. The Drilldown Choropleth is a map visual that displays divided regions that are highlighted indicating the relative value in each location. Module 58 – Drilldown Choropleth ...(more)

PowerApps – Building Timesheet App without Coding – Part 1 - There may be times that you or our team members are spending a lot of time managing tedious administration tasks and company data. Chances are you may have thought of how documentation automation will boost efficiency in your organisation, facilitate ......(more)

Integrate Power BI into Your Application: Part 5 – Data Source Management - New REST API of Power BI is revolutionary. In addition to embedding content in Power BI, ability to refresh data set from API, it also gives you many functions to work with Gateways and Data Sources. With this API, you can set up new data sources, clone ......(more)

Fixing Time Calculations in Power BI - In this video, Patrick shows why you may hit some issues with time calculations and how to fix time. He typically sees this when using a Data Warehouse. The post Fixing Time Calculations in Power BI appeared first on Guy in a Cube. ...(more)

Data Privacy Settings In Power BI/Power Query, Part 4: Disabling Data Privacy Checks - How you can disable data privacy checks and the pros and cons of doing so....(more)

Performance Tuning SQL Server

Balanced Power Saving T-SQL script - We can easily spend tens of thousands of dollars on core licences for SQL Server, and then we go and install the product on an operating system with the default Balanced Power Plan, which is, well, idiotic....(more)

A Better Way To Select Star - For those time when you really have to select star, Erik Darling has a way to defer the pain of scanning the clustered index until you’ve cut results down to a more reasonable bunch of rows, and in a way where you don’t have to rely on the optimizer choosing a Key Lookup plan, or forever forcing one where it might not be helpful....(more)

New Stack Overflow Public Database Available (2017-06) - Nick Craver and the kind folks at Stack Overflow publish their data export periodically with your questions, answers, comments, user info, and more. It’s available as an XML data dump, which I then take and import into SQL Server for teaching performance tuning....(more)

“What are you waiting for?” – Introducing wait stats support in Query Store - You can access wait stats information in your Azure SQL Database and SQL Server 2017 starting from CTP 2.0, using T-SQL....(more)

NoSQL

Mongodb – Iteratively Access Data Across all Databases and Collections - Often times I get to work on both SQL and NoSQL database technologies as our data is stored across multiple database platforms. This tends to be very interesting for me especially as a blogger, this helps me to analyze the differences that set apart ......(more)

Microsoft News

The Microsoft Most Valuable Professionals (MVP) Program - Microsoft Most Valuable Professionals are passionate technology experts who share their knowledge with the community. They are always on the “bleeding edge” and have an unstoppable urge to get their hands on new, exciting technologies. They have very ......(more)

Microsoft to lay off 'several thousand' employees - Microsoft is cutting 'several thousand' employees, mostly in its sales organization, following a reorganization earlier this week. ...(more)

Microsoft changes behavior of OneDrive on non-NTFS drives - Microsoft has changed the way OneDrive allows users to store files, but the sudden 'remedy' has left many unpleasantly surprised. ...(more)

Hardware News

Review: The AmpliFi HD (High-Density) Home Wi-Fi Mesh Networking System - I've been very happy with the TP-Link AC3200 Router I got two years ago. It's been an excellent and solid router. However, as the kids get older and the number of mobile devices (and smart(ish) devices) in the house increase, the dead wifi spots have ......(more)

HA/DR/Always On/Clustering

sp_AllNightLog: ¿Por que los queues? - Building stuff I sometimes really hate coming up with ideas, and much prefer someone to just say “I want this” so I can go out into the world and figure out how to do that. Occasionally though, I realize that I’m not going to get all that much direction. ...(more)

Building Always On Availability Groups - Part 1 - IntroductionBy David PostlethwaiteEnsuring that your databases can survive a major outage such as a server failure and can continue to work is an important task of being a DBA.Database Mirroring was introduced in SQL 2005 and was quite straight forward ......(more)

Upgrading our Test lab from SQL 2016 to 2017 CTP with Always On Availability Groups - At SQLUndercover we decided it would be a good idea to not only upgrade our test lab but to treat it as if it were a live system carrying out an in place upgrade with minimal downtime, here’s how we did it....(more)

ETL/SSIS/ELT

Creating the SSIS Catalog - If you are building SQL Server Integration Services (SSIS) packages, using the SSIS catalog as a deployment target is usually the easiest and most efficient solution. However, the SSIS catalog is not created by default, even when you select the SSIS ......(more)

DevOps and Continuous Delivery (CI/CD)

A really COOL feature we noticed on VSTS – New Release Definition Editor - The feature was introduced in New Release Definition Editor in Team Services. It’s therefore not really a new, but a cool feature that we explored and fell in with. Looking back – this is how one of our pipelines look when viewed in the current (old) ......(more)

DevOps is Ruining the DBA? - The truth is, the DBA is not ruined by DevOps, but the role is revolutionized....(more)

Data Science

Periscope Data | Automated Identification and Graphing of SQL Dependencies - Or how I brushed up on Python, GraphViz, and some clever RegEx. ...(more)

Data Access / ORMs

I love Entity Framework - I love Entity Framework. I also like (not love) nHibernate. That’s right, as a DBA and data professional, I’m telling you I love Object/Relational Mapping tools (ORM). I think this is a technology set that the DBA needs to more tightly embrace....(more)

Conferences and Events

Announcing the 2018 PASS Executive Committee - Grant Fritchey is the new President of PASS....(more)

Computing in the Cloud (Azure, Google , AWS)

Compliance on Azure Government - In this episode of the Azure Government video series, Zach Kramer talks with Matt Rathbun (CISO, Azure Government) about compliance in Azure Government. Matt and Zach discuss the various compliance regimes offered by Azure Government including FedRAMP ......(more)

Azure SQL Data Sync Test Drive and First Impressions - Azure SQL Data Sync is a service of Azure SQL Database that enables you to synchronize relational data you select across multiple cloud databases and SQL Server instances, in uni-directional or bi-directional way. This service is already in public preview, ......(more)

Azure CosmosDB

Walkthrough of Azure Cosmos DB Graph (Gremlin) - I show you the general tasks of Azure Cosmos DB Gremlin (graph) for your first use, and a little bit dive into the practical usage of graph query. This post will also help the beginner to learn how to use the graph database itself, since Azure Cosmos ......(more)

Analysis Services / BI on the MS Stack

The ALL() Function in DAX - In this article I am going to write about the ALL() function, show how it works and explain a common use case. I will also cover the unexpected behaviour that occurs when using the ALL() function in Power BI Desktop...(more)

Administration of SQL Server

SQL SERVER – The Patch Installer has Failed to Update the Shared Features - Applying a patch in SQL Server is a planned process which is followed across various companies. Most of the companies take time to apply the patch to make sure the stability of the patch can be validated. Let us see an error related to patch installer. One ......(more)

SQL Server Diagnostic Information Queries for July 2017 - This month, there are more minor updates to the all of the versions of the queries. I usually make additional minor updates to the queries during the month, so if you are in doubt, downloading the latest version is always a good idea. Rather than having ......(more)

Toolbox - Where Did All My Space Go? - This is the first in a new series of blogs I am going to create talking about useful tools (mostly scripts) that I use frequently in my day-to-day life as a production DBA. I work as a Work-From-Home DBA for a Remote DBA company, but 90+% of my job ......(more)

How to Automatically Purge Historical Data From a Temporal Table - Temporal Tables make analyzing time-series data a cinch, and because they automatically track row-level history, rolling-back from an “oops” scenario doesn’t mean you have to pull out the database backups. The problem with temporal tables is that they produce a lot of data. ...(more)

SQL Server Diagnostics Extension for SQL Server Management Server - Tim Ford previews the new SQL Server Diagnostics extension within SQL Server Management Studio, which will allow you to upload a dump file for root cause analysis, and to view any publicly available Knowledge Base (KB) articles existing that match the identified issues in the dump file. ...(more)

Index Rebuild, Reorganize, and Statistics in Maintenance Plans in SQL Server 2016 - Kendra Little shares what she's learned about the Rebuild Index Task, Reorganize Index Task, and Update Statistics Task in SQL Server 2016....(more)

DB-Engines Database Rankings - The DB-Engines Database Rankings ranks database management systems according to "popularity". Thomas La Rock explains what that means and sums up the latest findings and trends....(more)