SQL Blog Posts by Ben Nadel
This is everything that I have ever written about SQL. Of course, that doesn't mean I'm about to stop any time soon. I'm only getting warmed up!
November 2024
October 2024
May 2024
- Experimenting With Low-Level SQLite Access In Lucee CFML
- Creating In-Memory SQLite Databases Using JDBC In Lucee CFML
- Creating On-The-Fly Datasource Connections In Lucee CFML
- Experimenting With SQLite JDBC Connections In Lucee CFML
- Using Multiple Common Table Expressions In One SQL Query In MySQL
- Why I Avoid DEFAULT Column Values In My SQL Database
March 2024
February 2024
July 2023
- MySQL 8.0.13 Can Use An Existing Column As The DEFAULT Value For A New Column
- Considering UTC And Recording Activity Streak Data In ColdFusion
February 2023
December 2022
- Considering Nullable Date Columns As A Representation Of State In SQL
- Setting And Clearing Nullable Values In A Data Access Layer In ColdFusion
- Paging Through Data Using LIMIT And OFFSET In MySQL And ColdFusion
- Using INSERT INTO SELECT To Duplicate Rows In MySQL And Lucee CFML
- Using LATERAL Joins To Get "Top N" Records From Each Group In MySQL 8.0.14
November 2022
- Nesting JSON Functions Inside JSON_ARRAYAGG() And JSON_OBJECTAGG() In MySQL 5.7.38
- Preventing Unbounded Full-Table Scans In My ColdFusion Database Access Layer
October 2022
- A Database Column For "Date Updated" Has No Semantic Meaning, Nor Should It
- Converting UUIDs To Binary For VARBINARY(16) Storage In MySQL And ColdFusion
September 2022
June 2022
May 2022
- SQL "Join Tables" Are Just "Entity Tables" With Hard-To-Name Concepts
- Considering Approaches To Handling MySQL Key Conflicts In Lucee CFML
April 2022
March 2022
- Avoiding MySQL max_allowed_packet Errors By Splitting-Up Large Aggregation Queries In ColdFusion
- Playing With The MySQL 8 Document Store X DevAPI In Lucee CFML 5.3.8.201
- Using Common Table Expressions (CTE) To Create Derived Tables In MySQL 8
- Using LATERAL Derived Tables To Gather Row-Specific Aggregations In MySQL 8.0.14
- Tracking Database Schema And Schema Migrations In Git Version Control
- Upgrading My ColdFusion Blog From MySQL 5.7.10 To MySQL 8.0.28
February 2022
- Goodbye GROUP_CONCAT(), Hello JSON_ARRAYAGG() And JSON_OBJECTAGG() In MySQL 5.7.32
- Installing FusionReactor APM Showed Me A Huge Oversight In My ColdFusion Queries
- Irrational Guilt Over Using ORDER BY id In My ColdFusion SQL Queries
- Storing Encryption Key Version As A Prefix On The Encrypted Field In MySQL
January 2022
December 2021
- Recording Datadog / StatsD Gauges For Database Key Utilization In Lucee CFML 5.3.7.47
- Inspecting Primary And Secondary Index Key Utilization For MySQL 5.7.32 In Lucee CFML 5.3.7.47
November 2021
October 2021
- Making SQL Queries More Flexible With LIKE In MySQL 5.7.32 And Lucee CFML 5.3.7.47
- Creating A Group-Based Incrementing Value Using LAST_INSERT_ID() In MySQL 5.7.32 And Lucee CFML 5.3.7.47
- Creating A Group-Based Incrementing Value In MySQL 5.7.32 And Lucee CFML 5.3.7.47
- The Scope Of SERIALIZABLE Transaction Row-Locking Is Larger When Rows Don't Yet Exist In MySQL 5.7.32
- Using INTERVAL To Perform Date Math Without DATE_ADD() In MySQL 5.7.32
- CFQueryParam Value Attribute Seamlessly Handles Arrays In Lucee CFML 5.3.7.47
- Using WHERE TRUE To Create Dynamic, High-Performance SQL Queries In MySQL 5.7.32 And Lucee CFML 5.3.8.201
- Testing BIGINT AUTO_INCREMENT Usage In MySQL 5.7.32 And Lucee CFML 5.3.8.201
September 2021
- Exploring The Scope Of SERIALIZABLE Transaction Row-Locking In Lucee CFML 5.3.7.47
- Enforcing Unique Naming Constraints Using A SERIALIZABLE Transaction In Lucee CFML 5.3.7.47
- Generating SQL INSERT Statements From SQL SELECT Statements In MySQL 5.7.32
- I Wish My Relational Database Tables Were Narrower
March 2021
- Case Study: Removing Massive IN (ID-List) Clauses For Performance In MySQL 5.7.32 And Lucee CFML 5.3.7.47
- Killing Slow MySQL Queries In An Emergency In Lucee CFML 5.3.7.47
January 2021
December 2020
- Using COUNT(), COUNT(column), And COUNT(expression) Variations To Extract Row Metadata In MySQL 5.7.32
- Looking For Database Performance Bottlenecks And Optimizations Using The Sys Schema In MySQL 5.7
- Exploring Type-Coercion And Value Comparisons In A JSON Column In MySQL 5.7.32
- Experimenting With Virtual Indexed Columns In MySQL 5.7.32 And Lucee CFML 5.3.7.47
- Atomically Incrementing JSON Column Values In MySQL 5.7.32 And Lucee CFML 5.3.7.47
- More Experimenting With The JSON Column Type In MySQL 5.7.32 And Lucee CFML 5.3.7.47
- Experimenting With The JSON Column Type In MySQL 5.7.32 And Lucee CFML 5.3.7.47
October 2020
September 2020
June 2020
- Every Line Of Code That You Write Is An Explicit Decision To Make The Application Better Or Worse
- Using UPDATE + ORDER BY + LIMIT To Drive Multi-Worker Migrations In Lucee CFML 5.3.6.61
- You Can Use ORDER BY And LIMIT Within UPDATE And DELETE Statements In MySQL 5.6.37
- Why I Use TINYINT Columns Instead Of BIT Columns For Boolean Data In A MySQL Application
- I've Had Fundamental Misunderstandings Of How CFQueryParam Works Within MySQL Queries And ColdFusion / Lucee CFML
- Showing The Affects Of Database Index Structure On Membership Table JOIN Performance In MySQL 5.6.37
May 2020
- Performing A LEFT OUTER JOIN On An INNER JOIN In Order To Write More Expressive SQL In MySQL 5.6.37
- Using SELECT And UNION ALL To JOIN SQL Tables To CSV Data In MySQL
April 2020
March 2020
- Putting DEBUG Comments In Your SQL Statements Makes FusionReactor Query Tracing More Effective
- Deleting Data From A Table Using An INNER JOIN DELETE Query In MySQL 5.6.37
- Now Using utf8mb4 To Enable Emoji In My Comments
- Copying Data From One Table To Another Using An INNER JOIN UPDATE Query In MySQL 5.6.37
February 2020
November 2019
- Using MySQL's Null-Safe Equality Operator With CFQueryParam's Null Option In Lucee CFML 5.3.3.62
- Superficial Performance Comparison Between ColdFusion Query-of-Queries (QoQ) And Array Functions In Lucee CFML 5.3.3.62
- Replacing ColdFusion Query-of-Query (QoQ) INNER JOIN And LEFT OUTER JOIN With Array Functions In Lucee CFML 5.3.3.62
- Replacing ColdFusion Query-Of-Queries (QoQ) With Array Functions In Lucee CFML 5.2.9.31
- Serializing A MySQL RecordSet As A Set Of INSERT Statements Using Lucee CFML 5.2.9.40
October 2019
August 2019
June 2019
- Considering Index Design When Using A Nullable DateTime Column To Record A Scheduled Action
- Creating A Composite Index Using Ancestral Keys In A Hierarchical Database Table Design
- Keeping Prepared Statements Consistent Even With Dynamic Parameterized Queries In MySQL And ColdFusion
- SQL Queries That Look The Same Are Not Violating The DRY Principle
August 2018
- MySQL 5.7 InnoDB AUTO_INCREMENT Counters Get Reset After MySQL Service Restart
- Using "Safe Updates" To Prevent Unbounded UPDATE And DELETE Statements In MySQL
July 2018
October 2017
- MySQL's Slow Query Log "start_time" Column Is Actually The "end" Time Of The Query
- MySQL Multi-Range Read (MRR) Optimization Caused A Partial Database Outage
- Designing Data-Intensive Applications: The Big Ideas Behind Reliable, Scalable, And Maintainable Systems By Martin Kleppmann
August 2017
- Considering Uniqueness Constraints And Database Abstractions In Application Business Logic
- The MySQL 5.1 Java Driver Deprecates validationQuery Which Is Why "SELECT 1" May Not Show Up In Your ColdFusion Application
July 2017
- Wrapping Database Gateways In A Retriable Proxy For Lock Timeouts In ColdFusion And MySQL
- Using SLEEP() And innodb_lock_wait_timeout To Force Transaction Lock Timeouts In ColdFusion And MySQL
- ColdFusion Does Not Appear To Use The Connection Pool When Issuing KILL QUERY Commands
- CFQuery Timeout Uses KILL QUERY Command With MySQL 5 Driver In ColdFusion
- You Can Use Arrays In Field Equality Checks Within A Sequelize WHERE Clause In Node.js
June 2017
- Reflecting On Data Persistence, Transactions, And Leaky Abstractions
- It's Safe To Use Empty Arrays With The $in Operator In Sequelize
- The 10th Annual Regular Expression Day - June 1st 2017
May 2017
December 2016
November 2016
- Type Coercion Will Bypass Index Selection During Query Planning In MySQL
- Casting Bit Fields To Booleans Using The Node.js MySQL Driver
October 2016
September 2016
July 2016
April 2016
March 2016
- Putting DEBUG Comments In Your SQL Statements Makes Debugging Performance Problems Easier
- Grouping The MySQL PROCESSLIST By IP Address To View Connection Counts
January 2016
September 2015
July 2015
- Using Bit Values In COALESCE() In MySQL Results In Binary Values
- FusionReactor Offers Excellent Insight Into Java And ColdFusion Server Performance
- Performing Online ALTER TABLE SQL Migrations Without Any Downtime
March 2015
- Using GREATEST(), LEAST(), And Date/Time Values With COALESCE() In MySQL
- MySQL EXISTS() Operator Returns True Or False
September 2014
August 2014
- MySQL GROUP_CONCAT() Has Buggy Interaction With UNION Clauses
- MySQL GROUP_CONCAT() Fails Silently When It Hits Its Size Limit
July 2014
April 2014
January 2014
August 2013
August 2011
July 2011
January 2010
June 2009
March 2009
February 2009
January 2009
December 2008
- MySQL Cannot Convert Value '0000-00-00 00:00:00' From Column XX To TIMESTAMP
- MySQL: The Multi-part Identifier "u.id" Could Not Be Bound
- Getting ColdFusion To Work With MS SQL Server Express 2005 Databases
- Both MySQL and MS SQL Server Use @@Identity To Report Latest Auto-Incrementing Value
- Exploring The Cardinality And Selectivity Of SQL Conditions
- Rick Osborne On Database Indexing And SQL Best Practices
November 2008
September 2008
June 2008
April 2008
- Turning On Multiple Statements In ColdFusion 8 MySQL 4/5 Datasource
- Update To MSSQL To MySQL Translation (II)
- Update To MSSQL To MySQL Translation
March 2008
- Thoughts On Storing Duplicate / Calculated Data In The Database
- Ben Nadel's Easy Tips For Writing Better, More Optimized SQL
February 2008
January 2008
December 2007
- Getting One Record Per Group From A One-to-Many Join
- ColdFusion CFQueryParam Binding vs. SQL Execution
November 2007
- Grouping JOIN Clauses In SQL
- Getting @@RowCount After SQL INSERT INTO Statement
- Alf Pedersen On NULL Value Usage In Database Design
October 2007
- Exercise List: Designing The Database Schema
- Paginating Record Sets In ColdFusion With One SQL Server Call
- Using NULLIF() To Prevent Divide-By-Zero Errors In SQL
September 2007
- Ask Ben: Pulling SQL Records Based On A List Of IDs
- Are SQL PATINDEX() And The SQL LIKE Clause Doing The Same Thing?
- SQL User Defined Functions Are Slower Than Inline Logic
- Getting Only The Date Part Of A Date/Time Stamp in SQL Server (Revisited)
- SQL EXISTS Exploration (Thanks Christoph Schmitz!)
- SQL Optimization Case Study - JOIN Clause vs. IN Clause
- Using A SQL JOIN In A SQL DELETE Statement (Thanks Pinal Dave!)
- Using A SQL JOIN In A SQL UPDATE Statement (Thanks John Eric!)
August 2007
- Using CASE Statements In A SQL UPDATE Query
- Unexpected CF_SQL_BIT Behavior In ColdFusion ValueList() Function
July 2007
May 2007
- Updating In-Memory (Temporary) SQL Tables With UPDATE / DELETE Clauses
- Storing Float Values In An Integer Column Of A ColdFusion Query
- SQL Server Text Matching Is Case INSENSITIVE
February 2007
January 2007
- Internal Query Processor Error: The Query Processor Ran Out Of Stack Space During Query Optimization
- SQL Pivot Tables Are Rockin' Awesome (Thanks Rick Osborne)
- SQL Server NOLOCK / ROWLOCK Directives To Improve Performance
- SQL ISZERO() And NULLIF() For Dividing By Zero
- Cannot Use ColdFusion CFQueryParam With SQL TOP Directive
- ColdFusion CFQuery MaxRows Not A Bad Compromise When TOP Not Available
December 2006
November 2006
- SELECT TOP And ColdFusion Query Of Queries
- My Coding Methodology - Understanding The Madness And The Man Behind It
October 2006
- ColdFusion CFQuery BlockFactor Is Not Impressing Me
- Ask Ben: Getting A Random Date From A Date Range In SQL
September 2006
- Careful When Trying To CAST DATETIME AS INT
- Caution: ColdFusion Zero Date vs. SQL Zero Date
- SQL Optimization And ON Clause vs WHERE Clause
- Creating In-Memory Tables In SQL For Joining And Sumarizing
August 2006
- SQL COALESCE() Very Cool, But Slower Than ISNULL()
- SQL COALESCE() Like ISNULL() On Steroids ... With Caveats
- ColdFusion Query Error: Value Can Not Be Converted To Requested Type
- Adding Numbers To Date/Time Values
- SQL Date/Time BETWEEN And Comparison Operators Work With Floats
July 2006
- Ask Ben: Averaging Date/Time Stamps In SQL
- SQL IN Directive Much Faster Than Multiple OR Clauses
- Problems With Storing Date And Time Separately In SQL Server
- Ask Ben: Selecting Parts of a Date/Time Stamp In SQL
- Ask Ben: Comparing Only Times in SQL
- SQL AND / OR Order of Operations
- Getting Only the Date Part of a Date/Time Stamp in SQL Server
June 2006
- ColdFusion Query of Queries: Uses NULL Values, Returns Empty Strings
- Encountered "(. Incorrect Select Statement, Expecting a 'FROM', But Encountered '(' Instead, A Select Statement Should Have a 'FROM' Construct.
- ColdFusion: Error Occurred While Processing Request 10 >= 10
- LOCAL Variables Scope Conflicts With ColdFusion Query of Queries
- Why NULL Values Should Not Be Used in a Database Unless Required
- SQL LIKE Clause Case Sensitive in ColdFusion MX Query-of-Query
- ColdFusion SQL Error - [Table ....] Is Not Indexable By Name
- CreateDate() Much Faster Than DateFormat() For Date-Only Creation
- SQL UNION ALL Does Not Check Duplicates
- SQL Query Order of Operations
- SQL LIKE Directive Faster Than LEN() Method