The Complete Weekly Roundup of SQL Server News

Hand-picked content to sharpen your professional edge

Although many successful applications have been developed at home, it isn't easy to work remotely from home for any length of time. It is a skill that takes time to acquire, not only for the people working remotely but particularly from the team-members left in the office.

The biggest hurdle is often that the expectations of other people are all wrong. Still "working from home" are you? I'm sorry to hear that: I hope that you find a proper job soon. Note the inverted commas around the "working from home". It has become a euphemism, like "gardening leave" or "wanting to spend more time with your family". I have known a couple of best-selling authors who have been considered shirkers by their neighbors for not working in an office. It just doesn't seem like real work to the wider society. Some well-meaning neighbors even give them menial jobs just to get them out and about.

Anyone who has been used to office life, with the companionship, the water-cooler, the canteen and so on, can find it hard to adapt to home-working. The silence, and the sudden end to banter with your colleagues, takes its toll. You suddenly find that it's past midday and you've failed to get dressed properly, that you're bingeing on day-time television. You catch yourself staring blankly out of the window, or trying to explain things to the cat. The stack of tasks start to build up, and in the worst cases, the body rebels at the stress and tedium and you get sick.

Of course, any drop off in your productivity will be no surprise to those in the office. They're expecting it, because you are, after all, "working from home". It's ironic really, because in many ways office life provides a far less stressful refuge for the idle. If you have a large screen, preferably several of them, no manager suspects that you are spending your day arranging the evening's social life and looking at pictures of kittens. I once supervised a team that had installed a 'boss-button' key on their keyboards that, with one keypress, hid all the social media applications behind very convincing spreadsheets. Wise to their trick, I took great amusement in clicking the button as I walked past, to reveal their cat pictures and embarrassing messages to their social groups.

In truth, if you have a properly equipped home office and a good VPN link, there is little you can't do when working remotely. If you've acquired the self-discipline to work the right hours, and use all the workgroup coordination applications such as Trello, Slack and Skype to keep in constant communication with your office team, then you can avoid many of the above pitfalls.

The serious problems often come when office-based team members start to believe that the remote workers have somehow ceased to exist, however much they are contributing. We've devised great applications for remote and home working, but we've yet to adapt team processes to successfully accommodate them.

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

Automatically keeping ‘hotfix’ and production database schemas in sync - Feodor Georgiev demonstrates how to automatically compare the schema of two versions of the same database, and then subsequently deploy to the target database any differences detected in the source database....(more)

Webinar: How to get started with DevOps - More and more teams are turning to DevOps as way to improve the efficiency and quality of software delivery but it’s not always clear what's the best way to get started. Redgate have invited Steve Thair, CTO of the DevOpsGuys, to share some practical tips on how you can start implementing DevOps in your organization. Register for the free webinar....(more)

T-SQL

What kind of JOIN syntax is this? - Vladimir Oselsky stumbles across some very puzzling join syntax and tries to figure out its intent....(more)

Variable scoping in TSQL isn't a thing - It seems that unlike every other language I've worked with, in T-SQL all variables are scoped to the same local scope regardless of where in the script they are defined....(more)

New Freebie Course: SSMS Shortcuts & Secrets - Kendra Little offers a new online reference that shows you her favorite secret tricks and goodies in SSMS! ...(more)

Parsing Denormalized Strings with the STRING_SPLIT Function in SQL Server 2016 - STRING_SPLIT() is a table-valued function which allows you to parse a delimited string passed in as a parameter (along with a separation character as a second parameter) returning the output column [value] as a table valued result set that presents as a row for each delimited value between each separator character....(more)

Tech News : General Interest

Hackers Threaten to Erase Apple Customer Data - This is a weird story, and I'm skeptical of some of the details. Presumably Apple has decided that it's smarter to spend the money on secure backups and other security measures than to pay the ransom. But we'll see how this unfolds....(more)

SQL Server Security and Auditing

Upgrading to SQL Server 2016: New Data Privacy and Security Features - Chances are, it is less expensive to upgrade to SQL Server 2016 than to suffer a data breach....(more)

The Mount Rushmore of Lazy SQL Security - Dave Mason reveals the four recurring security blunders that drive him nuts. The blundees are seemingly oblivious. They react as if everything is fine and anyone that suggests otherwise is unreasonable....(more)

SQL Server on Linux

SQL Server on Linux: Running jobs with SQL Server Agent - SQL Server Team has announced that in CTP 1.4 you can schedule tasks using SQL Server Agent, when SQL Server is running on Linux....(more)

SQL Server Internals

Context in perspective 5: Living next door TLS - A reminder of how much one can do with thread-local storage, which forms the third and often forgotten member of the trio of places to put state....(more)

Security news and thoughts

Data breach disclosure 101: How to succeed after you've failed - Organisations rarely plan for how they should handle data breaches and when an incident does happen (and that seems to be a near certainty these days), they're left unprepared; they're in unfamiliar territory, there's enormous stress and pressures on them and frankly, they usually react pretty badly....(more)

Reporting Services

Drill from a mobile report to a paginated report in SSRS (SSAS) - What if the data in the parameter of the paginated report was populated from a SQL Server Analysis Services (SSAS) model? Does the drill URL change? It absolutely does. Let me show you how....(more)

Replication

SQL Server Replication enhancement in SQL Server 2016 - Replication is a widely-adopted feature in SQL Server to copy and distribute data and database objects from one database to another and then synchronizing between databases to maintain consistency. SQL Server 2016 brings a few enhancements that should make it easier to use, and work better with other SQL Server features....(more)

R Language

Is it possible to use RevoScaleR package in Power BI? - The idea of having a scalable environment and the parallel computational package with all the predictive analytical functions in Power BI is great. But is it even possible? Tomaz Kastrun investigates....(more)

PowerPivot/PowerQuery/PowerBI

The Latest and Best Way to Catch/Capture/Inspect Slicer Selections - Rob Collie checks out the new Concatenex function....(more)

Power BI Custom Visuals (Module 43 – Gantt) - How to use the Gantt Power BI Custom Visual to visualize project timelines and deliverable completion....(more)

Performance Tuning SQL Server

Easy way to get statistics histogram programmatically - Demonstrating use of the new Dynamic Management Function (DMF) sys.dm_db_stats_histogram, in the latest release of SQL Server 2016 SP1 CU2....(more)

Is a Cost Threshold For Parallelism of 5 right…for me? - Tim Peters' Cost Threshold For Parallelism is set to 5. he hopes never to have to defend his settings in a dark alley with Erik Darling....(more)

Plan Caching - Klaus Aschenbrenner investigates Plan Caching and its side-effects in SQL Server. ...(more)

SQLskills SQL101: Indexes on Foreign Keys - If I have a table that has 6 foreign key columns/references should I create one index with all 6 foreign key columns in it, or should I create 6 individual indexes – one for each foreign key reference? Kimberly Tripp provides the answer....(more)

Not the typical story about key lookups - Most of the time you'll suffer a performance degradation from bookmark (or Key) lookups, but SQLDoubleG shows you the 0,000001% case that makes SQL Server such an amazing system....(more)

Hardware Testing

Selecting and Configuring Hardware for SQL Server 2016 Standard Edition - Glenn Berry describes some common issues and pitfalls that you may run into when you install and use SQL Server 2016 Standard Edition on a new server with modern hardware....(more)

DevOps and Continuous Delivery (CI/CD)

Universal Architecture - Andrea Angella considers the Universal Architecture idea in light of the question "How can we prevent code becoming an intricate mess over time?"...(more)

Building SQL Server Projects with GitLab Runners - This post describes the set-up required for GitLab runners to automatically build SQL Server projects after each commit. ...(more)

Data Mining/Data Analysis

Basics of Probability - A refresher on some of the basic principles of probability that one needs while dealing with more advanced programming in R and data analysis....(more)

Hadoop at Strata: Not Exactly ‘Failure,’ But It Is Complicated - Has Hadoop failed to deliver the goods? That was a question on the minds of Strata + Hadoop World attendees last week, with opinions expressed both pro and con. Regardless of your stance on that question, it’s clear that the center of gravity has moved beyond the yellow elephant....(more)

Data Access / ORMs

Comparing performance of data access libraries using StackExchange/Dapper benchmark - What data access library should you use to access your data in SQL Server database? Jovan Popovic shows how to use the StackExchange Dapper benchmark to compare the performance of Dapper ORM and Entity Framework at returning a single row from a database....(more)

Conferences and Events

Registration is Open for GroupBy.org’s April Free Conference - GroupBy.org is BrentOzar.com's community training initiative where your votes pick the session lineup....(more)

Columnstore Indexes

Columnstore Index Returns Zero Rows… Which is One Row - I’ve never claimed to be great at math, but until recently I thought I knew how to count to one. Zero… one. That’s what we learned in kindergarten. Apparently SQL Server didn’t go to kindergarten, because it can’t even count to one in some execution plans....(more)

Columnstore Index – How to Estimate Compression Savings - The sp_estimate_data_compression_savings stored procedure to estimate the storage savings for ROW and PAGE compression has not beene xtended to estimate storage savings from columnstore index. Sunil Agarwal suggests a workaround....(more)

Career Growth

At New Clients, What’s the Worst That Could Happen? - Could you make a mistake that brings the system down? What’s the worst that could happen if this system goes down? Once it starts going down, can you stop it? Given all the risk, is the reward worth it?...(more)

Bugs/Patches for SQL Server

Performance and Stability Related Fixes in Post-SQL Server 2014 SP2 Builds - If you are running on the SQL Server 2014 SP2 branch, you really should be running the latest SQL Server 2014 SP2 Cumulative Update....(more)

SQL Server 2016 Service Pack 1 CU2 Released - Microsoft released SQL Server 2016 Service Pack 1 CU2, which is Build 13.0.4422.0. This CU has 101 fixes in the public fix list, by my count. This is a pretty large CU, and if you look at the fix list in more detail, many of them are for pretty significant issues with AGs, columnstore indexes, and general performance....(more)

Backup and Recovery

Backing up SQL Server on Linux using Ola Hallengrens Maintenance Solution - Rob Sewell on how to back up SQL Server databases on Linux using Ola Hallengren’s maintenance solution and the SQL Agent....(more)

When SQL Server Does NOT Use Write Ahead Logging - Kendra Little describes when SQL Server turns things upside down and doesn’t use write ahead logging: and what it has to do for recovery in these special cases....(more)

Analysis Services / BI on the MS Stack

SSAS 2016 Locking Improvements - People have long been aware of the server-wide lock taken out by SSAS when processing finishes – and the issues that this can cause. Fortunately, commit operations have been optimized considerably for tabular models in SQL Server 2016, meaning noticeable improvements in locking and blocking....(more)

Administration of SQL Server

Schedulers To Rule Them All - One of our production servers has 4×8 (4 sockets, 8 core) processors with hyper threading enabled. This results in 32 physical cores or 64 logical cores. Therefore, this particular server should have 4 NUMA nodes available. And yet, I was seeing 3 buffers aligned with 3 NUMA nodes. Where was the fourth buffer? Did it just not show up for work?...(more)

Prediction: SQL server DBA role - Long gone are the days where a DBA stays busy backing up databases and creating indexes. Today DBAs need to think about Automation, Powershell, Cloud technologies, SQL Server on Linux....(more)

Hidden Gem TRY_CONVERT to troubleshoot data type conversion - Many of us have experienced a dreaded data type conversion error when doing tasks like importing files, converting to new database schemas, etc. Which record caused the problem? I have used a few tricks in the past for this, but TRY_CONVERT makes it easy. ...(more)

DBCC CHECKDB And Read Only Databases - Do you need to run DBCC CHECKDB even on a read only database? YES! Here’s why: many forms of corruption come from storage....(more)

Why Developers Should Consider Microsoft SQL Server - Brent Ozar give the good, bad and ugly of choosing SQL Server....(more)

SQLskills SQL101: Updating SQL Server Statistics Part I – Automatic Updates - Erin Stellato explains how SQL Server statistics are updated, either by SQL Server or by you....(more)