ExpressProfiler for SQL server

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,

Generate random testdata with T-SQL

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)
   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)
   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) 
   3: BEGIN
   5:  declare @days_between int
   6:  declare @days_rand int
   8:  set @days_between = datediff(day,@fromDate,@toDate)
   9:  set @days_rand  = dbo.RandomInt(1, 10000)  % @days_between
  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

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
   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'))   
  20:     Set @counter = @counter + 1
  21: End


Hope this helps,

Simon Sinek: How great leaders inspire action

Simon Sinek explains his Golden Circle theory: start with why. “People don't buy what you do, they buy why you do it!”. He explains why Apple is successful in selling computer products, while DELL does essentially does the same thing. He explains why the Wright brothers were the first to build a human flying machine. He explains why Dr Martin Luther King had such a large audience.

Great way to spend 20 minutes of your weekend, watch this video:

Just optimized my site

Today I was trying to see if I can get a 100% score with Google Page speed. I started with a score of 65 out of 100. Now it’s time to quit, I achieved a score of 98%.

Most important changes to upgrade my score:

  • Configured expire headers and GZIP compression in IIS
  • Minified the Javascripts and CSS stylesheets
  • Moved static images to CSS as embedded base64 (used tool: web essentials extension). So less http requests!
  • Put javascript at the bottom of html as much as possible
  • Made favicon cacheable (
  • Used a HttpModule for cleaning up HTML (no comments, spaces, etc) A downloaded PageOptimiser source code and extended it to my needs.
  • Added a Google Analytics handler for a far-future expire date. GA Trick is described by Mads Kristensen.
  • Used Google CDN for jQuery and CDNJS