Joining Queries in Azure Data Factory on Cosmos DB Sources (2024-04-08)

SQLServerCentral Newsletter for April 8, 2024 Problems displaying this newsletter? [View online](. [SQL Server Central]( Featured Contents - [Joining Queries in Azure Data Factory on Cosmos DB Sources]( - [Option Recompile is a Magic Turbo Button That Actually Works.]( - [From the SQL Server Central Blogs - Finding the Latest Backup Timestamps for Your Databases]( - [From the SQL Server Central Blogs - Announcements from the Microsoft Fabric Community Conference]( - [SQL Server 2022 Query Performance Tuning: Troubleshoot and Optimize Query Performance]( Question of the Day - [Join Hint Limitations]( The Voice of the DBA  The First Rule This editorial was originally published on Aug 21, 2019. It is being republished as Steve is coaching volleyball in Utah. A long time ago I read a book called [The First Law](. It's a legal thriller, and I'd recommend it. It has nothing much to do with databases, but I was reminded of the book and the rules of any particular process when I saw a post called [The First Database Rule](. The post is from Seth Godin, who is really a marketing individual, but I do think his rule makes a lot of sense for clients. I also think that as both developers and database professionals, we forget that most of our clients would prefer to have our systems follow this rule: it should be as simple to fix an error as it is to make one. I've worked in many systems, and with many people, that expect data entry or loads to go perfectly. They expect things to work and want to blame the process, the person, the file, or something else when something doesn't work well. What might be worse sometimes is that we expect some data in the database to be immutable, like a PK, assuming that clients will have and enter all the correct data at once. Most of us do allow changes in our databases, but we don't make it easy, especially if it's a piece of data like a primary key. This is one reason I dislike many natural keys as PKs. We make mistakes when we enter them. Heck, I've probably typed my name a million times on a computer keyboard, and I still make mistakes. Even in data entry forms. What's worse, I have some auto-fill selections in my browser that are incorrect and I'll select the wrong one at times. Fixing mistakes ought to be easy. We ought to expect that we will get data wrong. We'll load it wrong, we'll transform it wrong, and our DBAs will type corrections wrong. Design the system to account for mistakes and ensure that problems can be quickly fixed. Whether by a client or a sysadmin. Agility ought not to be just how quickly we can change the software. It needs to include the ability to change the data, which is the most important part. Steve Jones - SSC Editor [Join the debate, and respond to today's editorial on the forums](   Featured Contents [SQLServerCentral Article]( [Joining Queries in Azure Data Factory on Cosmos DB Sources]( chandan.saxenaa@gmail.com from SQLServerCentral This article shows how you can use two Cosmos DB dataets in Azure Data Factory and join them on a common column. [External Article]( [Option Recompile is a Magic Turbo Button That Actually Works.]( Additional Articles from Brent Ozar Blog Guy Glantser is an Israeli SQL Server guru with a ton of great presentations on YouTube. I’ve had the privilege of hanging out with him in person a bunch of times over the year, and I’ll always get excited to do it again. He’s not just smart, but he’s friendly and funny as hell. [Blog Post]( From the SQL Server Central Blogs - [Finding the Latest Backup Timestamps for Your Databases]( SQLPals from Mission: SQL Homeostasis Finding the Latest Backup Timestamps for Your Databases I wanted to find out the most recent date and time each database was last backed up, focusing specifically on full, differential,... [Blog Post]( From the SQL Server Central Blogs - [Announcements from the Microsoft Fabric Community Conference]( James Serra from James Serra's Blog (Shameless plug: The price of my book “Deciphering Data Architectures: Choosing Between a Modern Data Warehouse, Data Fabric, Data Lakehouse, and Data Mesh” has dropped on Amazon to its... [SQL Server 2022 Query Performance Tuning]( [SQL Server 2022 Query Performance Tuning: Troubleshoot and Optimize Query Performance]( Additional Articles from SQLServerCentral Troubleshoot slow-performing queries and make them run faster. Database administrators and SQL developers are constantly under pressure to provide more speed. This new edition has been redesigned and rewritten from scratch based on the last 15 years of learning, knowledge, and experience accumulated by the author. The book Includes expanded information on using extended events, automatic execution plan correction, and other advanced features now available in SQL Server.   Question of the Day Today's question (by Steve Jones - SSC Editor):  Join Hint Limitations I am trying to enforce a remote join in my query between a small table in a local SQL Server and a large table that I access through a linked server. My local table uses a case-insensitive in English collation and the remote table has a case-sensitive collation in Spanish. I want to write this code: SELECT p.Name, pr.ProductReviewID FROM Production.Product AS p INNER REMOTE JOIN SQL02.Inventory.Production.ProductReview AS pr ON p.ProductID = pr.ProductID ORDER BY ProductReviewID DESC COLLATE Traditional_Spanish_cs_as ASC; What happens when I run this? Think you know the answer? [Click here]( and find out if you are right.    Yesterday's Question of the Day (by Thomas Franz) LIKE Condition for Date Which lines will I get back from the following statements: IF '2024_03_04' LIKE '[12][8901][0-9][0-9][ _-,.]%' SELECT 'underscore_1' IF '2024_03_04' LIKE '[12][8901][0-9][0-9][ _,.-]%' SELECT 'underscore_2' IF '2024-03-04' LIKE '[12][8901][0-9][0-9][ _-,.]%' SELECT 'dash_1' IF '2024-03-04' LIKE '[12][8901][0-9][0-9][ _,.-]%' SELECT 'dash_2'  Answer: only underscore_2 and dash_2 Explanation: When you use [ _-,.] in the LIKE condition as in the underscore_1 / dash_1 statement, the SQL server interprets it as "any char that is either a space, between underscore and comma or a dot", because the dash is a protected char when used in square brackets inside a LIKE condition. Since the underscore has the ASCII code 95 and the comma is 45 there are no numbers between, so both comparisions fail and underscore_1 / dash_1 will never be returned When you put the dash at the end (just before the closing ]) of this part of the LIKE condition as in underscore_2 / dash_2 SQL server is smart enough to recognize that this dash can't be a BETWEEN and uses it as valid char option and the interpretation will become "any char that is either a space, underscore, comma, dot or a dash". However to prevent mistakes regarding this undocumented "feature" (on the long term it is almost guaranteed that someone needs to add another char to the end of the list reintroducing this "bug") it is much better explicit escape the dash and use something as IF '2024-03-30' LIKE '[12][8901][0-9][0-9][ _/-,.]%' ESCAPE '/' SELECT 'dash_escaped' -- I wanted to use the common backslash here, but it gets removed by Wordpress. -- In your code you could of course use backslash instead of slash or any other char that should not be occur in your string IF '2024-03-30' LIKE '[12][8901][0-9][0-9][ _µ-,.]%' ESCAPE 'µ' SELECT 'dash_escaped' Ref: Wildcard - characters to match - [ [Discuss this question and answer on the forums](    Database Pros Who Need Your Help Here's a few of the new posts today on the forums. To see more, [visit the forums](. --------------------------------------------------------------- SQL Server 2017 - Administration [Downgrade from Enterprise to Standard (incl. SSAS, SSIS, SSRS)]( - Hi there, thanks for listening! We are preparing to downgrade one or our Instance from Enterprise to Standard. Thankfully, this is not a production server, so some downtime is not an issue. We are planning to make a snapshot of the VM so that we can rollback in case something goes wrong. I want to […] SQL Server 2017 - Development [Query help please]( - I have a history table that tracks changes to client records, im having trouble writing a query that will capture BOTH of these records based on (Where changedcolumns = 'Paid'). I need the OLD and NEW history in the result. [SQL agent help]( - Hi Guys, I have an SQL Agent job. It is called an SSIS Package and is stored in SSIS DB. I can see the SSIS Package completed without any problem. Job has only one step. However, the SQL Agent job never ends. Just for my testing, I created an SQL Agent job and used SQL […] SQL Server 2016 - Development and T-SQL [Tracking Database Growth - Script Automation & Reporting]( - Hello,  I found this script that helps me in populate a table with information about my database; specifically sizes. I am a looking for a way to automate the process wherein the data can be extracted to an excel spreadsheet for us to do reporting against to track our database size growth overtime. Your […] Development - SQL Server 2014 [Outer join table where there is 2 foreign keys to join]( - I am asked to provide a date in a query from a table that may or may not have a record. Thus the outer join. The issue is the joined table may have many records for the ID (foreign key) being joined, and the query is returning the first result not the last.  There […] SQL Server 2019 - Administration [Optimizing Always On failover alert]( - Hello, We are trying to optimize the no. of alerts generating while Availability Group fails over. Event ID: 1480: Database Replica Role Changed Event ID: 19406: Availability Group Replica Role changed. As per my understanding Event 1480 can never happen without 19406. Hence it is ok to disable alert for 1480. Please let me know […] SQL Server 2019 - Development [how to do this in SQL?]( - Hi everyone I am working on a query and I am totally stumped. I don't even know where to begin. I will give test data, expected outcome, and explanation of logic. Test Data: create table #test_table ( company char(4), trade_date date, type char(1), level int, price decimal(6,2) ) insert into #test_table values ('ABC','2024-03-21','L','100','23.45'), ('ABC','2024-03-21','L','200','33.12'), ('ABC','2024-03-21','L','300','21.05'), […] [Need Help: Polybase external table on Parquet file stored in Azure StorageV2]( - My goal is to create a Polybase external table from a Parquet file located in an Azure storage container. SQL Server 2019 (RTM-CU22-GDR) (KB5029378) - 15.0.4326.1 (X64) StorageV2 (general purpose v2) (LRS) File was exported from SQL Server using: parquet-cpp-arrow version 4.0.0 I know that the SAS token I'm using is probably good in terms […] [Help with reading from JSON array]( - Hi all, I am trying to read the following JSON code in SQL Server: declare @json nvarchar(max) set @json = ' { "WorkId":121, "Code":"UK_AAT", "UpdateAttributes": { "TriggerDateMapping": { "3N":"N/A", "10N": "N/A" }, "OverrideRules": [ {"Busket Rule":"A1", "Exit_Window": 15, "Strategy": "STE Triggered", "Start_Date": "Month 2" }, {"Busket Rule":"C2", "Exit_Window": 44, "Strategy": "STE", "Start_Date": "Month 2" }, […] [test sql servers]( - Hi we run 2019 standard. I'm torn between asking my dept head for 3 test sql servers for use by everybody so that we can test 3 projects at once vs enough horsepower on my local to run 3 instances.  I'm leaning toward one that is shared and whatever it takes to run 3 instances […] [error using #temp tables]( - Hi I am getting this error message when I try to re-use a temp table. The code is a bit long so I won't post it here. Here is a template on how the temp tables are being used. What am I doing wrong? Code: DROP TABLE IF EXISTS #TEMP1 DROP TABLE IF EXISTS #TEMP2 […] SQL Azure - Administration [Database Snapshot Alternative in SQL MI]( - Database snapshots are not supported in SQL Managed Instance, Is there a workaround for Database snapshot feature. Restore from point in time backups will take a lot of time for us. I want to create a readonly copy of a database from a specific point, how can this be achieved in SQL MI? Analysis Services [Dates are rendered in multiple formats in Excel against Cube]( - We upgraded a SQL2014 environment to a sql2016 one recently with a customer. This customer still uses the traditional SSAS Multidimensional cubes. After the upgrade the dates no longer present themselves in the singular same format within Excel reports. A value of 14 march 2024 is represented as '2024-03-14' correctly , but 4 march 2024 […] SQL Server 2022 - Administration [Post Upgrade Access issues - Databases in 120 Compatibility and Read_Only Status]( - I recently did an in place upgrade from 2014 to 2022, and couldn't connect to any of the databases that were in Read_Only state AND still in 120 compatibility mode, with the following error: "Cannot open database 'DBName' version 782. Upgrade the database to the latest version." After setting all the databases to 160 mode […] [SQL2022 CU12 Question]( - Hi. I installed SQL2022 CU12 and in the notes it says:  2937584 Fixes an issue in which the sp_server_diagnostics stored procedure doesn't respond to the Always On availability group (AG) resource DLL within the HealthCheckTimeout when the I/O takes a long time, which causes unnecessary restart and failover. For example, when the sp_server_diagnostics stored procedure is waiting for the PREEMPTIVE_OS_GETFINALFILEPATHBYHANDLE wait type. […]   [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. ©2019 Redgate Software Ltd, Newnham House, Cambridge Business Park, Cambridge, CB4 0WZ, United Kingdom. All rights reserved. webmaster@sqlservercentral.com  - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -