Tuesday, July 30, 2013

Microsoft Test Manager

Today I was conducting a test on Effektor using Microsoft Test Manager. It's a simple tool that makes it easier to record findings in explorative tests. I also got to do a bit of T-SQL and learned that you can't SELECT on an EXEC command and have to jump through all kinds of hoops if you are unlucky enough. If you are lucky you can INSERT INTO some tmp table and then select from there, but I had an error because of an excisting rollback in the procedure.

Yesterday was the first day in the coursera course "Introduction to mathematical Philosophy" which was really interesting. I learned a bit about Zeno's paradox and Cantor's theorem, although I think I will have to watch that last one again. The course also reminded me of a few of the interesting things from Discrete Math, really recommendable. Course is here: https://class.coursera.org/mathphil-001/class/index

Last thing was something I relearned but will have to try and remember. When working in windows always use "alt+space" a lot, it saves the long trip to the upper right corner.

Monday, July 29, 2013

MS Test

Today Michael Nygaard showed me how to do a unit test for Effektor. Finally the amount of interfaces begin to make some sense :)

Friday, July 26, 2013

70-461 Exam

Today I passed the 70-461 Microsoft exam about SQL Server 2012. I realised that I know nothing about varbinary and filestream, so I spend some time reading up on it here:

http://msdn.microsoft.com/en-us/library/gg471497.aspx

It's apperanetly the way to go for storing files in sql server.

Monday, July 22, 2013

A proper running average in TSQL

Sometimes I am amazed at the lenghts I have gone to in TSQL to construct things that fit in a few lines, once you know how. Today I was reading about window functions. They are excellent for the kind of grouping where you want to keep each row, but also include some totals or other things that relate to the group that that row belongs to. Things like a sale and the sale total for that category of items.

Previously I would have made a subquery that found the category totals and the joined the two tables, but using a window function you can do it like:

SELECT category, sale, sum(sale) OVER (PARTITION by category) as categorySale

It also makes running averages much much easier than some of the hack I remember doing before:

SELECT custid, freight, AVG(freight) OVER (PARTITION by custid ORDER BY OrderDate, orderid
ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) as movingaverage
FROM Sales.Orders

Friday, July 19, 2013

Triggers

Preparing for the 70-461 exam I read about triggers. They are store procedures that are automatically invoked upon some actions on a table. A kind of event handler for SQL-tables. They can be used to enforce rules that are more complex than what would fit in a CHECK constraint. They can also be used for any other kind of action that applies every time the data is edited. The max. depth for circular references is 32 calls and returning results from a trigger is deprecated.