The Complete Weekly Roundup of SQL Server News

Hand-picked content to sharpen your professional edge

As I write this I’m sitting on a ship out on the ocean. I’m currently taking part in the latest Tech Outbound event, a cruise at sea that is both a learning classroom and an deep networking event. I bring this up because while sitting in one of the classes, I learned something that explains so much about a lot of things that I just have to share it.

Kevin Kline, currently of SentryOne and one of the founders of PASS, told us a story about the founding of the PASS organization that I had never heard before. When they first started organizing PASS, 20 years ago, they had almost no funding. They wanted to put on an event, the first PASS Summit, but they had no idea how they were going to compete with much better funded events. Kevin then said that, while they can’t necessarily put on a better event, they can be better people. They would create a culture based on the concept of being aggressively friendly. It worked. To this day, what we now call the SQL Community has taken Kevin’s approach of being aggressively friendly to heart. You can see the aggressively friendly approach at work at PASS Summit, SQLSaturday events all over the world and all the local groups spawned from these. You’ll recognize the aggressively friendly approach in TechOutbound where they’ve also added inclusiveness to the culture. The establishment of culture survives and grows from the founders of the culture.

The question then becomes, what kind of culture are you building around yourself. Whether we’re talking about your corporate culture or just your choices of approach to other people, are you aggressively friendly, or just aggressive. Because the culture you build will be visible to others. Worse, or better depending on your point of view, that culture will be emulated. It’s your choice. You can concentrate on being aggressively friendly and inclusive like PASS and TechOutbound, or you can make other choices, positive or negative, about how you build and maintain the culture around you. Personally, I love the new phrase I learned this week, aggressively friendly, and I intend to attempt to emulate that better while being as inclusive as possible.

Grant Fritchey 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

Misuse of the scalar user-defined function as a constant - The incorrect use of a scalar UDF as a global database constant is a major performance problem and must be winkled out of any production code. SQL Prompt implements static code analysis rule, PE017, designed precisely to help you detect and rectify this problem - here's how it works....(more)

Customizing the SQL Prompt built-in snippets: a better ‘ata’ snippet - Snippets are a great feature of SQL Prompt. They save coding time, and introduce standards and consistency to the way you build code modules. ...(more)

T-SQL

SCHEMABINDING and Why It Can be Useful? - In SQL Server, when we use the “WITH SCHEMABINDING” clause in the definition of an object (view or function), we bind the object to the schema of all the underlying tables and views. This means that the underlying tables and views cannot be modified ......(more)

Synonyms, how and why. - Did you know SQL Server has a thing called a synonym? It’s not something you see used very often even though it’s been around for >10 years (SQL 2005). In fact, I’d be willing to bet that a good chunk of the people reading this have never (or just recently) ......(more)

Why nested views are bad - Nested views are bad. Let’s get that out of the way. What is a nested view anyway? Imagine that you have a SELECT statement you tend to use all over the place (a very common practice when checking user permissions). There are five base tables in the ......(more)

T-SQL Tuesday #99 – Great Debates : Unicode - In this post I want to share my opinion about using UNICODE data types by default for strings stored in SQL Server This post is part of the T-SQL Tuesday, which is a monthly blog party on the second Tuesday of each month. Everyone is welcome and have ......(more)

The Annals of Hilariously Bad Code, Part 2 - You’re Crazy In Part 1, I showed you code that I think has some anti-patterns in it. In case you didn’t recognize it, it’s actually code that Microsoft wrote. It’s from sp_delete_backuphistory, and it is plum awful. I have a lot of personal dislike for ......(more)

4 Rules of Right When Running a Query - Last month in January, I participated in the Idera #sqlchat. This is a monthly chat meeting that is organized by Idera Software and hosted by a SQL Server community member. This one was hosted by Monica Rathbun (B|T) and her topic for the month was ......(more)

The Lighter Side

How UPS delivers faster using $8 headphones and code that decides when dirty trucks get cleaned - Inside EDGE: the shipping giant’s ambitious, tech-driven bid to keep Amazon and others at bay. ...(more)

A solar panel on every roof in the US? Here are the numbers - Enlarge (credit: Jon Callas) When you’re scoping out possible futures, it’s useful to ask a lot of “what if?” questions. For example, what if we could install solar panels on every suitable roof in the United States? How much electricity would they ......(more)

The Falcon Heavy is an absurdly low-cost heavy lift rocket - Twenty-seven engines on the Falcon Heavy rocket, all burning their happy little flames. (credit: SpaceX) One may criticize the Falcon Heavy rocket for having a short launch manifest, as it has only two confirmed flights in the next year or so. There ......(more)

Hobbies, stress relief, and meeting management: T-SQL Tuesday #99 - It’s T-SQL Tuesday again! And in fact it’s the 99th one! Given that T-SQL Tuesday runs once a month that means that Adam Machanic’s (b/t) brainchild has been running for over 8 years! This month is hosted by none other than Aaron Bertrand (b/t). He’s ......(more)

T-SQL Tuesday #99 : Singing and Music - This month’s T-SQL Tuesday is hosted by Aaron Bertrand (b/t), and he offers as a topic what he calls Dealer’s Choice. We can either share something about which we’re passionate outside of the world of SQL Server or we can discuss a T-SQL bad habit. I’m ......(more)

How I Accidentally Found a Passion for Plants (#TSQL2sDay 99) - For TSQL Tuesday #99, @AaronBertrand gave us an invitation to write about something we’re passionate about outside of SQL Server. As a community we spend a lot of time digging into nerdy topics, which is fun, but I think it’s interesting to find out ......(more)

T-SQL Tuesday #99 : Getting Decked Out in Rubber and Lycra, My Non-techy Passion #TSQL2sday - So this month’s T-SQL Tuesday invitation comes from Aaron Bertrand. It’s a non-technical post this month as he asks us to write about a non-SQL or tech related passion. For me, my passion is Triathlon. I first got hooked on the sport back in around ......(more)

T-SQL Tuesday #99: Home Brewing - It is T-SQL Tuesday time for February 2018, which is being hosted by Aaron Bertrand. One of the choices that Aaron offered was to write about something you are passionate about, outside of the SQL Server or tech community. T-SQL Tuesday Making Beer ......(more)

#TSQL2sday: What I’m Passionate About – Sas Christian’s Art - For this month’s T-SQLTuesday, Aaron Bertrand asked what we’re passionate about – but outside of the tech community, and asked for pictures. Seems like a great way to get to know about us. From my personal blog and my Instagram feed, you probably already ......(more)

T-SQL Tuesday #99 – Door #1 - This months T-SQL Tuesday is hosted by Aaron Bertrand and the topic is Dealer’s Choice. Aaron gives us the choice to blog about a topic of our choice, either about a passion outside of the SQL Server community, or about a T-SQL bad habit. In this blog ......(more)

T-SQL Tuesday #99 : Dealer’s Choice - Welcome to my contribution to the 99th installment of T-SQL Tuesday, where Aaron Bertrand (b|t) gives us a choice to spread our wings and talk about our personal passions OR…play it safe and talk about our favorite T-SQL bad habit. This whole T-SQL ......(more)

TSQL Tuesday #99: SQL Tourism - It’s T-SQL Tuesday, the blog party that SQL Server expert Adam Machanic (blog|twitter) started. This month’s episode is hosted by Aaron Bertrand (blog | twitter). The topic: Dealer’s Choice (Door Number 1: #sqlibrium) When I was a kid, Choose Your ......(more)

SpaceX's Falcon Heavy Rocket Ushers in New Era in Space Operations - DAILY VIDEO: A Tesla Roadster flying to Mars is only the beginning; ex-Intel executives launch a new Arm server chip company; Rapid7 previews InsightPhish for phishing email security; and there's more. ...(more)

The car of the future is taking shape—and it will know how we feel about it - Few people want to go to Las Vegas immediately after the New Year. Never a fan of the place at the best of times, I dutifully boarded the plane anyway. Like it or not, if one wants to see everyone's ideas for the car of the near future, ......(more)

SQL Server Security

Enabling Guest in a Database–#SQLNewBlogger - Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers. The guest account exists in all your databases. This is installed by default, and guest is used to map a login that doesn’t otherwise ......(more)

Safely and Easily Use High-Level Permissions Without Granting Them to Anyone: Server-level - Part of having good security is giving users the fewest / least permissions possible in order to execute the code. However, we always seem to find ourselves needing to allow someone to execute something that requires one or two higher-level permissions. ...(more)

A Guide for Decrypting SQL Server Database Objects - Overview The SQL Server 2005 and SQL Server 2008 provide a new feature for encrypting data to protect it from unauthorized access or hacker’s attacks. To make the data accessible, it needs to be decrypted with the use of key or password. It is necessary ......(more)

SQL Server News

Sync SQL data in large scale using Azure SQL Data Sync - This post is authored by Xiaochen Wu, Program Manager, SQL Server. Azure SQL Data Sync allows users to synchronize data between Azure SQL Databases and SQL Server databases in one-direction or bi-direction. This feature was first introduced in 2012. ...(more)

The February release of SQL Operations Studio is now available - This post is authored by Alan Yu, Program Manager, SQL Server. We are excited to announce the February release of SQL Operations Studio is now available. Download SQL Operations Studio and review the Release Notes to get started. SQL Operations Studio ......(more)

Security news and thoughts

Microsoft’s compiler-level Spectre fix shows how hard this problem will be to solve - Enlarge (credit: Aurich Lawson / Getty Images) The Meltdown and Spectre attacks that use processor speculative execution to leak sensitive information have resulted in a wide range of software changes to try to limit the scope for harm. Many of these ......(more)

Mountain of sensitive FedEx customer data exposed, possibly for years - Enlarge / A redacted copy of data FedEx employees left on a publicly accessible Amazon bucket. (credit: Kromtech Security Center) Passports, driver licenses, and other sensitive documentation for thousands of FedEx customers were left online, possibly ......(more)

Microsoft: The Future of Digital Identity Protection Lies with Blockchain - Identity fraud is a growing problem in the digital era, but so is the lack of any legally verifiable identity at all. Microsoft believes a solution to these and other identity management issues can be found in blockchain and other distributed ledger ......(more)

Questions About Data Breaches that You Were Too Shy to Ask - News reports about data breaches are common occurrences. Even the companies that exist to keep our data safe are not immune. William Brewer answers the question about data breaches that you may be too shy to ask....(more)

R Language

What does Microsoft do with R? - I was genuinely chuffed to get a shout-out in the most recent episode of Not So Standard Deviations, the awesome statistics-and-R themed podcast hosted by Hilary Parker and Roger Peng. In that episode, Roger recounts his recent discovery of the Microsoft ......(more)

Product Upgrades and Releases

The SQLUndercover Inspector is now available! - The SQLUndercover Inspector is now available on GitHub, a quick run down of what it does can be found here: Introducing: The SQLUndercover Inspector, a quick video introduction can be found here and upcoming video tutorials will be released on Undercover ......(more)

SQL# Version 4.1 is released!! - Version 4.1 of SQL# has just been released! New functionality available only in Full version Sys_LockResource Returns the name of the specified Lock Resource given the Type, Subtype, DatabaseID, Description, and AssociatedEntityID. Works similarly ......(more)

PowerShell

Using PowerShell to navigate SQL Server as a drive - I might be a SQL Server pro, but I’m still a PowerShell novice. I learn best by doing, so I keep pushing myself to do more with PowerShell so that I can learn more. If you don’t already know this about the SqlServer PowerSehll Module, you can access ......(more)

PowerPivot/PowerQuery/PowerBI

Power BI Custom Visuals Class (Module 90 – Brick Chart MAQ Software) - In this module you will learn how to use the Brick Chart by MAQ Software. The Brick Chart provides a simple method for highlighting your best and worst performers in a 100 square colored chart. Module 90 – Brick Chart (MAQ Software) Downloads Power ......(more)

Power BI date table: create using DAX - In this video, Adam looks at how to create a Power BI date table using DAX. The end result uses a combination of the GENERATE(), ROW() and CALENDARAUTO() DAX functions to create a calculated table resulting in a Power BI date table. Using GENERATE and ......(more)

Running M Queries In Visual Studio With The Power Query SDK - Writing M in the Advanced Editor in Excel or Power BI can be a frustrating experience unless you’re the kind of masochist who loves writing code in Notepad. There are some options for writing M code outside Excel and Power BI, for example Lars Schreiber’s ......(more)

Power BI with different Network Visualizations - (2018-Feb-10) A few days ago Microsoft provided another update for its desktop version of the Power BI data analytical tool - https://powerbi.microsoft.com/en-us/blog/power-bi-desktop-february-2018-feature-summary/. Among all the major updates in this ......(more)

Performance Tuning SQL Server

Then Why Doesn’t SQL Always Seek? - Scan The Man There seems to be a perpetual battle waged against the Index Scan. At some point it was declared that scans were inferior to seeks, and all energy should be dedicated to eradicating them. Much like asking why the whole plane isn’t made out ......(more)

When Were Statistics Last Updated for a Heap? - I got a question last week from a very smart fellow: How can I tell when statistics were last updated for a heap? Before I could email him back, I soon got another email in which he answered his own question. I’m not saying that he’s smart because he ......(more)

Obsessing over query operator costs - A common problem when looking at execution plans is attributing too much meaning and value of the costs of operators. The percentages shown for operators in query plans are based on costs generated by the query optimiser. They are not times, they are ......(more)

Setting and Identifying Row Goals in Execution Plans - Introduction The SQL Server product documentation is a bit light on the topic of row goals. The main official references are in: Hints (Transact-SQL) – Query (FAST and DISABLE_OPTIMIZER_ROWGOAL hints) DBCC TRACEON – Trace Flags (Transact-SQL) (trace ......(more)

Microsoft News

Windows 10 S Build 17093 Helps Users Ditch Passwords - Windows 10 S users may never again encounter a password prompt in the newest Windows Insider test build. ...(more)

Hardware News

The Intel SSD DC P4510 SSD Review Part 1: Virtual RAID On CPU (VROC) Scalability - Today, Intel is introducing their 64-layer 3D TLC NAND to the enterprise SSD market with the new Intel SSD DC P4510 NVMe drive. They are also finally giving us a chance to test the Virtual RAID On CPU feature introduced with the Skylake-SP and Skylake-X ......(more)

The Latest High-Capacity M.2: The Samsung 860 EVO 2TB SSD, Reviewed - The Samsung 860 EVO is the mainstream side of Samsung's new generation of consumer SATA SSDs, though our 2TB M.2 drive is a bit of a niche product. Like the 860 PRO, the new 860 EVO provides the necessary improvements to keep up with today's competition, ......(more)

Panasonic's Toughbook 20 Rugged 2-in-1 Gets Faster CPU, Standard SSD - Panasonic has revamped its Toughbook 20 rugged 2-in-1 portable to include a faster Intel Core processor and a 256GB SSD storage drive in its standard configuration. ...(more)

VAIO S Laptops Updated With 8th Gen Core CPUs & TruePerformance to Prolong Turbo Time - VAIO has updated its popular 13-inch VAIO S laptops sold in the U.S. with Intel’s latest 8th Generation Core i5/i7 processors featuring four cores. Besides new CPUs, the systems have also added the company’s new TruePerformance technology, which is designed ......(more)

HA/DR/Always On/Clustering

SQL SERVER – Don’t Ignore Warning: Cluster Resource DLL Update Restart Check - This was one of the hard lessons learned, so I am sharing with blog readers. This is about an error in cluster resource DLL. While applying the patch to SQL instance, I cluster, sometimes we get warning “Cluster Resource DLL Update Restart Check.” If ......(more)

Graph Databases

Developing product recommendations on WideWorldImporters using SQL Graph - SQL Server has always provided tools to manage hierarchies and relationships, facilitating query execution on hierarchical data, but sometimes relationships can become complex. Think about many-to-many relationships, relational databases don't have a ......(more)

ETL/SSIS/ELT

Iterative data modeling to avoid dreaded ETL - Gain agility by loading first and transforming later.In today’s world full of big data, every large enterprise faces a similar problem: how do I leverage my data more effectively when it’s spread across dozens or even hundreds of systems? Businesses ......(more)

Database Design, Theory and Development

Tidy Data And Normalization - In Hadley Wickham’s paper on tidy data, he makes a few points that I really appreciated. Data sets are made up of variables and observations. In the database world, we’d call variables attributes and observations entities. In the spreadsheet world, ......(more)

Data Modeling Trends in 2018 - The Database Management and Data Modeling landscapes have evolved significantly in the past few years, from the traditional relational model to now include non-relational models as well. The growth of Big Data and it’s unstructured and semi-structured ......(more)

The Key to Relational Keys - A New Understanding - Note: I have been using the proceeds from my monthly blog @AllAnalytics to maintain DBDebunk and keep it free. Unfortunately, AllAnalytics has been discontinued. I appeal to my readers, particularly regular ones: If you deem this site worthy of continuing, ......(more)

Data Visualisation

ggplot2: cowplot - This is part seven of a series on ggplot2. Up to this point, I’ve covered what I consider to be the basics of ggplot2. Today, I want to cover a library which is still easy to use, but helps you create more advanced visuals: cowplot. I was excited ......(more)

Data Science

Data Science and Standard Patterns - In Data Science, the word “Pattern” has a specific meaning, involving the patterns that arise from data. This type of analysis is quite common in Data Mining and other technologies used by a Data Scientist. In IT practices such as systems architecture ......(more)

4 trends in security data science for 2018 - A glimpse into what lies ahead for response automation, model compliance, and repeatable experiments.This is the third consecutive year I’ve tried to read the tea leaves for security analytics. Last year’s trends post manifested well: from a rise in ......(more)

Data Privacy

Can Consumers' Online Data Be Protected? - Everything online is hackable. This is true for Equifax's data and the federal Office of Personal Management's data, which was hacked in 2015. If information is on a computer connected to the Internet, it is vulnerable. But just because everything is ......(more)

So what is a Data Privacy Impact Assessment and why should organizations care? - If you’ve read anything about the upcoming General Data Protection Regulation (GDPR), you’ll probably have seen the phrase Data Privacy Impact Assessment (DPIA) used. It’s similar to the current Privacy Impact Assessment (PIA) already in place in countries ......(more)

So, What is a Data Privacy Impact Assessment and Why Should Organizations Care? - The first two posts in this series on GDPR are: So, What is GDPR and Why Should Database Administrators Care? So, What is GDPR, and Why Should Your Customers Care? If you’ve read anything about the ......(more)

Eight Technical Tips for CISOs Racing Against the GDPR Clock - The EU's GDPR is the most sweeping change to data protection in the past 20 years. C-level executives everywhere are scrambling to get a handle on what it means to their organizations and how they are going achieve compliance. ...(more)

Conferences and Events

Sign up for more free training from Redgate - Redgate has committed to hosting a free virtual event in every quarter of 2018, and will be kicking this off on February 28 with a livestream themed around data privacy and protection. The agenda has now been released, so you can see who will be presenting, what they will be presenting, and how you can tune in to watch....(more)

Computing in the Cloud (Azure, Google , AWS)

Azure Data Architecture Guide (ADAG) - The Azure Data Architecture Guide has just been released! Check it out: http://aka.ms/ADAG Think of it as a menu or syllabus for data professionals. What service should you use, why, and when would you use it. I had a small involvement in its creation, ......(more)

New Windows container images of SQL Server 2017 on Windows Server 1709! - The Windows Server Core container images of SQL Server 2017 Developer & Express based on Windows Server version 1709 are now available on Docker Hub! These can be used in both Windows Server Containers as well as Hyper-V Containers. Windows Server version ......(more)

Welcome to the world of serverless computing - Serverless computing is a concept taking the place of big data as the hot-button topic in internet development circles these days, and the conversation seems to always start with a discussion about what serverless actually means....(more)

Career Growth

What Counts for a DBA – Deadlines - Everyone needs deadlines. Even the beavers. They loaf around all summer, but when they are faced with the winter deadline, they work like fury. If we didn’t have deadlines, we’d stagnate. ? Walt Disney I hate deadlines. Really. I truly hate them. The ......(more)

Big Data

Top Benefits of Big Data in the Healthcare Industry - by Angela Guess According to a recent press release, “Quantzig, a global analytics solutions provider, has announced the completion of their latest analytics article on the top benefits of big data in the healthcare industry. The advent of digital technology ......(more)

Backup and Recovery

TDE and backup compression – still not working? - Until SQL 2016 if you used TDE (Transparent Data Encryption) you couldn’t use backup compression. In 2016 Microsoft changed this, but it has been a rocky road. Backups work okay but in some circumstances people have found that they are corrupt when they ......(more)

Backup & Restore Script with a Move - Ok, I’ll admit it. I like scripts that are handy and do things. Especially if the scripts make my life easier. Now, not every environment is the same. Instances get configured differently or things change just due to the nature of the business. In ......(more)

Azure SQL Database

Migrating to Azure SQL Database with Zero Downtime for Read-Only Workloads - Special thanks to MSAsset engineering team’s Peter Liu (Senior Software Engineer), Vijay Kannan (Software Engineer), Sathya Muhandiramalage (Software Development Engineer II), Bryan Castillo (Principal Software Engineer) and Shail Batra (Principal Software ......(more)

Azure Database Migration Service – February Updates - Overview We are pleased to announce the latest updates to the Public Preview release of the Azure Database Migration Service (Azure DMS). Addition information about these important updates appears below. Support for additional regions. Users can now ......(more)

Azure SQL Data Warehouse and Data Lake

Azure Data Lake Command-line Quickstart using Azure Cloud Shell - Have you been using Azure Data Lake Analytics mainly from the Azure Portal or Visual Studio interfaces? Do you want to use Data Lake Analytics programmatically without installing anything? Do you want to get started with Azure PowerShell, but you’re ......(more)

Azure CosmosDB

Watch Now: How to Build Serverless Apps with Azure Cosmos DB and Azure Functions - Customers expect apps to offer event-driven, near real-time experiences. Now you can subscribe to changes in Azure Cosmos DB collections and trigger logic in real time while being globally distributed, and without deploying or managing any servers. ...(more)

Administration of SQL Server

Execute Multiple Files Using SQLCMD/SQLClient – Which Is Faster? - Hello! I’ve been working on a PowerShell module that will migrate the schema of an Azure Data Warehouse from one database to another. The process has through quite a few iterations, and at one point I was heavily using sqlcmd utility to execute each ......(more)

SQL SERVER – Script Level Upgrade for Database ‘master’ Failed Because Upgrade Step ‘sqlagent100_msdb_upgrade.sql’ – Error: 5041: MODIFY FILE Failed - I have helped many clients in upgrade failures via my On-Demand consulting and I am amazed to see various ways which can break SQL Server upgrade. In this blog we would learn about fixing error Script level upgrade for database ‘master’ failed because ......(more)

Database Maintenance Without Breaking The Bank - Database maintenance does not have to be expensive. There are free tools out there that will make your life easier. Of course, commercial DBA tools are worth their price – they are paid for a reason. Software like SolarWinds Database Performance Analyzer ......(more)

How to Schedule a Job in SQL Server? – Interview Question of the Week #160 - Question: How to Schedule a Job on SQL Server? Answer: A very old and widespread question, keep on coming back to in interviews. Let us see how to schedule a job on SQL Server. The first condition to schedule a job in SQL Server is to make sure that ......(more)

SQL SERVER – Full-Text Search Not Working For PDF Documents - Full-Text is a feature of SQL Server which allows linguistic search in various languages. In general, we use LIKE operator to search inside the column data, but it is not very efficient. There are multiple advantages of full-text search. You can read ......(more)

.NET Related Articles

JWT Authentication for Microservices in .NET - As software development evolves to service-oriented architectures, the underlying frameworks and methods used must change as well. Mircea Oprea demonstrates how to create a service to manage authentication across components and servers....(more)