Everything I Never Wanted to Know About Collation (DBW 2020-07-18)

 Database Weekly for July 18, 2020 Problems displaying this newsletter? [View online](. [Database Weekly]( The Complete Weekly Roundup of SQL Server News Hand-picked content to sharpen your professional edge Editorial  Digging Into Internals [Hugo Kornelis]( is a fellow [Data Platform MVP]( and friend. He runs [SQL Server Fast]( and we recently put up an interesting page with this title: [Debugging with debugger: investigating SQL Server's internal structures](. That's a great title, and it is for a presentation that he is building and getting ready to present. I suspect it will be at a virtual conference soon, but we'll see. Hugo digs deep into the details of how things work. If you read his blog regularly, you'll learn a lot about the ways in which SQL Server processes queries. You should learn how to better tune your own queries and produce efficient code. At least that's the goal. However, I wonder if most of you feel that way. While I see performance tuning sessions are often being the most popular topics at events, are you interested in internals? Do you actually use information from posts and presentations to dig in deeper to the way your queries are compiled and executed? I know Plan Explorer is a very popular tool among MVPs, and lots of people download it, but I still see so many basic questions on execution plans, I sometimes wonder if people are really using the tool to change they way they write code. If you want to learn more about query tuning, Hugo, [Grant Fritchey]( [Aaron Bertrand]( [Paul White]( [Erik Darling]( and many others will try to teach you. Just be sure you try and use the knowledge they impart in your daily work. Steve Jones - SSC Editor [Join the debate, and respond to the 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. Vendors/3rd Party Products [Automate Responses to Bad Deployments with SQL Monitor]( Jamie Wallis explains how SQL Monitor can both reveal quickly who ran a deployment, and when, and automate the incident-response workflow to ensure it's dealt with swiftly. By extending such workflows to development and test servers, as well as production, the feedback cycle starts earlier, and you can stop problems from ever reaching the users. [Using SQL Data Compare to Sync Reference Data]( [New release: Redgate Change Control now integrates with Git]( In Redgate Change Control v3.0, you can now commit your database changes to your local Git repository and collaborate with your team by pushing and pulling changes from the remote Git repository. If you’re using branches, you can also create and switch Git branches from within Redgate Change Control. .NET Related Articles [Building and consuming GraphQL API in ASP.NET Core 3.1]( GraphQL is an open source query language, originally developed by FaceBook. In this article, Joydip Kanjilal explains more about GraphQL and walks through a sample app. AI/Machine Learning/Cognitive Services [A new Rx: AI for operations in health care]( We may think of artificial intelligence (AI) in health care in terms of scientific advances, such as a cure for cancer or a science-fiction tricorder-like device. But in the... [OpenAI’s fiction-spewing AI is learning to generate images]( In February of last year, the San Francisco–based research lab OpenAI announced that its AI system could now write convincing passages of English. Feed the beginning of a sentence... Administration [Everything I Never Wanted to Know About Collation (14 minute video/podcast episode)]( I'm not an expert on collation in SQL Server, but there are a few important facts which I've had to learn the hard way. In this episode I share... The... [How to Customize the Microsoft Terminal]( There are many ways to configure Microsoft Terminal to fit your needs. Each release has refined or expanded upon the available customization options. The post How to Customize the Microsoft... [Get Alerted When Your SQL Server Restarts with sp_SendStartupMail]( When your SQL Server restarts, how do you find out? I’ve seen a few senior DBAs do a really smart trick: they write a stored procedure to send them... [On Dynamic Ports, Named Instances, and Firewalls]( Recently I had a client and their vendor trying to configure a firewall for a new installation of SQL Server 2019 on Windows Server 2019.Connections to the SQL Server... The... [What If You Really DO Need to Shrink a Database?]( You’ve heard that shrinking a database is bad because it introduces both external and internal fragmentation, it causes blocking, it causes transaction log growth while it runs, and it’s... Azure CosmosDB [How to do case insensitive searches on Azure Cosmos DB?](    Data gets saved as JSON in Azure Cosmos DB. JSON documents supports string, numeric and Boolean data types. Because of limited data types, you might end up... Azure SQL Database [Azure SQL Database and Backups]( Question, can you take a built-in backup ( a backup that Microsoft takes as part of the service) and create a new database from it on a NEW server... The... Career Growth and Certifications [T-SQL Tuesday #128–Learning From Others]( It’s the second Tuesday of the month and time fo... [The Ironic DBA—My First Year as a DBA [Part 3]]( T-SQL Tuesday is a monthly blog party for the SQL Server community. It is the brainchild of Adam Machanic (b|t) and this month’s edition is hosted by Kerry Tyler... The... Computing in the Cloud (Azure, Google, AWS) [Compiling a Custom Provider and Including for Terraform Cloud]( Assumptions You are familiar with the basics of setting up Go and can run basic Go commands like go build and go install and don’t need much guidance on that... The... Conferences, Classes, Events, and Webinars [Embracing DevOps through database migrations with Flyway]( Discover the aspects, methods, and strategies to build and manage your database deployments through CI/CD pipelines with open source tools like Flyway. Date and time: Wednesday July 29 & Friday July 31. Can't join us live? register to receive the recording. [Do More, With Less]( Next week, July 22 and 23, I’ll be presenting at Redgate Summit. Summt is our smaller, focused sessions. This one is about the concept of how DevOps, or, more... The... DMO/SMO/Powershell [Minimum Permissions for Get-DbaDbUser]( Words: 626 Time to read: ~ 3 minutes Update: 2020-07-15 – Thank you Garry Bargsley for being an unofficial editor ?? TL;DR: Get-DbaDbUser needs database access.To work against all... [Insights from your Database using PowerShell and TSQL]( The post Insights from your Database using PowerShell and TSQL appeared first on Ben Miller (@DBAduck). Data Privacy, Compliance, and GDPR [Is Your Company Prepared to Address New Data Privacy Regulations?]( Click to learn more about author Gerald Beuchelt. Two years have passed since the inception of the General Data Protection Regulation (GDPR). This legislation placed the privacy conversation front... [Ars readers hated this startup’s privacy policy—so the company changed it]( Neeva's new privacy policy is a significant improvement over the original. Data Science [No More Iris: Three Ways to Become a Better Data Scientist]( Click here to learn more about Ted Kwartler. Each semester I am tempted to teach my Harvard Extension students using standard data sets like Iris or Titanic. It would... Data Visualisation [Data visualization and location analytics: 'Where' can tell you 'why']( Human brains are hardwired to build maps. We navigate the world around us through the creation of mental maps. Maps that assemble abstract landmarks and build spatial relationships between... Database Design, Theory and Development [Debugging without debugger: investigating SQL Server’s internal structures]( This page contains the description for my conference session “Debugging without debugger: investigating SQL Server’s internal structures”. Description Target audience Experienced database developers and DBAs, plus all curious geeks.... [Database Fundamentals #27: Creating a Primary Key in the Table Designer]( Defining primary keys is the hardest part of the operation. You will need to work very closely with the business in order to define exactly what column or columns... The... DevOps and Continuous Delivery (CI/CD) [Database DevOps – Where do I start?]( By now you have most likely heard the term DevOps and you are most likely working for a company that has implemented DevOps is some manner, that could be... The... [How to Enable Fast Database DevOps Adoption Across the Enterprise]( As a DevOps Advocate at Redgate, I frequently work with Enterprise customers to help them transform their software development process for databases across their entire organization. I don’t do... [DevFinOps: Why Finance Needs to be Integrated with Development and Operations]( Click to learn more about author Jay Chapel. The formation of DevOps brought together two distinct worlds, causing a shift in IT culture that can only be made better... [Essential Practices to Integrate Database DevOps into Your Cloud Migration]( As working patterns change dramatically in 2020, we see an increasing number of customers and community members shifting components of their environment into the cloud. The 2020 State of... ETL/SSIS/Azure Data Factory/Biml [One Way to Incrementally Develop ADF Pipelines]( A friend pinged me recently to ask about rolling back Azure Data Factory (ADF) pipeline versions. My response was a question: Are you using source control with ADF? That... [Comparing SSIS and Azure Data Factory]( For the better part of 15 years, SQL Server Integration Services (SSIS) has been the go-to enterprise extract-transform-load (ETL) tool for shops running on Microsoft SQL Server. More recently,... The... [How To Use ‘Specify dynamic contents in JSON format’ in Azure Data Factory Linked Services]( Make any Azure Data Factory Linked Service dynamic! In a few different community circles I’ve been asked ‘how to handle dynamic Linked Service connections in Azure Data Factory if... Hardware [Western Digital releases new 18TB, 20TB EAMR drives]( EAMR offers similar density gains to HAMR and MAMR, with less exotic tech. [The Dell XPS 13 (9300) Review: Return of the King]( Dell changed the Windows laptop market in a single stroke with the launch of the updated XPS 13 back in 2015, ushering in the world of the InfinityEdge display,... Performance Tuning SQL Server [Using LAST_QUERY_PLAN_STATS in SQL Server 2019]( In my opinion, SQL Server 2019 is one of the greatest releases of the product that Microsoft has ever produced. The amount of improvements across the platform really allows... The... [SQL Server 2019: When Batch Mode On Rowstore Isn’t Better]( Um Hello WHAT DO YOU MEAN YOU’RE NOT ON SQL SERVER 2019 YET. Oh. Right. That. Regressed Look, whenever you make changes to the optimizer, you’re gonna hit some regressions. And it’s not just upgrading... [MAXDOP: not quite what you think]( An important change to the MAXDOP documentation was made last week, with a good write up by Pedro Lopes (Twitter), a Program Manager on the SQL Server team at... The... PowerPivot/PowerQuery/PowerBI [Adding PowerApps to Power BI]( Angela Henry discusses embedding PowerApps custom visuals in her Power BI report to keep up to date on her community activities.   [EXTERNAL TOOLS in Power BI Desktop… are MISSING???]( Excited for External Tools in Power BI Desktop and... [Power BI Monthly Digest – July 2020]( In this month’s Power BI Digest Matt and I will again guide you through some of the latest and greatest Power BI updates this month. The post Power BI Monthly... [Creating calculation groups in Power BI Desktop using Tabular Editor]( Learn how to create your first calculation group in Power BI Desktop using Tabular Editor in this step-by-step video. Product Reviews and Articles [Gathering SQL Server Performance Counters for Multiple Servers]( SQL Multi Script can easily be persuaded to run queries at the server level rather than the database level. It is also able to combine results from many databases... [Unboxing SQL Toolbelt: What is SQL Multi Script for?]( SQL Multi Script does one thing and does it well: it will run whatever script or scripts you select, on a list of databases, and collate all the results... [Setting up a New SQL Change Automation Project]( It’s been awhile since I’ve set up a new project in Redgate’s SQL Change Automation project. I’ve mostly used this in Visual Studio, but I need to demo something... The... [Exam Ref AZ-900 Microsoft Azure Fundamentals]( This book aims to help you pass the AZ-900 Microsoft Azure Fundamentals exam, how does it fare? Product Upgrades and Releases [Microsoft's new Microsoft Consulting Services unit to launch July 14]( More details about Microsoft's first reorg of its fiscal 2021 are coming to light, including how it is positioning its new separate consulting services unit. [Deployment Suite for Oracle: What’s New? Git Integration in Redgate Change Control v3.0]( In Redgate Change Control v3.0, you can now commit your database changes to your local Git repository and collaborate with your team by pushing and pulling changes from the... SQL Server Security and Auditing [SQL Server principals – Server Roles]( [MSSQLFUN & Decode ITeS becomes a family of 1700 individuals across the globe. Please join us on Facebook & YouTube.] Server Roles: Server roles are security principals to control access of... The... Security News and Issues [Twitter blocked tweets from verified accounts after a massive security breach]( What do Joe Biden, Barack Obama, Elon Musk, and Bill Gates have in common? Dozens of high-profile verified Twitter accounts were hacked on Wednesday, seemingly to push a cryptocurrency... [Microsoft urges patching severe-impact, wormable server vulnerability]( 17-year-old DNS flaw requires no user interaction and may be exploited soon. Software Development [Microservices Adoption in 2020]( Microservices seem to be everywhere. Scratch that: talk about microservices seems to be everywhere. And that’s the problem. Thinkers as dissimilar as Plato, Robert Boyle, and Keith Richards tend to agree about one thing: Talk is... [Society-Centered Design]( A World of Individuals Our world is on fire. For too long we have been complicit, maintaining the status quo. This must change. I’m writing this as we’re living... T-SQL [Sneaky Ordering With Row Numbers]( Puzzle Pieces I was investigating a query slowdown recently, and came across something kind of odd with windowing functions and order by. Let’s talk about these three queries: SELECT u.DisplayName, ... [The Length Limitations of LEN()?]( I was using LEN() to troubleshoot an issue I was h... [More CTE Myths: Persistent Expressions]( I Got Five On It I wish I had a dollar for every wrong thing I’ve heard about CTEs in my life. I’d buy a really nice cigar and light... Tech News [The online battle for the mental health of service workers]( Morgan Eckroth became famous on TikTok as morgandrinkscoffee. A 21-year-old barista and social-media manager for Tried & True Coffee in Corvallis, Oregon, she shares latte art, dramatic reenactments of... The Lighter Side [3D Printing Ear Savers Update July 14th 2020]( I’ve now been printing surgical mask straps aka “Ear Savers” since April 5th. It has been a wild journey getting started and obtaining all the supplies needed such as... The... [NASA’s first lunar habitat may be an RV-like rover built by Toyota]( “NASA’s budget is stretched pretty thin.” [Ford brings back the Bronco SUV with a dizzying array of options]( 2-door, 4-door, and Sport variants, 7 trim levels, and a ton of customization. [Enigma Machine for Sale]( A four-rotor Enigma machine -- with rotors -- is up for auction.... Virtualization and Containers/Kubernetes [SQL Server and Docker Compose]( I used to think that Docker Compose was used solely to spin up multiple containers, in fact I blogged about doing just that here. That opinion changed when I... The... [Google’s new Confidential Virtual Machines on 2nd Gen AMD EPYC]( With AMD’s market share slowly increasing, it becomes very interesting to see where EPYC is being deployed. The latest announcement today comes from AMD and Google, with news that...  [RSS Feed]( This email has been sent to {EMAIL}. To be removed from this list, please click [here](. If you have any problems leaving the list, please contact the webmaster@sqlservercentral.com. This newsletter was sent to you because you signed up at SQLServerCentral.com. 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 follow the instructions on the daily newsletter. ©2019 Redgate Software Ltd, Newnham House, Cambridge Business Park, Cambridge, CB4 0WZ, United Kingdom. All rights reserved. webmaster@sqlservercentral.com  - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -