The Complete Weekly Roundup of SQL Server News

Hand-picked content to sharpen your professional edge

I've been quite enjoying the task of writing about SQL Code Smells, recently, because of the increasing interest in the topic. My contribution to this over the past few years has been less like creative writing, and more like a continuing task of curating and promoting the ideas of other people. I've been maintaining a live list of SQL Code Smells on Github, which is then published on Simple-Talk and as a pdf. It started out in 2014 as a small booklet and has gradually increased in size, over the years.

Last year, SQL Prompt quietly introduced some code analysis capabilities. As you type in code, it underlines any code it considers 'suspect'. It introduced the idea of SQL Code analysis to a new audience of developers, who were surprisingly receptive to the idea that some of their code could be improved. Even I was amused to find how much of my own code was riddled with smells!

Redgate asked me to help to document the code smells that SQL Prompt detected and relate them to what was already in the SQL Code Smells booklet. I've also written several longer, explanatory articles on some of the common code smells. The reason I enjoy writing these longer articles is that there are constant surprises. Some things one is warned about turn out to have been long-dead implementation issues, whereas others that seemed marginal turn out to have the potential for dire consequences. Many coding patterns that have been promoted as best practices are highly debatable. I'm constantly finding out new things.

There are several times in the database cycle where it is good to have feedback about code quality. It is often useful to have it as we develop code, though it is occasionally distracting if the code you're writing is just temporary stuff, a one-off hack, or for testing. It is certainly a good idea to know about the issues within code that is part of a build, such as use of deprecated syntax, or SELECT *, or NOLOCK hints, because it is code that is checked in, and part of the trunk. The command-line version of SQL Codeguard will do this for you very simply.

Static code analysis has its limits, of course. You can extend what can be detected by analysing the live database, via the metadata. However, this still doesn't yet detect some of the worst crimes, which have little to do with syntax, but are errors of style or algorithm. It is difficult to identify these 'greater' code smells, mechanically. How do you detect the crime of using polymorphic association, or 'God Objects'? Unless we can find ways of detecting these greater heresies, we may be in danger of distracting developers away from the real problems, and towards the less significant details.

I like to think that simple code smells, such as excessive table width, often serve to indicate the possibility of more complex smells such as 'Use of God Objects' and other normalisation errors, although this isn't always true. Is there more we can achieve with code analysis?

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.

Webinars

How DevOps can help you stay ahead of the competition - Do you want to be the best at sprinting, or overcoming hurdles, or running further than the rest? The key is to find an approach that brings the elements together: removing silos between teams, adopting integrated technologies, automating processes that cause barriers and bottlenecks. ...(more)

Virtualization and Containers

The most useful Docker commands for DBA - A short guide to the basic docker commands most useful to a DBA....(more)

Vendors/3rd Party Products

SQL Code Analysis from a PowerShell Deployment Script - Database code analysis becomes more important as the team doing the database development gets bigger and more diverse in skills. Phil Factor discusses SQL Code Analysis from a PowerShell Deployment Script....(more)

T-SQL

Does It Matter Which Field Goes First in an Index? - Yes, it matters a lot, and in order to pick the right field order, you need to understand the selectivity of the data, the selectivity of your queries, and whether you’re doing equality searches or inequality searches, and what happens to the data after you retrieve it – like how it needs to be joined or ordered....(more)

Using UNPIVOT to Traverse a Configuration Table’s Rows and Columns - In principle, UNPIVOT is just syntax sugar for a bunch of UNION ALL subqueries, but it's very useful when you need to read spreadsheet-style data as though it were nicely normalized....(more)

Filtering Tables, Procedures, And Other Objects In SSMS - It you ever have to work in a database that has hundreds or thousands of database objects, then the little-known filtering capabilities of SSMS are very handy....(more)

SQL Transactions - Covering all the basic transaction modes, and including the rather knottier topic of nested transactions....(more)

sp_execute_external_script and Permissions - How to allow a non-admin database user to execute sp_execute_external_script, to work with SQL Server Machine Learning Services. The two steps are quite straightforward once you know what they are....(more)

AVG() in TSQL: Watch Your Data Types - Kendra Little reflects on just how tricksy and non-average that AVG() can be....(more)

T-SQL Window Functions and Performance - T-SQL window functions, introduced in 2005 with enhancements in 2012, are great additions to the T-SQL language. In this article, Kathi Kellenberger explains what you need to know to get good performance when using these functions....(more)

SQL Server Security

Use SSMS with a Different Windows Account – Back to Basics - Often, you'll need to work in SSMS as a different Windows User, to prove an account is working and has the appropriate level of access, or to connect to a Domain SQL Server from a computer in a different domain (or not on the domain)....(more)

Be our guest, be our guest, put our database to the test - Disabling the guest database principal in the user databases is recommended, though not in system databases. guest is only if you don’t have another user in the database. As soon as you have another user you no longer have access to any permissions associated with guest....(more)

Update your production servers and stop making excuses about it - When we ignore updates, we are ignoring preventable catastrophic problems; we are ignoring fixes to security bugs, performance bugs, and data corruption bugs. Each one of these things could give you a really bad day. In two out of three cases it might even be a career-limiting move....(more)

Principles of Data Protection - Protecting data in SQL Server is not as simple as setting a few properties. While there are great security features in SQL Server, such as Transparent Data Encryption, production data may end up in places throughout the organization. In this article, Brian Kelley talks about the best ways to secure data using the concept of least privilege....(more)

Replication

Optimizing Replication Agent profile parameters for better performance - The MSSQL Tiger Team performed a series of tests to measure the performance of log reader and distribution agents, while changing some of the parameters for these agents. This blog summarizes the outcomes and conclusions from this testing....(more)

PowerShell

Keeping your important files safe and secure - We all have important files of some sort or another but do we all look after them correctly? Do you do anything to ensure the data is unchanged from the time it is written to the storage to the time that you choose to access it? Let's see how we can use a simple PowerShell cmdlet called Get-FileHash to help us here....(more)

Simplifying snapshots - Database snapshots are very useful when performing upgrades and testing work. And now, a new set of dbatool snapshot commands makes them way easier to work with....(more)

PowerPivot/PowerQuery/PowerBI

Week to Date Calculation for Power BI with DAX - There are a number of pre defined DAX time intelligence calculations that help you to get analytics over time, such as year to date, same period last year, and etc. However, there is no calculation for Week to Date built-in....(more)

Number of Days between 2 Transactions Using DAX - Matt Allington sets out to data-mine his credit card data using Power BI, to find out how often he gets his hair cut....(more)

Performance Tuning SQL Server

Harvesting SQL Server Trace Flag 8666 optimizer stats detail from Query Plan XML - What about analyzing query plans over some period of time to see which stats are actually used in those plans? Then auto-stats which aren't used in that set of plans could be dropped....(more)

[Video] SQL Query Optimization: Why Is It So Hard to Get Right? - Dr David DeWitt describes the basic mechanisms used by modern query optimizers including plan enumeration, the use of histograms to estimate selective factors, and plan costing. He then describes a new approach to query optimization that he believes will revolutionize the optimization of queries in the cloud....(more)

What is the FCB_REPLICA_SYNC spinlock? - Paul Randal explains: in a nutshell, this spinlock is used to synchronize access to the list of pages that are present in a database snapshot....(more)

DevOps and Continuous Delivery (CI/CD)

SQL Server, Docker and Jenkins - Chris Adkin demonstrates why spinning up SQL Server inside a container as a deployment target for a continuous integration pipeline, is one of the best ways to leverage SQL Server and Docker....(more)

Database Design, Theory and Development

Understanding Relations Part 1: Tables? So What? - Most practitioners think that relational databases consist of tables, but do not ask themselves why and how is that significant for database practice. ...(more)

Data Visualisation

The Financial Times and BBC use R for publication graphics - While graphics guru Edward Tufte recently claimed that "R coders and users just can't do words on graphics and typography" and need additonal tools to make graphics that aren't "clunky", data journalists at major publications beg to differ. The BBC has been creating graphics "purely in R" for some time....(more)

Data Privacy and GDPR

Traditional database security doesn’t protect data - Alex Yates proposes that the features we think of as traditional database security are not sufficient to protect your data....(more)

Data Mining/Data Analysis

The Data Analysis Maturity Model – Level Three: Distributed, consistent reporting systems - Buck Woody continues our journey towards reliable analysis, built on trustworthy data....(more)

Data Access / ORMs

How not to structure your database-backed web applications: a study of performance bugs in the wild - A study of real-world applications, and a distillation of common performance anti-patterns, in particular problems people get into when using ORMs to handle persistence concerns in their web applications....(more)

Computing in the Cloud (Azure, Google , AWS)

Azure and Windows PowerShell: Getting Information - In the second part of his series, Nicolas Prigent describes in detail how to automate the login process using PowerShell and Azure CLI. Nicolas also explains how to retrieve information about your Azure subscription....(more)

Columnstore Indexes

Can Rowstore Compression Beat Columnstore Compression? - Columnstore has quite a few different tricks for compressing data. This blog post explores if it’s possible for a rowstore table to beat columnstore compression, even in the best case scenario for the columnstore table (no delta stores and rowgroups of the maximum size)....(more)

Columnstore Indexes – part 124 (“Estimate Columnstore Compression”) - The stored procedure sp_estimate_data_compression_savings will estimate how much storage we can save by enabling or moving to a more effective data compression method, but it doesn't support ColumnStore indexes. Until it does, Niko Neugebauer has you covered....(more)

Azure SQL Managed Instance

Point-in-time restore of a database on Azure SQL Managed Instance using AzureRm.Sql PowerShell library - Azure SQL Database Managed Instance is PaaS version of SQL Server hosted in Azure cloud. Managed Instance enables you to create a database as a copy of the existing database at some point in time using PowerShell. In this post you will see sample script that performs point in time restore of database....(more)

Quick-start script: Create Azure SQL Managed Instance using PowerShell - A simple PowerShell script that you can use to quickly create a new Managed Instance....(more)

Quick-start script: Setup Azure network environment for Azure SQL Managed Instance - Azure SQL Database Managed Instance is a fully managed SQL Server Database Engine hosted in Azure cloud and placed in your Azure network. In this post will be explained how to create environment where Managed Instances can be placed using the sample PowerShell script....(more)

Azure SQL Data Warehouse and Data Lake

Easier Azure Data Lake Store management: alerts for folders and files. - Setting up alerts for your Azure Data Lake Store to monitor when files or directories are created, accessed, modified, or deleted will help manage your account and data efficiently....(more)

Tips for Backup Azure Data Lake Store with Azure Data Factory - Since Microsoft hasn’t published a new version of ADLS with a clone feature we had to find a way to backup all the data stored in our data lake....(more)

Azure data Bricks – Part2 - The basics of how to upload data to Azure data Lake Store, get it into Azure Data Bricks, clean it using Scala and then start to do visualization and predictive analysis with R....(more)

Azure Analysis Services

How and when to scale up or out using Azure Analysis Services - You'll want to scale up when the processing engine is taking too long to process the data to build your models. You’ll want to scale out if you’re having problems with responsiveness with reporting because the reporting requirements are saturating what you currently have available. ...(more)

Administration of SQL Server

Bulk Changing SQL Server Agent Job Owners with dbatools and TSQL - Now we all know that having SQL Server Agent jobs owned by ‘Real’ users isn’t a good idea. Stuart Moore explains two ways you can find all the jobs owned by the user "OldDeveloper" and moving them to the "JobAccount" user. ...(more)

Installing SQL Server 2016 on Windows Server 2012 R2 (the KB2919355 issue) - While performing an in-place SQL Server upgrade for a customer, I came across the KB2919355 issue. This article will explain why this can be misleading, and show you how to resolve this problem....(more)