When I installed the free SQL Server Express edition one of the tooling I missed was SQL Server Profiler which comes with the paid version of SQL Server. Today I found a nice replacement: Express Profiler.
It’s simple but good enough for seeing what queries get fired to the database. And it’s small (a 140kb .exe file) and portable. So just put it on your DropBox in the <portable_apps> folder and you can use it everywhere!
Have fun,
Writing software is step one, testing it thoroughly is also very important. Sometimes you need to have some more test data in your database to see how well it performs when the dataset gets larger. Or you want to test how well your search solution is doing or paging is done within your application.
Of course you could create data by hand or create a little program to fire data insert statements to your database. But the fastest way is to create a T-SQL script. When generate test data, it’s always useful to create some randomness so that you can test better. Below I show some SQL functions that you can create and later utilize within CRUD statements.
Let’s start with a function that generates a random float. For randomness we first create a VIEW ‘RandomView’ that will helps us with our function:
1: CREATE VIEW RandomView
2: AS SELECT RAND() AS Val;
3: GO
The view returns a real random value. The random float function that uses the view can be create like this:
1: CREATE FUNCTION RandomFloat(@MinValue int, @MaxValue int)
2: RETURNS FLOAT AS
3: BEGIN
4: RETURN (SELECT ((@MaxValue - @MinValue) * Val) + @MinValue FROM RandomView);
5: END
6: GO
The create function can now easily be used in a select statement for generating a float between 1 and 100:
1: select dbo.RandomFloat(1, 100)
We could cast this to an int when needed, but make our life simple we could create a RandomInt like this:
1: CREATE FUNCTION RandomInt(@MinValue int, @MaxValue int)
2: RETURNS INT AS
3: BEGIN
4: RETURN Cast((SELECT ((@MaxValue - @MinValue) * Val) + @MinValue FROM RandomView) as int);
5: END
6: GO
Of course we could reuse the RandomFloat function here. But I would like to keep it standalone (apart from the RandomView.
Creating a random date is also not a built in feature of SQL server. We would like to create a function that accepts a MIN and an MAX date. And returns a date in between those limits. One way of doing this is:
1: CREATE FUNCTION RandomDate ( @fromDate date, @toDate date)
2: RETURNS DATE AS
3: BEGIN
4:
5: declare @days_between int
6: declare @days_rand int
7:
8: set @days_between = datediff(day,@fromDate,@toDate)
9: set @days_rand = dbo.RandomInt(1, 10000) % @days_between
10:
11: return dateadd( day, @days_rand, @fromDate )
12: END
13: GO
Now you can generate a random date between 1 october 2013 and 23 october 2013 with:
1: select dbo.RandomDate('10/1/2013', '10/23/2013')
For testing these functions we could do a ten-row test run with:
1: select top 10 ROW_NUMBER() over (order by TABLE_NAME) ROW
2: , dbo.RandomInt(1, 5) as RandomInt
3: , dbo.RandomFloat(100, 200) as RandomFloat
4: , dbo.RandomDate('10/1/2013', '10/23/2013') as RandomDate
5: , RAND() as RandomBuiltin
6: from INFORMATION_SCHEMA.TABLES
The output will look something like this:
So that’s about it for generating random data. The last thing I would like to share with you is how to insert 100 rows into a table with random data. It’s easy, but can save some time reinventing the wheel:
1: Declare @counter int
2: Set @counter = 1
3:
4: While @counter < 100
5: Begin
6: INSERT INTO MyTable
7: ([GUID]
8: ,[ExternalUserId]
9: ,[DateResponse]
10: ,[AverageRating]
11: ,[CommentText]
12: ,[DateCreated])
13: VALUES
14: (NEWID()
15: ,CAST(dbo.RandomInt(1000, 500000) AS VARCHAR)
16: ,GETDATE(),dbo.RandomInt(1, 5)
17: ,'Random Comment ' + CAST(dbo.RandomInt(1000, 500000) AS VARCHAR)
18: ,dbo.RandomDate('9/1/2013', '9/30/2013'))
19:
20: Set @counter = @counter + 1
21: End
Hope this helps,