SQL for Devs: Next-Level Database Techniques for Developers

✍ Scribed by Tobias Petry

• Year: 2022
• Tongue: English
• Leaves: 50
• Category: Library

✦ Synopsis

The Database Cookbook For Developers

You don't know SQL. Aside from basic CRUD statements, you haven't used any advanced database features. This free ebook will share many recipes to make development faster by using a lot of stuff you probably never heard of.

Your database knowledge is outdated

Your Tech Stack has changed dramatically in the past 20 years: Some technologies are now state-of-the-art, and some have been invented and are obsolete meanwhile. But you still use the same simple database concepts as many years ago. Every technology progresses, but databases don't invent anything new? Isn't that strange?

There have been countless improvements, but you don't know of them. When using ORM (Object-relational Mapping), most developers lose touch with database improvements as they are hidden from them. And that's good as you don't have to know every nifty detail. But when you know a little more about modern database features, you develop faster by relying on many fascinating features.

An evening is enough to attain more knowledge

Telling you that a single evening is enough to make you a database wizard is a lie. But you don't have to become one. You can take a shortcut by only learning essential features for developers. And this is exactly what this book is designed for.

This book is designed as a cookbook with many small independent recipes. Each one teaches you simple tips & tricks you can add to your application in a very short time. You should be able to read it thoroughly in a single evening and on the next day you can discuss those improvements with your colleagues. This is a whole different experience from all the books you bought but never had the time to read.

✦ Table of Contents

Contents
Data Manipulation
Prevent Lock Contention For Updates On Hot Rows
Updates Based On A Select Query
Return The Values Of Modified Rows
Delete Duplicate Rows
Table Maintenance After Bulk Modifications
Querying Data
Reduce The Amount Of Group By Columns
Fill Tables With Large Amounts Of Test Data
Simplified Inequality Checks With Nullable Columns
Prevent Division By Zero Errors
Sorting Order With Nullable Columns
Deterministic Ordering for Pagination
More Efficient Pagination Than LIMIT OFFSET
Database-Backed Locks With Safety Guarantees
Refinement Of Data With Common Table Expressions
First Row Of Many Similar Ones
Multiple Aggregates In One Query
Limit Rows Also Including Ties
Fast Row Count Estimates
Date-Based Statistical Queries With Gap-Filling
Table Joins With A For-Each Loop
Schema
Rows Without Overlapping Dates
Store Trees As Materialized Paths
JSON Columns to Combine NoSQL and Relational Databases
Alternative Tag Storage With JSON Arrays
Constraints for Improved Data Strictness
Validation Of JSON Colums Against A Schema
UUID Keys Against Enumeration Attacks
Fast Delete Of Big Data With Partitions
Pre-Sorted Tables For Faster Access
Pre-Aggregation of Values for Faster Queries
Indexes
Indexes On Functions And Expressions
Find Unused Indexes
Safely Deleting Unused Indexes
Index-Only Operations By Including More Columns
Partial Indexes To Reduce Index Size
Partial Indexes For Uniqueness Constraints
Index Support For Wildcard Searches
Rules For Multi-Column Indexes
Hash Indexes To Decrease Index Size
Descending Indexes For Order By
Ghost Conditions Against Unindexed Columns