The Basics of Good T-SQL Coding Style (Database Weekly 5/8/2017)

The Complete Weekly Roundup of SQL Server News In this issue: Virtualization and Containers - [Building a container running SQL Server 2014 SP2 Developer](#28866) - [Database Virtualization (Cloning) for SQL Server DBAs and Developers](#28812) Vendors/3rd Party Products - [SQL Data Mask: now featuring configurable masks](#28857) - [Basic data masking for development work using SQL Clone and SQL Data Generator](#28856) T-SQL - [Visual Checks on How Data is Distributed in SQL Server](#28914) - [The Basics of Good T-SQL Coding Style](#28899) - [Checking for the Existence of Global Temp Table in TSQL](#28870) - [Don’t Use the String Concatenation “Trick” in SQL Predicates](#28867) - [Something you didn't know about validating full-text indexing](#28853) - [Limiting the Max–#SQLNewBlogger](#28824) - [Multiple usage of functions to ensure “value” is not null?!](#28821) The R Language in Databases - [Real-time scoring with Microsoft R Server 9.1](#28898) - [Data Exploration in R – Part I](#28861) - [Error 0x80004005 In SQL Server R Services](#28855) - [Where Europe lives, in 14 lines of R code](#28827) - [Make pleasingly parallel R code with rxExecBy](#28826) Tech News : General Interest - [SQL is 43 years old - here’s 8 reasons we still use it today](#28903) SQL Server Security and Auditing - [Replacing an expiring SQL Server encryption key](#28907) SQL Server Security - [Login Failed for xxx – Who’s Keeps Trying to Connect to my Server?](#28892) - [Simple Security Report](#28816) SQL Server News - [SQL Server community-driven enhancements in SQL Server 2017](#28835) SQL Server Internals - [SQL Server Temporary Object Caching](#28897) - [Unsung SQLOS: The SOS_UnfairMutexPair behind CMEMTHREAD waits](#28869) Spatial Data - [Drawing (again) with SQL Server](#28913) Software Development - [Code Beautifier](#28810) - [SSDT Dev in Visual Studio Code](#28809) Security news and thoughts - [Password reuse, credential stuffing and another billion records in Have I been pwned](#28916) - [Forging Voice](#28891) - [Google Docs Phishing Attack Tricks Unsuspecting Users to Click](#28890) - [Reckon you've seen some stupid security things? Here, hold my beer...](#28848) - [Jumping Airgaps with a Laser and a Scanner](#28846) - [Stealing Browsing History Using Your Phone's Ambient Light Sensor](#28845) - [Verizon Data Breach Investigations Report Reveals Ransomware Surge](#28843) - [Microsoft Boosts Safety with Windows Defender Security Center](#28836) Product Reviews and Articles - [Basic data masking for development work using SQL Clone and SQL Data Generator](#28882) - [New Book: Tabular Modelling In SQL Server Analysis Services](#28876) - [SQL Data Mask: now featuring configurable masks](#28832) PowerShell - [Finding the Service Name–#SQLNewBlogger](#28911) - [Finding Free Space Per Drive & Mount Point in PowerShell](#28875) - [PowerShell Quickie: Measuring script performance with the Stopwatch class](#28811) PowerPivot/PowerQuery/PowerBI - [On-Premise Power BI VOL. 2](#28912) - [Ultimate Sharing Strategy: Power BI Apps](#28904) - [Announcing Power BI Report Server](#28884) - [Power BI Premium, Report Server, Apps and API](#28872) - [K-mean clustering In R, writing R codes inside Power BI: Part 6](#28860) - [Better use of colors in Power BI](#28859) - [Using Apps and App Workspaces in Power BI](#28858) - [Power BI Custom Visuals Class (Module 49 – SandDance)](#28815) - [Clustering Concepts , writing R codes inside Power BI: Part 5](#28807) - [Adam & Patrick Unplugged – April 2017 [EP2]](#28806) Performance Tuning SQL Server - [SQLskills SQL101: Query plans based on what’s in memory](#28885) - [Adaptive Joins And Local Variables](#28883) - [Video: How to Use SQL Server 2016 Live Query Statistics for Watching Live Query Execution](#28874) - [Comparing Plans via Query Store](#28871) - [New in SSMS: Query Performance Troubleshooting made easier!](#28868) - [Look Ma, Adaptive Joins](#28851) - [Cardinality Estimation Limitations](#28841) - [SQLskills SQL101: Sequential Throughput](#28838) - [Performance differences between RevoScaleR, ColumnStore Table and In-Memory OLTP Table](#28825) - [DBA 101: Tuning lookups](#28823) - [Use Report View in Perfmon to Compare Database Counters (free video)](#28813) NoSQL - [Who Won the Debate Between Apache Spark and Hadoop?](#28905) - [MongoDB and the occasionally naughty query](#28849) Microsoft News : General Interest - [Microsoft’s Cloud Continues To Drive Its Q3 Results Upward](#28844) - [OpenSSH Security Testing Kick Off](#28828) MDX/DAX - [Avoid measure and column with the same name in #dax](#28896) - [DAX calculations with hierarchies: Set the order straight.](#28818) Hardware News - [Intel Announces New DC P4500 And P4600 Datacenter SSDs](#28881) - [Intel Unveils the Xeon Scalable Processor Family: Skylake-SP in Bronze, Silver, Gold and Platinum](#28880) - [Microsoft Surface Laptop: Price, specs, availability](#28878) - [Western Digital Starts Shipments of HGST Ultrastar He12 12 TB HDDs](#28831) HA/DR/Always On/Clustering - [Why Log Shipping is Better than Database Mirroring for Migrations](#28909) - [SQL SERVER – Clustered SQL Resource Not Coming Online](#28840) ETL/SSIS/ELT - [Data Science, Unicorns, and Some Things That Never Change](#28915) - [Self-Serve Data Preparation: Help Yourself!](#28863) - [SSIS – Adding Connections](#28852) Database Design, Theory and Development - [Set Theory: the Method To Database Madness](#28902) - [The Necessity of Foreign Keys](#28865) Data Visualisation - [The Datasaurus Dozen](#28877) Data Mining/Data Analysis - [Tips for managing metadata in a data lake](#28888) - [Data preparation in the age of deep learning](#28887) Conferences and Events - [Presenting Intelligent Data Integration Precon at SQL Saturday–Louisville!](#28895) - [A Couple Weeks until VS Live–Austin](#28822) - [Companero Conference](#28808) Computing in the Cloud (Azure, Google , AWS) - [Expanding a DBA’s Role](#28854) Career Growth - [Free SQL Server DBA Fundamentals training (DFW)](#28864) - [How Can I Get Accepted to Speak at the PASS Summit?](#28814) Bugs/Patches for SQL Server - [Status Update On Query Plan Hash Bug](#28833) Big Data - [The rise of autonomous data platforms](#28889) Backup and Recovery - [Understanding Database Backup Encryption in SQL Server](#28873) Azure SQL Database - [Azure Load Balancers and SQL Server](#28901) - [Newly created Azure SQL databases will be encrypted at rest by default](#28894) - [Threat Detection in Azure SQL Database is now Generally Available](#28893) - [SQL Azure Database – Msg 40197, Level 20 – The Service has Encountered an Error Processing Your Request. Please Try Again. Error Code 40549](#28886) - [Sizing an Elastic Pool eDTU and Storage resources use Graphical runbook](#28879) - [Creating an Azure SQL Database via PowerShell](#28820) - [Removing an Azure SQL Database using PowerShell..](#28819) Azure SQL Data Warehouse and Data Lake - [Azure SQLDW–Distribution](#28850) - [Data Factory Scheduling](#28829) - [Azure Data Factory and the Case of the Missing JRE That Wasn’t](#28817) AI/Machine Learning/Cognitive Services - [Deep Learning Technology Brings Neural Networks into Practical Application](#28862) - [Finding Solace in Defeat by Artificial Intelligence](#28847) - [IBM Watson Showing Its Smarts on Factory Floors](#28842) - [16 Machine Learning Terms You Should Know](#28830) Administration of SQL Server - [Trace Flag 1117 Growth and Contention](#28910) - [It's Just Another 9002...another error like all the rest...](#28908) - [GroupBy conference recording of Introducing the SQL Server 2016 Query Store available now!](#28906) - [Visual Checks on How Data is Distributed in SQL Server](#28900) - [SQL SERVER – Ghost Cleanup Getting Hung and Blocking Checkpoint Process. What’s Wrong?](#28839) - [SQL Server Diagnostic Information Queries for May 2017](#28837) - [First Responder Kit Release: Ça plane pour moi](#28834) [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-08 [Foundry]( Could you help with some research? The Foundry team at Redgate want to hear about your experience with SQL Server asset management. Fill in this short survey to help out, and also to enter their prize draw for a $100 Amazon gift card. [Complete survey.]( [SQL Clone]( NEW SQL Clone - version 1 available now! Create copies of production databases and SQL backups in seconds and save up to 99% of disk space using SQL Clone. Redgate’s new tool removes much of the time and resource needed to create and manage database copies, allowing teams to work on local environments to develop, test and diagnose issues faster. [Try it free.]( [SQL Prompt]( Write, format, and refactor SQL effortlessly 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 tab coloring you’ll never deploy to the wrong environment again. [Download your free trial.]( Editorial - The Power BI Pivot This week we had quite a bit of Power BI content appear on various blogs and sites. Some of this was normal traffic, with Power BI gaining popularity and more data professionals writing about the topic. There are always quite a few blogs from [The Guy in a Cube](, including a look at [how to better use colors]( but I also see more and more posts from others. We had others about [R in Power BI]( and [custom visuals](. The big news this week was [the announcement of the Power BI Report Server]( from Microsoft. This is coming late in the second quarter of 2017, according to the SSRS blog, which I assume means in the next month. The second quarter is almost over, and with Microsoft ending their fiscal year in June, I'd expect this to release sooner than later. There is also a Power BI Premium, which apparently allows an enterprise to purchase capacity in the cloud and then concurrently deploy Power BI reports on-premises. Many people have been looking forward to this. I've been hearing from some reporting authors that we were going to get Power BI rolled into SSRS as an option for SQL reporting. Apparently we are, but there are some new costs here. This may change your calculation of whether this is a good move for your organization, but certainly I think that Power BI is one of the better reporting engines I've seen. The licensing is confusing, and I liked in a few other thoughts on the announcement from others, such as [Dan English]( and [J]([ames Serra](. Both note that the free tier gets to connect to the same data sources as the Pro previously could (including on premises data), which is good. The storage quota increases to 10GB, up to 8 refreshes a day and a bit more. Pro is still $10/month, and apparently is still needed for those that spend the money on Premium if they author reports or export to Excel, even if you have Enterprise Edition. At least, that's what Mr. English notes in the license terms. Alternatively you can spend a few thousand dollars a month for the cloud version and let all your employees access reports in the cloud or from a local report server. Power BI has been one of those amazing products that I've been very impressed with in the technical sense. I can build amazing reports, and get great interactivity, allowing me to analyze data in a way that hasn't always been possible in a visual tool. Almost every time I see a new type of report or some demo, I'm impressed with what's possible. I love the desktop tool for my own use, since it's a nice, responsive and easy to understand interface. However the deployment method (originally just with [PowerBI.com]() and the licensing have always struck me as a bit strange, and perhaps somewhat crippled. It certainly felt that many features didn't cost me money directly, but there was a cost. With this latest change, it feels like another pivot to try and increase revenue, but not in a way that's attractive to me. Microsoft wants to make money, and they've certainly invested money in the Power BI platform, so they get to make the rules. I do like Power BI, and if the costs make sense to you, and there's enough value, this is certainly a very powerful visualization and reporting tool that I can only imagine will become more and more useful over time. Steve Jones 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. Virtualization and Containers [] [Building a container running SQL Server 2014 SP2 Developer]( - One of the things about working with SQL in Docker is that you kinda have to use the images that are on the Docker Hub. Now this is great if you want SQL Server 2016 or 2017 but what about earlier versions? Now, this is a bit involved so I should point ......[(more)]( [] [Database Virtualization (Cloning) for SQL Server DBAs and Developers]( - The trend toward virtualized computing resources shows no signs of abating. Compute, memory, storage, and operating systems are commonly available on-demand.   Code is accessed via Git or other repositories, and can be deployed to automated ......[(more)]( Vendors/3rd Party Products [] [SQL Data Mask: now featuring configurable masks]( - SQL Data Mask is the latest prototype to come out of Redgate Foundry. It copies your database while anonymizing personal data, and you can use it to mask your databases right now, free of charge. Here are the details....[(more)]( [] [Basic data masking for development work using SQL Clone and SQL Data Generator]( - This article describes a lightweight copy-and-generate approach for making a sanitized version of a production database available to development teams with SQL Clone and SQL Data Generator....[(more)]( T-SQL [] [Visual Checks on How Data is Distributed in SQL Server]( - This article is all about how to go about looking at a visual representation of the way that data is distributed in tables, in a quick unpolished way, while you are programming with SSMS or whatever you prefer to use. The way that the values of your ......[(more)]( [] [The Basics of Good T-SQL Coding Style]( - TSQL Code must work properly and efficiently. That's not enough though. Unless you are working alone, have perfect memory and plan to never change job, then you need to comment and document your code, it must be inherently readable, well laid out, use informative and obvious names, and it must be robust and resilient; written defensively. It must not rely on deprecated features of SQL Server, or assume particular database settings. Robert Sheldon starts a series of articles that explains the basics....[(more)]( [] [Checking for the Existence of Global Temp Table in TSQL]( - Every now and again, I need use a global temporary table for some testing or demo code. And each time I do, I stumble a little bit when it comes to checking for the existence of the global temp table, in order to make my code re-runnable. Here’s the ......[(more)]( [] [Don’t Use the String Concatenation “Trick” in SQL Predicates]( - In SQL, quite often, we want to compare several values with each other. For instance, when we’re looking for a specific user by their first and last names, we’ll write a query like this one: SELECT * FROM customer WHERE first_name = 'SUSAN' AND last_name ......[(more)]( [] [Something you didn't know about validating full-text indexing]( - My favourite way to compare technical experience with others is to ask them about the ways in which something can fail. Anyone can recite BOL but it's edge-case knowledge that shows a DBA has been hardened through trench warfare. After all if you've seen my talk about operational validation, or better yet implemented it, you'll know that at scale, rare events aren't rare....[(more)]( [] [Limiting the Max–#SQLNewBlogger]( - Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers. I was chatting with someone the other day about MAX() and how it can work within a set of rows, rather than across an entire result ......[(more)]( [] [Multiple usage of functions to ensure “value” is not null?!]( - In this post I’m describing a piece of code that is not well written. The author (unknown) had applied three functions for a variable to ensure it never accepts NULLs. The code is given in the next screen shot. ScreenShot 1. The query with three functions ......[(more)]( The R Language in Databases [] [Real-time scoring with Microsoft R Server 9.1]( - Once you've built a predictive model, in many cases the next step is to operationalize the model: that is, generate predictions from the pre-trained model in real time. In this scenario, latency becomes the critical metric: new data typically become ......[(more)]( [] [Data Exploration in R – Part I]( - With the analytics work I’ve been involved with over the last 15 years, I estimate that 70% of the effort has been devoted to data access/integration/wrangling/curation, about 20% focused on data exploration, and the remaining 10% fitting algorithms/models. ...[(more)]( [] [Error 0x80004005 In SQL Server R Services]( - I recently worked through a strange error (with help from a couple sharp cookies at Microsoft) and wanted to throw together a quick blog post in case anybody else sees it. I have SQL Server R Services set up, and in the process of running a fairly complex stored procedure, got the following error message:...[(more)]( [] [Where Europe lives, in 14 lines of R code]( - Via Max Galka, always a great source of interesting data visualizations, we have this lovely visualization of population density in Europe in 2011, created by Henrik Lindberg:  Impressively, the chart was created with just 14 lines of R code: (To ......[(more)]( [] [Make pleasingly parallel R code with rxExecBy]( - Some things are easy to convert from a long-running sequential process to a system where each part runs at the same time, thus reducing the required time overall. We often call these "embarrassingly parallel" problems, but given how easy it is to reduce ......[(more)]( Tech News : General Interest [] [SQL is 43 years old - here’s 8 reasons we still use it today]( - In this year’s Stack Overflow Developer’s Survey 64,000 developers laid bare their innermost developer secrets. How they learn, the tools they use, what they want - it’s all there. The whole survey is a fascinating foray into the brains of developers and the global software industry....[(more)]( SQL Server Security and Auditing [] [Replacing an expiring SQL Server encryption key]( - So you’re using encryption in SQL Server, but you’ve discovered that the expiration date of a certificate is expiring. What do you do? The process of safely replacing the certificate is called rotating the encryption key. It’s important to do, and SQL ......[(more)]( SQL Server Security [] [Login Failed for xxx – Who’s Keeps Trying to Connect to my Server?]( - I recently ran into an issue in which a machine was connecting to SQL and continuously generating a login failure every second. The account name was NT AUTHORITY\ANONYMOUS LOGON which typically means a Kerberos related double hop connection with some ......[(more)]( [] [Simple Security Report]( - Quite a while ago I created a couple of little security scripts to help me out with permissions research. Over time they grew into the procedures sp_dbpermissions and sp_srvpermissions. I recently updated them to v 6.0 and one of the main reasons was ......[(more)]( SQL Server News [] [SQL Server community-driven enhancements in SQL Server 2017]( - This post was authored by the SQL Server Tiger Team Although SQL Server 2016 runs faster, SQL Server 2017 promises to run even faster and empower customers to run smarter with intelligent database features like the ability to run advanced analytics using ......[(more)]( SQL Server Internals [] [SQL Server Temporary Object Caching]( - Creating a table is a relatively resource-intensive and time-consuming operation. The server must locate and allocate storage space for the new data and index structures, and make the corresponding entries in multiple system metadata tables. All this ......[(more)]( [] [Unsung SQLOS: The SOS_UnfairMutexPair behind CMEMTHREAD waits]( - As with the droppings of the Questing Beast, we recognise synchronisation code paths by their emissions. But even when not leaving telltale fewmets, these creatures wander among us unseen, unsung, until shutdown doth us part. The place of the SOS_UnfairMutexPair Bob ......[(more)]( Spatial Data [] [Drawing (again) with SQL Server]( - You have my permission to skip this post. This one’s just for me. So I’ve been drawing again with SQL Server’s spatial results tab, the first time I posted something was with Botticelli’s Birth of Venus in More images from the Spatial Results Tab. Why ......[(more)]( Software Development [] [Code Beautifier]( - One of the challenges I've faced as a blogger is quickly reproducing code that looks good in HTML. I've tried a few different online code conversion sites, and even a C# library. But I never quite got the results I wanted. I found myself revising the ......[(more)]( [] [SSDT Dev in Visual Studio Code]( - I have been quite interested by vs code and have been using it more and more recently. I use it for all my GO (#golang FTW) work and also powershell and I have been toying with the sql tools team's sql extension which is great. For a long time I have ......[(more)]( Security news and thoughts [] [Password reuse, credential stuffing and another billion records in Have I been pwned]( - Sponsored by: Netsparker - Scan your websites & detect SQL Injection, XSS and other vulnerabilities with the dead accurate Netsparker web security scannerThe short version: I'm loading over 1 billion breached accounts into HIBP. These are from 2 different ......[(more)]( [] [Forging Voice]( - LyreBird is a system that can accurately reproduce the voice of someone, given a large amount of sample inputs. It's pretty good -- listen to the demo here -- and will only get better over time. The applications for recorded-voice forgeries are obvious, ......[(more)]( [] [Google Docs Phishing Attack Tricks Unsuspecting Users to Click]( - NEWS ANALYSIS: A massive phishing attack made use of an API called Google Docs to trick Google users into clicking and becoming victims. ...[(more)]( [] [Reckon you've seen some stupid security things? Here, hold my beer...]( - Sponsored by: Netsparker - Scan your websites & detect SQL Injection, XSS and other vulnerabilities with the dead accurate Netsparker web security scannerMy mate Lars Klint shared this tweet the other day: Your password is not unique. pic.twitter.com/ga4GwxtzrQ— ......[(more)]( [] [Jumping Airgaps with a Laser and a Scanner]( - Researchers have configured two computers to talk to each other using a laser and a scanner. Scanners work by detecting reflected light on their glass pane. The light creates a charge that the scanner translates into binary, which gets converted into ......[(more)]( [] [Stealing Browsing History Using Your Phone's Ambient Light Sensor]( - There has been a flurry of research into using the various sensors on your phone to steal data in surprising ways. Here's another: using the phone's ambient light sensor to detect what's on the screen. It's a proof of concept, but the paper's general ......[(more)]( [] [Verizon Data Breach Investigations Report Reveals Ransomware Surge]( - While some numbers have shifted, a Verizon researcher says that, year-over-year, little has actually changed and the same types of attacks continue to be successful. ...[(more)]( [] [Microsoft Boosts Safety with Windows Defender Security Center]( - The latest update to Microsoft's Windows 10 operating system features a number of changes to its built-in security protection. Rolling out in phases since April 11, the Creators Update adds a new dashboard display for Windows Defender, introduces dynamic ......[(more)]( Product Reviews and Articles [] [Basic data masking for development work using SQL Clone and SQL Data Generator]( - This article describes a lightweight copy-and-generate approach for making a sanitized version of a production database available to development teams with SQL Clone and SQL Data Generator. We build the latest database version (schema only), then copy ......[(more)]( [] [New Book: Tabular Modelling In SQL Server Analysis Services]( - My good friends Marco Russo and Alberto Ferrari have published a second edition of the SSAS Tabular book that we wrote together a few years ago. It’s called “Tabular Modelling In SQL Server Analysis Services” and it covers pretty much everything you ......[(more)]( [] [SQL Data Mask: now featuring configurable masks]( - SQL Data Mask is the latest prototype to come out of the Foundry. It copies your database while anonymizing personal data. You can use it to mask your databases right now, free of charge. What we’ve been up to: Progress on SQL Data Mask is advancing ......[(more)]( PowerShell [] [Finding the Service Name–#SQLNewBlogger]( - Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers. A quick one today, and this one I remembered without looking anything up. I recently needed the service name to kill it for a ......[(more)]( [] [Finding Free Space Per Drive & Mount Point in PowerShell]( - Back in 2010 I was inspired by a twitter conversation between Nicolas Cain (blog | twitter) and Dave Levy (blog | twitter) about checking Disk Space & Mount Points to create a PowerShell function called Get-DisksSpace which can tell you how much space ......[(more)]( [] [PowerShell Quickie: Measuring script performance with the Stopwatch class]( - Did you ever want discrete measurement of different parts of your code? Then you're in luck. ...[(more)]( PowerPivot/PowerQuery/PowerBI [] [On-Premise Power BI VOL. 2]( - Here we go again, round 2 (or should I say Vol. 2). I am back to discuss how I feel (and I am sure quite a few others do as well) about the new information we received this week on Microsoft Power BI on-premise. You can check out my first blog post regarding ......[(more)]( [] [Ultimate Sharing Strategy: Power BI Apps]( - I have previously written about some of the methods of sharing content in Power BI, such as Work spaces, Publish To Web, and Content Pack. Latest update of Power BI includes stunning update for sharing mechanism which is the best of all for sharing. In ......[(more)]( [] [Announcing Power BI Report Server]( - This post was authored by the Power BI Team Today Microsoft announced Power BI Premium — a capacity-based licensing model that increases flexibility for how users access, share and distribute content in Power BI. The new offering also introduces the ......[(more)]( [] [Power BI Premium, Report Server, Apps and API]( - Announced today are some really cool new Power BI features: Power BI Premium Previously available were two tiers, Power BI Free and Power BI Pro ($10/user/month).  The problem with Power BI Pro is that for large organizations, this can add up.  In addition, their ......[(more)]( [] [K-mean clustering In R, writing R codes inside Power BI: Part 6]( - In the previous post, I have explained the main concepts and process behind the K-mean clustering algorithm. Now I am going to employ this algorithm for classifying my Fitbit data in power bi.  as I have explained in part 5, I gathered theses data ......[(more)]( [] [Better use of colors in Power BI]( - In this video, Chuck Sterling (@chass) is our guest, and he talks about color themes within Power BI. He looks at how you can quickly create your own themes to use within Power BI, or use the theme gallery on the communities site to get pre-created themes ......[(more)]( [] [Using Apps and App Workspaces in Power BI]( - In this video, I introduce you to Apps and App Workspaces within Power BI. Apps are a new concept in Power BI and I dive into how to create, manage and use Apps. I will also touch on how Power BI Premium fits into the App story. Learn more about Apps ......[(more)]( [] [Power BI Custom Visuals Class (Module 49 – SandDance)]( - In this module you will learn how to use the SandDance Power BI Custom Visual. The SandDance visual is an incredibly interactive visual that allows you to create insights to view your data in multiple ways and with animations. Module 49 – SandDance Downloads Power ......[(more)]( [] [Clustering Concepts , writing R codes inside Power BI: Part 5]( - Some times we just need to see the natural trend and behaviour of data with out doing any prediction. we just want to check how naturally our business data can be grouped. According to the Wikipedia , Cluster analysis or clustering is the task of grouping ......[(more)]( [] [Adam & Patrick Unplugged – April 2017 [EP2]]( - In this video, Patrick and I talk about what is on our minds. We take a look at some updates in Analysis Services 2017 CTP 2.0. We look at the new navigation experience in Power BI that is coming out in early May. We also talk about some friends, Santa ......[(more)]( Performance Tuning SQL Server [] [SQLskills SQL101: Query plans based on what’s in memory]( - As Kimberly blogged about recently, SQLskills is embarking on a new initiative to blog about basic topics, which we’re calling SQL101. We’ll all be blogging about things that we often see done incorrectly, technologies used the wrong way, or where there ......[(more)]( [] [Adaptive Joins And Local Variables]( - With new features I really love kicking the tires to see how they work with existing features, and if they fix existing performance troubleshooting scenarios. One issue that I see frequently is with local variables. I’m not going to get into Cardinality ......[(more)]( [] [Video: How to Use SQL Server 2016 Live Query Statistics for Watching Live Query Execution]( - In this video, I show how to enable live query statistics in SQL Server 2016 in order to see execution flow for running queries, and how to watch the live execution plan for all queries using the lightweight profiling feature of SQL Server 2016 SP1:  The ......[(more)]( [] [Comparing Plans via Query Store]( - A small but nice little feature I have been using recently can be found within Query Store. Let’s say you have 2 Plan IDs for a query, naturally you want to view the execution plan for the different plans. In … Continue reading ? ...[(more)]( [] [New in SSMS: Query Performance Troubleshooting made easier!]( - The community already uses tools that can make it easier to read and analyze query plans (including SSMS), but these require significant expertise in understanding query processing and plans in order to be able to actually find and fix root causes. In ......[(more)]( [] [Look Ma, Adaptive Joins]( - But I just got the optimizer to pick an Adaptive Join! It took a few tries to figure out what would cause some guesswork to happen, but here it is....[(more)]( [] [Cardinality Estimation Limitations]( - (Be sure to checkout the FREE SQLpassion Performance Tuning Training Plan - you get a weekly email packed with all the essential knowledge you need to know about performance tuning on SQL Server.) As you might know, SQL Server uses internally Statistics ......[(more)]( [] [SQLskills SQL101: Sequential Throughput]( - Over the past couple of months, SQLskills has embarked on a new initiative to blog about basic topics, which we’re calling SQL101. We’ll all be blogging about things that we often see done incorrectly, technologies used the wrong way, or where there ......[(more)]( [] [Performance differences between RevoScaleR, ColumnStore Table and In-Memory OLTP Table]( - Running *.XDF files using RevoScaleR computational functions versus have dataset available in Columnstore table or in In-Memory OLTP table will be focus of comparison for this blog post. For this test, I will use the AirLines dataset, available here. ...[(more)]( [] [DBA 101: Tuning lookups]( - Sometimes it helps to go back to the basics and indexing is always a great topic. SQL Server has a wide variety of indexes; but, today we’re going classic and talking about Non-clustered indexes. Microsoft has a lot of great resources discussing these ......[(more)]( [] [Use Report View in Perfmon to Compare Database Counters (free video)]( - Sometimes you need to compare lots of counters at once – for example, counters that report at the database level. This can be frustrating in “Line” view, but the “Report” view in perfmon makes life much simpler. In this four minute video, I show you ......[(more)]( NoSQL [] [Who Won the Debate Between Apache Spark and Hadoop?]( - Click to learn more about video blogger Andrew Brust. The Big Data & Brews video blog series continues with host Andrew Brust, Senior Director of Market Strategy and Intelligence. The series touches on hot topics within the business of Big Data, Analytics, ......[(more)]( [] [MongoDB and the occasionally naughty query]( - It’s no secret that databases like uniqueness and high cardinality. Low cardinality columns do not make good candidates for indexes. A recent issue I had with MongoDB proved that NoSQL is no different in this regard. The MongoDB Query Planner is quite ......[(more)]( Microsoft News : General Interest [] [Microsoft’s Cloud Continues To Drive Its Q3 Results Upward]( - Microsoft has released it’s third quarter earnings today with the company reporting revenue of $22.1 billion with operating income at $5.6 billion, GAAP. Net Income during the period was $4.8 billion with a diluted EPS for the quarter being $0.61; during ......[(more)]( [] [OpenSSH Security Testing Kick Off]( - Over the past while, we’ve been very busy porting OpenSSH to Win32. We’ve been working with the OpenSSH community in our GitHub repository, and are nearing a point where we are taking steps to make it production ready. Security is obviously something ......[(more)]( MDX/DAX [] [Avoid measure and column with the same name in #dax]( - I recently wrote an article about duplicated names in DAX to highlight a possible case where you can have a measure with the same name of a column. This is not a good idea, mainly because the syntax to reference a measure and a column is identical, and ......[(more)]( [] [DAX calculations with hierarchies: Set the order straight.]( - [2017-Apr-30] If you have ever tried to create calculated measures in SSAS Tabular/ Power BI models where different levels of your model hierarchy had to be considered then you'd understand how DAX language doesn't have a straight way to identify parent ......[(more)]( Hardware News [] [Intel Announces New DC P4500 And P4600 Datacenter SSDs]( - Intel has announced the most significant updates to their enterprise PCIe SSD lineup since the launch of their first NVMe SSDs. The new Intel SSD DC P4500 and P4600 are the successors to the P3500, P3600 and P3700 series, modernizing the product line ......[(more)]( [] [Intel Unveils the Xeon Scalable Processor Family: Skylake-SP in Bronze, Silver, Gold and Platinum]( - One of the more interesting elements of last week’s news was that some of the names of the future Intel Xeon processors, based on the Skylake microarchitecture, were accidentally ‘leaked’ via an official Intel Product Change Notification. In that list, ......[(more)]( [] [Microsoft Surface Laptop: Price, specs, availability]( - Microsoft has added a new line of Surfaces to its family. The Surface Laptop is a premium device meant to run Windows 10 S. ...[(more)]( [] [Western Digital Starts Shipments of HGST Ultrastar He12 12 TB HDDs]( - Western Digital on Wednesday announced that it had begun to ship its HGST Ultrastar He12 hard drives with 12 TB of capacity. The HDDs are the first drives to employ eight platters, so the fact that Western Digital is now shipping them is important not ......[(more)]( HA/DR/Always On/Clustering [] [Why Log Shipping is Better than Database Mirroring for Migrations]( - This topic has come up several times recently, so I feel the need to blog on it. As the person who wrote the book on Database Mirroring, it will probably come as a surprise to many of you that I believe that log shipping is a much better tool for database ......[(more)]( [] [SQL SERVER – Clustered SQL Resource Not Coming Online]( - When I was on-site for the performance tuning workshop, few DBAs suddenly got call as there was an unexpected downtime of SQL Server. As per them, after some maintenance activities were scheduled, the SQL Server resource failed to come online on both ......[(more)]( ETL/SSIS/ELT [] [Data Science, Unicorns, and Some Things That Never Change]( - This post is a follow-up to Mind the Gap where I wrote about ?, the difference between our perception of ourselves and other’s perception of us. In Mind the Gap I focused on personal and community ramifications of minimizing your ?. In this post I write ......[(more)]( [] [Self-Serve Data Preparation: Help Yourself!]( - The Self-Serv Data Preparation trend is growing by leaps and bounds. Why? Well, there are a lot of reasons to like Self-Serve Data Preparation and Self-Serve Business Intelligence solutions. Here are just a few: Empowering Business Users Making the Best ......[(more)]( [] [SSIS – Adding Connections]( - While recently examining an existing SSIS package before implementing some needed changes I noticed the connection manager for multiple flat files....[(more)]( Database Design, Theory and Development [] [Set Theory: the Method To Database Madness]( - Now that we’re finally a third of the way through this series, things are finally starting to come together. Sure, we know about quite a few different data structures, how they work, which ones are fast, and how certain ones are more helpful for solving specific problems than others....[(more)]( [] [The Necessity of Foreign Keys]( - My May post @All Analytics."Thus, FKs are not just "a good database design habit", but "a user-oriented means" -- as Codd referred to them -- to represent information about relationships between objects of different types in relational databases. The ......[(more)]( Data Visualisation [] [The Datasaurus Dozen]( - There's a reason why data scientists spend so much time exploring data using graphics. Relying only on data summaries like means, variances, and correlations can be dangerous, because wildly different data sets can give similar results. This is a principle ......[(more)]( Data Mining/Data Analysis [] [Tips for managing metadata in a data lake]( - Metadata is central to a modern data architecture.Modern data architectures promise broader access to more and different types of data in order to enable an increasing number of data consumers to employ data for business-critical use cases. Examples ......[(more)]( [] [Data preparation in the age of deep learning]( - The O’Reilly Data Show Podcast: Lukas Biewald on why companies are spending millions of dollars on labeled data sets.In this episode of the Data Show, I spoke with Lukas Biewald, co-founder and chief data scientist at CrowdFlower. In a previous episode ......[(more)]( Conferences and Events [] [Presenting Intelligent Data Integration Precon at SQL Saturday–Louisville!]( - I am honored to present Intelligent Data Integration – a day-long precon at SQL Saturday #643 in Louisville Kentucky 4 Aug 2017! Synopsis : What is Intelligent Data Integration? SSIS packages developed using tried and true design patterns, built to participate ......[(more)]( [] [A Couple Weeks until VS Live–Austin]( - VS Live takes place in Austin on May 15-18, 2017. This is a great conference in a fun city. It’s a full stack event, with the change to learn about cloud computing, ALM/DevOps, SQL, and Visual Studio, all at the same event. You can move from room to ......[(more)]( [] [Companero Conference]( - I am extremely pleased to have the opportunity to speak at Compañero Conference. Compañero Conference (or CompaCon, as I can’t pronounce “Compañero” because I can’t roll that n) is a paid conference in Norfolk, Virginia, starting the evening of October ......[(more)]( Computing in the Cloud (Azure, Google , AWS) [] [Expanding a DBA’s Role]( - I have never really considered my self a System Administrator, but I am familiar enough with the Windows Operating System to consider myself a little dangerous. However, with more and more of our clients utilizing the power of Microsoft Azure VMs I see my role as a SQL Consultant starting in encompass System Administrator duties....[(more)]( Career Growth [] [Free SQL Server DBA Fundamentals training (DFW)]( - Free.  For real.  In person.  Interactive. I really like teaching the basics of anything to people new to it.  Right now that is SQL Server, and cycling (I am Kevin3NF or CoachKev depending…).  Every day on the various forums I see the same questions ......[(more)]( [] [How Can I Get Accepted to Speak at the PASS Summit?]( - There has been some discussion and concern over the changes to the speaker selection process for the PASS Summit. I’ve written some thoughts, as have others (Joey, Andy), but I was struck a bit by Eugene Meidiner’s post. In it, he asks, how can I speak? ......[(more)]( Bugs/Patches for SQL Server [] [Status Update On Query Plan Hash Bug]( - Not long ago I posted about a bugBlitzCache because Query Plan Hash in the XML was being truncated. Well, yesterday I got an email that Adam Machanic has never gotten (I kid, I kid). CLOSED AS FIXED Well, sort of. It’ll be fixed in vNext, and a CU for ......[(more)]( Big Data [] [The rise of autonomous data platforms]( - A possible solution to the complexities that plague big data projects.Big data today is, well, big. The 2016 Worldwide Semiannual Big Data and Analytics Spending Guide from IDC predicts that worldwide revenues for big data will grow from $130 billion ......[(more)]( Backup and Recovery [] [Understanding Database Backup Encryption in SQL Server]( - Terabytes of data, millions of rows; the entire business depends on this — a database administrator’s responsibility is as massive as it sounds. Apart from keeping the data secure, a database administrator also has to keep the system up and running, ......[(more)]( Azure SQL Database [] [Azure Load Balancers and SQL Server]( - Load balancing in Azure has more importance for the DBA, because it is essential for Windows Server Failover Clustering in Azure, whether it is for AlwaysOn Availaiblity Groups, Failover Clustered Instances, or any other highly-available solution. Azure load balancing works out the location of the availability group, and routes traffic there. The load balancer detects a failure, and routes traffic to the new primary replica. Joshua Feierman gives an overview of what is required....[(more)]( [] [Newly created Azure SQL databases will be encrypted at rest by default]( - Earlier today, we announced that all new Azure SQL databases will be encrypted with Transparent Data Encryption (TDE) by default, to make it easier for everyone to benefit from encryption at rest. The change in default will happen gradually by region. Visit ......[(more)]( [] [Threat Detection in Azure SQL Database is now Generally Available]( - We are excited to announce that Threat Detection is now Generally Available in Azure SQL Database. Threat Detection detects anomalous activities indicating unusual and potentially harmful attempts to access or exploit databases. Threat Detection leverages ......[(more)]( [] [SQL Azure Database – Msg 40197, Level 20 – The Service has Encountered an Error Processing Your Request. Please Try Again. Error Code 40549]( - While trying to help one of my clients, I was shown below error which was received using bulk insert command. The command was used to move data from Azure Blob Storage to SQL Azure Database. Let us see how to fix the error processing request. Msg 40197, ......[(more)]( [] [Sizing an Elastic Pool eDTU and Storage resources use Graphical runbook]( - This runbook sizing an elastic pool set Maximum DTUs utilized by all databases in the pool  . And set Maximum storage bytes utilized by all databases in the pool. You can look up the available characteristics and tiers on ......[(more)]( [] [Creating an Azure SQL Database via PowerShell]( - This post is about using the brilliance of PowerShell to script the creation of databases in Azure. Background: Apart from the obvious question of why not? The actual reason is one of standards and how I did not adhere to my own standards that I’ve been ......[(more)]( [] [Removing an Azure SQL Database using PowerShell..]( - In this post we will remove some databases located in Azure. This related to my last post where I am cleaning up some badly named databases and replacing them with standardised database names. So we have this situation: We want to get rid of the top ......[(more)]( Azure SQL Data Warehouse and Data Lake [] [Azure SQLDW–Distribution]( - We've spoken about the MPP nature of SQLDW, and how it scales by changing the number of compute nodes associated to a fixed number of storage distributions. But the way that you organise data across these distributions can drastically change how the system performs - if it's done badly, one of your compute nodes will be doing all the work and your performance will be very poor. ...[(more)]( [] [Data Factory Scheduling]( - Often questions arise on how to correctly schedule Azure Data Factory pipelines. In this post we will look at some of the possible options and how they can affect your scheduling. The first two options revolve around changing when the datasets will become ......[(more)]( [] [Azure Data Factory and the Case of the Missing JRE That Wasn’t]( - On a recent project I used Azure Data Factory (ADF) to retrieve data from an on premises SQL Server 2014 instance and land them in Azure Data Lake Store (ADLS) as ORC files. This required the use of the Data Management Gateway (DMG). Setup was quick ......[(more)]( AI/Machine Learning/Cognitive Services [] [Deep Learning T