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

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'

T-SQL: Create All Backups

First and foremost, always have a backup. Check out CreateBackupAll.sql to create backups and maintenance jobs for all user databases. Modify the settings for transaction log backups, re-indexing and database consistency checks.

If you use the index rebuild, you will need the sp_rebuild script. Procedure sp_rebuild uses dbcc dbreindex to re-index the clustered index, which takes care of all supporting indices. This may not be the best option for all of your databases. There are a few re-indexing options. With SQL 2005 Microsoft is suggesting alter index rebuild over defragindex and dbreindex.