SQL Heresies, a CPU-unfriendly Stored Procedure and a Fake Job Title Generator (Database Weekly 9/24/2018)

The Complete Weekly Roundup of SQL Server News In this issue: Webinars - [Gene Kim joins Redgate to discuss The 2018 Accelerate State of DevOps Report](#33145) Virtualization and Containers - [Docker For Windows: Create a Linux Container on Windows 10](#33170) - [New Whitepaper: Architecting Microsoft SQL Server on VMware vSphere](#33169) T-SQL - [T-SQL job title generator](#33180) - [How to Write a Multiplication Aggregate Function in SQL](#33179) - [Functions on the Fly](#33176) - [One Hundred Percent CPU](#33175) - [A Simple Stored Procedure Pattern To Avoid](#33174) - [The Trigger Roundup–T-SQL Tuesday #106](#33159) - [You can't do DELETE TOP (X) with an ORDER BY](#33158) - [Extracting JSON Values Longer Than 4000 Characters](#33157) Security news and thoughts - [Fifty Different Ways to Enact Data Privacy Laws?](#33147) Reporting Services - [SSRS – Keep Column Headers Visible](#33165) R Language - [LTV prediction for a recurring subscription with R](#33184) - [Efficient data management and SQL data selection in R](#33167) Python in SQL Server - [sp_translate, A Universal Translator in SQL Server](#33161) PowerShell - [Not all PowerShell shells are equals](#33177) - [Simplifying disaster recovery with dbatools](#33155) PowerPivot/PowerQuery/PowerBI - [How to Configure the Power BI Gateway to use Dataset Connection Parameters](#33182) - [DAX Guide – the reference I had been wanting](#33152) - [Power BI Introduction: Publishing Reports to the Power BI Service — Part 8](#33146) Performance Tuning SQL Server - [Finding & Fixing Statistics Without Histograms](#33173) - [Query Store and Log Backups](#33172) - [The Cause of Every Deadlock in SQL Server](#33166) - [Developer’s Choice: Hinting Query Execution model](#33164) - [Don’t Just Rely on Query Execution Stats for T-SQL Execution](#33160) - [Testing the Performance of Individual SQL Statements within a Batch using SQL Prompt](#33149) HA/DR/Always On/Clustering - [New VLF status value](#33156) - [SQL Server with Cluster Shared volumes (CSV) – Part 2](#33151) ETL/SSIS/ELT - [The Changing Face of ETL](#33154) DevOps and Continuous Delivery (CI/CD) - [Posting SQL Server notifications to Slack](#33150) Database Design, Theory and Development - [Don't Conflate/Confuse Primary Keys, PK Constraints, and Indexes](#33162) Data Visualisation - [A Comparison of Data Visualization Tools](#33153) Data Science - [Data Science Virtual Machine](#33183) Computing in the Cloud (Azure, Google , AWS) - [Controlling Data Access in Azure for Administrators and Owners](#33181) - [Using Azure Storage Explorer](#33148) Backup and Recovery - [Public Service Announcement: check your backup notifications](#33178) Azure SQL Managed Instance - [Persisting job history in Azure SQL Managed Instance](#33171) Azure SQL Database - [Configuring Firewall Access for SQL Database on Azure](#33163) Administration of SQL Server - [6 DBA Lessons I Wish Someone Would Have Taught Me Earlier](#33168) [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-09-24 [SQL Provision]( NEW SQL Provision: Create, protect, & manage SQL Server database copies for compliant DevOps Create and manage database copies effortless and keeps compliance central to the process. With SQL Provisions virtual cloning technology, databases can be created in seconds using just MB of storage, enabling business to move faster. Sensitive data can be anonymized or replaced with realistic data to ensure data is protected as it moves between environments. [Download your free trial]( [SITC]( SQL in the City Summits - New York, London & Chicago This October, Redgate are inviting those interested in learning how their business can benefit from implementing Compliant Database DevOps to attend one of our SQL in the City Summits. If you manage SQL Server databases, or manage a team of people who do so, Redgate’s SQL in the City Summit is the conference for you. Find out who’s presenting and register for a Summit near you today. [Register now]( [SQL Compare]( The industry standard for comparing and deploying SQL Server database schemas Trusted by 71% of Fortune 100 companies, SQL Compare is the fastest way to compare changes, and create and deploy error-free scripts in minutes. Plus you can easily find and fix errors caused by database differences. [Download your free trial]( Editorial - Are there such things as SQL heresies? Whenever I get a spare moment, I check out some of the classic SQL Server 'heresies', to see whether they are still valid. SQL Heresies start out innocently, sometimes as a blog post or a paragraph on MSDN, but they soon pass into the pool of shared wisdom, of DBAs and developers. Over time they ossify to the point where they are treated as holy writ. Always declare your variables at the start of the batch! (Yes, it's a better style perhaps but doing otherwise no longer triggers a recompile). Never use SELECT * (there are rare occasions when it is correct to do so). Never have a USE command within a batch (why ever not?). Never use scalar functions for maintaining global constants; (the problem is actually one of using [unverified functions that aren't schema-bound](). Although the marketing people at Microsoft like to trumpet the 'big new things', such as Query Store, the real pleasures of upgrading SQL Server is in noticing that some of the little problems were fixed. Often, a sticky problem is solved, but goes unannounced save for a hand-waving mention of 'performance improvements'. Stuff gets fixed, but the 'Don't do x' code commandments of the SQL tribe never seem to get updated to read, 'Before SQL Server 2008 r2, don't do x'. Another problem with creating a SQL heresy is that many of the things that look wrong are perfectly fine, in the right circumstances and when done by an expert. Even the dreaded NOLOCK habit can't be anathematized entirely, because Microsoft use the hint extensively in their system stored procedures. You need to hedge what you say with 'ifs' and 'buts', or like Paul Randal, sigh and say, 'it depends'. There is usually a reason for everything in SQL Server, though one sometimes struggles to imagine what it was. Occasionally, we have to tell the indignant cub developer who has just used an OPTION (HASH GROUP, FAST 10), 'Sorry but that is for expert use only, and you ain't quite there yet.' There is no shame in this. Many of the dark corners of the SQL cupboard are intended for use by the programmers who maintain the rich undergrowth of system procedures, views and functions. Whereas we blink in some perplexity at their code, they would be just as baffled were they to try to understand some of the SQL tricks we employ in support of the work of the average commercial organisation. So, let's qualify our favorite SQL heresies with the versions to which they apply, and the usage for which they are bad. Otherwise we risk losing the wonderful spirit of invention that has produced so many of the interesting techniques that have made our working lives easier and more interesting. 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. Webinars [] [Gene Kim joins Redgate to discuss The 2018 Accelerate State of DevOps Report]( - Tuesday 25 September, 17.00-18.00 BST: Microsoft MVP Steve Jones is joined by acclaimed author and researcher Gene Kim to discuss the latest in all things DevOps. They offer a closer look at the key findings in the 2018 Accelerate State of DevOps Report, and investigates the growing importance of the database in successful DevOps and IT performance....[(more)]( Virtualization and Containers [] [Docker For Windows: Create a Linux Container on Windows 10]( - How to create a Linux container in Windows 10 using Docker for Windows. If you want to perform the same task on Windows Server, you’ll need Docker Enterprise Edition (Docker EE) instead. ...[(more)]( [] [New Whitepaper: Architecting Microsoft SQL Server on VMware vSphere]( - Klaus Aschenbrenner alerts us to a new whitepaper that is a must read from anyone who has a virtualized SQL Server running on top of VMware....[(more)]( T-SQL [] [T-SQL job title generator]( - Are you an Insane Extended Event Warrior? Or a Super High Nested Query Developer?...[(more)]( [] [How to Write a Multiplication Aggregate Function in SQL]( - Very occasionally, we do not need to aggregate multiple values in a sum (through addition), but in a product (through multiplication)....[(more)]( [] [Functions on the Fly]( - So you’ve wisely decided to put your business logic in your database, but for whatever reason you can’t use inline user defined functions, even though they encapsulate logic without hurting performance. That’s OK, because you can build functions on the fly in a single statement. Sort of....[(more)]( [] [One Hundred Percent CPU]( - A naughty stored procedure that pushes CPUs to 100%....[(more)]( [] [A Simple Stored Procedure Pattern To Avoid]( - Erik Darling demonstrates a bad variable assignment pattern....[(more)]( [] [The Trigger Roundup–T-SQL Tuesday #106]( - Steve Jones summarizes the entries to his "Trigger Headaches or Happiness" T-SQL Tuesday....[(more)]( [] [You can't do DELETE TOP (X) with an ORDER BY]( - If you need to use TOP to delete rows in a meaningful chronological order, you must use TOP together with an ORDER BY clause in a subselect statement....[(more)]( [] [Extracting JSON Values Longer Than 4000 Characters]( - Bert Wagner's automated process for parsing JSON strings into a relational format stumbles over a long string....[(more)]( Security news and thoughts [] [Fifty Different Ways to Enact Data Privacy Laws?]( - Now that the GDPR has gone into effect, many are wondering what will happen in the United States. California is the first state to enact similar legislation, called the California Consumer Privacy Act of 2018 (CCPA). In this article, William Brewer explains the history of the law, what it means for companies doing business with California residents, and how it compares to the GDPR....[(more)]( Reporting Services [] [SSRS – Keep Column Headers Visible]( - Sometimes when creating a report for SQL Server Reporting Services, you want the column headers to remain visible while you scroll down the report page. Adjusting the Tablix properties isn’t as straightforward as it should be, and can be frustrating to figure out. ...[(more)]( R Language [] [LTV prediction for a recurring subscription with R]( - Customers lifetime value (LTV or CLV) is one of the cornerstones of product analytics because we need to make a lot of decisions for which the LTV is a necessary or at least very significant factor. In this article, we will focus on products/services/applications with recurring subscription payments....[(more)]( [] [Efficient data management and SQL data selection in R]( - Before running your data analysis, every data scientist needs to make data management, data cleaning and data selection....[(more)]( Python in SQL Server [] [sp_translate, A Universal Translator in SQL Server]( - Just One Example Of How Powerful the Python/SQL Partnership Can Be....[(more)]( PowerShell [] [Not all PowerShell shells are equals]( - Users use Outlook that is a 32-bit process. If they click on link that points to a script, it will spawn a 32-bit console and run a 32-bit powershell.exe child process....[(more)]( [] [Simplifying disaster recovery with dbatools]( - The new Export-DbaInstance command in dbatools is a wrapper for over 50 export scripts, to help make Disaster Recovery easier to manage....[(more)]( PowerPivot/PowerQuery/PowerBI [] [How to Configure the Power BI Gateway to use Dataset Connection Parameters]( - A service provider or vendor might want to publish multiple copies of a report that should connect to different database servers or databases. Paul Turley shows how copies of a report can be deployed to different workspaces and then queries can be parameterized to use different database connections....[(more)]( [] [DAX Guide – the reference I had been wanting]( - Marco Russo announces a new 'DAX Guide' website, offering a complete reference to the DAX language. Every function is presented with its complete syntax, a short description, and links to related functions and articles....[(more)]( [] [Power BI Introduction: Publishing Reports to the Power BI Service — Part 8]( - Power BI Desktop is typically used to create reports that will eventually be published to Power BI Services where dashboards can be built. Robert Sheldon demonstrates how to publish reports from Power BI Desktop to the Power BI Service. Once published, the visualizations can be filtered, drilled into, or pinned to a dashboard....[(more)]( Performance Tuning SQL Server [] [Finding & Fixing Statistics Without Histograms]( - If you have a database that’s been passed along from one SQL Server to another, gradually upgraded over the years, or if you’ve had a table that’s been loaded but never queried, you can end up with a curious situation. ...[(more)]( [] [Query Store and Log Backups]( - Query Store, like any other data written to a database, whether a system table or a user table, is a logged operation. However, at what point was the Query Store information written to disk? ...[(more)]( [] [The Cause of Every Deadlock in SQL Server]( - Deadlocks are the result of application code combined with a database schema resulting in access patterns which lead to a cyclical dependency. That’s right. I said it. Application code causes deadlocks....[(more)]( [] [Developer’s Choice: Hinting Query Execution model]( - One of the reasons we introduced USE HINT query hints back in SQL Server 2016 SP1 was to provide knobs that are sometimes required, in a fully supported way, without having to remember trace flag numbers....[(more)]( [] [Don’t Just Rely on Query Execution Stats for T-SQL Execution]( - Matthew McGiffen explains that dm_exec_query_stats only captures figures for cached query plans, so if you have a query that’s not caching a plan or doing work that’s not actually part of a query, then it won't show in in the query execution stats....[(more)]( [] [Testing the Performance of Individual SQL Statements within a Batch using SQL Prompt]( - With a few clicks to invoke and execute the Prompt snippet, we get the execution plan for any batch, along with the SQL text and query execution statistics for every SQL statement in the batch....[(more)]( HA/DR/Always On/Clustering [] [New VLF status value]( - Paul Randal encounters a VLF status code that was added back in SQL Server 2012, and can show up on an Availability Group secondary replica, when a log file growth (or creation of an extra log file) has occurred on the primary replica but it hasn’t yet been replayed on the secondary replica. ...[(more)]( [] [SQL Server with Cluster Shared volumes (CSV) – Part 2]( - A walk though of the installation of a failover cluster Instance, leveraging CSVs....[(more)]( ETL/SSIS/ELT [] [The Changing Face of ETL]( - Data is not only generated by systems but when combined with other data and insights can actually be used to power systems. The lines between analytic systems and transactional systems are blurring. It's ETL, Jim, but not as we know it....[(more)]( DevOps and Continuous Delivery (CI/CD) [] [Posting SQL Server notifications to Slack]( - Alessandro Alpi demonstrates how to integrate SQL Server task notifications with one of the most used collaboration tools: Slack....[(more)]( Database Design, Theory and Development [] [Don't Conflate/Confuse Primary Keys, PK Constraints, and Indexes]( - Clearing up any confusion between what a Primary Key is, and what an Index is, and how they are used....[(more)]( Data Visualisation [] [A Comparison of Data Visualization Tools]( - Recommendation on which data visualization tools might be right for you, depending on your job role and requirements. ...[(more)]( Data Science [] [Data Science Virtual Machine]( - Data Science Virtual Machine (DSVM) is a virtual machine on the Azure cloud that is customized for doing data science. DSVM has some pre-configured and pre-install tools that help users to build the AI applications. DSVM will assist data science team to access a consistent setup. In this post, a brief introduction to DSVM and how to install it will be provided. ...[(more)]( Computing in the Cloud (Azure, Google , AWS) [] [Controlling Data Access in Azure for Administrators and Owners]( - Recently a customer expressed concern that an owner of an Azure resource group automatically gains access to the data within the services contained in the resource group. Melissa Coates examines some options for handling the security differently....[(more)]( [] [Using Azure Storage Explorer]( - Azure Storage can store many types of data, from NoSQL tables to VHDs. In this article, Supriya Pande explains how to work with Azure Storage Explorer, a tool that makes it easy to manage storage in Azure. She also provides an example of using the Azure Storage .NET SDK to upload files....[(more)]( Backup and Recovery [] [Public Service Announcement: check your backup notifications]( - Are your backup failure notifications working? Are you sure?...[(more)]( Azure SQL Managed Instance [] [Persisting job history in Azure SQL Managed Instance]( - Azure SQL Managed Instance is a fully managed SQL Server hosted in Azure cloud. Although it supports many SQL Server features, there are some constraints compared to SQL Server that you manage. One of the constraint that might be an issue is the fact that SQL Agent keeps a limited history of job executions that cannot be changed. In this post you will see one way to workaround this....[(more)]( Azure SQL Database [] [Configuring Firewall Access for SQL Database on Azure]( - One of the tasks that I noticed tripped us up in configuring SQL Database on Azure was configuring the firewall for SSMS, (SQL Server Management Studio) or Visual Studio, (VS) access. ...[(more)]( Administration of SQL Server [] [6 DBA Lessons I Wish Someone Would Have Taught Me Earlier]( - Number 6: For maximum learning, you need peers and challenges...you need to tackle new challenges that you haven’t seen before, and you need outside opinions to challenge what you think you already know. ...[(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