The Complete Weekly Roundup of SQL Server News

Hand-picked content to sharpen your professional edge

In the last-minute panics before GDPR came into effect, few of us turned our attention to the many websites of the European Union. Surely, they are shining beacons of compliance, with their own sensible range of requirements? Well, security companies who have investigated the EU's own websites have been surprised to find several examples of non-compliance.

The European Union has, on its 'official website', leaked the personal details of hundreds of private individuals who have attended its workshops, events and conferences. Although consent to store the details was obtained in some cases, this isn't recorded in most, and the information has been retained amongst the mass of 16.5 million pages for up to ten years.

It is not the only EU-related site that hasn't yet done the necessary work, according to Indivigital. Cedefop, for example, the European Centre for the Development of Vocational Training, serves third-party scripts, cookies and content without the users' explicit content, yet not meeting the criteria for exemption, contrary to Regulation 6.

Anything like this would constitute a breach of GDPR had any other organization done it. When asked why, an EU spokesman evidently reported to the Telegraph that the European Commission was 'separate' from the data protection regulations for 'legal reasons' and that their law did not come into effect for them until this autumn.

Those of us who work with organizations to explain the necessity for the GDPR, and who try to reassure the data people who are in the firing line that their work is important, will probably be as irritated as I am about the EU's non-compliance. Technically, there may be a loophole that gives them more time to put their house in order, but by exploiting the loophole for its own convenience, the EU broadcasts the wrong message.

Just the other day, I was advising a charity about what they needed to do, stressing the work was necessary to provide a fair and common basis for the correct handling of personal data. They bought into the idea that it provided a legal backing for common courtesy and decency, in ensuring the privacy of the ordinary person. Yet there, on the dark side, is the EU, in whose name these necessary standards have been enforced, seemingly carrying on as if it didn't really matter. One suspects that they secretly eat bananas of 'abnormal curvature' too.

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.

Vendors/3rd Party Products

Building reusable table build scripts using SQL Prompt - You’ve been working on a query, function or procedure and you want to store the results of executing it in a table; maybe a table variable, or a temporary table. These all require column definitions, and such things can be tiresome to do. Phil Factor demonstrates how to use SQL Prompt to do most of the mindless tasks that you’d otherwise be obliged to undertake by hand....(more)

Using SQL Data Compare to Synchronize Custom Error Messages - In this recent Redgate article Steve Jones provides a useful trick to Synchronize Custom Error Messages using SQL Data Compare...(more)

T-SQL

Behind Every Trivial Plan Is A Good Demo - Erik Darling on the complexity that can hide just a couple search phases beyond a Trivial plan....(more)

Mysterious Forwarded Records - When people think about Heaps and the problems they can cause, they don’t often think of temp tables and table variables as Heaps....(more)

Row Width Impact on Version Store Usage under Snapshot Isolation - The impact of Snapshot and Read Committed Snapshot Isolation on the version store isn’t simply on the number of updates and deletes run. It will also vary based on the width of your rows — how many columns are in the table, what data types the columns have, and what data you have in the table....(more)

How to Group By “Nothing” in SQL - The SQL standard knows a lesser known feature called GROUPING SETS. One particular side-effect of that feature is that we can group by “nothing” in SQL. ...(more)

The Importance of ORDER BY - Everyone, at the beginning of their SQL career, get’s told that it is important to include an ORDER BY if they want the results ordered. Otherwise the order in which they are returned is not guaranteed. But it's easy to forget....(more)

Triggers vs. Default Constraints: Choose Wisely - Don't use an AFTER INSERT trigger if a DEFAULT constraint will do the job....(more)

Performing Data Changes Audit Using Temporal Table - SQL Server 2016 introduced a feature called ‘System versioned temporal table’. Whereas with a normal table, you can retrieve current data, using a system-versioned temporal table, you can retrieve data which was deleted or updated in the past....(more)

Importance of Statistics - Monica Rathburn shows where to find information about what your statistics contain and the impact of over and under estimations caused by stale or missing statistics, or even data skew. ...(more)

SQL Server Security

Lost that SQL Server Access? - If you've administrative access to Windows but have lost access to SQL Server, Jason Brimhall's trick will take you from no access to a sysadmin in a matter of seconds....(more)

HASHBYTES Scalability - Joe Obbish observes that the HASBYTES function could see significantly improved scalability if it wasn’t necessary to allocate and deallocate a page for every execution of the function....(more)

SQL Server News

Cumulative Update #9 for SQL Server 2016 SP1 - The 9th cumulative update release for SQL Server 2016 SP1 is now available for download at the Microsoft Downloads site....(more)

Cumulative Update #1 for SQL Server 2016 SP2 - The 1st cumulative update release for SQL Server 2016 SP2 is now available for download at the Microsoft Downloads site....(more)

SQL Updates Newsletter – May 2018 - Recent Releases and Announcements, Troubleshooting and Issue Alerts, Blogs, Articles and more....(more)

R Language

Taking Screeenshots of Webpages using R - Programmatically taking screenshots of a web page is essential in a testing environment....(more)

Native scoring in SQL Server 2017 using R - Native scoring is a much overlooked feature in SQL Server 2017 (available only under Windows and only on-prem), that provides scoring and predicting in pre-build and stored machine learning models in near real-time....(more)

PowerShell

Stuck on older versions of sql server? check out our trace commands - If you’re still using super old versions of SQL Server and don’t have access to awesome XEvents, then dbatools has some commands to help simplify trace management....(more)

Finding Parameters that do not match Column Names - How can I search for any parameter that does not match the column name, against many different parameters and many different databases? Easier than you might think with dbatools' Find-DbaStoredProcedure, and a RegEx....(more)

New in PowerShell 6: Positive And Negative Parameter Validation - In PowerShell 6, there’s something new and cool you can do with ValidateRange. You can specify in a convenient new syntax that the value must be positive or negative....(more)

PowerPivot/PowerQuery/PowerBI

Choosing a Color Palette For Your Power BI Report - Color is a powerful attribute in data visualization. In a good visualization, it can focus attention and enhance meaning and clarity. When color is used poorly, it creates clutter and confusion....(more)

Using Email Attachments As A Data Source In Power BI - If you get the source data for your Power BI reports – usually Excel or csv files – as an email attachment, then if you are using Exchange or Exchange Online Power BI connect directly to the attachment without you needing to download it....(more)

DateAdd vs ParallelPeriod vs SamePeriodLastYear - What is the difference between using the SamePeriodLastYear function and using ParallelPeriod with Year parameter? Or using ParallelPeriod for a month versus DateAdd for a month ago?...(more)

Power BI Data Security – Power BI Report Server - Unlike Power BI Service which leverages the Office 365 security model with workspaces and apps, Power BI Report Server only supports deploying Power BI Desktop files as Power BI Reports in SQL Server Reporting Services. ...(more)

Performance Tuning SQL Server

Query to retrieve Staistics Data: dm_db_stats_histogram - Starting with SQL Server 2016 Sp1 CU2, a new way of directly querying statistics, specifically the histogram, has been introduced: dm_db_stats_histogram....(more)

Actual Number of Rows are not always accurate - Klaus Aschenbrenner shows a concrete example where in an Actual Execution Plan the Actual Number of Rows are WRONG!...(more)

NoSQL

4 Types of NoSQL Databases - Thomas Henson recalls "ETL hell", using SSIS to pull unstructured data from a sensor and put it into a SQL database. However, with NoSQL databases, this type of workflow is old school....(more)

ETL/SSIS/ELT

Three ways to build SSIS projects in VSTS - When setting up Continuous Integration in VSTS, chances are you’ll run into the problem of building SSIS projects: where database projects are building just fine, SSIS just doesn’t build out-of-the-box (at least not at the time of writing). Here’s how you fix it....(more)

Using SSIS to Retrieve JSON Files - Using SQL Server Integration Services (SSIS) to automate the retrieval of JSON files and store them on a local (or network) drive....(more)

Temp Tables In SSIS - Tim Mitchell explains why using temp tables in an SSIS load is usually a bad idea, and shares a few alternative design patterns to help avoid using temp tables in SSIS packages....(more)

DevOps and Continuous Delivery (CI/CD)

The 2018 State of DevOps Survey. Don’t just wait for it. Be a part of it. - The annual State of DevOps Survey from DevOps Research and Assessment (DORA) has become a valued part of the IT information landscape. Each year, it identifies the issues that matter the most to IT professionals, and unlocks new findings to help improve the resource management, productivity and quality of IT teams. You can help us by taking part in the survey which will also cover areas like IaaS and PaaS, monitoring, testing, workflows, culture, security, and reliability...(more)

Data Visualisation

Why no one is Reading your Report - Five reasons no-one is reading your report, despite all the work you put into it....(more)

Data Mining/Data Analysis

2018 Gartner Critical Capabilities Results for Analytics and BI - Critical Capabilities ranks vendor capabilities by use case. This report should be the one getting referenced in technical solution reviews to understand feature / function / use case strengths and weaknesses....(more)

Azure SQL Managed Instance

Change size of Azure SQL Managed Instance using PowerShell - Azure SQL Managed Instance is fully-managed SQL Server Database Engine hosted in Azure cloud. With Managed Instance you can easily add/remove cores associated to the instance and change the reserved size of the instance. You can use PowerShell to easily manage size of the instance and automate this process....(more)

Administration of SQL Server

Updated query to view data in the error log - A script that pulls all of the data from the error log and dumps it into a temp table....(more)

Spreading Job Run Times Out on an MSX/TSX Scheduled Job - How to schedule your SQL Server maintenance jobs so they run in different time blocks....(more)

How to Tell If Your SQL Server Has Too Much Memory - Just occasionally, SQL Server really does have more memory than it needs....(more)

tempdb: Are We There Yet? - Dave Mason on how to set a performance condition alert for the "Free Space in tempdb (KB)" counter, or a WMI alert for auto-growth, so that you know immediately if tempdb is running low on space....(more)