A Fine Slice Of SQL Server
Laetitia 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 closedSteve Jones points out one of the changes to T-SQL I really like:
This assumes I’ve added a table called dbo.Order with a PK of OrderID.
However, I can do this in the CREATE TABLE statement, like shown below. I add a new section after a column with the CONSTRAINT keyword. Then I name the constraint, which is always a good practice. I can then add the FK keyword, the column and the references that connects this child column to the parent column.
This came about in SQL Server 2014, along with In-Memory OLTP and the ability to create indexes inline with the table create script. It’s a minor quality of life thing but I do enjoy it.
Comments closedRobert Sheldon creates some keys:
MySQL supports six basic types of constraints for ensuring data integrity:
PRIMARYKEY,NOTNULL,DEFAULT,CHECK,UNIQUE, andFOREIGNKEY. In this article, I introduce you to each constraint type and provide examples for how they work. The examples include a series ofCREATETABLEstatements that demonstrate different ways to incorporate constraints into your table definitions. If you’re not familiar with theCREATETABLEstatement or how to create tables in a MySQL database, refer to an earlier article in this series that introduces you to the statement.
In short, they support the same set that SQL Server users are used to. But do read on to see the nuances behind each of these.
Comments closedQuestion: How can I find tables in a SQL Server database that have a Primary Key ?
Click through for three separate ways to answer this question, though one of them uses the dreaded ANSI-89 syntax, which is loathsome. An upgrade to ANSI-92 syntax, however, would make it a fine query.
Comments closed