Computing index numbers at top speed, Properly Persisted Computed Columns, and WannaCry Over Spilled Data (Database Weekly 5/27/2017)

The Complete Weekly Roundup of SQL Server News In this issue: Vendors/3rd Party Products - [We’re making SQL Server audits easier – can you help us?](#29063) T-SQL - [Disabling the identity cache in SQL Server 2017](#29072) - [Scalar Functions In Views: Where’s The Overhead?](#29069) - [SQL Server User-Defined Functions](#29064) - [Simple SQL: Attribute Splitting](#29061) The Lighter Side - [Announcing HASSP](#29074) Security news and thoughts - [WannaCry Over Spilled Data](#29065) Reporting Services - [SSRS Subscription Schedules – Enhanced](#29079) - [SSRS Subscription Schedules](#29071) PowerPivot/PowerQuery/PowerBI - [PowerShell Remoting in Multi-Platform Environments using OpenSSH](#29093) - [Dynamic Power BI reports using Parameters [video]](#29067) - [Reusing Datasets Imported to the Power BI Service](#29066) Performance Tuning SQL Server - [Properly Persisted Computed Columns](#29084) - [When a Nonclustered Index and Statistics Make a Query Slower](#29077) - [Tracking Lookup Operations in SQL Server](#29070) Microsoft News - [Coming Soon – PowerShell in Azure Cloud Shell](#29083) Microsoft News : Patches, Bugs - [Microsoft R Open 3.4.0 now available](#29082) - [Announcing SQL Server 2012 Service Pack 4](#29081) Microsoft News : Security - [Microsoft Said To Buy Cyberecurity Firm Hexadite for $100 Million](#29087) DevOps and Continuous Delivery (CI/CD) - [Free webinar: Microsoft’s Agile Transformation Story](#29060) Data Privacy - [Now Available: Guide for enhancing privacy and addressing GDPR requirements with the Microsoft SQL platform](#29089) - [Now Available: Guide for enhancing privacy and addressing GDPR requirements with the Microsoft SQL platform](#29080) Data Mining/Data Analysis - [How to compute index numbers at top speed](#29078) Computing in the Cloud (Azure, Google , AWS) - [Getting What You Need From Azure Storage Disks](#29062) Career Growth - [Chief Data Officer Playbook: How to Be Successful in This Ground-Breaking Role](#29076) - [Flaming Hair And The Apocalypse](#29075) - [Technoloy Gender Gap by State](#29068) Azure SQL Database - [The Biggest Threat To Microsoft’s Future is Artificial Intelligence](#29086) - [Microsoft Introduces Low Priority VMs to Cut Batch Processing Pricing](#29085) Administration of SQL Server - [Finding Queries that Cause Wait Stats in SQL Server](#29092) - [How To Use Temporal Tables For Easy Point-In-Time Analysis](#29090) - [Disabling the identity cache in SQL Server 2017](#29088) - [Experienced DBA: Rookie Mistake](#29073) .NET Related Articles - [Visual Studio Toolbox: SQL Server Data Tools in your DevOps Pipeline](#29091) [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 2017-05-29 [Database DevOps]( State of Database DevOps Report Learn how teams are extending DevOps practices to SQL Server databases. [Get the report.]( [SQL Monitor]( New SQL Monitor Reporting Module SQL Monitor now includes a new Reporting module. It allows you to create customized reports, combining the individual server performance metrics available in SQL Monitor with summary information on your entire SQL Server estate. These reports can then be exported to pdf and emailed to you on a scheduled basis. [Download SQL Monitor now and get a 14 day free trial.]( [SQL Source Control]( How to track every change to your SQL Server database See who’s changing your database, alongside affected objects, date, time, and reason for the change with SQL Source Control. Get a full change history in your source control system. [Learn more.]( Editorial - The Rate of Change of Data When you get into a muddle with your data, it manifests in many ways. Caching is one symptom. Often, people tackle problems with the scaling of data-driven websites by introducing caching. This means that, instead of getting the data from the database, it is held, temporarily, on the web server. It is worth looking at what is going on with caching. For example, I once worked on a booking website that cached on the web server the data for a calendar of the year, with a 10-second refresh rate. It didn't make a lot of sense, seeing that the dates for religious and national holidays are fairly-firmly fixed. Unless some election propels an unlikely eccentric into power who goes on to create four new national holidays, we're pretty safe with even a one-year refresh rate for a calendar. Some data should never change, such as account entries. I've seen many databases where the previous years' trading is recalculated from the individual entries on every report. If that sort of data ever changes retrospectively in your organization, I'd be fascinated to know why. I once came across a database (well, ten linked databases actually, one for each year!) that recalculated a whole ten year grant-allocation history every time anyone enquired. The whole topic of aggregation tables, along with several other aspects to database science, were a mystery to the team in charge of it. I'm not suggesting that web server caching should hold data for very long, but that the database itself should deal as intelligently as possible with repeated calls for the same information. Generally, I like to create data-driven websites where each entity has its safe caching range defined within the database. After all, the rate of change of the data, and the characteristics of the way it changes, are aspects of your data that it is important to know about, if you are interested in performance and scaling. Where values change constantly you never cache them, and if they change randomly, you need a 'stale' toggle to trigger a refresh all the way to the application. Nowadays, I do the preliminary caching within the database, in a variety of ways, but I also expose the metadata about the caching requirements of the database entities, which then allows the individual applications who need to access the data to do caching as well Some might argue that this is putting "application logic" in the database. I disagree. Surely, it is the database that should know about the data and be the primary source, not just of the current values but also of the volatility of the data, the rate of change, the type of information (personal, financial, organization, and so on). It makes sense to hold this information in just one place and there are plenty of reasons for favouring the database as that place. 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 [] [We’re making SQL Server audits easier – can you help us?]( - Redgate's Foundry team are starting work on a solution to reduce the pain and time-cost of providing audit evidence for SQL Server user access. If you have an audit coming up in the next quarter, they'd love to hear from you. This blog post lays out what they learnt from their research, and how you might be able to help....[(more)]( T-SQL [] [Disabling the identity cache in SQL Server 2017]( - In SQL Server 2017, we can disable the cache that SQL keeps on identity values by using the ALTER DATABASE SCOPED CONFIGURATION command. This prevents any gaps in the IDs, if there is an unexpected restart or a failover to a secondary database....[(more)]( [] [Scalar Functions In Views: Where’s The Overhead?]( - Erik Darling explores the side effects of relying on an old view that has scalar valued functions in it....[(more)]( [] [SQL Server User-Defined Functions]( - User-Defined Functions (UDFs) are an essential part of the database developers' armoury. They are extraordinarily versatile, but just because you can even use scalar UDFs in WHERE clauses, computed columns and check constraints doesn't mean that you should. Multi-statement UDFs come at a cost and it is good to understand all the restrictions and potential drawbacks. Phil Factor gives an overview of User-defined functions: their virtues, vices and their syntax....[(more)]( [] [Simple SQL: Attribute Splitting]( - If the design of a relational database is wrong, no amount of clever DML SQL will make it work well. Dr. Codd’s Information Principle is that you have, inside the entity tables, the columns that model the attributes of that entity. The columns contain scalar values. Tables that model relationships can have attributes, but they must have references to entities in the schema. You split those attributes at your peril. Joe Celko explains the basics....[(more)]( The Lighter Side [] [Announcing HASSP]( - Oh, you put your database in the cloud? We're putting one through them....[(more)]( Security news and thoughts [] [WannaCry Over Spilled Data]( - Is the WannaCry incident a wakeup call for the industry? William Brewer discusses the recent ransomware attack on the NHS...[(more)]( Reporting Services [] [SSRS Subscription Schedules – Enhanced]( - SSRS provides the capability to review the scheduled reports (subscriptions) in a highly-detailed fashion. This article will dive into the source of this scheduling information within the ReportServer database....[(more)]( [] [SSRS Subscription Schedules]( - Jason Brimhall uncovers one of the sources of report scheduling information, within the ReportServer database....[(more)]( PowerPivot/PowerQuery/PowerBI [] [PowerShell Remoting in Multi-Platform Environments using OpenSSH]( - This post introduces PowerShell Remoting in Multi-Platform Environments, specifically using OpenSSH....[(more)]( [] [Dynamic Power BI reports using Parameters [video]]( - Patrick LeBlanc shows you how you can use a parameter, within a Power BI report, to dynamically change the data in a report....[(more)]( [] [Reusing Datasets Imported to the Power BI Service]( - I'm a big fan of reusing Power BI datasets whenever possible to minimize redundant datasets floating around the organization. It's less maintenance, less chance of calculation differences, and less data refreshes to schedule....[(more)]( Performance Tuning SQL Server [] [Properly Persisted Computed Columns]( - A major cause of poor performance is a simple failure to use an indexed or persisted computed column value as expected. ...[(more)]( [] [When a Nonclustered Index and Statistics Make a Query Slower]( - Nonclustered indexes are awesome in SQL Server: they can get you huge performance gains. But we can’t always create the perfect index for every query. And sometimes when SQL Server finds an index that isn’t quite perfect and decides to use it, it might make your query slower instead of faster....[(more)]( [] [Tracking Lookup Operations in SQL Server]( - How can I find out on which Clustered Index the most Lookup Operations have been performed?...[(more)]( Microsoft News [] [Coming Soon – PowerShell in Azure Cloud Shell]( - At BUILD 2017, Microsoft announced the preview of Azure Cloud Shell supporting the Bash shell, and are now adding PowerShell support to Azure Cloud Shell, which gives you a choice of shell to get work done....[(more)]( Microsoft News : Patches, Bugs [] [Microsoft R Open 3.4.0 now available]( - Microsoft R Open (MRO), Microsoft's enhanced distribution of open source R, has been upgraded to version 3.4.0 and is now available for download for Windows, Mac, and Linux....[(more)]( [] [Announcing SQL Server 2012 Service Pack 4]( - Microsoft have announced the last service pack for SQL Server 2012....[(more)]( Microsoft News : Security [] [Microsoft Said To Buy Cyberecurity Firm Hexadite for $100 Million]( - Reports indicate that Microsoft has agreed to buy Israeli cybersecurity company Hexadite for around $100 million. Hexadite uses artificial intelligence (AI) to analyze and address small-scale network attacks, a frequent problem for many large enterprises....[(more)]( DevOps and Continuous Delivery (CI/CD) [] [Free webinar: Microsoft’s Agile Transformation Story]( - Learn how Microsoft adopted DevOps at scale in this free Redgate webinar on June 12th. Donovan Brown, Senior DevOps Program Manager in Microsoft’s US Developer Division, will share how Microsoft embarked on its own DevOps journey reducing a three-year release cycle down to three weeks. Sign up now....[(more)]( Data Privacy [] [Now Available: Guide for enhancing privacy and addressing GDPR requirements with the Microsoft SQL platform]( - Data privacy and data security have become one of the most prominent topics in organizations in almost every industry across the globe. New regulations are emerging that formalize requirements around these topics, and compel organizations to comply. The upcoming EU Global Data Protection Regulation (GDPR) is one of the most noteworthy of these new regulations. ...[(more)]( [] [Now Available: Guide for enhancing privacy and addressing GDPR requirements with the Microsoft SQL platform]( - The GDPR takes effect on May 25, 2018 and sets a new global bar for privacy rights, security, and compliance. It mandates many requirements and obligations on organizations across the globe. Complying with this regulation will necessitate significant investments in data handling and data protection for a very large number of organizations....[(more)]( Data Mining/Data Analysis [] [How to compute index numbers at top speed]( - This article presents different techniques to compute a rownumber column in DAX-based on a specific ranking, comparing slow and optimized approaches....[(more)]( Computing in the Cloud (Azure, Google , AWS) [] [Getting What You Need From Azure Storage Disks]( - If you need persistent data disks for Azure IaaS VMs that are supported on both Windows and Linux then you will be interested in Azure Storage Disks. These can increase the storage capacity of your VMs by up to a terabyte per disk, and they not only allow several availability options, but also offer a range of performance in terms of I/O throughput and latency. With right configuration, you can create as much of the right sort of storage as you need....[(more)]( Career Growth [] [Chief Data Officer Playbook: How to Be Successful in This Ground-Breaking Role]( - Companies looking to grow and extract value from their data are increasingly turning to Chief Data Officers (CDOs) to execute their data strategy. The role is new, and a playbook is necessary to address the many challenges CDOs face....[(more)]( [] [Flaming Hair And The Apocalypse]( - There will be times when you're approached by someone in a frenzy, their hair seemingly on fire. They'll go on to insist the world will come to an end if you don't handle some task for them immediately....[(more)]( [] [Technoloy Gender Gap by State]( - Despite overwhelming evidence showing the harm done to the companies themselves by gender discrimination, corporations around the world persist in overlooking qualified candidates based on gender. ...[(more)]( Azure SQL Database [] [The Biggest Threat To Microsoft’s Future is Artificial Intelligence]( - Artificial intelligence will not only change how and where compute happens but also the underlying structure of the technology that we use every day....[(more)]( [] [Microsoft Introduces Low Priority VMs to Cut Batch Processing Pricing]( - By using low-priority VMs, Microsoft is enabling a way to use Azure Batch with price reductions of up to 80%....[(more)]( Administration of SQL Server [] [Finding Queries that Cause Wait Stats in SQL Server]( - You’ve got some troubling wait stats in SQL Server. How can you tell which queries are causing those waits? Learn the pros and cons of different techniques to track down the cause of both common and tricky waits in SQL Server, including CXPACKET, PAGEIOLATCH, LCK, RESOURCE_SEMAPHORE, and THREADPOOL waits....[(more)]( [] [How To Use Temporal Tables For Easy Point-In-Time Analysis]( - Have you ever needed to look at what data in a table used to look like? If you have, it probably took a knuckle-cracking filled session of writing group-by statements, nested sub-queries, and window functions to write your time-travelling query. Fortunately SQL Server 2016 introduces a new feature to make our point-in-time analysis queries easy to write: temporal tables....[(more)]( [] [Disabling the identity cache in SQL Server 2017]( - One of the changes that’s been brought into the database engine in SQL Server 2017 is the ability to disable the cache that SQL keeps on identity values by using the ALTER DATABASE SCOPED CONFIGURATION command....[(more)]( [] [Experienced DBA: Rookie Mistake]( - The other day I had to update some records, in Production. I’m a firm believer of using explicit transactions and double checking things before committing a transaction. However, this means that I have to COMMIT said explicit transaction. And not go to lunch without doing so. Can you see my mistake? I bet you can....[(more)]( .NET Related Articles [] [Visual Studio Toolbox: SQL Server Data Tools in your DevOps Pipeline]( - Visual Studio 2017, SSDT and SQL Server 2016/SQL Database (on Azure) can be used in both migration and state based database development approaches....[(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 ©2017 Redgate Software Ltd, Newnham House, Cambridge Business Park, Cambridge, CB4 0WZ, United Kingdom. All rights reserved. Contact: webmaster@sqlservercentral.com