SQL and the Snare of Three-Valued Logic

The whole subject of the Three-Valued (also known as ternary, trivalent or 3VL) Logic of SQL tends to trip people up. This is hardly surprising in view of the fact that it involves an esoteric Polish mathematician and because it behaves differently in the DDL (Data Declaration Language) and the DML (Data Manipulation Language). In response to requests, Joe Celko comes to the rescue and makes it all seem simple.… Read more

Median Workbench

SQL Server database engine doesn't have a MEDIAN() aggregate function. This is probably because there are several types of median, such as statistical, financial or vector medians. Calculating Medians are essentially a row-positioning task, since medians are the middle value of an ordered result. Easy to do in SQL? Nope. Joe Celko explains why… Read more

Temporal Data Techniques in SQL

In the first part of this series on Temporal Data, Joe explained how it is that the Common Era calendar is irregular and mentioned that, although there are ANSI/ISO Standards for temporal operations in SQL, every vendor has something different. Now, he discusses other factors to take into account when using temporal data such as Holidays, and discusses a few techniques using Calendar, Report Usage and History tables… Read more

Temporal Datatypes in SQL Server

In the first of a series of articles on the tricks of tackling temporal data in SQL, Joe Celko discusses SQL's temporal data types and agonizes over the fact that, although there are ANSI/ISO Standards for temporal operations in SQL, every vendor has something different. He explains the mysteries of such things as time-zones, lawful time, UTC, CUT, GMT, CE, DST, and EST.… Read more

Constraint Yourself!

In his first article for Simple-Talk, Joe Celko demystifies the use of Constraints, and points out that they are an intrinsic part of SQL and are a great way of ensuring that a business rule is done one way, one place, one time. Almost all database programmers will find something new and useful in this article.… Read more