Piotr Rodak

Good practices - database programming, unit testing

2010-05-24T00:27:00Z

Jason Brimhall wrote today on his blog that new book, Defensive Database Programming , written by Alex Kuznetsov ( blog ) is coming to bookstores. Alex writes about various techniques that make your code safer to run. SQL injection is not the only one vulnerability the code may be exposed to. Some other include inconsistent search patterns, unsupported character sets, locale settings, issues that may occur during high concurrency conditions, logic that breaks when certain conditions are not met....(read more)

The penultimate audit trigger framework

2010-05-10T23:18:00Z

So, it’s time to see what I came up with after some time of playing with COLUMNS_UPDATED() and bitmasks. The first part of this miniseries describes the mechanics of the encoding which columns are updated within DML operation. The task I was faced with was to prepare an audit framework that will be fairly easy to use. The audited tables were to be the ones directly modified by user applications, not the ones heavily used by batch or ETL processes. The framework consists of several tables and procedures...(read more)

COLUMNS_UPDATED() for audit triggers

2010-04-28T22:27:00Z

In SQL Server 2005, triggers are pretty much the only option if you want to audit changes to a table. There are many ways you can decide to store the change information. You may decide to store every changed row as a whole, either in a history table or as xml in audit table. The former case requires having a history table with exactly same schema as the audited table, the latter makes data retrieval and management of the table a bit tricky. Both approaches also suffer from the tendency to consume...(read more)

Coding standards

2010-03-11T22:07:00Z

This post will be about coding standards. There are countless articles and blog posts related to this topic, so I know this post will not be too revealing. Yet I would like to mention a few things I came across during my work with the T-SQL code. Naming convention - there are many of them obviously. Too bad if all of them are used in the same database, and sometimes even in the same stored procedure. It is not uncommon to see something like create procedure dbo . Proc1 ( @ParamId int ) as begin declare...(read more)

An internal error occurred on the report server - no disk space for database.

2010-02-23T22:04:00Z

Last week was pretty hectic for me. I was developing SSRS report that was to be deployed to our QA, UAT and production environments. The report consists of many sections, which I had to implement as tables rather than subreports, because SSRS for SQL Server 2005 has many constraints, being unable to display dynamic headers and footers from subreports one of them. Having about 15 rather complicated tables on one design area is not a very comfortable way of work. The whole IDE is slow and tends to...(read more)

Kayleigh

2010-02-22T23:46:00Z

It's been about 25 years since I've heard Misplaced Childhood or the first time in my life. There was time when I could sing the whole album along with Fish . Thankfully karaoke and youtube weren't as popular in Poland in 1985 as they are now, or I could've ended up in a lip sync hit like these two chaps: http://www.youtube.com/watch?v=tMI7h78mDMA I learned a lot of english words from lyrics, sometimes to great joy of my english speaking friends. Yet I just realized today, what 'dancing...(read more)

relative query cost not accurate when using set rowcount

2010-01-27T22:33:00Z

When tuning performance of a query, it is quite common to compare different variants of the query in the same batch and compare the execution plans to see if changes that you made actually work for better or not. Often you would compare just relative cost of execution plan of a query within the batch. I lived for a long time with innocent assumption that the relative cost is accurate and reliable. As Gail Shaw showed in her blog , this assumption doesn't hold if you use scalar functions within...(read more)

like in the old times

2010-01-22T14:35:00Z

My little brother got recently an old 50mm Pentax-M 1:2 lens. While this lens is not known for the best image quality amongst Pentax prime 50mm lenses, it gave me first opportunity to take pictures 'the old way' on my K10D. This is quite nostalgic feeling to have to focus and set aperture manually like in the old '80s with famous soviet camera Zenith . My brother has also some old lenses with M42 mount. I bought adapter allowing me to connect them to my camera and indeed, it works. I...(read more)

updating column with a random value

2010-01-13T21:54:00Z

I have been working recently on load testing of our ETL. One of the factors that determines amount of transfered data is a lookup table that is joined to the source. I populated this table with aproppriate values, and then realized that I also need some values in second column, and they were null. Let's say the table looks like that: create table t101 ( a int , b char ( 10 ) ) go insert t101 ( a , b ) select 1 , '' union all select 2 , '' union all select 3 , '' union...(read more)

updlock, holdlock and deadlocks

2010-01-04T22:10:00Z

We have a process that manages sliding window over a number of tables in our reporting database. This process is triggered by first call to our reporting ETL. There is table that contains two dates that are important for the partitioning and business requirements. The table is build as the following example: if object_id ( 'dbo.tDateTest' ) is not null drop table dbo . tDateTest go create table dbo . tDateTest ( FromDate datetime not null, ToDate datetime not null ) The table contains only...(read more)