Plenty of Performance, Power, and Foreign Keys

Database Weekly for April 27, 2024 Problems displaying this newsletter? [View online](. [Database Weekly]( The Complete Weekly Roundup of SQL Server News by SQLServerCentral.com Hand-picked content to sharpen your professional edge Editorial  One more reason to use foreign key constraints Since the title might be considered a bit vague, I don’t want you to wade through the article to figure it out. I will spare you the typical clickbait introduction, with me telling you what a foreign key constraint is, and why it and all the other constraint types provided by relational engines are useful. I will get straight to the point first: One more reason to use them is that they are very helpful when using a large language model AI (LLM), like [Microsoft Copilot, currently in preview for Azure SQL](. If you are a relational database programmer, you have hopefully heard about foreign keys. They let you set up a relationship between one set of columns, to another set of columns. For example, if you have an invoice table with a customerId and a customer table with a primary key of customerId, a foreign key can establish a relationship between these two columns. Since the primary topic of this discussion is the benefits for AI of using foreign key constraints, let’s ignore all the other values of foreign key constraints. The fact that they help the integrity of your data, can be useful in query optimization, and are at the very least, awesome documentation, let’s just ignore all of that and say none of that matters at all. If you ask an LLM to join our hypothetical invoice table to an equally hypothetical customer table, the likely shared column of customerId would certainly be recognized as a likely way to join the two table. But what if your table has other shared column names. RowCreatedTime, Name, etc. How would it know that these too weren’t keys that needed to be joined on? It wouldn’t. And since there is a very common practice of naming the surrogate key ID in every table? It is not possible to have two ID columns in the same table. So, we typically end up with Invoice.CustomerId joining to Customer.Id. But let’s be clear, not every table has even that level of clearly named columns. Let’s take a rather silly example of a couple of tables named T1 and T2. Their key columns are T11 and T21. In T11, there is a column T25 that references T11. Now, I did say this naming standard was silly, but this absolutely happens. And not always in as orderly a fashion as this. Sometimes table and column names may actually be something as “interesting” as a GUID (not the values, but the actual tables and columns). If you need to join T1 to T2, without a foreign key, you will 100% need to know the structure… and no LLM will likely ever be able to figure that out with any regularity. Certainly not in the amount of time you have hoped. But if you have that foreign key in place… not only will your LLM be able to write T2 JOIN T1 on T2.T25 = T11; your programmers won’t be able to mess up your data and forget to only put values into T25 that exist in T11. Which is quite a feat because it was really hard just typing this and only needing to remember two awful column names. So don't forsake the wonderfully powerful foreign key constraint, if for no other reason than the joy of letting AI do some of the dirty work of joining tables for you in the future! Louis Davidson (@drsql) [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. AI/Machine Learning/Cognitive Services [6 Best Prompt Engineering Tools in 2024]( From Past News - RSS Feeds Prompt engineering tools are software platforms that help business owners, content creators and prompt engineers craft effective prompts that maximize output from their large language models (LLMs) and generative AI tools [Human-AI Collaboration: How AI Can Enhance Human Capabilities and Ethical Considerations]( From Dataversity Artificial intelligence is here to change the world, and it is up to us to embrace this technology and use it responsibly to reap its full potential. Although critics... Azure SQL [Testing of the Microsoft Azure emissions insights capability within Microsoft Fabric]( From Kevin Chant Reading Time: 5 minutes In this post I want to share the results of my initial testing of the Microsoft Azure emissions insights capability within Microsoft Fabric. Which... Career, Employment, and Certifications [Author’s responsibility]( From SQLBI Digital media content creators have an ethical res... [can an introvert be a powerful presenter?]( From Storytelling with Data It often surprises people to discover that confide... Case Studies [Four steps towards tackling the complexity of managing multiple database platforms]( From Blog – Redgate Software The 2024 State of the Database Landscape survey showed that 79% of businesses are now using two or more database platforms (vs 62% in 2020). Businesses revealed that this growth was largely due to the differing use cases across each platform... Community Interests [Google’s Sundar Pichai Has No Time for an Employee Rebellion]( From IT Pro - Microsoft Windows Information, Solutions, Tools The tech giant’s business challenges have deservedly curbed its acceptance of political activism on company time, writes Dave Lee. Conferences, Classes, Events, and Webinars [Four steps towards tackling the complexity of managing multiple database platforms]( 79% of us are now using two of more database platforms - fantastic for leveraging a range of benefits, but not so great when it comes to levels of complexity. Looking for ways to overcome this? Here are four steps to take towards multi-database simplicity. DMO/SMO/Powershell [How to Troubleshoot SQL Server With PowerShell.]( From StraightPath Solutions SQL Blog PowerShell is an essential tool for SQL Server database administrators looking to streamline their workflow and automate repetitive tasks. When it comes to troubleshooting your SQL Server instances, PowerShell... Data Science [Building a Culture of Data]( One of the major trends in enterprise computing, and really in enterprises themselves is an increased emphasis on data. My career has always revolved around data, but this is a new focus for many parts of the organization. Even business units that traditionally don’t care about data realize that access to more, and better, data can make their job easier or expand their capabilities. Database Design, Theory and Development [SQL Logic]( In this article, I will discuss the history and thinking behind several types of logic that are typically associated with writing relational database code. Because of how relational databases implement NULLs, it definitely ... DocumentDB/Key-Value/Graph/other NoSQL Databases [Building MongoDB Aggregations in MongoDB Compass]( In the previous article in this series, I demonstrated how to build and run an aggregate statement in MongoDB Shell. For the examples in that article, I used the version of MongoDB Shell that is embedded in MongoDB Compass. This article continues the discussion on aggregate statements, except that the focus is now on the graphical components built into the Compass interface. Oracle/PostgreSQL/MySQL/other RDBMS [Recursive Common Table Expressions in Postgres]( From Curated SQL Ryan Booz explains how recursive common table expressions work: The first two articles in this series demonstrated how PostgreSQL is a capable tool for ELT… [Robins Tharakan: Unlock PostgreSQL Superpowers with pg_tle]( From Planet Postgres pg_tle - A Must-Know for DevelopersPostgreSQL... [Recursive CTEs: Transforming and Analyzing Data in PostgreSQL, Part 3]( From Simple Talk The first two articles in this series demonstrated... [PostgreSQL Row Visibility Indicators]( From Curated SQL Cary Huang explains how row visibility works in Po... [General availability: Azure Cosmos DB for PostgreSQL geo-redundant backup and restore]( From Azure Updates Store cluster backup in another region. Restore cluster in the primary or another region. [David Wheeler: Mini Summit Four]( From Planet Postgres My thanks to Jonathan Katz for his presentation, “Trusted Language Extensions for PostgreSQL”, at last week’s Postgres Extension Ecosystem Mini-Summit. As usual I’ve collected the transcript here interspersed with... [Using Common Table Expressions: Transforming and Analyzing Data in PostgreSQL, Part 2]( In the first article in this transforming data series, I discussed how powerful PostgreSQL can be in ingesting and transforming data for analysis. Over the last few decades, this was traditionally done with a methodology called Extract-Transform-Load (ETL) which usually requires external tools. The goal of ETL is to do the transformation work outside of the database and only import the final form of data that is needed for further analysis and reporting. However, as databases have improved and matured, there are more capabilities to do much of the raw data transformation inside of the database. Performance Tuning SQL Server [What Else Happens When Queries Try To Compile In SQL Server: COMPILE LOCKS!]( From Erik Darling Data What Else Happens When Queries Try To Compile In S... [Troubleshooting Mysterious Blocking Caused By sp_getapplock]( From Brent Ozar Unlimited I’m kinda weird. I get excited when I’m troubleshooting a SQL Server problem, and I keep hitting walls.  I’ll give you an example. A client came to me because they were struggling with sporadic performance problems... [The How To Write SQL Server Queries Correctly Cheat Sheet: Conditional Join and Where Clauses]( From Erik Darling Data One Way Or Another The OR operator is a perfectly valid one to use in SQL statements. If you use an IN clause, there’s a reasonable chance that the... PowerPivot/PowerQuery/PowerBI [Implementing a Star Schema for a Power BI Semantic Model]( From Curated SQL Nikola Ilic reminds us to keep Ralph Kimball’s Data Warehouse Toolkit book at hand: But, what is a star schema in the first place? I… [Comparing Cumulative Values for Events across Different Periods]( From Curated SQL Kurt Buhler performs comparative analytics: In a previous article about format strings, we showed an example of how format strings can improve visualizations. The visualizations in that… [Issues around Power Apps in Source Control]( From Curated SQL Deborah Melkin continues a series on Power Apps: As a developer, I have two things I have to do: 1. Check code into source control… [Comparing cumulative values for events in different periods]( From Sqlbi TThis article describes how to compare time series that occur in different periods by standardizing the timelines to days since a specific event. ... [Comparing cumulative metrics for events with different start dates]( From SQLBI How to compare time series that occur in different periods by standardizing the timelines to days since a specific event... R Language [Selecting the Top N Values by Group in R]( From Curated SQL Steven Sanderson searches for subsets: In data analysis, there often arises a need to extract the top N values within each group of a dataset.… [Dropping Data Frame Columns in R]( From Curated SQL Steven Sanderson performs feature selection: As an R programmer, one of the fundamental tasks you’ll encounter is manipulating data frames. Whether you’re cleaning messy data… [Checking Row Existence across Data Frames in R]( From Curated SQL Steven Sanderson wants to check for row existence:... Security News and Issues [UnitedHealth Data Leak May Affect ‘Substantial’ Swath of U.S.]( From IT Pro - Microsoft Windows Information, Solutions, Tools The company said a ransom was paid to protect pati... T-SQL and Query Languages [Searching for a String Position]( From Curated SQL Chad Callihan goes index hunting: SQL Server has the CHARINDEX() and PATINDEX() functions that can both be used to find the position of a value… [How Long Will It Take to Learn SQL? The Truth Might Surprise You]( From SQLServerCentral Blogs In today's data-driven world, SQL (Structured Quer... Tools for Dev (SSMS, ADS, VS, etc.) [Connecting to SQL Server LocalDB from SSMS]( From Curated SQL Greg Low doesn’t need a fully-fledged instance of SQL Server: I saw a question on the Q&A forums about how to connect to the LocalDB… [Checking SSIS Package Performance]( From Curated SQL Andy Brownsword digs into SSISDB: I’ve recently been reviewing SSIS packages to make some performance fixes and needed a way to validate the results of… [Building a Docker image with Docker Build Cloud]( From SQLServerCentral Blogs In a previous blog post we went through how to build a Docker container image from a remote (Github) repository. Here we’re going to expand on that by actually... The...  [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  - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -