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 members of the set, and it should be possible to validate and verify them. Celko somehow involves watches and taxi cabs to illustrate the point.
… Read more
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 manipulating dates and times.
… Read more
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 how the SQL Standard could soon evolve to deal with data in ways that allow aggregation and windowing in cases where the data quality is less than perfect
… Read more
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
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 listed as a 'SQL Code Smell'. Joe Celko makes the case.
… Read more
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 were difficult to solve with constraints. Suddenly, Alex Kuznetsov came up with a good solution, and so now history tables can benefit from more effective integrity checking. Joe explains...
… Read more
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 Routines varies according to the importance to you of SQL Standards. Joe Celko discusses the pros and cons of the front-runners
… Read more
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 requires considerable subtlety to prevent bad data getting in, and if it does, locating and fixing the problem. Joe Celko shows how useful a State transition graph can be, and how essential it can become with the time aspect added.… Read more
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 principles are misunderstood or flouted, and Joe continues to itemise bad design practices that can cause subsequent grief for the application developers.… Read more
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 ways around the difficulty and broods on the power of the VALUES constructor.… Read more
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 using triggers? Joe adds some thoughts.… Read more
Joe Celko, the Database Developer and writer from Austin Texas, is not a man to mince his words. His encyclopedic grasp of SQL and relational Databases in general comes from a mix of academic knowledge and practical experience. In discussions he can be fascinating, cantankerous, amusing and satirical, but he is never ever dull, as Richard Morris found out when we sent him to interview the SQL language's most famous advocate.… Read more
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
Compliant Database DevOps
