Click here to monitor SSC
A service from Red Gate
Home SQL .NET SysAdmin Opinion Blogs Forums Books
Sign in  | Join  | Help
Authors  >  Joe Celko

Joe Celko Joe Celko
Joe Celko is one of the most widely read of all writers about SQL, and was the winner of the DBMS Magazine Reader's Choice Award four consecutive years. He is an independent consultant living in Austin, TX. He has taught SQL in the US, UK, the Nordic countries, South America and Africa.
He served 10 years on ANSI/ISO SQL Standards Committee and contributed to the SQL-89 and SQL-92 Standards.
He has written over 800 columns in the computer trade and academic press, mostly dealing with data and databases. He is the author of eight books on SQL for Morgan-Kaufmann, including the best selling SQL FOR SMARTIES.
Joe is a well-known figure on Newsgroups and Forums, and he is famous for his his dry wit. He is also interested in Science Fiction.
Voting Paradoxes: a SQL Stumper
by Joe Celko | 28 October 2011 |  9 comments |
Voting systems can become very complex, and some of them are easy to manipulate by tactical voting. Joe takes a couple of voting systems and wonders how you would implement them in SQL. He's even more curious as to how you, the reader, would do so. Read more...
Mimicking Network Databases in SQL
by Joe Celko | 26 September 2011 |  7 comments |
Unlike the hierarchical database model, which created a tree structure in which to store data, the network model formed a generalized 'graph' structure that describes the relationships between the nodes. Nowadays, the relational model is used to solve the problems... Read more...
Mimicking Magnetic Tape in SQL
by Joe Celko | 17 August 2011 |  21 comments |
The sequential nature of early data storage devices such as punched card and magnetic tape once forced programmers to devise algorithms that made the best of sequential access. These ways of doing data-processing have become so entrenched that they are still used in... Read more...
Arrays in SQL that Avoid Repeated Groups
by Joe Celko | 05 July 2011 |  5 comments |
It is certainly possible to fake an Array in SQL, but there are only a few occasions when it would be the best design. Most often, the wish for an array in SQL is a sign of a forlorn struggle against poorly-normalised data. One of the worst sins against Codd is the... Read more...
A Tale of Identifiers
by Joe Celko | 09 June 2011 |  22 comments |
Identifiers aren't locators, and they aren't pointers or links either. They are a logical concept in a relational database, and, unlike the more traditional methods of accessing data, don't derive from the way that data gets stored. Identifiers uniquely identify... Read more...
TIME Gentlemen please! The SQL Server temporal datatypes
by Joe Celko | 12 May 2011 |  12 comments |
If you are still using the old Sybase DateTime datatype, it is a good idea to move your code to the more standard datatypes that were introduced in SQL Server 2008. Joe Celko explains why, and walks through some of the history of the TSQL way of storing and... Read more...
Check your Digits
by Joe Celko | 07 April 2011 |  2 comments |
The most persistent struggle in data processing has been to ensure clean data. There are many ways that data can be incorrect and a database must check, as best it can, that the data is correct. The CHECK constraint is ideally suited for this sort of work, and the... Read more...
Improving Comparison Operators and Window Functions
by Joe Celko | 01 April 2011 |  9 comments |
It is dangerous to assume that your data is sound. SQL already has intrinsic ways to cope with missing, or unknown data in its comparison predicate operators, or Theta operators. Can SQL be more effective in the way it deals with data quality? Joe Celko describes... Read more...
SQL Server CASE Law
by Joe Celko | 03 March 2011 |  11 comments |
SQLs CASE expressions can be powerful magic, but can trap the unwary who are used to the more familiar CASE statements of procedural languages. Read more...
Look-up Tables in SQL
by Joe Celko | 01 February 2011 |  27 comments |
Lookup tables can be a force for good in a relational database. Whereas the 'One True Lookup Table' remains a classic of bad database design, an auxiliary table that holds static data, and is used to lookup values, still has powerful magic. Joe Celko explains.... Read more...
BIT of a Problem
by Joe Celko | 04 January 2011 |  13 comments |
The BIT data type is an awkward fit for a SQL database. It doesn't have just two values, and it can do unexpected things in expressions. What is worse, it is a flag rather than a predicate, and so its overuse, along with bit masks, is a prime candidate for being... Read more...
Contiguous Time Periods
by Joe Celko | 22 November 2010 |  8 comments |
It is always better, and more efficient, to maintain referential integrity by using constraints rather than triggers. Sometimes it is not at all obvious how to do this, and the history table, and other temporal data tables, presented problems for checking data that... Read more...
VALUES() and Long Parameter Lists - Part II
by Joe Celko | 28 October 2010 |  1 comment |
The use of the comma-separated list of parameters to a SQL routine, that Phil Factor calls the 'comedy-limited list, is a device that makes seasoned SQL Database developers wince. The best choice of design for passing variable numbers of parameters or tuples to SQL... Read more...
State Transition Constraints
by Joe Celko | 08 October 2010 |  8 comments |
Data Validation in a database is a lot more complex than seeing if a string parameter really is an integer. A commercial world is full of complex rules for sequences of procedures, of fixed or variable lifespans, Warranties, commercial offers and bids. All this... Read more...
The DIS-Information Principle, Part II
by Joe Celko | 01 September 2010 |  16 comments |
Database design simply involves populating a schema with tables that model sets of entities and relationships. A table will contain Columns that model an entity's attributes and contain scalar values. What could go wrong? Plenty, unfortunately, when these simple... Read more...
The DIS-Information Principle: A Splitting Headache
by Joe Celko | 17 August 2010 |  15 comments |
You can easily re-factor bad DML code, but if a database design is wrong, you can do little to rescue the problem, even with expert queries. So what constitutes 'wrong RDBMS design? What are these errors that continually crop up? How can you recognise them and fix... Read more...
VALUES() and Long Parameter Lists
by Joe Celko | 22 July 2010 |  15 comments |
To make progress as a relational Database programmer, you have to think in terms of sets, rather than lists, arrays or sequential data. Until that point, you'll feel the need to pass lists, arrays and the like to functions and procedures. Joe suggests some unusual... Read more...
Binary Trees in SQL
by Joe Celko | 22 June 2010 |  7 comments |
A number of hierarchies and networks are most convenently modelled as binary trees. So what is the best way of representing them in SQL? Joe discards the Nested Set solution in favour of surprisingly efficient solution based on the Binary Heap. Read more...
Book Review: Defensive Database Programming With SQL Server
by Joe Celko | 10 June 2010 |  1 comment |
It distils a great deal of practical experience; the writing of it was a considerable task; It packs in a great deal of information. Alex's book shows how to write robust database applications, and we can all learn from it. We took the book to a critic who never... Read more...
Celko's SQL Stumper: Eggs in one Basket
by Joe Celko | 29 March 2010 |  36 comments |
Joe Celko reveals the winner of his Easter Stumper: the puzzle of designing an apparently simple database to deal with the process of packing eggs into cartons. It wasn't quite as easy as it looked. Read more...
Procedural, Semi-Procedural and Declarative Programing Part II
by Joe Celko | 02 March 2010 |
SQL Server accommodates a whole range of programming styles and will even allow you to create code that is wholly procedural. Is a declarative approach inevitably better? Can it be difficult to maintain? Can you avoid the performance problems of procedural code by... Read more...
Procedural, Semi-Procedural and Declarative Programming in SQL
by Joe Celko | 15 February 2010 |  1 comment |
A lot of the time, the key to making SQL databases perform well is to take a break from the keyboard and rethink the way of approaching the problem; and rethinking in terms of a set-based declarative approach. Joe takes a simple discussion abut a problem with a UDF... Read more...
Celko's SQL Stumper: The Class Scheduling Problem
by Joe Celko | 19 January 2010 |  19 comments |
What can we use in SQL instead of E. F. Codd's T theta operators for best-fit? Joe Celko returns with another puzzle that isn't new, in fact it already features “Swedish”, “Croatian” and “Colombian” solutions in chapter 17 of Joe's 'SQL for Smarties' book. These... Read more...
Celko's SQL Stumper: The Data Warehouse Problem
by Joe Celko | 25 September 2009 |  19 comments |
Joe Celko comes back with a puzzle that isn't new, but one where the answer he originally gave now seems archaic: It is a deceptively simple problem, but is it true that the new features of SQL have simplified the solution? We leave it to the readers to provide the... Read more...
Causation, Correlation and Crackpots
by Joe Celko | 15 September 2009 |  8 comments |
Joe Celko explores the dangers of muddling correlation and causation, emphasises the importance of determining how likely it is that a correlation has occurred by chance, and gets stuck into calculating correlation coefficients in SQL. Along the way, Joe illustrates... Read more...
Getting rid of SQL Code
by Joe Celko | 20 August 2009 |  32 comments |
Joe becomes intrigued by the way that experts make errors in any area of technology, and suggests that the problem is more that of mindsets than lack of knowledge. He illustrates the point with SQL Development by means of the "Britney Spears, Automobiles and Squids"... Read more...
Celko's Summer SQL Stumpers: Prime Numbers
by Joe Celko | 23 July 2009 |  43 comments |
Joe Celko kicks off our series of Summer SQL Stumpers with a challenge to improve on his solution to calculating the prime numbers between 1 and 10000. Once the various solutions have been contributed and judged, the winner will be announced. The competition will be... Read more...
Avoiding the EAV of Destruction
by Joe Celko | 18 June 2009 |  8 comments |
A forum posting, from someone who wanted a better solution to the common problem of handling global settings in a database, leads Joe Celko into a fascinating discussion of the ramifications of the various solutions. Read more...
SQL and the Snare of Three-Valued Logic
by Joe Celko | 07 May 2009 |  10 comments |
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... Read more...
Median Workbench
by Joe Celko | 05 April 2009 |  12 comments |
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... Read more...
Divided We Stand: The SQL of Relational Division
by Joe Celko | 17 February 2009 |  7 comments |
Businesses often require reports that require more than the classic set operators. Surprisingly, a business requirement can often be expressed neatly in terms of the DIVISION relationship operator: How can this be done with SQL Server? Joe Celko opens up the 'Manga... Read more...
Temporal Data Techniques in SQL
by Joe Celko | 18 January 2009 |  7 comments |
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... Read more...
Temporal Datatypes in SQL Server
by Joe Celko | 16 December 2008 |  44 comments |
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... Read more...
Unique Experiences!
by Joe Celko | 18 November 2008 |  17 comments |
You'd have thought that a unique constraint was an easy concept - Not a bit of it; it can cause a lot of subtle problems in database designs. Joe Celko goes over the ground of unique keys, primary Keys, foreign keys and constraints. Read more...
Constraint Yourself!
by Joe Celko | 26 October 2008 |  19 comments |
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... Read more...
Over 400,000 Microsoft professionals subscribe to the Simple-Talk technical journal. Join today, it's fast, simple, free and secure.

Join Simple Talk


Privacy policy | Terms and conditions | ©2005-2012 Red Gate Software
About | Site map | Become an author | Newsletters | Contact us