Friday, February 12, 2010

New Data Types in SQL Server 2008

http://www.simple-talk.com/sql/learn-sql-server/sql-server-2008-the-new-data-types/

Great post for reference.

Selecting a random sample

If you are interested in querying for a random X number of records, or TOP Y percent of records, you can use the following syntax:


SELECT TOP 5
        *
FROM    YourTable
ORDER BY NEWID()



SELECT TOP 50 PERCENT
        *
FROM    YourTable
ORDER BY NEWID()

Random Number Generator

---- Create the variables for the random number generation
DECLARE @Random INT ;
DECLARE @Upper INT ;
DECLARE @Lower INT

---- This will create a random number between 1 and 999
SET @Lower = 1
---- The lowest random number
SET @Upper = 999
---- The highest random number
SELECT  @Random = ROUND(( ( @Upper - @Lower ) * RAND() + @Lower ), 0)
SELECT  @Random

Thursday, February 11, 2010

Checking if a table exists before dropping it

I often see folks create "temporary" tables that are not true #temp tables - with the intent of refreshing the entire table's contents.  This could be for data warehousing purposes or to simply have a table of aggregated data in your OLTP system for reporting purposes.  To ensure the table is not there before you execute your SELECT INTO statement, use the following syntax:

IF OBJECT_ID('YourTempTable') IS NOT NULL
     DROP TABLE YourTempTable

-- then populate the table with your data
SELECT * INTO YourTempTable FROM...

Pretty straight forward.