Troublesome Names, Parameter Sniffing Problems and more in Database Weekly (3/12/2018)

The Complete Weekly Roundup of SQL Server News In this issue: Vendors/3rd Party Products - [Redgate’s support for Azure SQL Database Managed Instances](#31370) - [Consider using [NOT] EXISTS instead of [NOT] IN (subquery)](#31337) T-SQL - [I Most Certainly Do Have A Join Predicate](#31378) - [Transact-SQL STRING_AGG](#31373) - [Using Stored Procedures in SQL Server that return several results](#31368) - [Forced Plan Confusion: Is_Forced vs Use Plan = True](#31361) - [EstimateRowsWithoutRowGoal helps you see: is it a statistics problem?](#31360) - [Query Store Internals: Indexes on the system views](#31356) - [A Problem with Boolean Logic](#31355) - [How to Get a Random Row from a Large Table](#31354) - [Every Single Execution Plan is an Estimated Plan](#31353) - [Charles Bachman and Pointer Chains](#31346) SQL Server Security - [Safely and Easily Use High-Level Permissions Without Granting Them to Anyone: Database-level](#31350) Security news and thoughts - [GDPR Checklist - Getting Ready for New Regulations in Europe](#31376) Reporting Services - [Using R in SQL Server Reporting Services (SSRS)](#31351) R Language - [Understanding Rolling Calculations in R](#31372) Product Upgrades and Releases - [Popular Python Data Science Platform Anaconda Now Shipping with Microsoft VS Code](#31339) PowerPivot/PowerQuery/PowerBI - [An In-Depth Look At The Csv.Document M Function](#31379) - [Managing “all” functions in DAX: ALL, ALLSELECTED, ALLNOBLANKROW, ALLEXCEPT](#31377) - [Data Refresh Issues in the Power BI Service Due to Invalid Dates](#31371) Performance Tuning SQL Server - [Profiler for Extended Events: Quick Settings](#31366) - [Row Goals, Part 3: Anti Joins](#31365) - [Troubleshooting Parameter Sniffing Issues the Right Way: Part 3](#31362) - [READ COMMITTED SNAPSHOT ISOLATION and High version_ghost_record_count](#31342) Hardware Testing - [Low Latency Memory](#31364) HA/DR/Always On/Clustering - [Undercover Toolbox: sp_WhatsMyAG](#31375) - [Availability Group round-robin read-only routing isn't magic](#31357) DevOps and Continuous Delivery (CI/CD) - [The top 7 benefits of DevOps for IT Managers](#31340) Database Design, Theory and Development - [Physical Independence Part 1: Don't Mix Model with Implementation](#31358) Data Science - [Data Processing: An Example](#31348) Data Privacy - [Data Governance and GDPR: How the Most Comprehensive Data Regulation in the World Will Affect Your Business](#31347) Computing in the Cloud (Azure, Google , AWS) - [What is Azure SQL Database Managed Instance?](#31369) - [Things to consider for a cloud migration](#31338) Columnstore Indexes - [Large CCI ETLs Cannot Scale Without TF 834](#31349) Analysis Services / BI on the MS Stack - [Using the Analysis Services Execute DDL Task](#31363) - [How to use RANKX in DAX (Part 1 of 3 – Calculated Columns)](#31352) Administration of SQL Server - [Simply Debugging](#31374) - [How to change SQL Server ERRORLOG location](#31367) - [Using SWITCH On A Single Partition](#31359) - [SQL Server Graph Databases – Part 1: Introduction](#31344) - [Scripting the Description of Database Tables Using Extended Properties](#31341) [Database Weekly - www.databaseweekly.com]( The Complete Weekly Roundup of SQL Server News Hand-picked content to sharpen your professional edge SQL Server News for 2018-03-12 [SQL in the City]( Free data protection and privacy livestream Many of you will be aware that the new GDPR legislation comes into effect in May and, in light of this, Redgate recently hosted a livestream that included sessions to help you become best equipped to deal with the challenges GDPR brings to compliant database management. [Watch the recording]( [SQL Prompt]( Write, format, analyze, and refactor SQL fast with SQL Prompt Writing SQL is 50% faster with SQL Prompt. Your SQL code can be formatted just the way you like it, you can create and share snippets with your team, and with code analysis you get suggestions to improve your SQL as you type. [Download your free trial]( [ReadyRoll]( Database migrations inside Visual Studio Feeling the pain of managing and deploying database changes manually? Redgate ReadyRoll creates SQL migration scripts you can use to version control, build and release, and automate deployments. [Try it free]( Editorial - Troublesome Names One of the ways that is often suggested for preventing SQL Injection is to validate the inputs. If, for example, you were accepting input from a name field, you should check the input for any SQL statements. Any seasoned developer will flinch at the idea. The input of any application comes in a surprisingly rich variety, even in its every-day legitimate usage. There are many legitimate names that cause big problems to poorly-written computer applications. After years of misery, Christopher Null famously went public on his woes with his famous blog post, [Hello, I'm Mr. Null. My Name Makes Me Invisible to Computers](. "Most will accept "Null" without complaint. Some will loop back to the input screen and tell the user to try again, that the last name field can't be blank (But it's not blank! That's just my name!) Some will tell the user that "Null" is a reserved term that can't be used. And some will just crash." In fact, Null is a name with a long history, originally from Donegal in Ireland, and meaning an Ulsterman. Punctuation in names has always caused problems too. Some years back, so many people with apostrophes in their name, such as O'Brien, found that so many applications crashed around their ears that many dropped the centuries-old usage. The problem was that developers would merely concatenate a string into a SQL Statement within the application. $SQLString = "insert into Customer (surname) select '"+name+"'" It could even be that a struggling O'Neil discovered SQL Injection when he finally snapped, and typed into the Surname field…. O';Truncate table customer – …to spite the lazy programmer. So, surely, we really should be checking the input by looking for SQL keywords or punctuation? Even with names derived from Europe we're in trouble. We can't look for signs of someone attempting to elevate permissions, because Grant'is the name of the current PASS president. We can look for Table, surely? No, the Table family exist, and derive their name from their origins in Tapeley, in Devon UK. OK but surely nobody is called 'Alter'? Well, that is actually both a surname and a given name, a Yiddish word derived from alt, meaning 'old'. The Delete family derive their name from the [French name Dillet](. The Join family is well-known in France, and so are the Joines in Britain. The Case family (name from the Old Norman French word 'casse') thrive in New York. The Files family come from Lancashire. There are people with the surname Drop, from Norfolk. The With family come originally from Middlesex, There are many companies with Select in their name. There is a record of a Create family in the 1750s but the name is probably no longer current. Mercifully, there is nobody called FillFactor. You might be safe in counting DBCC as a dodgy string, but not much else. (Thanks to William Brewer [for the information](). To add to our difficulties, not only are the plenty of apostrophes in surnames, but also a whole range of punctuation, including full stops, question marks, hyphens and en-dashes. For the full gamut of variation in names, see the classic [Personal names around the world.]( You might think that creating a simple name-and-address database is a simple thing that can be safely left to the cub programmer, but in fact it can be one of the more awkward tasks that developer's face. Phil Factor Phil Factor from [SQLServerCentral.com]( » [Join the debate, and respond to today's editorial on the forums]( --------------------------------------------------------------- The Weekly News 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 [] [Redgate’s support for Azure SQL Database Managed Instances]( - This week Microsoft released the public preview of Azure SQL Database Managed Instances – an exciting new option for running SQL Server workloads in the cloud. This blog post explains what they are, and how Redgate's SQL Toolbelt supports them....[(more)]( [] [Consider using [NOT] EXISTS instead of [NOT] IN (subquery)]( - The query optimizer now treats EXISTS and IN the same way, whenever it can, so you’re unlikely to see any significant performance differences. Nevertheless, you need to be cautious when using the NOT IN operator if the subquery’s source data contains NULL values. If so, you should consider using a NOT EXISTS operator instead of NOT IN, or recast the statement as a left outer join....[(more)]( T-SQL [] [I Most Certainly Do Have A Join Predicate]( - You wrote a query. You joined tables.You have the right ON clause.You have the right WHERE clause.But the query plan has a problem - a red warning cross on the join operator!...[(more)]( [] [Transact-SQL STRING_AGG]( - Creating comma separated strings from a column, or delimited strings as I like to call it, is a very common problem in SQL. Beginning with SQL Server 2017 and Azure SQL Database, there is now another option to the existing set of solutions, STRING_AGG().I would like to convince you to use STRING_AGG over the other methods....[(more)]( [] [Using Stored Procedures in SQL Server that return several results]( - Stored Procedures give you more freedom than functions, and so they would be the obvious way of developing processes in SQL Server. There is one longstanding problem with them though, although it is possible to send several results to the application from a stored procedure and read them without problems, you have big problems capturing more than one in SQL Server. ...[(more)]( [] [Forced Plan Confusion: Is_Forced vs Use Plan = True]( - Identifying that a query plan has been bossed around in Query Store can be a bit tricky, because it can appear in different ways....[(more)]( [] [EstimateRowsWithoutRowGoal helps you see: is it a statistics problem?]( - SQL Server Management Studio version 17.5 adds a welcome feature for execution plans: a new visual attribute named EstimateRowsWithoutRowGoal....[(more)]( [] [Query Store Internals: Indexes on the system views]( - As our Query Store gets bigger, queries against the Query Store tables will start running slower unless we use the indexes properly....[(more)]( [] [A Problem with Boolean Logic]( - You need to return rows where either A or B is true, and C is true, but your results aren't as expected. What’s the problem? Operator Precedence, that’s the problem....[(more)]( [] [How to Get a Random Row from a Large Table]( - Brent Ozar offers four ways to get a random row from a large table....[(more)]( [] [Every Single Execution Plan is an Estimated Plan]( - All the execution plans are estimated plans. All of them. There fundamentally isn’t any such thing as an “Actual” plan....[(more)]( [] [Charles Bachman and Pointer Chains]( - Joe Celko reminisces about the origins of databases and one of the early pioneers, Charles Bachman. He explains how pointer chains were used to traverse data in the NDL standard and referential integrity that we use today....[(more)]( SQL Server Security [] [Safely and Easily Use High-Level Permissions Without Granting Them to Anyone: Database-level]( - Module Signing, introduced in SQL Server 2005, uses Certificates and/or Asymmetric Keys to selectively apply additional permissions to code: Stored Procedures, Triggers, Scalar UDFs, and Multi-statement TVFs....[(more)]( Security news and thoughts [] [GDPR Checklist - Getting Ready for New Regulations in Europe]( - Even if your company only has one customer in the European Union (EU) the General Data Protection Regulations affect you. This informal GDPR Checklist, formulated with help from experts in the field, will assist you in preparing for the regulations implementation and your compliance....[(more)]( Reporting Services [] [Using R in SQL Server Reporting Services (SSRS)]( - Tomaz Kastrun demonstrates how using the privileges of R language to enrich your data, your statistical analysis or visualization is a simple way to get more out of your reports....[(more)]( R Language [] [Understanding Rolling Calculations in R]( - In R, we often need to get values or perform calculations from information not on the same row. We need to either retrieve specific values or we need to produce some sort of aggregation. This post explores some of the options and explains the weird (to me at least!) behaviors around rolling calculations and alignments....[(more)]( Product Upgrades and Releases [] [Popular Python Data Science Platform Anaconda Now Shipping with Microsoft VS Code]( - Release 5.1 of Anaconda, the data science and machine learning platform, now includes Visual Studio Code as an IDE. This is part of a wider collaborative effort between Anaconda Inc. and Microsoft....[(more)]( PowerPivot/PowerQuery/PowerBI [] [An In-Depth Look At The Csv.Document M Function]( - CSV files are one of the most commonly used data sources in Power BI and Power Query/Get&Transform, and yet the documentation for the Csv.Document M function is very limited and in some cases incorrect. In this rather long post I’ll show you as many of the capabilities of this useful function as I’ve been able to discover....[(more)]( [] [Managing “all” functions in DAX: ALL, ALLSELECTED, ALLNOBLANKROW, ALLEXCEPT]( - This article provides a complete explanation of the behavior of the ALLxxx functions in DAX. When used as filters in CALCULATE, ALLxxx functions might display unexpected behaviors....[(more)]( [] [Data Refresh Issues in the Power BI Service Due to Invalid Dates]( - A customer experienced a problem where the refresh of the data imported to a PBIX always worked in Power BI Desktop, but intermittently failed in the Power BI Service....[(more)]( Performance Tuning SQL Server [] [Profiler for Extended Events: Quick Settings]( - As of SSMS 17.4 we have been given the ability to control XEvents Profiler just a tiny bit more. ...[(more)]( [] [Row Goals, Part 3: Anti Joins]( - An anti join is also known as an anti semi join. It returns each row from join input A for which no match can be found on input B. Paul White investigates how the optimizer applies row goals when optimizing anti-join queries....[(more)]( [] [Troubleshooting Parameter Sniffing Issues the Right Way: Part 3]( - You aren’t comparing apples to apples when troubleshooting parameter sniffing issues in production if the source query is parameterized, like a stored procedure, and you are testing with a local variable....[(more)]( [] [READ COMMITTED SNAPSHOT ISOLATION and High version_ghost_record_count]( - Developer shops may decide to use Read Committed Snapshot Isolation (RCSI) to reduce contention, and possibly improve performance, but it is not without its own pitfalls. In this article, Uwe Ricken describes a situation where long running transactions caused a severe performance degradation when RCSI was in use....[(more)]( Hardware Testing [] [Low Latency Memory]( - We need to wake to the fact that scaling performance with multi-processor systems should no longer be the standard default approach. Once we accept the single processor system approach, it is easy to realize that a new low latency memory would additional huge value....[(more)]( HA/DR/Always On/Clustering [] [Undercover Toolbox: sp_WhatsMyAG]( - If you happen to be managing SQL Servers with a large number of databases and availability groups, it can sometimes be difficult to keep track of which database belongs to which availability group....[(more)]( [] [Availability Group round-robin read-only routing isn't magic]( - I've been hearing about round-robin read-only routing ever since SQL 2016 came out but whenever I tried to test if it's working it never seemed to be. But now I know exactly how it works and there's a few loopholes where it may not trigger, and they're not the documented ones you're thinking of....[(more)]( DevOps and Continuous Delivery (CI/CD) [] [The top 7 benefits of DevOps for IT Managers]( - David Linwood, a highly experienced IT Director, conducted his MSc research project with the intention of discovering the real benefits of #DevOps - this is what he found...[(more)]( Database Design, Theory and Development [] [Physical Independence Part 1: Don't Mix Model with Implementation]( - Old DBMSs based on hierarchic and network data models forced users and applications into the details of how data are internally stored and accessed (i.e., implementation) when they accessed databases. Such details are an irrelevant distraction from what users do and when they changed, applications no longer worked and required maintenance....[(more)]( Data Science [] [Data Processing: An Example]( - Having spent a lot of time talking about data acquisition, data cleansing, and basic data analysis, Kevin Feasel demonstrates how some if this works in practice, using a data professional salary survey....[(more)]( Data Privacy [] [Data Governance and GDPR: How the Most Comprehensive Data Regulation in the World Will Affect Your Business]( - If you’re a data professional, data governance and GDPR are likely at the top of your agenda right now. Because if your organization exists within the European Union (EU) or trades with the EU, the General Data Protection Regulation (GDPR) will affect your operations. Despite this fact, only 6% of organizations say they are “completely prepared” ahead of the mandate’s May 25 effective date, according to the 2018 State of Data Governance Report....[(more)]( Computing in the Cloud (Azure, Google , AWS) [] [What is Azure SQL Database Managed Instance?]( - Managed Instance is a new managed database service that represents fully-managed SQL Server Instance in Azure cloud. It shares the same code with the latest version of SQL Server Database Engine and has the latest features, performance improvements, and security patches. This service is currently in public preview....[(more)]( [] [Things to consider for a cloud migration]( - Introducing a cloud migration is becoming an integral facet of modernization in any business strategy, and these days there are more than a handful of aspects to consider. While there is a wealth of information out there, we would recommend you start your cloud migration journey by considering the four most important factors: ...[(more)]( Columnstore Indexes [] [Large CCI ETLs Cannot Scale Without TF 834]( - Large servers may experience a scalability bottleneck related to the RESERVED_MEMORY_ALLOCATION_EXT wait event during loading of columnstore tables. This blog post shares a reproduction of the issue and discusses some test results....[(more)]( Analysis Services / BI on the MS Stack [] [Using the Analysis Services Execute DDL Task]( - Hitting a bug in Visual Studio 2017/SSDT 15.5.2 that will not allow you to use the Analysis Services Process Task if your target version of SSIS is 2016, finally introduced SQLSwimmer to the new Analysis Services Execute DDL Task....[(more)]( [] [How to use RANKX in DAX (Part 1 of 3 – Calculated Columns)]( - When I first started to play with DAX, one of the functions that seemed to confuse me more than it should, was how to add ranking to my data....[(more)]( Administration of SQL Server [] [Simply Debugging]( - Kenneth Fisher explains the simple principals that everyone should know and apply when debugging....[(more)]( [] [How to change SQL Server ERRORLOG location]( - To change or move ERROLOG path, use SQL Configuration Manager, change parameter “-e”, to point to the new location and then restart the SQL Service....[(more)]( [] [Using SWITCH On A Single Partition]( - In a nutshell, you can use the SWITCH function to quickly move a table, which is a single partition, and all of its data to a new table or schema....[(more)]( [] [SQL Server Graph Databases – Part 1: Introduction]( - SQL Server 2017 now includes a new feature to represent complex relationships in data called Graph Databases. Robert Sheldon introduces Graph Databases in the first article of this new series....[(more)]( [] [Scripting the Description of Database Tables Using Extended Properties]( - Stored procedures, for example, are very easy to document. The comment block at the beginning stays with the code and a CREATE or ALTER script contains everything to reproduce the proc. SQL Server tables, however, are more difficult to document. You can use Extended Properties to document columns and constraints, but working with Extended Properties is difficult at best. Phil Factor demonstrates ways to easily add Extended Properties to your build scripts....[(more)]( --------------------------------------------------------------- Administrative To be removed from this list, please click [here]( Note: This is not the SQLServerCentral.com daily newsletter list, and unsubscribing to this newsletter will not stop you receiving the SQL Server Central daily newsletters. If you want to be removed from that list, you can [change your profile]( or follow the instructions on the daily newsletter. --------------------------------------------------------------- This transmission is ©2018 Redgate Software Ltd, Newnham House, Cambridge Business Park, Cambridge, CB4 0WZ, United Kingdom. All rights reserved. Contact: webmaster@sqlservercentral.com