The Complete Weekly Roundup of SQL Server News

Hand-picked content to sharpen your professional edge

I was at a developer's conference a while back, listening to a developer holding forth, up on the podium. I must admit that my attention was wandering slightly when I was jarred to attention by the phrase "and we saved several SQL joins by doing so". Eh? I was suddenly all ears.

It seems that he was explaining how, by clever use of views and functions, they had eliminated a few 'expensive' SQL joins. In short, he was evangelizing as bad a strategy as you could imagine. Views upon views and table-valued functions gathered up a huge gob of data, constituting an entire customer object, and hurled the whole quivering mass into the application. Most of the data was entirely irrelevant to the process but it was sent anyway; customer history, previous addresses, payment details, the lot.

Leaving to one side the major crime of requesting more data than you need, there was the irritating assumption that a join would always take a significant time. "I'm on the train; I'll be home in twenty joins" I imagined him telling his family, as he started his commute home. He'd just assumed that fewer joins meant faster queries, and if it still seemed slow, well, what more can one do? That's SQL databases for you.

If you get into the habit of checking your timings, you are likely to know that a query join is generally either fine or awful, with only very rare half-measures. If queries with more joins are more likely to be slower, it is just that there is an increased chance of kicking up a bad'un, such as a join that highlights a problem with your indexing strategy. Once you've fixed the bad'uns, the problems generally vanish. Of course, if you like to join tables to unindexed views or TVFs, then you should expect all joins to be as interminable as Franz Shubert Lieder songs.

There are many techniques for timing database routines, and they all have their place. However, some are more important than others. For the developer, I reckon that the routine end-to-end timings for a process, such as instantiating a customer object and populating it with the required data, tells you the basics. It answers the question 'Do you have a problem?' It can't tell you the cause. It could be as broad a problem as a slow network; it could be an immaculately-contrived and beautifully indexed procedure that is doing something very foolish, such as stocking the enormous customer object; it could be as trivial as a missing hint or option in a routine. Who knows? But until you're clear and precise about the symptoms, you can't expect to find a cure.

Phil Factor.

Phil Factor from SQLServerCentral.com

» Join the debate, and respond to today's editorial on the forums

All the headlines and interesting SQL Server information that we've collected over the past week, and sometimes even a few repeats if we think they fit. These headlines are gathered throughout the week and are posted in real time at the website. Check there for information throughout the week or enjoy this weekly summary of the SQL Server world.

Vendors/3rd Party Products

SQL Clone Quick Tip: Offloading DBCC checks - If corruption creeps into a database it can and will derail the best-laid disaster recovery plans. To be certain that a database is corruption-free, you need to run regular DBCC CHECKDB checks on that database. Unfortunately this is a resource-intensive task, but SQL Clone can help....(more)

Alert Inbox Grouping Preview in SQL Monitor - The SQL Monitor team are hard at work, improving how their users manage alerts. This blog post from Ally Parker explains what they're currently working on, and how you can get involved by giving your feedback....(more)

Remembering passwords in SQL Compare and SQL Data Compare - The Redgate SQL Compare team have recently added a feature to automatically populate your SQL Server credentials when you’re using SQL Compare or SQL Data Compare - here are the details of the new feature....(more)

How to record T-SQL execution times using a SQL Prompt snippet - In this quick tip, Phil Factor shares the SQL Prompt snippet he uses as a standard testbed for getting execution times for procedures and functions....(more)

T-SQL

Temporal table maker - Oh, we don't know what data we need yet so could you keep it all, forever? ... Temporal tables to the rescue?...(more)

Graph Matching with T-SQL Part 3: Maximum Matching - Itzik Ben-Gan offers a solution for maximum matching, where a maximal matching is a matching to which you cannot add any more edges of the graph, and a maximum matching is a maximal matching with the maximum possible number of edges from the graph....(more)

A Subtle Difference Between COALESCE and ISNULL - Shane O'Neill points out a lesser-known difference between how COALESCE and ISNULL work....(more)

How NOLOCK Will Block Your Queries - Use on NOLOCK is dangerous; reading uncommitted reads can return dirty data, phantom reads, and non-repeatable reads. I’ve known about all of those above problems, but there’s one problem that I’ve never heard of until recently: NOLOCK can block other queries from running....(more)

Automatically Reseeding Identity Values on SQL Server - You have tables that have a lot of data inserted into them and deleted that use identity values and run out integers to use. Tracy Boggiano explains how to automatically reseed them, in cases where it's safe to do so....(more)

Why is a value in DATETIME2 8 bytes, but in BINARY it is 9 bytes? - The reason that a DATETIME2 (or TIME) data type is one byte longer when converted to a binary value is because the precision is encoded directly into the value. This is to ensure no information is lost when converting between data formats....(more)

New Features in SSMS 17.3 - Wayne Sheffiled plays with two significant new features of SSMS: Import Flat File Wizard and XEvent Profiler....(more)

What's the difference between RANK, DENSE_RANK, and ROW_NUMBER? - In short, they are only different when there are ties...Douglas Kline demonstrates....(more)

SQL Server Security

Audit SQL Server Jobs - Thomas LaRock explains why SQL Server Audit is one of those features that doesn’t get enough love and attention....(more)

Legacy apps that don’t believe in schemas - What if you have a legacy app that doesn’t schema-prefix its database objects, but you want it to work with a specific assigned schema? Daniel Hutmacher shows a quick and easy solution, with a catch....(more)

Security news and thoughts

Disqus Demonstrates How to Do Breach Disclosure Right - We all jumped on "the Equifax dumpster fire bandwagon" recently and pointed to all the things that went fundamentally wrong with their disclosure process. But it's equally important that we acknowledge exemplary handling of data breaches when they occur because that's behaviour that should be encouraged....(more)

Changes in Password Best Practices - NIST recently published its four-volume SP800-63b Digital Identity Guidelines. Among other things, it makes three important suggestions when it comes to passwords....(more)

PowerShell

Update Variables - In this article I demonstrate how to update a collection of variables in PowerShell. It's a bit more complex than you may think, but not hard at all....(more)

Forgettable improvements to PowerShell over the ages - I've been using PowerShell 5.0 and 5.1 for a long time now and sometimes realise I'm still thinking of the 2.0 and 3.0 era. I wanted to revisit some of the more forgettable improvements....(more)

PowerPivot/PowerQuery/PowerBI

Power BI Cleanup Tool; Time Saving with Power BI Helper - Power BI files can easily get big. You can have 50 tables in a Power BI model, and 25 reports. When Power BI file gets in that size, maintenance is always an issue....(more)

Offline model editor experience for Power BI - Marco Russo describes how to use Tabular Editor, a free and open source tool based on the official TOM library, with a Power BI model....(more)

Power BI Custom Visuals Class (HTML Viewer) - How to use the HTML Viewer to display the results of HTML code within your Power BI reports....(more)

Conditional Formatting in Power BI - Conditional formatting is available in both Table and Matrix visuals in Power BI with multiple formatting types and accessible from both the fields and format views. ...(more)

Performance Tuning SQL Server

How to Log Wait Stats to Table with sp_BlitzFirst - When you’re analyzing SQL Server performance, one of the best places to start is wait stats. What’s your SQL Server waiting on?...(more)

How to Choose a SQL Server Health Check - The question isn’t whether to do one but, “How?” In this article, I’ll discuss the pros and cons of the following options: Roll your own, Download free scripts or Pay someone....(more)

Relativity SQL Server – What I Wish You Know - Mike Walsh's "top 5" list of things he wishes accidental DBAs knew about SQL Server....(more)

SQLskills SQL101: Should you kill that long-running transaction? - Don’t always knee-jerk and decide to cancel a problematic, long-running query without thinking about what the effect of that will be....(more)

XE Profiler – the new feature available in SSMS v17.3 - On SSMS's new QuickSessionStandard and QuickSessionTSQL templates for profiling with Extended Events....(more)

Microsoft News : General Interest

Windows Phone is now officially dead: A sad tale of what might have been - Microsoft's Joe Belfiore tweeted confirmation of something that has been suspected for many months: Microsoft is no longer developing new features or new hardware for Windows Mobile. ...(more)

Microsoft News : Patches, Bugs

Microsoft Windows 10, Server 2016 patching error borks users' systems - Patches that Microsoft published for Windows 10 and Windows Server 2016 have resulted in problems for some business users. Here are some ways administrators can fix things....(more)

Data Science

Understanding ANOVA - Analysis of variance, is a term given to a set of statistical models that are used to analyze differences among groups and if the differences are statistically significant to arrive at any conclusion. ...(more)

R's remarkable growth - Python has been getting some attention recently for its impressive growth in usage. Since both R and Python are used for data science, I sometimes get asked if R is falling by the wayside, or if R developers should switch course and learn Python. My answer to both questions is no....(more)

Conferences and Events

Discover database DevOps and more at PASS Summit - PASS Summit lands in Seattle from October 31 to November 3 this year, bringing with it the latest thinking and developments in the SQL Server world. This blog post goes into the details of three database DevOps sessions that Redgate will be presenting during the conference....(more)

Career Growth

Why do managers go bad? - Many people don’t work in an environment that encourages intrinsic motivation. Even those who work in places that promote these ideals often find that over time, things change for the worse. Why does this happen?...(more)

Setting the Summit Pre-Con Attendance Record - Brent Ozar on the work that went into getting 360 registrations (a record) for his PASS Summit pre-con....(more)

Big Data

Unstructured Cosmos - Derik Hammer explores Cosmos DB as a store for unstructured internet of things data in the form of vending machine supply data....(more)

The BigData Legacy - BigData is leaving its legacy in the ability to accept data even if it doesn’t exactly fit the structure you have. I know plenty of systems that will break if the data arriving is in the wrong structure, which makes change and adaptability hard to achieve. A BigData solution can help mitigate that risk....(more)

Big data, IoT, Business Intelligence and small data (T-SQL Tuesday #95) - A company should consider if they get “bang for the buck” from their standard BI toolset, before investing heavily in big data projects....(more)

Backup and Recovery

SQL Server 2017 Encrypted Backups And Compression - What effect does encryption have on backup compression? ...(more)

Azure SQL Database

Secure your on-premises network outbound connection to Azure SQL Database by locking down target IP addresses - Most people familiar with Azure SQL DB (aka SQL Database) are aware of the firewall setting requirements of SQL DB, which are very important to lock down connections to SQL DB on Azure. ...(more)

SQL Vulnerability Assessment - Thomas LaRock on the new wSQL Vulnerability Assessment (VA) tool for Azure SQL Database....(more)

Azure SQL Data Warehouse and Data Lake

A Look at ADLS Performance – Throughput and Scalability - This article describes the performance characteristics of Azure Data Lake Store (ADLS) relative to Azure disk storage when used with Cloudera Distribution for Hadoop (CDH)....(more)

Running U-SQL on a Schedule with Azure Data Factory to Populate Azure Data Lake - Seen steps to created a scheduled job for standardizing JSON input files using USQL....(more)

Analysis Services / BI on the MS Stack

Creating A Partitioned Table In SSAS Tabular 2017 And SSDT Using M Functions - The latest release of SSDT has proper support for shared expressions, and Chris Webb shows a simple example of how to use it to create a partitioned table using M functions....(more)

AI/Machine Learning/Cognitive Services

Hunting for the True Location, with Machine Learning - Gail Shaw uses Machine Learning to try to predict the location of her company's end of year party....(more)

Administration of SQL Server

Easy way to create policies using SSMS - Dennes Torres describes an interesting feature of SSMS that makes it easy to create policies to check our server's compliance....(more)

The Death of SQL Server Service Packs - SQL Server Service Packs are going away, starting with SQL Server 2017. I talk about why I think this is a good thing, and discuss Cumulative Updates, Service Packs, and the process of updating SQL Server....(more)

Toolbox - Fix Your FILEGROWTH - A fillegrowth reset script which generates the ALTER DATABASE statements to set the FILEGROWTH increment based on the individual file's current size....(more)