The Complete Weekly Roundup of SQL Server News

Hand-picked content to sharpen your professional edge

I'm always amazed to see the zeal with which some database programmers format their SQL code. SQL, of course, sees no meaning or significance in whitespace so, unlike languages like Python, it allows you the choice and freedom to be as absurd as you wish in the way you layout your code. I've yet to find two SQL programmers who are prepared to agree on how SQL code should be formatted.

I'm one of the grey-muzzled SQL Coders who prefer just the occasional discreet couple of spaces to indicate subordinate clauses in an expression, but there are some programmers who insist on spraying their SQL all over the page. It becomes less like text and more like a table.

SQL was intended to be a declarative 4GL language, where queries described what was wanted in the result rather how to get the results procedurally. In a sense, it was close to a natural language. Written language, as text, isn't formatted in columns preceded with commas, for example. Sure, we have bullet points in moderation, but in general we write in such a way that indentation and formatting has little semantic significance.

With SQL code, by contrast, we might start by deciding that we want all T-SQL reserved words to be in uppercase, and do our user-defined object names such as tables and columns in camel case, with the first letter capitalized. It isn't strictly necessary unless you choose to make your database case-sensitive for some reason. By putting the T-SQL reserved words in upper case, it helps when reading code quickly, because it is, otherwise, easy to miss out the start of subordinate clauses starting with keywords such as FROM, ON, GROUP BY, INNER JOIN and so on. We may choose to put these subordinate clauses on a new line, though this can look a bit daft in a very simple SELECT clause. Having done this, it is easy to fall prey to the urge to revert to the old days of procedural languages and indent lines accordingly.

From then onwards, there is a temptation to do all sorts of formatting to make things line up and structured. How do you handle code blocks? How should subqueries, or expressions be formatted? It can all get surprisingly complicated.

After a certain point, the time it takes to do all this formatting slows productivity significantly. The task is an obvious candidate for automation, and there are plenty of SQL code-formatters around. These formatters need to accommodate a wide variety of preferences in laying out SQL. They also have to be able to format code to a variety of corporate or organizational standards.

I like molding SQL layout to my own preferences but should we have enforced 'standard' layouts to writing SQL, or do we allow written SQL to reflect the person who originally wrote the code?

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.

Tech News : Webcasts

Free Redgate DLM Demo Webinar - In this webinar on Tuesday March 29th, Steve Jones will show you how to use Redgate DLM with TFS, TFS Build & MS Release Management. Learn how to version control your databases, include them in your CI process and set up automated deployments. ...(more)

Product Reviews

Book Review: Next Generation Databases: NoSQL, NewSQL, and Big Data - This book aims to help you choose the correct database technology, in the era of Big Data, NoSQL, and NewSQL, how does it fare? ...(more)

Microsoft News : General Interest

Microsoft provides a few more details on its SQL on Linux plans - While it fleshes out its SQL Server on Linux strategy, Microsoft is moving ahead with SQL Server 2016 for Windows, which is now at the Release Candidate 1 stage....(more)

Hardware News

Intel’s ‘Tick-Tock’ Seemingly Dead, Becomes ‘Process-Architecture-Optimization’ - Intel’s Tick-Tock strategy has been the bedrock of their microprocessor dominance of the last decade. ...(more)

Blogs : Administration

Reorganize Columnstore Indexes - Ascript to help figure out when to reorganize columnstore indexes in SQL Server 2016....(more)

Clustered ColumnStore Indexes – Space Savings - Klaus Aschenbrenner on the space savings that you can make by using a Clustered ColumnStore Index....(more)

Changes to a Writable Partition May Fail Unexpectedly - If you use table partitioning with one or more partitions stored on a read-only filegroup, SQL update and delete statements may fail with an error. Of course, this is the expected behaviour if any of the modifications would require writing to a read-only filegroup; however it is also possible to encounter this error condition where the changes are restricted to filegroups marked as read-write....(more)

Minimal Logging when you can’t change the code - Minimal logging is super cool. If you follow all the rules, there’s a pretty good chance it will work! Unfortunately, it’s not always up to you. All sorts of ISVs put out all sorts of bad ideas in form of code....(more)

Blogs : Analysis Services / BI

Monitoring SSAS Multidimensional Non Empty Filtering Using Profiler, Part 1 - Chris Webb explores the different types of non empty filtering that can occur in an MDX query, how they can be monitored using Profiler and what you can do to improve their performance....(more)

Overview of SSAS Tabular in DirectQuery Mode for SQL Server 2016 - Reviewing the basics of DirectQuery mode in SQL Server Analysis Services (SSAS)....(more)

Power BI Filled Map; the Good, the Bad, and the Ugly - Tips on using filled map in your Power BI solution, existing issues in the current version of filled map and things that you need to be aware of when you work with this visualization element....(more)

Blogs : Azure SQL Database

Cross Database Query in Azure SQL Database - You can’t query across databases in Azure SQL Database… or can you?...(more)

Azure SQL Databases with Powershell: Components - Mike Fal explains how to create Azure SQL Databases with PowerShell....(more)

Azure SQL Database learns and adapts with your application - According to the Verizon 2015 Data Breach Investigations Report, in 60% of cases, attackers are able to compromise an organization in minutes, yet only less than a quarter of these cases are detected within days or less. What if your database could learn the patterns of your workload and alert you of a potential breach before it’s too late?...(more)

Blogs : Backup and Recovery

The ‘In Recovery’ Mystery - James Anderson on the mystery of the database that keeps slipping in and out of recovery mode....(more)

Blogs : Big Data

A journey to a movie recommender in U-SQL - Dave Ballantyne dips his toes into the Azure Data Lake with the “Hello World” of a Big Data system, the Movie Recommender....(more)

Blogs : Computing in the Cloud

What is Azure Active Directory? - You’ve probably heard of Azure Active Directory (AAD) even if you don’t know how it differs from Active Directory in Windows Server. Azure AD is a multi-tenant cloud-based directory and identity management service that offers a subset of the services of Windows Server AD but in the cloud....(more)

A sample application for pulling data from SQL into an Azure Event Hub - Occasionally, we need to pull data from a SQL Azure table and push it into an Azure Event Hub. For example, I have a number of cases where I need to push data from my original streaming source into SQL Azure so I can run some complex joins and queries then push the updated records to an Azure Event Hub. ...(more)

Blogs : Events

SQL Bits 15 Agenda published - The agenda for the biggest SQL Server conference in Europe, May 4-7, 2016....(more)

Blogs : Hardware

How many NUMA nodes should I have if I have lots of RAM and just a few cores? - Some systems out there which have a lot of RAM, but only a few processors and these machines may need a non-standard NUMA configuration in order to be properly setup. ...(more)

Blogs : High Availability/Disaster Recovery

Calculating Some Max Mirror Stats - Sheldon Hull wanted to calculate how many mirroring databases I could run on a non-Enterprise Edition server....(more)

Blogs : Integration Services/ETL

ETL Auditing - It happens far too often: Once an ETL process has been tested and executes successfully, there are no further checks to ensure that the operation actually did what it was supposed to do. ...(more)

Blogs : Performance and Tuning

3 Tricks with STATISTICS IO and STATISTICS TIME in SQL Server - When you need to measure how long a query takes and how many resources it uses, STATISTICS TIME and STATISTICS IO are great tools for interactive testing in SQL Server. I use these settings constantly when tuning indexes and query....(more)

Make Extended Events Great… Finally - SQL Server 2016 adds a ton of super cool information in execution plans, and an Extended Events session to expose the same information for all queries....(more)

SQL Server Maintenance: The Overlooked Index - Quite a lot has been written about SQL Server index maintenance. One area that doesn't get much attention is full text index maintenance. Full text indexes can suffer from fragmentation, which can lead to degraded performance. Let's look at one way to address this....(more)

Different query plans for “OR” type queries - The SQL Server query optimizer can find interesting ways to tackle seemingly simple operations that can be hard to optimize....(more)

Blogs : R Language

R Markdown Custom Formats - R Markdown supports all the usual formats, such as HTML, PDF and MS Word, but is also fully extensible and as a result there are several R packages that provide additional formats. ...(more)

Blogs : Service Broker / SOA

SQL Server Service Broker – Introduction - Colleen Morrow starts a series of blog posts that will walk through the Service Broker architecture, how Service Broker works, how to troubleshoot when things go wrong....(more)

Blogs : Software Development

Trying to impress people with your code - It is also common for us, humans, to be proud of our craft and even brag about it from time to time. Programmers are no exception....(more)

Blogs : Spatial Data

SQL Server CPU utilization in Graphical form - Benjamin Nevarez described in his blog on how to "Get CPU Utilization Data from SQL Server" and he inspired me to draw a diagram of the CPU utilization....(more)

Blogs : SQL Server 2012

SQL Server 2012 Service Pack 3 CU2 Available - On March 21, 2016, Microsoft released SQL Server 2012 Service Pack 3 CU2, which is Build 11.0.6523.0. This Cumulative Update has 20 hotfixes in the Public fix list, which is a relatively low number for a SQL Server 2012 Cumulative Update....(more)

Blogs : SQL Server 2016

Truncate Table with Partitions Fails if You Have Non-Aligned Indexes - SQL Server 2016 brought in a cool new little feature for table partitioning: you can now truncate individual partitions. There’s one little gotcha, though: you can only do this if all the indexes on the tables are “aligned”....(more)

SQL 2016 – It Just Runs Faster: LDF Stamped - Creation or expansion of the LDF file involves writing the entire series of 0x00’s to the new bytes for the log. SQL Server 16 changes the stamp to 0xC0’s instead of 0x00s. This cal lead to performance gains because many of the new hardware implementations detect patterns of 0x00’s. The space is acquired and zero’s written to stable media, then a background, hardware based garbage collector reclaims the blocks....(more)

Blogs : T-SQL

Moving data from relational to JSON columns and vice versa - Unlike other pure relational or pure NoSQL databases, Sql Server do not forces you to store data in relational or JSON format. You can choose any format you want, put some data in scalar columns and other in JSON columns. ...(more)

Microsoft Fixed My Biggest SQL Server Pet Peeve (Two Years Ago) - The best way to avoid tempdb GAM and PFS contention caused by table-valued parameters (TVPs) is to use Memory-Optimized Table Variables....(more)

T-SQL Window Function Speed Phreakery: The FIFO Stock Inventory Problem - Sometimes, in the quest for raw SQL performance, you are forced to sacrifice legibility and maintainability of your code, unless you then document your code lavishly. Phil Factor's SQL Speed Phreak challenge produced some memorable code, but can SQL features introduced since then help to produce code that performs as well and is also easy to understand? Aunty Kathi investigates....(more)

Improving temp table and table variable performance using memory optimization - In-Memory OLTP can provide big performance improvements for transactional workload. However, adopting this technology in an existing app comes at a cost, and you need to have enough available memory for the core transactional tables....(more)

Blogs : Virtualization

SQL Server in Windows Containers - A step by step guide of setting up SQL Server Express 2014 in a Windows Container by using a community-contributed Dockerfile....(more)