Category: Architecture

Jamal Hansen embraces the power of “and”:

At the beginning of this series, I promised that even if you know how to use an Object Relational Mapper (ORM) to interact with a database, knowing SQL would make you a better developer. Now that we have covered everything from SELECT to parameterized queries, it is time to answer the question that every Python developer eventually asks: when should I use an ORM, and when should I just write SQL?

I think there’s a reasonable split between the two, though the choice of ORM matters a lot as well. The heavier the ORM (nHibernate and Entity Framework, I’m looking at you), the more performance you’re trading off. By contrast, a micro-ORM like Dapper has a much slimmer profile, and if you combine that with judicious use of stored procedures for non-trivial work, the outcome is usually good.

Andy Brownsword creates a reference:

Large data solutions can span multiple databases. I’ve recently shared how amazing Database Projects can be but objects which reference across database boundaries can throw up warnings. Is the code solid or have you picked the wrong table and column combination? – you won’t know, and the code is fragile.

This is where References come into play. They make our projects aware of other databases so objects can reference them without unnecessary warnings. Plus intellisense is a nice bonus.

There are a few ways to handle these dependencies depending on how your projects are organised and what tooling you’re using, so let’s jump into the problem and check out the different options.

This was, without a doubt, one of the most painful things around working with database projects in Visual Studio. Not only do you need to keep up with your own database, but also the databases you reference. In addition, if the databases you reference happen to reference your database, you’ll end up with circular dependencies that will fail. The “best” solution was to create a stripped-down version of one of the databases that doesn’t include references, and then use that to jump-start the second database, and then use that to create the full version of the original database. So now you’re maintaining three separate database projects (at a minimum—this assumes you have one pair of databases with cross-referenced dependencies). Heaven help you if you have a cross-referencing love triangle.

Radim Marik digs into views:

VIEWs should be the cleanest abstraction SQL, and therefore Postgres, has on offer. I love the concept. The promise of decoupling logical intent from physical storage is perfect on paper. In practice, few things in the database world trigger such a heated debate or carry as much historical baggage. VIEWs mix big promises with false hopes, and the promises rarely survive contact with production.

The appeal is straightforward. Abstract “active customer” once and reuse it everywhere. Every query, report and dashboard uses the same definition. The “active customer” then becomes the foundation of a “customer orders” view, which in turn powers an operational “customer summary” view.

Some of these pain points are PostgreSQL-specific, but others (especially around the performance of nested views) resonates with SQL Server as well.

Brent Ozar answers a question:

At least once a month, I get this question from a client:

We have big data, and we want to save money. We want to move the older data down to some kind of archives that will cost us less.

I ask exactly one followup question:

Are you willing to modify the app that queries the data, or do you want the users to still be able to query the old data in their existing screens and reports?

Brent recommends trying a linked server approach. I’d also toss PolyBase in to the mix, though it’s going to have similar pros and cons to linked servers in this specific scenario. I have an old post on cold storage with PolyBase but the mechanisms haven’t changed much. I do have some sample scripts in my PolyBase talk’s GitHub repo, though fair warning that these are going away soon as I rebuild the talk for 2025.