Simple SQL: Optional Parameters, Static Statements

Dynamic SQL, as wonderful a thing as it is, is horrible for security and maintenance. Your best bet is to avoid it. People often whine about making parameters optional. If you set the parameters that you are not using to null, this is a simple thing to do:

declare
@state char(02),
@zip char(05)

set @state = 'CA'
set @zip = null

select
Address
from
CustomerAddress
where
State = coalesce(@state, State)
and Zip = coalesce(@zip, Zip)

Simple and elegant.

Tags: sql

Null Understanding

Defining Null

Contrary to the first sentence of the Null SQL entry on Wikipedia, null does not mean 'no value' (the page on Three Valued Logic (3VL) gets it right). If it it did, 'select * from Address where Zip = NULL' would return the same results as 'select * from Address where Zip is null'. Strictly speaking, when a value is null it means that there may be a value or there may not be a value; we do not know the value. Null is the unknown-value-placeholder.

To Null, or Not to Null

Jihad! Nulls create a nice bifurcation in the database world. Basically, if you think nulls are bad, you get lumped in with the theorists. If you think nulls are good, well, a programmer or a practitioner (it gets a little muddy--null?). The truth is, null values and logic do have their place. It may not be a comfortable place (and not somewhere I want to go), but they have come to be a necessity.

Nulls are handled differently than known values. Consequently, the presence of null values often creates complexity. If the value is missing, we sometimes want to include it in our set, and sometimes not... Isn't that a key point to 3VL?

The important point is to use nulls as they were intended; to reflect a value in an unknown state. Do not use the null for non-values or missing values--that serves to perpetuate the misunderstanding.

Personally, I am on C.J. Date's side--don't use them. Even so, there are times when a design is conducive to null logic.

Codd later proposed a Four Value Logic for the Relational Model. By that time (and in part because), nulls were being misused by the practitioners. I wonder if the 4VL had survived if we would still misuse nulls or if the whole debate would be null and void...




Tags: null, sql

PL/SQL: Trapping NO_DATA_FOUND

Doing a lookup in a procedure and don't want it to die with a NO_DATA_FOUND error? You could trick it into returning a null by using an aggregation function, but that may add I/O's. You could write your own function to handle it, but isn't that a bit kludgey? Wrap the offending statement in its own block with an exception handler:

if(iLOGIN_UID = 0 or iLOGIN_UID is null) then
begin
   select
       LOGIN_UID
   into
       oReturn
   from
       LOGIN
   where
       GUID = iGUID;

   exception when NO_DATA_FOUND then oReturn := null;
end;
else
   oReturn := iLOGIN_UID;
end if;

Tags: plsql, oracle

T-SQL: Random Data Generation

-- Retrieve a random row
select top 1
   *
from
   Employee
order by
   newid()

This uses the newid() function which generates a random hex string.

For the rest of the random data, we will be using the rand() function.

Please note that you may want to use a seed with rand(). Check Books Online for a standard example of using getdate() to accomplish this.

We typically want whole numbers from rand(), constrained to a range.

We accomplish by using the following formula:

(max - min - 1) * rand() + 1

Using this formula and some functions, we can easily generate numbers, strings and dates:

-- Retrieve a whole number between @min and @max
select convert(int, ((@max - @min - 1) * rand() + 1), 0)

-- Retrieve a string of x characters (10)
declare @str varchar(10)

set @str = ''

while(len(@str) <>
begin
   -- Select a number from 1 - 26,
   --    add 64 (ascii offset), and convert to a character
   set @str = @str
       + char(64 + convert(int, ((26 - 1 - 1) * rand() + 1), 0))
end

print @str

-- Retrieve a date between now and (now - x) for x of 99 days
select dateadd(d, convert(int, -1 * ((99 - 1 - 1)
   * rand() + 1), 0), getdate())

Tom Kyte is On the Ball

Yesterday, Tom Kyte posted about 'Worst Practices.' Today, he follows up with three videos about pathetic performance--worth watching.

And the really exciting part? For the first time in months, you can actually Ask Tom!

Why Programmers Don’t Like Relational Databases

This article was written a few months ago, but I just happened upon it. It makes some very good points about why many programmers view DBA's as evil.

Formatting

Formatting your code is just good practice. It matters less how you format your code than having a consistent appearance. I am using ‘formatting’ pretty loosely by including some general coding practices.

Recognizing that formatting SQL is largely preference, here is how I like it and why.


1. Keywords should be lowercase.

Many people go with uppercase; I find it distracting.

SELECT * FROM Widgets WHERE Quantity >= 20;

Looking at the above statement, we see ‘SELECT FROM WHERE!’ The heart of it is dwarfed by the keywords.


2. Use whitespace.

In simple examples, this seems tedious, but the point is that we demarcate the sections; we can easily see where each part of the statement is and what belongs to it.

select
    *
from
    Widget
where
    Quantity >= 20; 

Simple statements, we can overlook this. In a procedure, putting a singleton select or any obvious statement on one line with a comment immediately above it is fine as long as the statement is not overly complex.

-- Get the widget name
select Widget into xWidget from Widget where WidgetID = 168; 

I am old school, so I miss table joins in the where clause; the reason being formatting. The formatting looks awkward with joins in the from clause since there is no good way to get all of the pieces to line up.

Example 1a:

select
    a.x,
    a.y,
    b.c
from
    a
    inner join b
        on b.a = a.a
where
    a.c = 1; 

Example 1b:

select
    a.x,
    a.y,
    b.c
from
    a,
    b
where
    b.a = a.a

    and a.c = 1; 

Depending on what tools you are using, you may have some whitespace sensitivity issues (which really makes me angry). But the second statement reads easier to me. I immediately see that there are two tables, and one join.

The first statement is a bit cumbersome. I see the inner join and the on, so I assume that they are joined properly. It is really just the formatting that I do not care for. You could go with:

from
    a inner join
    b
        on b.a = a.a

The annoyance being that as soon as you alias your tables, it decreases the readability. The nice part of joining in the from clause is that it reduces the likelihood of Cartesian products. I am not condoning performing joins in the where clause, just mentioning that I prefer everything simpler (lines up better that way).


3. Alias tables

Meaningful tablenames segue into easy aliases. I can see using ‘a,’ ‘b’ and ‘c’ in examples, but in the real world they make it more complicated than not aliasing. You do not arbitrarily name tables—treat aliases the same.

select
    m.Manufacturer,
    p.Product + ‘ ‘ + p.Size Product,
    q.Quantity
from
    Manufacturer m,
    Product p,
    Quantity q
where
    p.ManufacturerID = m.ManufacturerID
    and q.ProductID = q.ProductID;

4. Use comments 

By and large, I think that code is self-documenting. I prefer block comments (‘/* … */’) for ideas, or logical separations. I use inline comments (‘-- …’) to describe a statement. I comments statements only if they do not read easily. If you are nesting a few functions, or a non-intuitive clause, comment it. I consider conditional operations a logical idea and typically comment them.

-- Number of whole weeks times five days each
oDay := ((floor((xEnd - xStart) / 7)) * 5);

-- Add difference in days outside of whole weeks
oDay := oDay + mod((xEnd - xStart), 7);

5. Use Blocks

Parentheses and begin/end allow you to show your intentions. Much like whitespace, it delineates your ideas, and is often syntactically required.

At a minimum, anytime I use an OR clause, I put parentheses around my idea to show that the condition was deliberate.


I believe that those are my top preferences. Take the ACID properties beyond transactions. What are your thoughts?

Cartesian Products

When a report monkey asks why they are receiving an exponentially greater resultset than expected, we are often presented the opportunity to say, 'Cartesian product.' It is a wonderful phrase. I mean, if you can sling words like that, you must be good at your job. But making complex ideas sound simple is what really shows understanding. So when they follow up with, ‘Is that bad?’ respond with, 'You probably forgot a join.'

So what is a Cartesian product? It is a set of all possible sets. Let’s simplify this and deal with two source sets. Table Product and table Quantity. Note the compound keys. It is easier to avoid Cartesians using surrogate keys.


Table Product

Manufacturer	Product	Size
Cogswell	Cog	5
Cogswell	Sprocket	1
Omicron	Widget	3
Majobber	Thingey	1

Table Quantity

Manufacturer	Product	Quantity
Cogswell	Cog	264
Omicron	Widget	17

Prior to SQL-92, tables were joined in the where clause. While it keeps the from clause cleaner, it does make it easier to forget joins on more complex queries.

Cartesian Statement:

select
 p.Product,
 q.Quantity
from
 Product p,
 Quantity q;

Resulting Cartesian Product:

Product	Quantity
Cog	264
Cog	17
Sprocket	264
Sprocket	17
Widget	264
Widget	17
Thingey	264
Thingey	17

Since we did not specify how the tables relate to each other, we are relating one set to another set. Meaning, each row in one set is equal to every row in the other set; a Cartesian product.

Specifying joins in the from clause does not eliminate Cartesian products. It may even make them more difficult to spot.

Incorrect Quantity by Manufacturer:

select
 Manufacturer,
 sum(Quantity) Quantity
from
 Product  p
 inner join Quantity q
  on q.Manufacturer = p.Manufacturer;

Manufacturer	Quantity
Cogswell	528
Omicron	17

This is not a Cartesian product. Perhaps ‘Cartesian byproduct’ works, or ‘nested Cartesian?’ The query will equate every manufacturer record in Product to every manufacturer record in Quantity.

In any event, it appears to work because some (ok, in this case just one) of the answers are correct; Omicron does have a total quantity of 17. Cogswell however is wrong. Both product lines for Cogswell were married to one Quantity record, thereby inflating the number. The point is, don’t label every malformed query a Cartesian product; you never know when some math major will give you a verbal beatdown.

Another way of looking at Cartesians is considering them many-to-many joins. If your intent is a Cartesian product, use CROSS JOIN. In my experience, I have found one time that I actually wanted to use a Cartesian product… and I wanted to use it. Ultimately, it was not the correct approach. Not that I have never created Cartesians, just that they have never been intentional.

Cardinality

Need to know how many rows are in your tables?

• T-SQL - The Transact-SQL statement uses sysindexes

• PL/SQL - The PL/SQL block requires a schema name and does an actual count against the table. If you have current statistics (or permissions to update them), you can just do 'select table_name, num_rows from all_tables'