Wolfram Fail

I have been looking forward to Wolfram Alpha going live. I am no longer so impressed. After trying to find how many state employees California has, to no avail, I asked a simpler question. It looks like, contrary to the hype, Google is safe for a while...

Flipping Awesome

PressFlip is a learning recommendation service... It is smart. And cool--so cool it destroyed my thesaurus. Text analytics is heading for huge, so check out a very slick implementation. Read Ted's unofficial press release. That guy can write... I missed his style.

Tags: text analytics

Deifying Logic

I stumbled upon a very cool diagram. It illustrates, literally (err... figuratively? (seriously, why is 'literally' so often misused?)) "16 permutations formed by affirming or denying two propositions, A and B." A strong understand of basic logic helps us with all facets of life, not just optimizing our query skills.

Renewed Love

I just read an article on Database Debunkings and was reminded of how great a resource that site is. There are some great articles and fun reading.

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())

PL/SQL: Random Data Generation

I recently created a script to generate random data. I did not want random strings for names, so I downloaded name lists from the U.S. Census names file. The data is all uppercase, I preferred to go with sentence case. An easy update statement later and I had names.

In order to randomly retrieve a row:

select
        NameLast
into
        xNameLast
from
        (
                select
                        NameLast
                from
                        NameLast
                order by
                        dbms_random.value
        )
where
        rownum = 1;

Why does this work? We are limiting the resultset to one row by using 'where rownum = 1'. That row is being randomized by the derived statement's ORDER BY clause referencing a random value... Which I do not understand. ORDER BY needs to reference columns, which in this case would always be one. Yet it does randomly select a row. So it must be sorting it based on a surrogate column or some such. I would ask Tom, but he is busy now.

Random names add some authenticity to the data set and just makes for good reading; Charity Certain and Refugio Cheeseman are two of my favorites.

I thought about loading a dictionary file so that I could randomly generate phrases... Selecting a random number of random words would be an easy enhancement.

In any event, feeding your procedures with random data is easy to do with PL/SQL using the DBMS_RANDOM package. The following statements outline how to generate most values.

-- Retrieve a whole number between x and y
select trunc(dbms_random.value( x, y)) from dual;

-- Retrieve a string of x characters
select dbms_random.string('A', x) from dual;

-- Retrieve a date between now and (now – x)
select sysdate - (trunc(dbms_random.value(0, x))) from dual;