Ti Point Tork

Nat’s 2022 Technical Link Pile: SQL Server/Databases

December 30, 2022 – 7:09 pm

See the Intro for context.

[20221231] Job Drain Pattern — we make heavy use of this internally. Cf In-Memory Outbox: an easy-to-use, reliable solution to perform atomic operations that update a database and send/publish messages, and it works for any database updates that are sent as commands (delivered by durable message queues).

[20221231] Make Your Database Tables Smaller — an argument against a Deleted column (namely, it permits partial deleting in a dependency tree, thus breaking foreign keys; and it makes every query more complex because you’re saying AND Deleted IS NOT NULL). Alternate proposal is to stash deleted rows as JSON in a “deleted rows” table. Different use case than us, though, because we need historic data to be consistent whereas author is only ever caring about current state.

[20221224] Recommended Approach to Encryption in SQL Server – he’s serialising a book chapter from the looks.

[20221224] ERD Intro – very basic, good for newbies.

[20221224] Database Devops – what an automated workflow looks like, and how it comes to the manual one.

[20221223] Liquibase — open source version control for database schemas with rollback and more.

[20221221] DBSnapper — for snapshotting and sanitising a database, eg to make test from live.

[20221210] Mangle — Google project, a programming language for deductive database programming. It is an extension of Datalog, with various extensions like aggregation, function calls and optional type-checking.

[20221119] Project Cambria — an isolated software layer that translates data between schemas on demand. This layer allows developers to maintain strong compatibility with many schema versions without complicating the main codebase. Translation logic is defined by composing bidirectional lenses, a kind of data transformation that can run both forward and backward.

[20221119] cozo — general-purpose, transactional, relational database that uses Datalog for query, is embeddable, and focuses on graph data and algorithms.

[20221029] Electric SQL — database for local-first apps that handles replication and schema changes.

[20221029] Lenses — central translation layer between schema.

[20221029] Data Lakehouse Paper — store in column-oriented formats like Apache Parquet, then provide fast metadata and search on top.

[20221008] Database Management with CI/CD – Commit database scripts to version control; Use database management tools; Keep changes small; Decouple deployment from data migrations; Set up continuous deployment and migration pipelines; Make migrations additive; Rollback with CI/CD; Don’t do a full backup unless it’s fast; Consider blue-green deployments. And what testing looks like.

[20220910] TigerBeetle — an open source accounting-focused database (everything is double entry!).

[20220718] T-SQL Comments – single line, block, the UI for block comment/uncommenting, and advice on when to comment.

[20220629] Things You Should Know About Databases – intro to B+ trees, read types, indexes, etc.

[20220629] PRQL – Pipelined Relational Query Language, a SQL replacement that understands pipelines, which transpiles to SQL.

[20220629] A Relational Model of Data for Large Shared Data Banks – Codd’s 1970 paper establishing relational databases and the set logic behind them.

[20220628] How to Handle Missing Information Without Using NULL – what it says on the box (via How can I avoid NULLs in my database, while also representing missing data?)

[20220504] Declarative Schema Migration for SQLite – maintain a schema in a file, build a clean DB with that schema, use db’s reflection tools to find columns tables indexes etc and diff against current state, adjust accordingly. Hacker News discussion has experiences of different ways of doing migrations.

[20220324] Replicating SQL Server – snapshot to file, reconstruct in new server, as way to avoid having to do a full copy over to switch servers.

[20220315] SQL Server I/O Basics – The purpose of this paper is to explain the Input/Output (I/O) requirements for SQL Server database file operations so that vendors and customers can evaluate and adjust their environments to meet the needs of SQL Server. Important When planning, deploying, and maintaining a Microsoft SQL Server installation, ensure that the I/O system supports all the factors outlined in this article. (Chapter 2)

[20220315] FORMAT is Expensive – other ways to have custom date formats without the performance hit of FORMAT. Materialising pre-populated column witchcraft.

[20220310] SQL Server Sends Passwords in Plaintext – when a database client logs in using SQL Server authentication (as opposed to Windows authentication), it sends the password (over not-necessarily-secure TLS).

[20220309] SQL Performance Explained – the book, apparently.

[20220309] Use the Index, Luke – free ebook by the guy who wrote “SQL Performance Explained” which really gets you going.

[20220225] On Anki’s Database Structure – an interesting critique of Anki’s database structure. We could do one for our schema too …

[20220203] varchar(n) vs varchar(max) – max can’t be indexed

[20220203] How SQL Server Executes a Query – this series will be a great introduction to what happens behind the scenes. I like episodes 1 and 2.

[20220202] Data Pages – 8k, 8096 bytes used for data (rest = header and row index). Possible to waste a lot of space if your records are enormous and don’t fit well into a page.

[20220202] Casting – use TRY_CAST or TRY_CONVERT to return NULL if the cast can’t happen. CONVERT lets you specify “style” but fk it’s ugly.

[20220201] SQL Server performance tuning using free tools

[20220201] Comparing GUID and uniqueidentifier values

[20220201] Detecting SQL Memory Pressure in 60 Seconds

[20220201] Introduction to SQL Server Sequence objects

[20220201] SQL Server Backup Examples

[20220201] SQLFacts – a free toolkit for SQL Server database engineers

[20220201] @return_value in SQL Server stored procedures

[20220201] Accelerated Data Recovery, checkpoints, SQL Server 2019

[20220201] SQL FOR loop alternatives – how to sin in SQL

[20220201] Automating triggers for data auditing in SQL Server

[20220201] Views in SQL Server 2019