The Complete Weekly Roundup of SQL Server News

Hand-picked content to sharpen your professional edge

One of the mysteries of IT is that you can call yourself an IT professional without any qualifications whatsoever. If you are clever with the use of words, you can do it without experience as well. I must admit that I'm fairly relaxed about this because some of my best development teams have benefitted greatly from people with raw talent but little in the way of formal qualifications or experience.

However, for anyone used to a better-regulated profession such as Medicine, Architecture or the Law, it comes as a shock. I can see why too. As the surgeon sharpens up his knives and starts eyeing you up, it is comforting to think they are properly trained and qualified. Why are we so relaxed about this in IT?

One answer is that from top to bottom of the IT industry, you will find 'New Men'; people who have reinvented themselves and even lied about their experience and qualifications. There was a time when your experience and qualifications, as stated on your CV, were alone enough to get you a job. Now, of course, when we require expertise, we've had to adopt the defensive technique of requiring 'technical interviews' or exams to prospective job candidates.

One argument I hear that just won't wash states that the industry completely changes every three years or so, requiring us to completely re-learn our tech skills, and therefore rendering experience and qualification less relevant, because the knowledge gained quickly becomes archaic. This is a ridiculous and pernicious idea. The core insights of computing were all in place by the mid-nineteen-eighties. In fact, there is very little that I learned in the nineteen seventies that I don't still use. Experience gives you a neat and adaptable mental database in which it is easy to stuff new knowledge and expertise.

Another skill that comes from years of reading articles, and sitting in presentations, by the New Men of IT is to completely switch off if your internal Bullshit-o-meter goes 'ding'. You sit there with an intelligent and alert look on your face, nodding occasionally, whilst the mind races around working out how to fix all those computing problems that are squirrelled away in your memory.

You can never completely avoid the meaningless word-salad of the 'New Men' of IT, but nature kicks in with excellent avoidance mechanisms. It is in gifts like this that experience in IT has its power.

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 : General Interest

For Microsoft, Aggressive Is The New Normal - In the past week, we have seen Microsoft directly go after Oracle, offer up a tool to make it easier to ditch Evernote and pit employees against admins when it comes to Windows 10. ...(more)

Stop Taking Your Database Administrators for Granted - We need alignment with our C-level leaders. We need to collaborate with our lines of business. And we certainly need to get in front of the big trends reshaping the IT landscape. But we never get around to talking about the DBA. And that’s a problem, because none of those other good things will happen until we truly empower our DBAs....(more)

Tech News : Security

How your data is collected and commoditised via “free” online services - Your data is a commodity and as such, others will attempt to extract it from you and sell it. Remember this the next time a site like the ones described in this article request it from you; how are they justifying their online presence? Are you the product? Probably....(more)

SQL Server News

Feedback on SQL Server Documentation - If you care about documentation please provide feedback. The SQL Server writers are working to improve the documentation on MSDN/BOL, and need your help....(more)

Microsoft News

Why Microsoft is porting SQL Server to Linux - The real reason for Microsoft's move to Linux has now become apparent. It is trying to target Oracle customers and woo them over....(more)

Blogs : Administration

Why most of you should leave Auto-Update Statistics on - Erik Darling proves using Extended Events that updating statistics, even on some pretty good sized tables, doesn’t really take that long....(more)

Index Cannot Be Reorganized… - For months, maybe even years, things are running smoothly. Never a failure. Never an error. Just routine log review day after day. Then one day it happens – there is an error. The index maintenance script failed one night....(more)

Database Scoped Configuration in SQL Server 2016 - SQL Server 2016 provides us with a lot of existing configuration options that we can configure at the database level, rather than just instance-level, without using any specific Trace Flags....(more)

Blogs : Analysis Services / BI

Nested Display Folders in SSAS - Recently it was announced that Power BI would now support the SSAS display folders in “exploration mode”. Exploration mode is a fancy term for a live connection to an SSAS cube or model. Anyway, great news, as it drastically improved usability of Power BI Desktop when browsing cubes....(more)

Creating a Date Dimension in a Tabular Model - As well as its multidimensional model, SQL Server Analysis Services (SSAS) now has a tabular model of database that either runs in-memory or in DirectQuery mode. The in-memory analytics engine allows the users of Excel or Power View to base reports on tabular model objects. Having shown how to handle date-based information using the Multi-dimensional model, Dennes now turns his attention on the in-memory tabular model....(more)

Auto install of PowerBI - Simon Sabin uses a few bits of PowerShell to automate installing PowerBI...(more)

Enable business insights for everyone with SQL Server 2016: Part 2 - SQL Server 2016 offers a number of significant enhancements including an improved Tabular model, improvements to the DirectQuery storage mode and more....(more)

Filter Arguments in CALCULATE - A filter argument in CALCULATE is always an iterator. Finding the right granularity for it is important to control the result and the performance. This article describes the options available to create complex filters in DAX....(more)

Blogs : Backup and Recovery

Restoreability and SSMS - Tibor Karaszi describes a couple of cases where SSMS will generate restore commands that will fail, because the restore sequence is based on backups that are impossible to restore from....(more)

Blogs : Big Data

The Future of Data and Databases - According to Tom Smith, it's consolidation around big data with rationalization down to 10 core technologies....(more)

Blogs : Computing in the Cloud

Unable to connect to SQL Server on Azure Virtual Machine–VNET Problem - We have seen repeated mistakes by SQL Server users where they took all the effort creating SQL Server VMs and client VMs (like IIS) but they ended up not able to talk to each other. They created the VMs in different VNETs....(more)

Blogs : DMO/SMO/Powershell

Introduction to PowerShell Variables - Jeff Hicks covers the concept of variables in PowerShell from a beginner's perspective....(more)

More on SQLPS and SQL Server on Linux - Chrissy LeMaire on the current state of SQL Server on Linux, and SQLPS....(more)

Blogs : Excel

Generating Fixed-Width Text Files In Excel With Power Query - While it’s fairly common to need to load fixed-width files using Power Query or Power Query, occasionally you might want to use Power Query and Excel to create a fixed-width output for another system, or maybe to create some test data. ...(more)

Blogs : Integration Services/ETL

Working with ragged right formatted files in SSIS - Unfortunately, as ETL developers, we often have to extract data from an imperfect world which has flat files formatted in all sorts of ways....(more)

Blogs : NOSQL

Going Social with DocumentDB - Windows Development MVP Matias Quarantaas describes a perfectly normalized and pretty data structure… that fails for the requirements of data storage for social networks. Enter DocumentDB....(more)

Blogs : Performance and Tuning

The SQL Server 2016 Query Store: Forcing Execution Plans using the Query Store - The SQL Server 2016 Query Store can give you valuable performance insights by providing several new ways of troubleshooting queries, studying their plans, exploring their context settings, and checking their performance metrics. However, it can also directly affect the performance of queries by forcing Execution Plans for specific queries....(more)

Live Query Statistics Don’t Replace Actual Execution Plans - I like SQL Server’s new Live Query Statistics feature a lot for testing and tuning large queries. One of my first questions was whether this could replace using actual execution plans, or if it’s useful to use both during testing. Spoiler: Both are useful. And both can impact query performance....(more)

How to identify the source of page splits in a database - Page splits have the ability to slow down our queries and increase the amount of IOs needed to retrieve our data. We need to monitor and identify the source of page splits to avoid them in our databases....(more)

Blocking operators and actual row counts - Query plans can sometimes be hard to read, and other times can be downright mystifying. Gail Shaw investigates a sort operator, a normal sort, that is reducing the row count....(more)

Added per-operator level performance stats for Query Processing - An upcoming SQL Server 2014 Service Pack will add runtime information to Showplan, and expose a new Extended Event in SQL Server 2016 RC0....(more)

Blogs : R Language

Scoring R Models with Excel - Enhancements to the AzureML R package make it easy to publish R functions that consume data frames as Azure Web Services. A very nice consequence is that it is now feasible to develop predictive models in R and enable the Excel powered business analysts in your organization to use your model to generate predictions with new data. ...(more)

Blogs : Reporting Services

SQL Server 2016 RC1: What’s new in Reporting Services - SQL Server 2016 RC1 contains a bunch of enhancements to Reporting Services, culminating in the first relatively “feature-complete” preview release of SQL Server 2016 Reporting Services....(more)

Blogs : Software Development

Reference data as code - Vladimir Khorikov describes a powerful technique that can potentially save you a lot of work and make your code much more concise: representing reference data as code....(more)

Blogs : SQL

Analyzing Your Email with SQL - how much time do you spend managing your email inboxes? Find out with SQL....(more)

Blogs : SQL Server 2014

Getting a functional Books Online - The local BOL for SQL Server 2014 has been broken for a long time. Tibor Karaszi shows how to fix it....(more)

Blogs : SQL Server 2016

SQL 2016 Technical Deep Dive Video – Summary of all Videos - Simon Sabin provides a handy categorization of the new SQL Server 2016 Technical Deep Dive videos....(more)

SQL Server 2016: Changes in default behavior for autogrow and allocations for tempdb and user databases - In SQL Server 2016, the functionality provided by TF 1117 or 1118 will be automatically enabled for tempdb, meaning better out-of-the-box scalability and throughput for SQL Server applications that use tempdb heavily....(more)

New DMF for retrieving input buffer in SQL Server - A new DMF for retrieving the input buffer for a session/request (sys.dm_exec_input_buffer) is now available in SQL Server 2016 RC0. This is functionally equivalent to DBCC INPUTBUFFER but has a few advantages....(more)

Blogs : T-SQL

Performance Surprises and Assumptions : STRING_SPLIT - For many years, Aaron Bertrand have advised to STOP DOING THIS IN T-SQL. And then SQL Server 2016 came along…...(more)

Query Active Directory directly from SQL Server - Have you ever had the need to query Active Directory right out of SQL Server using native TSQL? There are 3 ways to accomplish this....(more)

Find the Partitioning Key on an Existing Table with Partition_Ordinal - This seems easy, right? Nope, it’s not. If you just right click the table and say “Script to new window”, it does NOT script the partitioning scheme and column you chose as the partitioning key. ...(more)

The Joy of Joining on NULLs - Some of the worst performing queries use ISNULL either in the JOIN or WHERE clause. Still. To this day. In the age of the hoverboard, people are still putting functions where they don’t belong. ...(more)

Splitting Strings: SQL Server 2016 To The Rescue! - Erik Darling swoons over the new Built-in Table-Valued Function STRING_SPLIT, in SQL Server 2016....(more)

SQL Server 2016: What time is it? - SQL 2016 introduces a function to help alleviate the issues we have when it comes to finding the current time. No, it’s not spacetime (I wish), nor is it a stardate (how cool would that be?), it is the ability to transform a given time into your local time by using the AT TIME ZONE function....(more)

Blogs : Virtualization

The Experience of Switching from VMware to Hyper-V - Brent Ozar is tempted by the Vaio Canvas Z, a powerful Windows 10 tablet, but is underwhelmed with the Hyper-V virtualization capabilities....(more)