by Stephen Horsfield
3. March 2008 17:08
Overview
I've been looking at the performance of SQL Server 2008, following the recent SQL Bits conference (see http://www.sqlbits.com), and have been looking at creating some test data for my analysis. This is probably old stuff to most of you, but I hit an interesting issue...
Context
Specifically, I've been looking at the performance of the new filtered indexes support in 2008. If you haven't come across these, I recommend that you have a look but I'll probably post about it in the next few days (no promises, though!). To do the analysis I wanted a table with heavily weighted data so I thought I'd just generate it using the RAND() function. Only, the RAND() function didn't seem to give particularly random data.
Here's my original code:
SELECT TOP(1000)
RAND() AS [value]
FROM master.sys.objects a, master.sys.objects b
GO
What results do you expect? This is what I get, and I've checked the same occurs in SQL Server 2005:
0.863657983742771
0.863657983742771
0.863657983742771
0.863657983742771
0.863657983742771
0.863657983742771
0.863657983742771
...
So, the RAND() function was only executed once for the query. Not quite the random data I'd hoped for :(
Solution
The most efficient solution when generating many rows is to use a table-valued CLR function to generate the row data. For testing, I had two options:
- a temporary table
- a table variable
I cannot use the RAND() function within a table-valued user-defined function, so I had to use one of these approaches. I'll show the table variable solution but the temporary table solution is virtually identical. Here it is:
BEGIN
SET ROWCOUNT 0
DECLARE @randomNumbers TABLE (value float)
DECLARE @i int
SET @i = 0
WHILE @i < 1000
BEGIN
INSERT INTO @randomNumbers (value) SELECT RAND()
SET @i = @i + 1
END
SELECT * FROM @randomNumbers
END
GO
Of course you need to adjust this script for it to be useful :)
Versions
Metadata
Subscribe