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:

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 (http://www.guusbeltman.nl/FavIcon.ashx)
  • 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

Tech Days 2013 NL – Day 2

I started the 2nd day of the Microsoft Tech Days with a level 400 session.

Hugo Kornelis – T-SQL User-defined functions

Hugo showed us why T-SQL User defined functions can be catastrophic for our database performance by demonstration it with live examples (he had 7 slides in his presentation deck!). With the statement:

SET STATISTICS IO ON;

You can see how many read/writes each table receives and how the SQL optimizer does clever things behind the scenes. Example: removing joined tables from the execution when it isn’t part/effects of the result.

Facts that I remembered
  • Query plan must be read from right to left (already knew that, but not everybody does).
  • User-defined functions are a black box for the SQL optimizer. This can affect the actual query plan.
  • Estimated query plan doesn’t show the actual reads/writes when UDF is involved
  • Table valued user defined function returns a table are better for the optimizer (no scalar value), but doesn’t solve the duplicate code issue.
  • Inline table valued user defined functions delivers statistics to the SQL optimizer. From a conceptional point of view this is actually a parameterized view (=macro). This delivers the performance you need, but can be hard to write. You need statements with CROSS APPLY.
  • Not actually part of the topic: COALESCE(..expression..) is the better version of the ISNULL(..expression...) function.
Relevant links

Mads Kristensen – Creating Visual Studio Extensions

Bart de Smet session was cancelled, so this session was a last-minute addition to the conference. There were a few reasons to go here:

  1. There are not a lot of session about this topic (and thorough documentation is not available).
  2. Mads is the founding father of the Web Essentials extension, this session should be interesting.
Developing extensions

He showed that almost everything is possible to extend. It’s only sometimes difficult to find out ‘how’. Creating extensions is mostly done by ‘Search Driven Development’. So use Google to find the info you need to complete your code. One of the reasons is the use of magic strings that are used for hooks in VS.

For extensions you need the Microsoft Visual Studio 2012 SDK. After you installed the VS2012 SDK you get extra extensibility project templates. Most of the times the ‘package’ template is best.

Testing and debugging

To test and debug your extension you just can press F5 then the Experimental Instance of Visual Studio will be launched as a testing container. This special version of VS has it’s own registry settings, so it doesn’t conflict with your normal version.

The VSIX package

A .vsix is just a zip file, so you can rename it and view the contents. Strangest thing is that VS doesn’t compress the package. So if you like to release a smaller package you best can: 

  1. Rename to .ZIP
  2. Extract the files
  3. Rezip the files
  4. Rename to .VSIX

Note to self: try this stuff at home!

Relevant links

 

Alan Smith – Web Site Authentication with Social Indentity Providers

The session from Alan Smith was mainly about ACS: Azure Control Service. Microsoft is supporting multiple identity providers: Google, Yahoo, Facebook and LiveID accounts. All providers supports only the name identifier claim (which is unique and immutable). Only Google and Yahoo also deliver the name and email address as extra claims.

Steps within the ACS portal:

  1. Register the application (should match the realm)
  2. Define the rule and claims
  3. Add federation to the Asp.net project (think about setting the certificate validation to Off in the web.config of you development environment).
  4. Protect the resources with security settings in the web.config
Small notes

- Security token = set of claims + encryption (optional) + signed

- Within ACS it’s possible to define extra Role Claims.

- Relying party should trust the identity provider

Blog of the Alan Smiths

Cross Platform Mobile Application Development met Xamarin and T4

Presentation was given by three developers of Avanade. There were no real demo’s, only some code on slides. This is a bit disappointing, it’s a developer conference!

Things that I remembered
  • Targets on iOS, Android, WinRT (all phone and tablets). See screenshot example application.
  • Mvvm Cross goal = 80% reusable .NET code for all platforms
  • Makes use of Portable Class Library (1 assembly per platform)
  • Mvvm Cross delivers IoC, plugins, converters and more..
  • Uses T4 for generating code based on Views in XML
  • Mvvm Cross – Views are platform specific
  • Still need account for all stores (Windows Marketplace = 99 USD per year, Android Play Store = 25 USD per app, Apple iStore = 99 USD per year).
  • Xamarin is a commercial product. Single developer is 299 USD. See pricing for more.
  • Avanade created their on layer on top of Mvvm cross, called Tea Light.
  • Visual Studio has no coding assistence for T4. Avanade developers recommended Tangible T4 Editor. This is also a commercial product.

Tip: for testing on mobile devices can be done in the cloud by using PerfectoMobile.com

 

Jeff Prosise – HTML 5: The Good, the bad, and everything in between

HTML5small Jeff presentation was covering misc. HTML5 features with a lot of demo’s. He provided each HTML5 feature with a Grade (A to F) and explained us why with Pro’s and Con’s which was nice!

See the video, it says it all..

Relevant links