A Fine Slice Of SQL Server
Joe Celko provides some context:
The
LIKEoperator has been in SQL since the very beginning. It’s a very weak form of pattern matching, that has only two simple wildcards. The wildcards are ‘_‘ (underscore) and ‘%‘ (percent). They respectively stand for any single character and for zero or more characters in a substring. The syntax is pretty straightforward:
Read on for examples of LIKE in action, including in check constraints.
Hans-Juergen Schoenig causes and fixes a problem:
In this case, we want to store departments and employees. Every department will need a leader, and every employee will need a department. We cannot have a department without a department leader – but we cannot have an employee without a department either.
Click through to see how you can resolve this kind of paradox with Postgres.
Comments closedJoe Celko defends the honor of the check constraint:
The problem is that newer SQL programmers (and even some more experienced ones) don’t take advantage of these constraints. Consider this slight rewrite of an actual posting on an SQL forum. In the original, the poster thought that identifiers should be integers and that ISO–11179 naming rules do not apply, so I cleaned it up a little, but this will make my point.
Click through to learn more about check constraints.
Comments closedLaetitia Avrot shares some advice:
This month’s PGSQLPhriday event is about UUId thanks to my post calling for a
fightdebate on the topic.I will answer a question a friend developer asked me: “What is the best when we need a primary key? UUID, CUID, or TSID?”
I hadn’t heard of two of these, but Laetitia provides some links to learn more about them and then offers up some advice on whether to use any of them. And the advice sounds a lot like the advice for SQL Server.
Comments closedChad Callihan doesn’t need solo rows; he needs team players:
SQL Server can be pretty smart when it comes to avoiding work it’s not required to do. One instance of this is the concept of join elimination. Join elimination occurs when queries involving joins may only need to check one table instead of two (or more) based on foreign key constraints.
Let’s take a look at join elimination in action.
Read on for one example of join elimination and one more reason why those foreign key constraints are so useful.
Comments closedJosephine Bush walks around town with a lantern looking for a good foreign key:
There are plenty of times I’m called upon to fix data. To do this, I must know what dependencies are in the database. Foreign keys are a crucial aspect of maintaining data integrity within relational databases. They establish relationships between tables, ensuring data references remain consistent and accurate. In an Azure SQL Database, identifying and managing foreign keys is essential for maintaining a well-structured and reliable database architecture.
Click through for a primer on foreign key constraints, a few ways to find them, and some closing thoughts on working with tables containing foreign key constraints.
Comments closedEtienne Lopes takes us through a scenario:
Let me start this post with a question, “Do you think that it can be beneficial to have a single column index for the foreign key column in the child table?“
Well, I believe I can ear three types of answers to this question:
- Always!
- Never!
- It Depends…
Click through for Etienne’s answer. I’d still prefer these indexes to have multiple uses, which generally means having enough columns on the index to act as a covering index for one or more important queries. But Etienne does show a good use case for this single-column index.
Comments closedBrian Bønk slips out of the constraints:
When working with data and building data models, I personally seldom use the constraints feature on a database. Call me lazy – but I think constraints are adding unnessesary complexity when building data models for reporting. Especially if you are working with the some of new platforms – like Microsoft Fabric, where you are using staleless compute, aka. data storage is seperated from the compute layer.
I understand the need for contraints on other database systems like OLTP systems.
In reporting models it can be somewhat usefull to have constraints between tables, as they help/force you to some level of governance in your datamodel.
But how can we use this in Microsoft Fabric and are they easy to work with?
Read on for those answers. I will note that I’m a stickler about constraints in transactional systems, though I agree that constraints in warehouses are not critical—assuming, at least, that you’re following the Kimball approach and have one and only one mechanism to write data, and that you have other mechanisms for vetting data quality.
Comments closedMeagan Longoria gives us a warning:
I’ve been building lakehouses using Databricks Unity catalog for a couple of clients. Overall, I like the technology, but there are a few things to get used to. This includes the fact that primary key and foreign key constraints are informational only and not enforced.
If you come from a relational database background, this unenforced constraint may bother you a bit as you may be used to enforcing it to help with referential integrity.
Read on to see what is available and why it can nonetheless be useful in some circumstances.
Comments closedSteve Jones links to Steve Jones talking about Steve Jones:
I had written about a FK in a CREATE TABLE statement recently, but the second half of this was that after the original question, the person asked if this would also work for a self-referencing FK. It does, and I wrote this to show that.
Click through for an example of how to do this.
Comments closed