Changing Data Types on Big Tables; Join Cheatsheet (Database Weekly 2/24/2018)

The Complete Weekly Roundup of SQL Server News In this issue: Webinars - [PASS Marathon GDPR in partnership with Redgate](#31220) T-SQL - [SQL Server on Linux: CU4 – NewSequentialId() – Uuid](#31229) - [Windowing without a partition by or an order by](#31228) - [The Challenge Is on: Multi-Way Number Partitioning with T-SQL and SQL CLR](#31224) - [Outputting Status in Your T-SQL Code](#31219) - [Get the join cheat sheet!](#31218) - [CROSS JOIN for fun](#31217) SQL Server Security - [Recovering a TDE protected database without the Certificate](#31227) Security news and thoughts - [New Spectre/Meltdown Variants](#31246) - [Cyber Hacks Cost Up to $109 Billion in 2016, U.S. Estimates](#31225) Reporting Services - [Capture SQL Server Reported Errors using Extended Events](#31216) PowerShell - [Populating PowerShell module FunctionsToExport automatically](#31222) PowerPivot/PowerQuery/PowerBI - [How to: Build a SQL Server Dashboard with Power BI](#31240) - [Power BI Issue Fix: Import from Excel Workbook Contents; Password Protection Failure](#31212) Performance Tuning SQL Server - [SQL Server Diagnostic Information Queries for February 2018](#31242) - [Using WITH (NOEXPAND) to Get Parallelism with Scalar UDFs in Indexed Views](#31238) - [New SQLChallenge: Defuse the Deadlock](#31215) - [More Showplan enhancements – UDFs](#31214) Microsoft News - [Microsoft to Devote $500 Million in Cash, Services to Support Startups](#31244) HA/DR/Always On/Clustering - [SQL SERVER- Unable to Failover AlwaysOn Availability Group to Disaster Recovery Site](#31243) DevOps and Continuous Delivery (CI/CD) - [How to build a DevOps roadmap to kickstart your digital transformation journey](#31210) DBA Tools - [What’s new in SSMS 17.5: Data Discovery and Classification](#31241) - [dbachecks – Configuration Deep Dive](#31226) Database Design, Theory and Development - [Uniqueifier details in SQL Server](#31234) - [Indexing and Partitioning](#31223) Data Science - [The Microsoft Team Data Science Process](#31221) Data Privacy - [Data Governance vs. Data Architecture](#31213) - [Free data privacy and protection livestream on February 28](#31211) Conferences and Events - [It’s events season – will you join us?](#31236) Computing in the Cloud (Azure, Google , AWS) - [How to Develop and Test Software with Azure VMs](#31245) - [Azure Functions and App Service Authentication](#31233) - [Your guide to Azure services for apps built with Xamarin](#31231) Bugs/Patches for SQL Server - [Cumulative Update #4 for SQL Server 2017 RTM](#31232) Azure SQL Database - [Migrating SQL Server databases from AWS EC2 to Azure SQL Database using the Azure Database Migration Service](#31230) AI/Machine Learning/Cognitive Services - [The Microsoft Artificial Intelligence Landscape – And What to use When](#31239) Administration of SQL Server - [Changing Data Types on Large Tables: The INT to BIGINT Conundrum](#31247) .NET Related Articles - [URL Matching in C#](#31248) - [Run Visual Studio always as administrator in Windows 10](#31235) [Database Weekly - www.databaseweekly.com]( The Complete Weekly Roundup of SQL Server News Hand-picked content to sharpen your professional edge SQL Server News for 2018-02-26 [SQL Compare]( The industry standard for comparing and deploying SQL Server database schemas Trusted by 71% of Fortune 100 companies, SQL Compare is the fastest way to compare changes, and create and deploy error-free scripts in minutes. Plus you can easily find and fix errors caused by database differences. [Download your free trial]( [Database DevOps]( Continuous Delivery for SQL Server Databases Spend less time managing deployment pain and more time adding value. [Find out how with database DevOps]( [SQL Monitor]( Don’t just fix SQL Server problems, prevent them from happening SQL Monitor helps teams looking after SQL Server be more proactive. Not only does SQL Monitor alert you to current issues, it gives you the information you need to stop them happening in the future. [Download SQL Monitor now and get a 14 day free trial]( Editorial - What Counts for a DBA – Inspirations In order to be great, you need to have an idea what great is. A quote I use in every one of my database design presentations is by one of my non-technical inspirations, C. S. Lewis. The quote is from his book, [An Experiment in Criticism](dp-kindle-redirect?_encoding=UTF8&btkr=1). “There are no variations except for those who know a norm, and no subtleties for those who have not grasped the obvious.” While his point had nothing to do with managing and coding for SQL Server, the basic premise applies to just about everything you do: one must know what “good” is before you can attempt to also be good. How we know what good looks like is almost always through a series of the inspiring people who lead us down a path. Having the right people to inspire you is very important in determining if you end up a DBA or a developer, or heaven forbid, a manager. We are shaped and molded by people along the way, starting when we are very young, leading up to the very moment we retire to sit on the porch and shoo kids from our lawns. And you, dear reader, are an inspiration to everyone you meet, either for good, or for bad, whether you like it or not. When it comes to working with SQL Server, I have a few people who inspired me from my very start. One of my first, and still one of my primary inspirations is Kalen Delaney. I first met her in the last century, largely in the UseNet forums, and then at a few writer’s conferences that Microsoft used to put on. I wanted to write books as well as hers, and while I have yet to write a book as great, I still try. Her 2000 internals book is the only book I paid full price for, at a conference no less, and have the signed book is sitting on my shelf. Many other people have inspired me in the SQL community in other ways. Kevin Kline has inspired me to be a chapter leader for the Nashville SQL Server user group. Patrick LeBlanc and Brent Ozar (to name a few) inspire me to be a better speaker by making it seem so effortless. Tim Ford has always amazed me as a volunteer, business man, and game show-host (along with being an awesome co-author of our book on DMVs). There are many more that I don’t have bytes to cover, but I have a new inspiration who is a large name in the SQL Community, [Catherine Wilhemsen](. Not because of some success she had, but because of a failure she encountered that is similar to one a lot of speakers have had (including myself). The difference is that she wrote about it. I won't reiterate the failure (read [here]( for that, but I have personally experienced similar failures at times, with no snow covered scapegoat around), but if she can fail, and tell 100s or 1000s of people about it, wow. I was inspired by her blog. If she (and others I knew in the comments of that blog) had that happen, then my past embarrassments sting a bit less. Not all inspirations will come from the SQL community. Your parents, teachers, coworkers at Wendy’s, and friends were all a part of what makes you what you are. Some of them were positive influences, some were bad influences, some were influences by not being around or existing in the first place. There are no grade school classes on being a DBA, and not many colleges offer DBA classes either. Let’s face it, something inspired you to become a DBA, or if you are an accidental DBA, something inspires you not to change career paths to something else. Some inspirations are just simply people you look up to for some reason other than their technical skills. My wife inspires me with how hard she works and cares about what she does, even if she sometimes gets burned out by it. When I am tired and feeling like it is all too much and just want to give up and take a long nap under a rock somewhere, I draw inspiration from a few musicians such as Paul McCartney, Roger Daltrey, and Mavis Staples. Not only are they amazingly talented, but these are people in their 70s who can get up on a stage for hours singing loud and bringing the house down. If they can do it, so can I. (Which coincidentally was the exact phrasing a person recently said when they passed me on a hiking trail one afternoon as I struggled along in the opposite direction from the end of the trail.) Be careful that an inspiration doesn’t start to become a hero. Inspirations are just people, and they will often behave in ways you will not like. They may not believe like you believe. Some people who are inspirations in a positive way, may inspire you to do better than them in other ways. Two things are for certain, first, you wouldn’t be who you are without the inspirations you had. Second, your future will be heavily affected by who you are inspired by, so be careful as you choose your inspirations. That database system you will manage in the future depends upon it. Along these lines, one thing that often concerns me with people is they feel the need for their inspirations to look and be like them. As I wrote about in my blog: [What Counts for a DBA – Blindness](, people are people, and if you didn’t know what someone looked or sounded like, you can still be inspired by them. I try to forget about everything about a person except the way they inspire me. Real life inspirations that have real experiences that other people can associate with…these are the inspirations we need most. Louis Davidson (@drsql) from [SQLServerCentral.com]( » [Join the debate, and respond to today's editorial on the forums]( --------------------------------------------------------------- The Weekly News 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 [] [PASS Marathon GDPR in partnership with Redgate]( - This special edition of the PASS Marathon series will focus on the General Data Protection Regulation (GDPR). You’ll learn how the legislation impacts SQL Server data professionals around the globe and practical steps you can take to help ensure you’re ready for when GDPR enforcement takes effect on May 25th....[(more)]( T-SQL [] [SQL Server on Linux: CU4 – NewSequentialId() – Uuid]( - Prior to SQL Server 2017 CU4 for Linux (Linux only) the generation of a sequential UUID may not function as expected. SQL Server’s NewSequentialId() calls the Windows API UuidCreateSequential, which is limited in SQLPAL, prior to the CU 4 update. SQL ......[(more)]( [] [Windowing without a partition by or an order by]( - If you’ve never worked with windowing functions they look something like this: SELECT name, max(create_date) OVER -- No this isn't meant to make sense (PARTITION BY lower(left(name,1)) ORDER BY owner_sid) FROM sys.databases; The other day someone ......[(more)]( [] [The Challenge Is on: Multi-Way Number Partitioning with T-SQL and SQL CLR]( - The challenge is known as multi-way number partitioning. Given a set of numbers S, representing quantities or weights, and a number of desired partitions, k, divide the quantities into k partitions such that the quantities are as evenly distributed among ......[(more)]( [] [Outputting Status in Your T-SQL Code]( - Often, you have some code that is taking a long time, and you want to know how much time sections of the code is taking, and you likely want to know immediately as your code executes. In this blog I will talk about a couple of the techniques you can use to make this happen, either in a permanent manner, as well so in a message....[(more)]( [] [Get the join cheat sheet!]( - Download and print this nifty little PDF with all of the INNER, LEFT, RIGHT, FULL and CROSS JOINs visualized! It’ll look great on your office wall or cubicle. Your coworkers and your interior decorator will love you for it. How it works: For each join ......[(more)]( [] [CROSS JOIN for fun]( - My son, who wishes to be known as “Pigeon” for the duration of this blog, was mixing the names of Hogwarts houses over a game of chess* with his brother: “ONE MILLION POINTS TO GRYFFINPUFF!” So I said, “Hey guys, I’m going to do a nerdy thing with my ......[(more)]( SQL Server Security [] [Recovering a TDE protected database without the Certificate]( - If you’ve been careful and done everything right when you’re setting up TDE then you shouldn’t run into this problem. We all make mistakes though, and we’ve all been asked to deal with environments that haven’t been so carefully managed. But what if ......[(more)]( Security news and thoughts [] [New Spectre/Meltdown Variants]( - Researchers have discovered new variants of Spectre and Meltdown. The software mitigations for Spectre and Meltdown seem to block these variants, although the eventual CPU fixes will have to be expanded to account for these new attacks. ...[(more)]( [] [Cyber Hacks Cost Up to $109 Billion in 2016, U.S. Estimates]( - The report details the range of threats that U.S. entities face from actors, including corporations and countries such as Russia, China, Iran and North Korea. ...[(more)]( Reporting Services [] [Capture SQL Server Reported Errors using Extended Events]( - In my introduction to Extended Events blog, I mentioned that the xEvent is my favorite toy for performance troubleshooting. This blog may make you to start playing with it. In this tip, I will be discussing how you can find what caused SQL Server to ......[(more)]( PowerShell [] [Populating PowerShell module FunctionsToExport automatically]( - If you've written a PowerShell module you'll be familiar with the FunctionsToExport portion of the .psd1 module manifest that starts out like this: This array defines what functions should be accessible from outside of the module, being almost everything ......[(more)]( PowerPivot/PowerQuery/PowerBI [] [How to: Build a SQL Server Dashboard with Power BI]( - A couple of years ago when Power BI Desktop was released I did a blog post on How to: Build a SQL Server Dashboard with Power BI Desktop. Today I will be presenting to the local MN SQL Server User Group on this very topic. The example I will be doing ......[(more)]( [] [Power BI Issue Fix: Import from Excel Workbook Contents; Password Protection Failure]( - One of the great features of Power BI Desktop is the ability to import your entire Power Pivot Excel model into it. This is an awesome feature, because you can import the entire model including tables, relationships, calculations, and hierarchies into ......[(more)]( Performance Tuning SQL Server [] [SQL Server Diagnostic Information Queries for February 2018]( - This month, there are more minor updates to the all of the versions of the queries, primarily in the comments and documentation. I have developed a T-SQL script that you can use to check whether your instance of SQL Server has been patched to mitigate ......[(more)]( [] [Using WITH (NOEXPAND) to Get Parallelism with Scalar UDFs in Indexed Views]( - Scalar functions are the butt of everybody’s jokes: their costs are wrong, their STATS IO results are wrong, they stop parallelism when they’re in check constraints, their stats are wrong in 2017 CU3, they stop parallelism in index rebuilds and CHECKDB, ......[(more)]( [] [New SQLChallenge: Defuse the Deadlock]( - When I first created SQLWorkbooks, I distilled what it was all about down into one sentence: “Learn SQL Server by Solving Problems.” This month, I introduce my first SQLChallenge, which distills that down into practice. The SQLChallenge features 23 minutes ......[(more)]( [] [More Showplan enhancements – UDFs]( - As I mentioned on yesterday’s post, with the recent release of SQL Server 2017 CU3, we released yet more showplan enhancements: you can see other posts related to showplan enhancements here. In this article I’ll talk about the second showplan improvement ......[(more)]( Microsoft News [] [Microsoft to Devote $500 Million in Cash, Services to Support Startups]( - Participants in the Microsoft for Startups program will receive Azure cloud credits, marketing and sales support and a chance at getting funded. ...[(more)]( HA/DR/Always On/Clustering [] [SQL SERVER- Unable to Failover AlwaysOn Availability Group to Disaster Recovery Site]( - One of my clients took my assistance to deploy Always On availability group. After few months, they added a Disaster Recovery node to windows cluster and availability group also. Later, they contacted me that they are not able to move availability group ......[(more)]( DevOps and Continuous Delivery (CI/CD) [] [How to build a DevOps roadmap to kickstart your digital transformation journey]( - By harnessing the concept of agility to a methodology that enables constant software innovation, DevOps allows organizations to respond dynamically to changing market conditions and rising customer expectations....[(more)]( DBA Tools [] [What’s new in SSMS 17.5: Data Discovery and Classification]( - This post is authored by Alan Yu, Program Manager, SQL Server. We are excited to announce the release of SQL Server Management Studio (SSMS) 17.5! Download SSMS 17.5 and review the Release Notes to get started. SSMS 17.5 provides support for almost all ......[(more)]( [] [dbachecks – Configuration Deep Dive]( - Today is the day that we have announced dbachecks a PowerShell module enabling you to validate your SQL Instances. You can read more about it here where you can learn how to install it and see some simple use cases 108 Configurations One of the things ......[(more)]( Database Design, Theory and Development [] [Uniqueifier details in SQL Server]( - This is a follow up post for �€¦ns-and-error-666/, for the ones that want to delve into the subject. First a quick background on uniqueifiers... A uniqueifier (or uniquifier as reported ......[(more)]( [] [Indexing and Partitioning]( - Partitioning tables is a great tool to increase the manageability of your data. Being able to move large amounts of data in and out of a table quickly is incredibly helpful. However, partitioning comes with a whole bunch of caveats and we need to be ......[(more)]( Data Science [] [The Microsoft Team Data Science Process]( - This is part one of a series on launching a data science project. This is the beginning of a series of posts around growing a data science project from the germ of an idea to its fruition as a stable oak. Before I get into the process, I want to start ......[(more)]( Data Privacy [] [Data Governance vs. Data Architecture]( - While Data Architecture focuses on technology and infrastructure design, Data Governance encompasses the people, the process, the workflow, as well as the architecture needed to support governance. So, even though Data Architecture is critical to Data ......[(more)]( [] [Free data privacy and protection livestream on February 28]( - Redgate’s Data Platform MVPs, Steve Jones, Grant Fritchey and Kathi Kellenberger, will head up the speaker line-up of this Data Privacy and Protection -themed livestream. Many of you will be aware that the new GDPR legislation comes into effect in May and, in light of this, the agenda will include sessions to help you become best equipped to deal with the challenges GDPR brings to compliant database management....[(more)]( Conferences and Events [] [It’s events season – will you join us?]( - There are three major events coming up, and you’re invited! This week Redgate are excited to be exhibiting as Gold sponsors at SQLBits 2018. SQLBits is THE largest SQL Server conference in Europe, and best of all this year it’s taking place just down ......[(more)]( Computing in the Cloud (Azure, Google , AWS) [] [How to Develop and Test Software with Azure VMs]( - In this post, I will discuss Azure DevTest Labs, a way to provide self-service for developers and testers with helpful automation but budge control for the business.   Lack of Control When I started working with Microsoft Azure, I quickly learned ......[(more)]( [] [Azure Functions and App Service Authentication]( - Azure App Service has a handy authentication integration that takes away the work of integrating with various identity providers (currently: Azure Active Directory, Facebook, Google, Twitter and Microsoft Accounts). Azure Functions are built on the same ......[(more)]( [] [Your guide to Azure services for apps built with Xamarin]( - When talking about app development today, the cloud is almost always part of the conversation. While many developers have an idea of the benefits that cloud can offer them – scalability, ready-to-use functionality, and security, to name a few – it’s ......[(more)]( Bugs/Patches for SQL Server [] [Cumulative Update #4 for SQL Server 2017 RTM]( - The 4th cumulative update release for SQL Server 2017 RTM is now available for download at the Microsoft Downloads site. Please note that registration is no longer required to download Cumulative updates. To learn more about the release or servicing ......[(more)]( Azure SQL Database [] [Migrating SQL Server databases from AWS EC2 to Azure SQL Database using the Azure Database Migration Service]( - Amazon Web Services EC2 is a well-known Infrastructure as a Service (IaaS) platform, and several organizations use EC2 to host various RDBMS platforms, including Microsoft SQL Server. Using an IaaS platform in the cloud provides certain advantages, such ......[(more)]( AI/Machine Learning/Cognitive Services [] [The Microsoft Artificial Intelligence Landscape – And What to use When]( - Artificial Intelligence (AI), at its broadest definition, is simply “a machine that can act using human-style reasoning or perception”. Of course, the technologies used to enable that definition are far from simple themselves. Artificial Intelligence ......[(more)]( Administration of SQL Server [] [Changing Data Types on Large Tables: The INT to BIGINT Conundrum]( - Changing a data type seems like a simple task unless the table is quite large and downtime must be kept to a minimum. Danny Kruge walks you through the process he created to change an INT to a BIGINT in a large table....[(more)]( .NET Related Articles [] [URL Matching in C#]( - Comparing URLs in C# code is a common task and seems simple. Camilo Reyes shows us that there are many pitfalls to avoid since people can come up with several ways to type the same URL. He then demonstrates how to solve several URL comparison problems....[(more)]( [] [Run Visual Studio always as administrator in Windows 10]( - If you plan on using Visual Studio with IIS it will need to run as an administrator. This is so Visual Studio can interact with IIS to set up the necessary virtual directories. If you don't run Visual Studio as an administrator then it won't even load ......[(more)]( --------------------------------------------------------------- Administrative To be removed from this list, please click [here]( Note: This is not the SQLServerCentral.com daily newsletter list, and unsubscribing to this newsletter will not stop you receiving the SQL Server Central daily newsletters. If you want to be removed from that list, you can [change your profile]( or follow the instructions on the daily newsletter. --------------------------------------------------------------- This transmission is ©2018 Redgate Software Ltd, Newnham House, Cambridge Business Park, Cambridge, CB4 0WZ, United Kingdom. All rights reserved. Contact: webmaster@sqlservercentral.com