The Complete Weekly Roundup of SQL Server News

Hand-picked content to sharpen your professional edge

Have you ever had a difficult time explaining your what your job entails to your parents or other family members? You may be working in a field that didn’t exist 20 years ago, such as mobile device development, or your job may be so specialized that non-technical people will never understand.

Now that my job title is “editor,” I pause for a few seconds when someone I don’t know very well asks me what I do for a living. Since I have been a developer or database professional for over 20 years, I want to just say that I work with databases. I end up explaining that I now edit an online technical journal, but my specialty is data. My ego makes me think that a technical career is a higher calling, so I end up explaining more than is needed.

I’m not alone in having a difficult time explaining what I do, especially to non-technical folks. For them, it is often easier to say “I work with computers.” Of course, that is a risky answer because some of us do not want to be the helpdesk for family and friends. I managed to avoid that trap years ago. When asked for help, I would say that I could help them design a database, but I’m not that good with personal computers.

In today’s world, the number of possible titles is growing. Try telling your grandmother that you are a “Scrum Master.” That sounds more like the name of a rock band than a profession. Other titles that might confuse grandma are “Architect” and “Evangelist.” There are many specialties, and sometimes a title at one organisation will not mean the same at another.

As a database administrator, I did whatever needed doing to keep things running at the organisation. Sometimes these were typical DBA tasks, but I needed to be able to understand the front-end applications as well. It was not easy explaining my job to my parents. On one holiday trip with extended family, I had to skip out on the fun for a few hours and be online for a software upgrade at the company. My parents didn’t understand why I couldn’t just tell them that I wasn’t available. To my parents, work was something that they were always able to leave behind and forget about when they were not at the office or factory. That was not the case for me, especially at that particular job.

Explaining our careers to the people in our lives is not always easy. I’m convinced that just saying “I work with computers” is good enough most of the time.

Kathi Kellenberger 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.

Webinars

SQL in the City Streamed December 2018 - The theme for December 2018's SQL in the City Streamed is Your Journey to Compliant Database DevOps. The schedule includes sessions designed to broaden your skillset, support your ongoing learning, and keep you up-to-date with what’s new in the industry and at Redgate....(more)

Creating a data culture can transform risk calculation for insurers - James Boother from COEO will be joining us for a live video session to discuss the seven steps some organisations are already taking to predict future risks with even greater certainty and the opportunities Data Mastery presents insurers....(more)

Enable Business Agility Through Database DevOps - Founder and Chief Technologist at Nebbia Technology, Esteban Garcia, joins Redgate to discuss how DevOps helps technology teams to go faster and automate everything. As teams lower the cycle time between idea and production, how can organizations leverage these new capabilities to improve product delivery and quality?...(more)

Getting executive buy in for DevOps - 3 top tips - In this webinar, Microsoft MVP Kendra Little will discuss the value of DevOps from the perspectives of CEOs, CIOs/CTOs, and Managers. She will explore how the role of CIOs and CTOs are undergoing a major transformation, and how DevOps aligns with that transformation....(more)

Vendors/3rd Party Products

Monitoring Changes in Permissions, Users, Roles and Logins - Compliance means keeping a close grip on any changes to the permissions and access control of a database. Sadly, the law has had to acknowledge, from bitter experience, that it is not just external intruders who want to do this, but it could also be attempts at fraud or data theft from within the organisation. Permission changes are certainly one of the things that security experts advise you look out for; you need the equivalent of CCTV trained on your servers....(more)

How to reset your development database in seconds using SQL Clone - Let’s say you’re making experimental changes to your development database and, to explore a hypothesis, you’ve just dropped a table. How long does it take you to restore the database to its previous state, so you’re ready to continue testing? If it’s long enough to go fetch a coffee, then it’s too long....(more)

T-SQL

String or binary data would be truncated: get the full picture in SQL Server 2017 - SQL Server 2019 Preview (CTP 2.0) introduced a long-awaited improvement to an error message that’s been around in SQL Server for many years, but was unhelpful......(more)

The 5 Scariest Moments for a SQL Server Developer - While families and friends are scaring each other this Halloween week with stories of ghosts and ghouls, I thought it’d be way scarier to talk about truncate tables and source control. Accidental Data Deletion You’ve spent all morning loading millions ......(more)

WAITFOR DELAY - Too Much TIME DATETIME On My Hands? - There are certain design patterns in T-SQL that give me pause. They may not be "code smells" per se, but when I encounter them I find myself thinking "there's got to be a more sensible way to accomplish this". WAITFOR DELAY is one example. I've used ......(more)

Security news and thoughts

Windows 10 Security Checklist Starter Kit - Use this checklist to see how your company stacks up in several key areas. ...(more)

Storage Isn’t Safe from Cyber Criminals - Black hat hackers are coming up with more sophisticated forms of attacks all the time; storage is not immune to their efforts. ...(more)

Product Reviews and Articles

SQL Server 2017 Query Performance Tuning, 5th Ed - A popular performance tuning book gets updated for SQL Server 2017, how does it fare?...(more)

SQL Prompt Code Analysis: Table does not have clustered index (BP021) - With a few exceptions, every table should have a clustered index. However, they are not always essential for performance. The value of a clustered index depends on the way a table is used, the typical pattern of queries, and how it the table is updated. ...(more)

The Data Catalog comes of Age - Nowadays, it isn’t just banks and multinational corporations who have to be rigorous about data. Even modest organisations who would previously been unable to afford the storage, tooling and processing power required, now have sophisticated data processing ......(more)

PowerShell

What Is SQLPSX? - SQLPSX is a useful library you can add to PowerShell to automate many DBA tasks. In this article, Laerte Junior demonstrates several useful commands and shows you how to create a SQL Agent job using this library to run daily DBA checks....(more)

PowerPivot/PowerQuery/PowerBI

Creating Calculated Columns Using DAX - In this article, Andy Brown shares his knowledge of how to create calculated columns in tables, using Power BI as the host software (all of the formulae shown would work equally well in PowerPivot or SSAS Tabular). This article is designed to be the first in a series of articles which will show you how to master programming in DAX. ...(more)

Power BI Date Dimension; Default or Custom? Is It Confusing? - If you have worked with Power BI for some time, you know that there are two types of the Date dimensions; Custom or built-in/Default. It is always confusing for people, that which date dimension is good to use, and what is the difference between these ......(more)

Performance Tuning SQL Server

Explicitly Drop Temporary Tables Or Wait For Cleanup? - I was recently asked if we are going to see performance differences if we explicitly drop temporary tables. I couldn’t remember the specifics, but I said it actually didn’t matter. However, that answer has bugged me, so I set up a quick test. Explicitly ......(more)

Using MAXDOP to fix a performance problem - As DBA’s we are sometimes put in a rock and hard place when it comes to database performance.  This situation recently happened with a production application and some nasty production performance issues one particular query was having. We get a call ......(more)

ETL/SSIS/ELT

SSIS Project Incompatible - Has this ever happened to you? You are opening an SSIS project and… it won’t open. Instead of a Control Flow filled with awesome tasks and containers, you see a message in Solution Explorer telling you “The application is not installed.” You know this ......(more)

DevOps and Continuous Delivery (CI/CD)

How to create DACPAC file? - In this post I will explain what the DACPAC is and how to create it. In previous post in DevOps series I described how to create new, empty database project in Visual Studio. One of the ways how to import an existing database (from a server) was import ......(more)

Deep Into Windows

Windows Server 2019 and What We Need to Do Now: Migrate and Upgrade - The general availability of Windows Server 2019 is big news. Here's how to migrate and upgrade with no surprises.  ...(more)

Database Design, Theory and Development

Steps to Create an In-Memory table for SQL Server - Creating a table to be in memory compared to standard tables that use the files on disk can have several performance benefits for high transaction databases. In memory tables store data in server memory compared to disks and can help eliminate locks ......(more)

Understanding Conceptual vs. Data Modeling Part 3: Don't Conflate Reality and Data - In Part 1 and Part 2  we explained that between 1975-81, when the E/RM and RDM were introduced, there was no distinction between an informal conceptual and a formal logical level. In 1980, however, Codd defined a formal data model and in the later 80s ......(more)

Data Science

Data Science in Visual Studio Code using Neuron, a new VS Code extension - Guest post by Lorenzo Silvestri, Electronic and Information Engineering Student at Imperial College London. Introduction In this post, I’ll give a short explanation of neuron, a Visual Studio Code extension that aims to be a one-stop-shop for data scientists. ...(more)

Data Privacy, Complianace, and GDPR

Data Governance Operationalization: The Gap - A decade ago, consultants had to create awareness & educate clients about governance; highlight regulatory risks, compliance requirements, penalties, etc. It was more like selling an insurance product. ...(more)

Avoid Scandal: Don’t Let Your Data Maintenance Get Sloppy - It’s well understood that data is critical to driving good business decisions, but it’s easy to forget that data also supports a number of other activities. Data is at the center of everything businesses ......(more)

Is your SQL Server environment ready for GDPR? Part 2 - In my previous blog post of this topic, I talked about the definition of what GDPR is and also described the first two phases of Microsoft’s recommended workflow in order to be in compliance with this data regulation. The Discovery and Manage phase was about ......(more)

Computing in the Cloud (Azure, Google , AWS)

Top 5 Strategic Considerations for Choosing Architecture Components in Azure - Microsoft Azure reference architectures, such as the diagram displayed below, can be very helpful when planning an implementation: Although we have best practices and common practices, there is rarely just one right answer. Nearly every technology present ......(more)

IBM Pursues Amazon Into Cloud - The Red Hat deal represents an admission by IBM that in-house growth wasn’t going to be enough to keep the company from falling permanently behind in a market that is growing in importance and size. ...(more)

Career Growth

Cloud Computing Certification Helps IT Recruitment Efforts - Paying for employees to get a cloud computing certification is helping ManTech recruit the best in government IT. ...(more)

So You Want to be a Data Engineer? - The work of Data Engineers is extremely technical. They are responsible for designing and maintaining  the architecture of data systems, which incorporates concepts ranging from analytic infrastructures to Data Warehouses. Data Engineers need to have ......(more)

Big Data

Data Warehouses and GPUs: Big Data at High Speed - “Three years ago it was tough to tell the market that they should put a Data Warehouse on top of something that runs on top of GPUs,” said Ami Gal, CEO and co-founder of SQream. “Now it’s clear that GPUs are storming A.I., Machine Learning, and data ......(more)

Azure DevOps

Tracking Cross-team Projects in Azure DevOps Boards - App Dev Manager Ricardo de Almeida shares insight on helping customers solve problems with software portfolio tracking by using Azure DevOps and TFS in a more interactive way. The first step is by helping customer understand that agile project management ......(more)

Azure DevOps Roadmap update for 2018 Q4 - In order to provide you with visibility into several of our key investments, we post quarterly updates to the roadmap on our Features Timeline page. Today, we’re sharing the latest for the final quarter of this calendar year. You’ll notice items are ......(more)

AI/Machine Learning/Cognitive Services

Machine Learning 101 - Click to learn more about author Steve MacLauchlan. By now you’re probably well aware that Big Data and Artificial Intelligence are major disruptors in almost every single vertical. Understanding the landscape can be challenging, particularly for business ......(more)

Is Artificial Intelligence Storage Better Than Traditional Cloud Storage? - NetApp's Octavian Tanase discusses the ways in which artificial intelligence storage will increase companies' ability to exploit data. ...(more)

Administration of SQL Server

SQLCLR vs SQL Server 2017, Part 9: Does PERMISSION_SET Still Matter, or is Everything Now UNSAFE? - SQL Server 2017 introduced a new security restriction for SQLCLR in the form of a system configuration option named, “CLR strict security”. So far, this series – SQLCLR vs SQL Server 2017 ......(more)

Signing Stored Procedures That Access Multiple Databases - A while back I wrote a post, Digitally Signing a Stored Procedure To Allow It To Run With Elevated Permissions Recently Manish Kumar asked an interesting question, what do you do if your proc accesses multiple or even all the databases on the server? So, ......(more)