Random Number Generator

Method 1 : Generate Random Float Numbers
SELECT RAND( (DATEPART(mm, GETDATE()) * 100000 )
+ (DATEPART(ss, GETDATE()) * 1000 )
+ DATEPART(ms, GETDATE()) )


Method 2 : Random Numbers Quick Scripts
---- random float from 0 up to 20 - [0, 20)
SELECT 20*RAND()
-- random float from 10 up to 30 - [10, 30)
SELECT 10 + (30-10)*RAND()
--random integer BETWEEN 0
AND 20 - [0, 20]
SELECT CONVERT(INT, (20+1)*RAND())
----random integer BETWEEN 10
AND 30 - [10, 30]
SELECT 10 + CONVERT(INT, (30-10+1)*RAND())


Method 3 : Generate Random Numbers (Int) between Range
---- 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 -1) * RAND() + @Lower), 0)
SELECT @Random


Method 4 : Random Numbers (Float, Int) Tables Based with Time
DECLARE @t TABLE( randnum float )
DECLARE @cnt INT; SET @cnt = 0
WHILE @cnt <=10000
BEGIN
SET @cnt = @cnt + 1
INSERT INTO @t
SELECT RAND( (DATEPART(mm, GETDATE()) * 100000 )
+ (DATEPART(ss, GETDATE()) * 1000 )
+ DATEPART(ms, GETDATE()) )
END
SELECT randnum, COUNT(*)
FROM @t
GROUP BY randnum


Method 5 : Random number on a per row basis
---- The distribution is pretty good however there are the occasional peaks.
---- If you want to change the range of values just change the 1000 to the maximum value you want.
---- Use this as the source of a report server report and chart the results to see the distribution
SELECT randomNumber, COUNT(1) countOfRandomNumber
FROM (
SELECT ABS(CAST(NEWID() AS binary(6)) %1000) + 1 randomNumber
FROM sysobjects) sample
GROUP BY randomNumber
ORDER BY randomNumber

Finding Log Size for all Databases in SQL Server

Monitoring the size of Transaction Log files is one of the important tasks for a SQL Server DBA. I regularly monitor my database log files that it do not grow tremendously in size and potentially run out of space. Below script will give the list of Databases and their Transaction Log files size in MB in the descending order.



SELECT INSTANCE_NAME AS [Database],
(CNTR_VALUE/1000) AS Size_In_MB FROM MASTER.dbo.SYSPERFINFO
WHERE COUNTER_NAME LIKE '%Log File(s) Size (KB)%'
AND INSTANCE_NAME NOT IN ('_TOTAL','mssqlsystemresuorce')
ORDER BY Size_In_MB DESC

Finding Databases which had Transaction Log Growth

The following T-SQL script will give the list of the databases and how many times the log file grew by using the auto-grow value. This information is available since the last restart of the SQL Instance and will be reset again when the Instance is restarted. This list will not count if you grow the log file manually.



SELECT INSTANCE_NAME,cntr_value FROM MASTER.dbo.sysperfinfo
WHERE COUNTER_NAME = 'Log Growths'
AND INSTANCE_NAME NOT IN ('_Total','mssqlsystemresource')
AND CNTR_VALUE <> 0

Conversion from Hex String to VarBinary and vice versa

-- Conversion from hex string to varbinary: 


DECLARE @hexstring VarChar(MAX);
SET @hexstring = 'abcedf012439';
SELECT  CAST('' AS XML).Value('xs:hexBinary( substring(sql:variable("@hexstring"), sql:column("t.pos")) )', 'varbinary(max)')
FROM (SELECT CASE SubString(@hexstring, 1, 2) WHEN '0x' THEN 3 ELSE 0 END) AS t(pos)
GO


-- Conversion from varbinary to hex string: 


DECLARE @hexbin VarBinary(MAX); 
SET @hexbin = 0xabcedf012439; 
SELECT '0x' + CAST('' AS XML).Value('xs:hexBinary(sql:variable("@hexbin") )', 'varchar(max)'); 
GO

Get number of days in a month using SQL

Use below code to get the number of days in a month, given Month and year.

DECLARE @MonthNumber AS VarChar(3), @YearNumber AS VarChar(4)
SET @MonthNumber = 'Feb'
SET @YearNumber = '2009'


SELECT datediff(day,CAST(('01-' + @MonthNumber + '-' + @YearNumber) AS DateTime),dateadd(day,-1,dateadd(month,1,CAST(('01-' + @MonthNumber + '-' + @YearNumber) AS DateTime)))) + 1