The Complete Weekly Roundup of SQL Server News

Hand-picked content to sharpen your professional edge

At some point, early in the development of an application, a prototype is usually created. Prototypes are useful for getting early feedback from the users, and to make user-interface problems more obvious. We used to call these prototypes 'clays', after the practice of the car industry of creating a facsimile made from clay to showcase the design of a new car design to the general management.

It takes a lot of care to make a good prototype; it must look convincing. The clay cars had to look so real as to make you believe that you could hop right into it and drive off. Drawings or wireframes just don't serve as well because the observer cannot make that imaginative leap.

With data-driven applications, the verisimilitude includes the data. A prototype application should have data in it that is so close to real data that even the dullest, most literal-minded manager could look beyond the detail, to the important matters.

This isn't easy, as I have found to my cost in the past. I was once demonstrating a prototype application to a group of managers at a manufacturing plant. It looked the part, and the managers stared in awe at the screen, as if hypnotized. Some were fascinated to see how all those tedious and inaccurate manual processes could be so easily eliminated.

"It is a great application. When can we have it?" one of them asked, suddenly.

I smiled indulgently. "I'm glad you like it, but of course this merely demonstrates how the application would appear, once we'd spent all the necessary months of work to turn the prototype into reality"

He turned glum, and scowled, brooding on the slowness of IT in "finishing anything off". I continued with the demonstration and it was going pretty well, when one of the quietest of the managers suddenly interjected.

"No, there is something really wrong here!"

There was a ghastly pause.

"In that previous screen, you showed a message from the superintendent saying that we were running short of tuning relays. And yet, in this screen, that fact hasn't been ratified by the plant manager to turn into an order!"

It seemed that some were struggling to understand the nature of my mysterious conjuring trick. "Well, as I say, this is a prototype. It only illustrates the processes and the way we envisage the user-interface!"

A frisson of diffuse anxiety ran through the ranks of the audience. Having captured his audience, the guy was unstoppable.

"…and we just don't handle flexible grommets that way either, on a production line, because these are sourced from another part of the company!"

"Quite; there are many details to tidy up, and processes to analyze, which is why we are facing several months work"

"Well why didn't you find them out first before you showed us this?!"

It was the fury and disbelief of hearing that Santa isn't real. I lost the audience. After this outburst, several others felt impelled to point out that they too had noticed irrelevant inconsistencies in the mock data.

I am not sure what the moral of the story is, beyond stating that prototype applications need the same care as prototype cars, or mobile phones, in getting as close as possible to reality. You can't use real data, but the care you take with making it convincing will enable you to survive a brush with even the most literal minded manager.

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.

Webinars

Free webinar: Database DevOps demo - Microsoft MVP Grant Fritchey, and Redgate’s Arneh Eskandari, will show you how Redgate’s Database DevOps solution works to improve your database development and deployment processes. You'll see a demo of tools from Redgate's SQL Toolbelt and see how they plug into GIT, Jenkins and Octopus Deploy. There will also be plenty of time to ask Grant and Arneh questions....(more)

Vendors/3rd Party Products

Finding code smells using SQL Prompt: procedures that lack schema-qualification - SQL Prompt implements a static code analysis rule which will check code automatically via the EXECUTE command, without the schema being specified....(more)

T-SQL

Dynamic SQL can present a Security Problem - Eric Blinn rewrites a stored procedure to use dynamic SQL but finds that when you call your statement through sp_ExecuteSQL the permissions are lost....(more)

T-SQL Tuesday #98 – Your Technical Challenges Conquered - Arun Sirpal hosts T-SQL Tuesday and challenges participants to describe a difficult technical problem that they conquered, their troubleshooting mind-set, and what they did to overcome the challenge....(more)

TSQL Object Names can be Wrong, even If sp_rename was never used - While it is documented and known that sp_rename does not update the object name in the stored string definitions for Stored Procedures, Triggers, Functions, and Views, it is neither documented nor known that changing the Schema for one of these types of objects also leaves the definition unchanged....(more)

SQL Server Security

Understanding the Meltdown exploit – in my own simple words - Klaus Aschenbrenner explains how the Meltdown vulnerability works....(more)

How to Check if your Database Server is Protected from Meltdown and Spectre - Microsoft has made available a Powershell script to use to see if your system is protected. Tom LaRock shows you exactly what to do in order to check....(more)

SQL Server News

Cumulative Update #3 for SQL Server 2017 RTM - The 3rd cumulative update release for SQL Server 2017 RTM is now available for download at the Microsoft Downloads site....(more)

Reporting Services

Mobile Report Publisher – Dashboards Everywhere - The new feature, Mobile Reports, was added to SSRS in 2016. It provides the ability to create attractive and useful dashboards that can run on devices and most browsers. Robert Cain shows you how to get started building Mobile Reports with the Mobile Report Publisher....(more)

Replication

Simulating Bad Networks to Test SQL Server Replication - SQL Server replication works well on a low latency, high bandwidth network. When actual networks have high latency or limited bandwidth, problems may occur. You can quickly set up a test environment to emulate high latency and limited bandwidth by using free, open source software such as WANem (Wide Area Network emulator)....(more)

Python in SQL Server

Get more out of Python on SQL Server 2017 - Gerald Britton presents some common use cases for Python in SQL Server 2017, including regular expressions and handling CSV formatted data....(more)

PowerShell

PowerShell Core 6.0: Generally Available (GA) and Supported - PowerShell Core 6.0 is a new edition of PowerShell that is cross-platform (Windows, macOS, and Linux), open-source, and built for heterogeneous environments and the hybrid cloud....(more)

PowerShell Regex Example: Incrementing Matches - Say you have a string that reads “first thing {} second thing {}” and you want to get to “first thing {0} second thing {1}” so that you can use the -f operator to insert values into those spots? ...(more)

PowerPivot/PowerQuery/PowerBI

“In the Previous” Date Filters In Power BI/Get&Transform/Power Query - Chrsi Webb demos what the built-in relative date filters actually do and how you can get change them to do something more useful....(more)

Crushing Your Goals with Power BI: Getting Started - How to use PowerBI for tracking progress towards personal or business goals....(more)

SQLCMD Mode (Day 8) - Wayne Sheffield is writing a series of SSMS tips and tricks. Here, he explains SQLCMD mode and how to use it to run different parts of a script won different instances of SQL Server (for example, to set up database mirroring)....(more)

Comparing two SQL instances - A quick and easy way to check that two instances that were part of an Always On availability group each contained the same databases....(more)

Performance Tuning SQL Server

SQL Server 2017 CU3 adds tempdb spill diagnostics in DMVs and Extended Events - Whenever I’m teaching people about query tuning, two things come up almost without fail: parameter sniffing and tempdb spills....(more)

Statistics and PowerBI Visualizations, Part Two - How differences in the Cardinality Estimators on SQL Server versions can cause differences in the execution plans and affect the resources needed....(more)

Statistics Use, Extended Events and Execution Plans - When your index isn’t being used, how do you tell how or why something else is being done? Well, that’s largely down to row counts which brings us to statistics....(more)

Hardware Testing

Performance Effects of Meltdown and Partial Spectre Fixes on Intel Core i7-7500U Laptop - On his laptop, Glenn Berry observes a 15.1% reduction in single-threaded CPU performance and a 2.8% reduction in multi-threaded CPU performance....(more)

Intel Releases Meltdown, Spectre Patch Benchmarks - Intel has released numbers from its own benchmarks on the impact of Meltdown/Spectre patches on Windows, and the results show an up to 21% decrease in benchmark performance for 6th-gen Intel CPUs (Sky Lake)....(more)

Quick performance check with latest patches - From the description given of the fixes, my expectation is that performance impact of patching the Meltdown and Spectre bugs will be seen in operations that involve kernel, which include disk and network IO. I would except loop joins and key lookups to be more impacted that scan IO because of the number of IO....(more)

ETL/SSIS/ELT

ETL Instrumentation: Logging SSIS Variable Values - Is there an SSIS Catalog Logging Mode that will display the value of variables? Yes, but you'll have to open existing packages, open Variables, check the checkbox for the property, and then change the property value – for every. single. variable. Unless you use a Script Task....(more)

DevOps and Continuous Delivery (CI/CD)

100 Percent Online Deployments: Keep Changes OLTP-Friendly - Using the Blue-Green deployment method, database changes are decoupled from applications changes. That leaves us with one last challenge to tackle. The schema changes have to be performed while the application is online. It’s true that you can’t always write an online script for every kind of schema change you want....(more)

Automated Developer Machine Setup - Part II - Developer Tool Suite - How to automate the install of a common developer tool suite, and why it's important....(more)

100 Percent Online Deployments: Blue Green Details - Blue-Green Deployment means replacing old blue things with new green things as an alternative to altering things. But Blue-Green doesn’t work with databases, so Michael J Swart explains the Blue-Aqua-Green method, which helps keep databases and other services online 24/7....(more)

The 2018 State of Database DevOps report - In this year's survey, over 700 IT professionals across a range of sectors, in organizations of every size around the globe were asked about their plans to adopt DevOps, how they thought the database fitted into the picture, and what they thought the biggest challenges were....(more)

The top 7 business benefits of establishing DevOps in your organization - Any change within an organization requires an investment of time, expertise and money. Therefore, it is natural that leaders should assess the potential of DevOps in terms of what it will deliver: Managers should ask the question: What is to be gained by changing our current way of doing things?...(more)

Database Design, Theory and Development

Understanding Relational Keys - A New Perspective: Primary Keys - There is a general and persistent lack of foundation knowledge in the industry and keys are not an exception. 70% of searches hitting this site are about keys, just one indication that this fundamental relational feature is poorly understood decades after the RDM....(more)

It’s All in the Name, Index Naming Conventions - Taking a little time to name things appropriately can go a long way, it can not only be time saving but can help to reduce redundancy....(more)

Data Warehousing

What is a cloud data warehouse? - Amazon Redshift was announced in November of 2012 and became the first cloud data warehouse, opening a whole new segment of technology. What is a cloud data warehouse exactly?...(more)

Data Visualisation

Know Your Data - Your dashboard’s users are going to want answers to particular questions. Knowing those questions and what they’re expecting is critical to building a good dashboard. And that doesn’t mean coming to them with a data dictionary and expecting them to put all the pieces together....(more)

Know Your Audience - Before you build a dashboard, make sure you understand your users, and how they need to use it....(more)

Data Science

DevOps for Data Science – Release Management - Release Management (RM), as a concept, is essentially what it says – determining a method of releasing new and changed software into an environment in a planned fashion. While this sounds simple, it actually takes quite a bit of forethought and planning, and involves not only the technical teams, but several business teams as well....(more)

Data Mining/Data Analysis

Context Transition and Filters in CALCULATE - This article explains how the context transition interacts with the filter arguments of a CALCULATE function in DAX. This is important in order to avoid unexpected results with complex calculations made in filter arguments....(more)

Computing in the Cloud (Azure, Google , AWS)

Microsoft Azure Threat Detection Types - Thomas LaRock explores the threat detection types related to SQL Injection, in Azure's Audit and Threat Detection feature...(more)

Columnstore Indexes

Columnstore Indexes – part 118 (“SQL Server 2017 Editions Limitations”) - Niko Neugebauer explores limitations in ColumnStore support in the Standard and Express Editions of SQL Server 2017. ...(more)

Career Growth

The 2018 Data Professionals Salary Survey Results - How much do database administrators, analysts, architects, developers, and data scientists make? We asked, and 3,113 of you from 73 countries answered this year....(more)

Administration of SQL Server

How to Size Your Database Files? - Let’s say you need to create a new SQL Server database, which is going to grow very fast, but you don’t know how fast. What would be the initial size of the data file?...(more)

SQL Server Configurations – Back to Basics - Adopting the mentality that “some assembly is required” with your environments is a good approach. It will help keep you on top of your configurations....(more)

45 New Trace Flags - Joe Obbish offers a list of 45 undocumented trace flags, with brief descriptions (sometimes guesses) of their behavior. ...(more)

Common Tempdb Trace Flags – Back to Basics - Jason Brimhall introduces default behaviors and settings in tempdb along with some best practices....(more)

In-Memory OLTP: Detaching/Attaching from one server to another - If you need to move an in-memory database moved from one server to another, you might not be able to just copy the files over, along with the Filestream folders, and attach....(more)

.NET Related Articles

Webinar - I/O Reduction Software - Improve SQL Performance - We are delighted to be co-hosting this webinar again with Condusiv, discussing how their I/O reduction software significantly improves SQL performance on 25 January 2018. ...(more)