SQL Tips and Tricks

Is anyone willing to share general guidance on where to draw the line when it comes to using DB configuration to speed things up ( almost "buy") vs. basically doing things manually ("build")? In my limited experience it often falls to app developers because competent DB admins are all getting paid much more to work elsewhere (as mentioned above, it is important to know the DB).

My canonical example is large volumes of data that accrue over time with the most recent accessed most often, where the DB admins can partition things or do partial indexes to keep access fast, but the app developers can move records into a separate archive table sometimes behind the scenes while still supporting things like (eventual) search of the whole data set. (A note here that it feels like a tool could do a lot of the initial heavy lifting to automate splitting one table into many when it makes sense -- perhaps when limited by a cloud DB's missing features)

Another management option sometimes accommodated by the DB vs. doing manually is to store all large blobs/files in their own separate database (filesystem?!) for a different storage configuration etc.

I imagine it can go as far as basically implementing an index manually: one massive table with just an auto-incrementing primary key but tons of columns then setting up a table with that ID and a few searchable columns (including up to going full text search/vectors I guess).

Edit: one useful tip manually implementing the Materialized View pattern with MSSQL 2016+: use partition switching as well explained and implemented by https://github.com/cajuncoding/SqlBulkHelpers?tab=readme-ov-... (incidentally the most commercially useful out-SEO'd tiny-star-count library I've ever found, focused on bulk inserts into MSSQL using .NET). I think this is a good example of drawing the buy/build line in the right place with the automation of the partition switching.

Just append a random constant at the end of every SELECT column list instead, 42 to the rescue! (I kid, I kid.)

I can't tell yet whether my experiment starting an all-OR WHERE clause with 0=1 so each OR could start the next line would go over like a lead balloon here too or not.

One thing I've actually found useful especially in SQL is always including a single-line comment in front of the closing of every multi-line comment, so that using a single-line comment to toggle off the opening of the multi-line comment toggles the entire block back into action and the end is already set (the closing comment is itself already commented out). This obviously doesn't work if multi-line comments start nesting, but that can confuse parsers enough already.

Trying to get an entire team on the same page with SQL formatting is one of the mothers of all bikesheds. In any case it's useful to be aware of the idioms whether or not they are personally palatable.

Snowflake, Databricks & Terradata support it, it’s very useful in data warehouses.

I'm not a fan of "just in case" development. Not when it comes to interfaces and also not regarding this `where 1=1` placeholder. Do things when you need them. Not if you think you might need them someday in the futures. Also, production code is not the place to keep dev helpers around. Do what you want in dev time, but for prod code readability and clear intent is much more important.

Are there any tools or tips to help speed up the "which JOIN is duplicating data" hunt?

Usually my biggest problem is getting all the query parameters lined up to reproduce the issue! (Being able to flip on extended logging or a profiler can make this easy.)

Cutting out the result columns when disabling JOINs to narrow it down is straightforward but tracking columns down in WHERE clauses quickly tends not to be.