Whitepaper : I Do Declare

Most of us began programming in procedural languages. But SQL is a declarative language. Chris Date described the difference as “What, not How” in one of his books. SQL has three sub-languages; the DDL or Data Definition Language, the DML or Data Manipulation Language and the DCL or Data Control Language.

The DCL is the sub-language where we control user and admin access to the database. It is a procedural language whose major commands are GRANT (give a privilege to a user) and REVOKE (remove a privilege from a user). SQL Server also has a DENY command that permanently blocks a user’s access to a database resource. This sub-language is the smallest and the one that nobody learns in school.

The DML is where you do the queries that return the data from the schema (SELECT) and statements that change the data (for users UPDATE, INSERT, DELETE, and for admin use ALTER, DROP, CREATE) in the schema. This is where we spend most of our time, both in the classroom and on the job.

The DDL gets some attention in the classroom, but most working programmers are not allowed to add or remove schema objects. This is an administrative power, as it should be. I have worked in a shop where everyone could do an ALTER TABLE and seen the chaos that an unpredictable schema creates. But most of the hard work in SQL should be done in the DDL.

This whitepaper is an attempt to fill the gaps in the SQL programming language commonly found in the education of working programmers. The whitepaper describes data types, exact numeric data types, heuristics for exact numeric data, approximate numeric data types, other numeric types, character data types, problems with strings, problems of string equality and ordering, heuristics for strings, the default clause temporal data, not NULL, CHECK(), the references specification, referential actions, nested unique constraints, overlapping keys, and heuristics.