The Complete Weekly Roundup of SQL Server News

Hand-picked content to sharpen your professional edge

It always surprises me when the old topic of SQL coding standards gets such a good response. I've gone on about it on several occasions, usually after coming across some particularly egregious examples of bad work. However, the enthusiastic reaction to Robert Sheldon's recent 'The Basics of Good T-SQL Coding Style' shows that a good message bears repeating, especially when written so meticulously. By 'Coding Style' we're not talking just about code formatting and documenting, because even if you get that right, you can still fail if the algorithm you use is wrong, or if you fail to consider the database design. Actually, there are plenty of ways of goofing up with SQL. In my SQL Code Smells booklet I list over a hundred of them. The great SQL Blitz is growing into an excellent way of seeing the extent of the technical debt in a SQL Server database. Nasty database surprises can thereby be turned into the preferable, but still horrible, database revelations.

Although there is a lot you can do with SQL Server Policy-based management, you can extend your reach with static code analysis before a build, and there are a whole host of tests you can devise at build-time, to spot such danger-signals as table smells.

I admit that I've spent quite a while pestering the good people in Redgate's SQL Prompt team about SQL Code Formatting. I'm keen on formatting: in the days before Microsoft released their .NET SQL parser, I even wrote a rudimentary SQL-based SQL Code prettifier and formatter. I've always loved automatic code formatters even in the dark days when I programmed in C for a living. The code formatter in SQL Prompt was always useful but a pernickety coder such as I always had to tidy up the result afterwards. After an enormous amount of work, the formatting feature in Prompt has been re-written to bend to nearly all the bizarre and wildly-varying SQL Coding preferences that have been suggested by the existing community of users. It copes with practically all my preferences and I can even indicate sections of code that it should leave out.

Like many programmers, I get bored easily. We're not all naturally 'detail' people. If a job can be automated, then it should be, I reckon. Checking for coding style and reviewing code is so much easier when using techniques and tools to help, as long as the tools are your slave, and not your master. Policies can destroy creative thought if applied too mechanically. In a complex system, such as a database, best-practices so often blur into 'it depends'.

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.

Virtualization and Containers

How to attach a SQL Server database in a Linux Docker container - Use the docker CP command to transfer the MDF file between your local filesystem and the container, then attach it....(more)

Copying files from/to a container - Last week I was having an issue with a SQL install within a container and to fix I needed to copy the setup log files out of the container onto the host so that I could review. But how do you copy files out of a container?...(more)

Working with Windows Containers and Docker - Part 3 - So far, in this series, Nicolas has shown how to get simple container instances up and running with just some basic background information. Now we need to understand the differences between Linux containers, Windows Server containers and Hyper-V containers. We can then define, create and run multi-container Docker applications, and port existing Windows Container VMs to Docker....(more)

Vendors/3rd Party Products

A day in the life of a Redgate Product Support Engineer - When someone buys software from Redgate, it’s often not the end of the story. It can be the beginning of a journey because support is frequently part of the package. But what do support actually do, and what does a day in the life of a Product Support Engineer at Redgate involve?...(more)

Why Redgate are removing a feature from SQL Backup Pro - Everyone needs to back up their SQL Server databases, and SQL Backup Pro lets people do it across different and multiple versions of SQL Server, and compress, verify and encrypt backups at the same time. All from a centralized GUI, and with the option to automate backups, backup verification, restores and log shipping too. Version 9 has just been released and they've done something unusual, they've added one major feature… and removed another....(more)

Extending DevOps practices to SQL Server databases - In this free demo webinar, Steve Jones and Arneh Eskandari will show how Redgate’s database DevOps solution works to improve your database development and deployment processes. With a focus on using Team Foundation Server (TFS) work items, the webinar will demonstrate how Redgate tools plug into TFS, TFS Build, and Microsoft Release Management. Register now....(more)

T-SQL

Creating FOREIGN KEY constraints as enabled, trusted, non-trusted, and/or disabled - Louis Davidson is building a utility to drop the foreign key constraints on a table, before truncating it, but then replace them without having to save off the script manually....(more)

Searching SQL Code via sys.sql_modules - A quick tip on searching stores procedures via T-SQL....(more)

Memory optimized table variable and cardinality estimate - Jack Li attempts to clarify cardinality estimation for a memory optimized table variable....(more)

UNION ALL Optimization - Given that the SQL Server optimizer can often reorder things like joins and aggregates to improve performance, it is quite reasonable to expect that SQL Server would also consider reordering concatenation inputs. In fact, the SQL Server optimizer does not do this. More precisely, there was some limited support for concatenation input reordering in SQL Server releases up to 2008 R2, but this was removed in SQL Server 2012, and has not resurfaced since....(more)

Will SQL Server Always Do Index Seeks on Numbers? - There’s two parts to this sargability thing. First, can SQL Server take your search arguments and turn them into an index seek rather than a scan? Second, can SQL Server use your search arguments combined with statistics to make a good estimate on how many rows will come back?...(more)

What You Need to Know about Adaptive Joins over Rowstore - Itzik Ben-Gan demonstrates an example for the classic plan reuse shortcoming with parameter-sensitive queries, then how adaptive joins can alleviate the problem. ...(more)

SQL Server 2017: Adaptive Join Internals - SQL Server 2017 brings a new query processing methods that are designed to mitigate cardinality estimation errors in query plans and adapt plan execution based on the execution results. This innovation is called Adaptive Query Processing....(more)

Reporting Services

Installing Multiple Instances of SSRS - Multiple SSRS instances, one database engine instance. It's really not that hard when you're not burdened with false assumptions, as Dave Mason explains. ...(more)

PowerShell

Why PowerShell? - Get-Process | Sort-Object -Descending CPU | Select-Object -First 10 ...the PowerShell reads like a sentence. Get-Process, Sort-Object, Select-Object. No surprises. the commands do exactly what they say. This means, I can put stuff like this in a script, and other people can read it without 20 years of experience. ...(more)

PowerPivot/PowerQuery/PowerBI

DAX Functions, DirectQuery And Unrestricted Measures - If you use Power BI to connect to a data source such as SQL Server in DirectQuery mode, you’ll find that you cannot use the complete range of DAX functions inside your calculations...unless you enable the “Allow unrestricted measures in DirectQuery mode” option....(more)

Power BI Custom Data Connectors - Chris Webb explains why custom data connectors are one of the biggest things that’s happened to Power BI in a long time. Microsoft’s most successful products are always the ones that are platforms for its partners to build their own solutions on top of, and Power BI is no exception. ...(more)

Power BI Free is the Main Problem Here - Matt Allington explains why he's disappointed by, firstly, a lack of a suitable Power BI pricing model for small to medium sized enterprises (SMEs), and secondly the decision to prevent free and premium users from using “Export to Excel”....(more)

Performance Tuning SQL Server

One SQL Cheat Code For Amazingly Fast JSON Queries - How non-persisted computed column indexes make your JSON queries high performance...(more)

SQL Server 2017: How to Get a Parallel Plan - In SQL Server 2017 there is one more way to force parallel plan, that involves using the undocumented hint ENABLE_PARALLEL_PLAN_PREFERENCE....(more)

SQL Server 2017: Statistics to Compile a Query Plan - How to determine what statistics are used by the optimizer during a plan compilation in SQL Server 2017....(more)

SQL Server 2017: Sort, Spill, Memory and Adaptive Memory Grant Feedback - Dmitry Pilugin investigates some Batch Mode Sort peculiarities....(more)

SQLServer:Databases(_Total)\Transactions/sec vs SQL Statistics\Batch Requests/sec (video) - A quick 5 minute demo showing that the SQL Statistics\Batch Requests/sec counter sees more of your workload than the Databases(_Total)\Transactions/sec counter does....(more)

The Difficulty of Tuning Queries Over a Database Link - Tuning queries over database links is hard. Much much harder than tuning “ordinary” queries. Ideally, you’ll simply avoid database links and run all queries on a single instance, but sometimes that’s not possible. This is an Oracle article, but the lessons are broadly applicable....(more)

NoSQL

Azure Cosmos DB - "Azure Cosmos DB contains a write optimized, resource governed, schema-agnostic database engine that natively supports multiple data models: key-value, documents, graphs, and columnar." Wow. I think....(more)

Open source SQL database CockroachDB hits 1.0 - CockroachDB, an open source, fault-tolerant SQL database with horizontal scaling and strong consistency across nodes—and a name few people will likely forget—is now officially available....(more)

Microsoft News

Microsoft to add support for SUSE, Fedora Bash shells to Windows 10 - Microsoft is continuing to bring more Linux shell options to Windows 10, Windows Server and its Azure Portal....(more)

Serving AI with data: A summary of Build 2017 data innovations - Joseph Sirosh summarizes Microsoft data innovations announced at Build 2017, including Azure Cosmos DB, as well as support for MySQL and PostgreSQL asPlatform as a Service (PaaS) offerings through Azure....(more)

LINQ/Entity Framework

A Visual Lexicon of LINQ - LINQ is best learned from examples, but few LINQ resources supply, along with the code, pictures that illustrate what each associated LINQ operator in the code is doing. This article is a visual index of all LINQ operators, that explain clearly with code and illustrations what even the most arcane LINQ operators actually do. To increase your enlightenment, it is accompanied with a reference chart to provide even more detail. Michael Sorens is, with these two articles, determined to persuade you of the power of LINQ....(more)

Hardware Testing

How to calculate RAID 5 Parity Information - The first time that you look at a RAID 5, you think that some magic is involved, because based on the parity information you can reconstruct 2 different pieces of information. But when you look at the details at the low level you can see that there is no magic – it’s a simple XOR logic operation that makes everything possible....(more)

DevOps and Continuous Delivery (CI/CD)

T-SQL Tuesday #90 – You are doing “Continuous Integration” wrong! - Do you Commit less frequently than “several times a day”, or work on feature branches that are not merged back to master “several times a day”? If so, you are not doing CI....(more)

The Need for Database DevTest - Phil Factor on why you must design any application, from the ground-up, for ease of testing and diagnosis, and why this is an especially urgent requirement, in a dynamic multiuser system such as a database....(more)

Building a Faux PaaS, Part 1: The SQL Server DevOps Scene in 2017 - What would SQL Server need to go up against the Chaos Monkey? What if, at the push of a button, we could deploy a VM with the right Windows config, set up clustering, get SQL Server installed correctly, restore the right databases, and join an Availability Group?...(more)

T-SQL Tuesday #90 – The Elephant’s Name is Database - In my experience, the database is the final thing to be thought of when it comes to continuous deployment. Nobody wants to tackle it and it can be hard to get right. It is the elephant in the room....(more)

Deploying Often is a Very Good Idea - Dan McKinley explains why you should deploy small diffs as often as possible....(more)

T-SQL Tuesday #90 – Shipping Database Changes - A T-SQL Tuesday about your thoughts or experiences with database deployments....(more)

47 Things You Incorrectly Assume About Shipping Database Changes #tsql2sday - Brent has been a developer who had to deploy changes, a DBA who had to deploy other peoples’ changes, an open source maintainer, and even been a software vendor who had to hand other people scripts to deploy in their own environment. Hoo, boy, he has some scars to talk about....(more)

Data Privacy

Personal Data, Privacy, and the GDPR - Now that there have been well-publicised examples of the awful consequences of data breaches and data misuse, there is increasing public pressure for legislation on privacy and personal data that has enough clout to prosecute serious offenders. In the vanguard has been the EU data protection regulation, soon to be succeeded by the GDPR. It defines IT practices for data that are likely to extend worldwide. William Brewer gives a rundown of what he sees as the implication for IT practice....(more)

Data Access / ORMs

The Case of Entity Framework Core’s Odd SQL - Richie Rump's jaw hits the floor on seeing an example of the SQL that Entity Framework Core (EF Core) was generating to accomplish an insert....(more)

Computing in the Cloud (Azure, Google , AWS)

Azure Database Migration Service now available for preview - Using this new database migration service simplifies the migration of existing on-premises SQL Server, Oracle, and MySQL databases to Azure, whether your target database is Azure SQL Database, Azure SQL Database Managed Instance or Microsoft SQL Server in an Azure virtual machine....(more)

How to Secure Your Azure Storage Infrastructure - Azure storage is an essential foundation for the more sophisticated services that Microsoft Azure provides. It is therefore important to understand how to make access to your data in Azure storage secure, to control access appropriately, to log activity and to get metrics on usage. Security in Azure can be easily managed and controlled via policies. There are a variety of ways to achieve the types of control over access that your applications need, as Christos Matskas explains....(more)

Columnstore Indexes

Batch Mode Hacks for Rowstore Queries in SQL Server - Kendra Little explains a few ways to trick SQL Server into using batch mode without really using columnstore....(more)

Career Growth

Asking Questions - "Sometimes we just need to ask questions out loud in order to help ourselves think". A nice comment from Tom LaRock on a good post from Andrew Pruski....(more)

10 Tips on How to be a Great Programmer - Producing simplicity in a library is much easier than doing that in business logic. Can we achieve it? Perhaps. By practicing. By refactoring. But like great software, simplicity is not built in a day....(more)

Thoughts on public speaking / presenting / teaching - The greats weren’t great because at birth they could paint. The greats were great cause they paint a lot. Likewise with public speaking; it takes practice to get good, or even comfortable, with it....(more)

Azure SQL Data Warehouse and Data Lake

Security in Azure SQL Data Warehouse - Grant Fritchey discusses firewall security, login security and auditing....(more)

Azure Analysis Services

Choosing tier in Azure Analysis Services - This article describes the differences in the levels available in Azure Analysis Services (Azure AS), comparing them with the features in SQL Server Analysis Services (SSAS) on-premises....(more)

Administration of SQL Server

SQL Profiler will never die - SQL Profiler has been deprecated, which means that it is scheduled to be removed in a future version of SQL Server. However, at Microsoft Amp last month Scott Guthrie announced that SQL Profiler would be available for Azure SQL Database. So, I wouldn’t expect Profiler to go away anytime soon....(more)

If I Took Another DBA Job, My First Question Would Be… - Brent says: “How many people are in the on-call rotation with me, and can I give them each a technical test?...(more)

.NET Related Articles

10 Unsung Visual Studio Time-Savers - We all have our favourite third-party extensions to Visual Studio, and although we all like Resharper, there are many others that could well make your development work easier. To celebrate the fact that Visual Studio Enterprise now includes ReadyRoll Core, SQL Prompt Core, and SQL Search as part of the Data storage and processing workload, Michael Sorens describes nine of his current favourite extensions, and wonders if he's missing anything in his list....(more)

Improvements to Model Binding in ASP.NET Core - Model Binding has been a popular feature of ASP.NET MVC, but has, before now, had some minor restrictions. ASP.NET Core now has enhanced model binding: If a value passed via routes, posted data, or query strings couldn't be bound appropriately, it forced a global exception. Now, there are a number of ways of providing a more graceful reaction to incorrect data. You can now force the binding to a given source or create new sources for binding to. Dino Esposito explains....(more)