Happy holidays!

var weather   = 'frightful';
var fire      = 'delightful';
var placeToGo = false;

for (var i = 0; i < 3; i++) {
    letItSnow();
}

console.log("Happy Holidays to you all!!");
 

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.

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)
   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:

GB_Screenshot_029

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,

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: