The Complete Weekly Roundup of SQL Server News

Hand-picked content to sharpen your professional edge

Back in my early days as a database administrator, almost 16 years ago, I happened upon a web site called SQL Server Central. Because I found so many useful articles and forum posts, the site was open in my browser at work every single day. It was a great source of information, and eventually, I found that I was developing “friendships” with some of the other forum members, even though this was before the days of the social media we have today. There was a nice sense of comradery as people tried to help each other.

A couple of years later, my manager allowed me to attend PASS Summit in Seattle. I didn’t quite connect with many people that year, but I learned a lot. Since the experience was worthwhile, my manager agreed to send me again the next year. This was the year that really changed both my career and life because of a couple of professional development sessions. One encouraged me to become a volunteer with PASS. The other encouraged me to begin writing and speaking. That was the year that I became part of the community, what we now call #sqlfamily.

Just like a regular family, sometimes we don’t get along, and sometimes we fight amongst ourselves. But, generally, I believe that most of us care about each other. We are always willing to help each other, be that by providing a technical answer or donating towards a fundraising goal. The hardest part is when we must mourn the loss one of our members who has left this earth.

Events like PASS Summit and SQL Saturday have helped make the data platform community what it is today, but there have been several trends started at PASS Summit that have helped people get to know each other and build that sense of community. One of the trends is the wearing of kilts at PASS Summit and other SQL events. My colleague at Redgate, Grant Fritchey, began the tradition back in 2009. Each year, increasing numbers of #sqlkilts are worn on day two of PASS Summit.  

I must take credit for starting the second trend, #sqlkaraoke. I invited a friend to visit a karaoke bar, Seattle’s Bush Garden, with me in 2006. Four of us attended karaoke that first night, but it within a couple of years, we would go with thirty or more people. Twelve years later, entire bars are booked for karaoke parties at PASS Summit. And don’t worry if you are not inspired to get up and sing, you can just enjoy watching everyone else.

In addition to kilts and karaoke, #sqlfamily members also run, pray, take photos, visit museums, ride roller coasters, ski, hike, and more together. Most people attend events to learn from the sessions, but connecting through other fun activities is probably just as valuable.

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.

Vendors/3rd Party Products

Avoid use of the MONEY and SMALLMONEY datatypes - One of SQL Prompt's code analysis recommendations is that you avoid using the MONEY and SMALLMONEY datatypes - this article from Phil Factor explains why....(more)

Custom Metrics for Detecting Problems with Ad-hoc Queries - Whatever development methodology you use, you must check on the quality of the code before releasing a version of a database. A common crime is the unnecessary overuse of ad-hoc queries by applications. There is nothing wrong with the occasional use of an ad-hoc SQL query, but there is usually something very wrong in running a query repeatedly without parameterizing it....(more)

A strategy for implementing database source control - Much has been written on the benefits of having a database under source control, though many articles are clear on “why” but conspicuously vague on “how”. In this ahis article, David Poole describes what was required of his organization's database source control solution, how they approached the challenges, and how using Redgate SQL Source Control as a template helped. ...(more)

T-SQL

Finding overlapping ranges of data - This week, I had a problem where I needed to find and eliminate from the results of my query, data with overlapping ranges. I have written about this topic before, in my database design book book, in regards to building a trigger to avoid overlapping ranges. But even though I have written on the topic there, I still use Google just like you to get quick help (I use books when I want to learn, or expand my knowledge on a topic in depth, blogs when I need a simple answer to a simple or complex question.) ...(more)

Best-practices, guiding principles of choosing procedure parameters - Designing implementation of interface is as critical as designing interface itself. In this post we will discuss some advice on how to choose the right set of parameters for your procedures ...(more)

Fix WHERE clause AND/OR/NOT confusion with truth tables - The ANDs, ORs, and NOTs in a T-SQL query's WHERE clause can get confusing, especially if you're thinking of it like plain-Jane algebra. Here I show you how to figure out exactly what a query is doing, using truth tables! The post Fix WHERE clause AND/OR/NOT ......(more)

Exploring system tables, views, SPs etc - I wanted to spend a few minutes highlighting a couple of important tools for figuring out what information you have available to you within SQL Server. sys.all_objects and sys.all_columns. Note the All. These system views are almost exactly the same ......(more)

Checking for temporary table existance - I was recently reviewing a newly created T-SQL stored procedure. This procedure was verifying temporary table existence with the following code: IF exists (select * from tempdb..sysobjects where name like '#fg%') DROP TABLE #fg ...(more)

Why Table Join Orders In Relational Databases Don’t Matter - I had a great question submitted to me that I thought would make for a good SQL Server blog post: …I’ve been wondering if it really matters from a performance standpoint where I start my queries. For example, if I join from A-B-C, would I be better off starting at table B and then going to A & C?...(more)

Tech News

McAfee Study Reveals 1-in-4 Organizations Using Public Cloud Has Had Data Stolen - by Angela Guess A recent press release reports, “McAfee, the device-to-cloud cybersecurity company, today announced its third annual cloud adoption and security report, Navigating a Cloudy Sky: Practical Guidance and the State of Cloud Security. The ......(more)

Tech Industry Signs Accord to Protect People From Cyberattacks - Microsoft Corp., Facebook Inc. and dozens of other technology companies from around the world have come together to help protect people from malicious cyberattacks. ...(more)

Security news and thoughts

Why IoT Security Issues Still Loom Large in Health Care - According to recent research from IoT security company Zingbox, health care organizations are wrestling with an array of IoT security issues. ...(more)

How Data Breaches Affect the Corporate Bottom Line - Using events reported as cyber-breaches in the nonprofit Privacy Rights Clearinghouse, a team of economists from Singapore, Cyprus, Hong Kong and the U.S. examined which firms are at highest risk of attack and what the consequences are. ...(more)

Reporting Services

Fix: Reporting Services Configuration Manager can’t find Power BI Report Server - I just spent ages trying to work out why I couldn't connect to Power BI Report Server using the Reporting Services Configuration Manager. Amazingly, I hadn't had to reconfigure it since I installed it, but now I needed to change the email account. And ......(more)

PowerPivot/PowerQuery/PowerBI

Demystifying Tabular Object Level Security (OLS) - Demystifying Tabular Object Level Security (OLS) April 15, 2018/0 Comments/in Blog /by Prologika - Teo Lachev / 389 Views Object-level security (OLS) is a frequently requested security feature when implementing semantic models. For example, a current project disallows some sales persons to see sensitive measures, such as Margin and Profit. In Multidimensional, modelers could use cell security to meet such requirements with the risk of compromising performance. Starting with SQL Server 2017 (compatibility level 1200), Tabular supports object-level security to disallow access to entire tables or specific columns without a performance penalty. As it stands, OLS has the following limitations (for a full list, review the documentation):...(more)

Performance Tuning SQL Server

Query Store Cleanup Can be Part of a Blocking Chain - Forgetfulness can lead to learning something new. This is a bit of a nightmare when it happens in production, but a treat when it happens in an isolated test system– and that’s how I learned this. I left a bit of blocking open on my test VM, and forgot ......(more)

ETL/SSIS/ELT

In-Memory OLTP: A Case Study - Watch this week’s episode on YouTube. When In-Memory OLTP was first released in SQL Server 2014, I was excited to start using it.  All I could think was “my queries are going to run so FAST!” Well, I never got around to implementing In-Memory OLTP.  ......(more)

Data Flow Buffer Size - I started answering a question on SQL Community Slack’s #ssis channel and I realized this would be better served as a blog post. The question was about three SSIS Data Flow properties: DefaultBufferSize, Engine Thread and DefaultBufferMaxRows....(more)

DevOps and Continuous Delivery (CI/CD)

The future of database DevOps - In this post, Redgate's Foundry team reflect on their assessment of the status of DevOps for the database. As well as giving you a behind-the-scenes look at how they've arrived at this thinking and setting out their future direction, they also invite you to shape the future of database DevOps by getting involved....(more)

Database Design, Theory and Development

SQL: Are bit columns useless in SQL Server indexes? - If you are aware of Betteridge's law of headlines, you already know the answer, but let me explain. There are a lot of odd myths that surround SQL Server. One of the more persistent ones is related to indexes on columns that hold bit values. A SQL Server ......(more)

A New Understanding of Keys Part 1: Primary Key Formal Mandate and Pragmatic Selection - Note: This the first of three re-writes of older posts to bring them in line with McGoveran's formalization and interpretation[1] of Codd's true RDM. They are short extracts from a completely rewritten paper #4 in the PRACTICAL DATABASE FOUNDATIONS series[2] ......(more)

Data Visualisation

Boxes and Lines…Er, Whiskers - I had to cut short my last dabbling in distribution post because I had to get to Game 2 of Round 1 of the playoffs between the Vegas Golden Knights and Los Angeles Kings, which was am amazing game with not one, but two, overtime periods! That made for ......(more)

R & Python 101: Complex Visuals - One of the most impactful ways data teams can communicate is by turning ideas into visuals to share with internal stakeholders. With Python and R, chart creators have more controls to better customize the final visualization that appears in their Periscope ......(more)

Data Privacy and GDPR

Webinar: 6 Principles of the GDPR and SQL Provision - On April 24, I’ll be hosting a webinar that talks about the GDPR and how you can help ensure compliance in your development environments. I’ll talk about some of the issues and show how SQL Provision can help. The GDPR is complex, but it certainly does ......(more)

Drive GDPR Initiatives with the Power of End-User Computing - Sponsored by The General Data Protection Regulation (GDPR) went into effect in May 2016, giving all organizations two years to put the right people, processes, and tools in place to comply. This regulation, which starts on May 25, 2018, aims to har ...(more)

Conferences, Classes, and Events

24 Hours of PASS – April 25th–26th - Well, it is that time of the year, again. PASS puts on a 24 Hours of PASS where 1 hours session go on once an hour for 24 hours. This is free training usually on a certain topic. The topic this time around is Cross Platform SQL Server Management. The ......(more)

Computing in the Cloud (Azure, Google , AWS)

10 Things You Need to Know about Working with Providers of Data Storage in the Cloud - Here's what you need to know about the hidden costs, risks and other considerations that must be taken into account when using a provider for data storage in the cloud. ...(more)

Career Growth

3 Ways Communication Can Help Analysts Be More Successful - Being a great analyst is more than SQL, Python or reporting—great analytics is a human exercise, a communications skill and way to grow a data-driven culture. Here are 3 simple techniques will help take your dashboards from simple intrigues to impactful ......(more)

AI/Machine Learning/Cognitive Services

Python Jupyter Notebooks in Azure - There’s a new feature in Azure, and I stumbled on it when someone posted a link on Twitter. Apologies, I can’t remember who, but I did click on the Azure Notebooks link and was intrigued. I’ve gotten Jupyter notebooks running on my local laptop, but ......(more)

What Do AI and Machine Learning Mean for DBAs - Over the course of the past few decades, major trends in technology have shaped and reshaped the role of the DBA in many organizations. As DBAs manage an increasing amount of data, they have also seen ......(more)

Azure ML Package-Part1 - Azure ML Package is another Microsoft related package that allows you to upload and download datasets to and from AzureML, to interrogate experiments, to publish R functions as AzureML web services, and to run R data through existing web services and ......(more)

Administration of SQL Server

SQL Server Features Discovery Report - I don't need to validate SQL Server installations on a regular basis. When the need arises, my preference is to run the SQL Server features discovery report. Further, I prefer to run it from the command line. After looking up command line parameters ......(more)

Provisioning SQL Server Instances with Docker - With SQL Server 2017, Microsoft announced the exciting news that SQL Server would now run in Docker containers. Laerte Junior provides a guide to get started creating SQL Server instances in Docker....(more)

How to sync user logins across SQL Server instances – dbatools is brilliant - This blog post is about how brilliant dbatools are. In this case – for syncing user logins between SQL Server instances. Background: Whenever I do my “DevOps and the DBA” talk I dedicate a minute or two talking about dbatools....(more)

.NET Related Articles

Calling Http endpoints in T-SQL using CURL extension - 12 0 SQL Server Database Engine don’t have built-in functions that would enable you to send information to some API using http protocol. If you would need to call some REST endpoint or a web hook from the T-SQL code, you would need to use WebClient or WebRequest classes from .Net framework and expose them as T-SQL function or procedure. In this post you will see how to create that kind of extension....(more)