I am sorry that it has been several days since I posted!  I have neglected my Platypus blog for studying for 70-461 and other parts of life.  I will be more dedicated and try to publish Monday, Wednesday and Friday.  I think that is an attainable publishing schedule.  If I have something important to share I will, but will try to shoot for that!

So, on to today’s topic.

Sometimes it is the subtleties that make for the biggest “aha” moments.  In an earlier post, I had some “aha”s in understanding some of the concepts of databases and set theory.  Besides those “aha”s, I have had at least one in each chapter of the testing guide for 70-461.  Just goes to show that no matter what you think you know there is always more out there to learn.  I hope that you find at least one new thing that you didn’t know either!

Chapter 1: Query Processing Sequence

The first thing I learned was the Logical processing of the SQL Statement.  I knew that it was written as:

SELECT…FROM…WHERE…GROUP BY…HAVING…ORDER BY

but I had never really given much thought into how the database itself processes that command.

Once I read it I realized that it made sense to do it this way:

FROM…WHERE…GROUP BY…HAVING…SELECT…ORDER BY

which IS the way an SQL query statement is evaluated.

I had always known that you can’t refer to a field in the WHERE clause by an alias given in the SELECT clause but didn’t know WHY that was true.  It is because when the WHERE is being evaluated the SELECT has not yet been evaluated so the alias is unknown to the WHERE clause.  However, the ORDER BY clause is processed AFTER the SELECT and it CAN use the aliases.  Makes total sense now!

Chapter 2: Auto-Generating Keys

I ALWAYS use an Identity property on a field in all my tables to create an auto-generating unique key.  It doesn’t matter if there is some other field that NEVER contains a duplicate.  LIAR!  At some point in time, in some universe, in a future far, far away, someone WILL duplicate that data.  You won’t be there to see it, but it will cause HAVOC in someone’s life.  In order to prevent that, just add your own unique key to all your tables.

That obviously isn’t what I learned in Chapter 2, I have been doing that for years.  What I did learn though is that there are OTHER types of auto-generating keys!

The Sequence Object – This is like a global auto-generating key!

GUIDs – I have used Global Unique Identifiers (GUIDs) just minimally in my programming and NEVER knew that you could use them in databases too!  You can generate sequential or non-sequential GUIDs and they are guarantee to be unique across space and time (and maybe even other dimensions!!).  There are two SQL functions that can create GUIDs – NEWID and NEWSEQUENTIALID.

Another useful tip I found in Chapter 2 had to do with starting at the lowest possible number if you are going to be using sequential numeric keys.  I always started at 1.  I don’t know why, it just seemed logical.  It is the first record in the table, it should be number one!  However, Chapter 2 points out that if I am using an INT datatype (which I almost always do), I could start at -2,147,48,648 and double my available pool of IDs.  I had never thought of that!

Case Expression – I was chastised in this chapter.  I, like many, have always refered to it as a CASE statement.  That is apparently the incorrect terminology.  It is an expression since it returns a value.  I also learned there are two ways to write the expression.  There is the simple form and the searched form.

I have always used the searched form.

CASE WHEN IsActive = '0' THEN 'No' WHEN IsActive = '1' THEN 'Yes' ELSE 'Unknown' END

And while technically that is correct and works, it could be written using the the simple form:

CASE IsActive WHEN 0 THEN 'No' WHEN 1 THEN 'Yes' ELSE 'Unknown' END

I had never seen this structure for a CASE Expression.  It is used when you want to take a simple input and compare it.  In this case, the field IsActive can either be zero or 1 and in our query we want to show ‘Yes’ for 1 and ‘No’ for 0 (zero) and ‘Unknown’ if it is anything else.  I had the opportunity to use this structure the very next day!

The searched form can be used to decide output based on predicate logic:
CASE WHEN Price > 100 THEN 'Pricey' WHEN Price < 5 THEN 'Cheapy' ELSE 'Just Right' END

The book doesn’t say if there is a difference in the database to process the two different forms. I will have to go research that and will let you know what I find.

70-461 Status

I am currently reading Chapter 8 which puts me about 1/2 way through the book. I am feeling really good about my existing ablities and knowledge and am still learning more and more! I hope to schedule the test at the end of May!