Category Archives: 70-461
Delimiting identifiers in sql server
There are two means of delimiting identifiers in T-SQL:
1) Using Square Brackets e.g. [MyColumn] this is proprietary to T-SQL
2) The ANSI standard way using double quotes, “MyColumn”
But delimiting is only required when an identifier does not conform to the rules for formatting identifiers. These rules are that the first character must be an upper or lower case letter, underscore, @ sign or number sign. The identifier cannot be a reserved word, no embedded spaces and no supplementary characters. If the identifier does not meet these requirements it must be delimited.
WHY YOU SHOULD NOT USE ‘SELECT *’
There are several reasons not to use SELECT * in your code, here are just some:
1) Often you only need a subset of attributes so it generates more network traffic then required
2) Does not avail of covering indexes in the table if they are present
3) If the table definition changes you could end up with more columns than requested when you originally wrote the query which could have unforeseen results.
Aristotle in the database
If databases truly followed Aristotle’s Law of Excluded Middle (http://plato.stanford.edu/entries/contradiction/) then we would not have to contend with the joys of NULL and it would adhere strictly to two-valued logic. Interestingly Ted Codd wanted to be able to denote missing values in the relational model. To do this he referred to two kinds of missing values: missing but inapplicable and missing but applicable. An example of missing but applicable being a patient who has private health care and does not want to, for privacy reasons, provide their number. A patient who simply doesn’t have a private health number being an example of missing but inapplicable. Querying in SQL the column that contains a patients private health insurance number, one can conclude without doubt that they have private health insurance if the column is populated. However if it is NULL, one cannot determine if the patient does not have private health insurance or if they are merely withholding it. A variation on Keats “Known unknown”. And so this leads to three-valued predicate logic in the database: TRUE, FALSE and UNKNOWN, It is the handling of the unknowns that presents the real challenges in T-SQL…
Ode to Georg Cantor
Delving into Exam 70-461, the following quote from Georg Cantor is lain out at the very beginning to show the genesis of set theory and by extension SQL. Georg Cantor was the creator of mathematical set theory and defined a set as follows:
By a set we mean any collection M into a whole of definite, distinct objects m (which are called the elements of M) of our perception or of our thought.
There are more details on Cantor in this excellent documentary by Marcus De Sautoy: