Wednesday, 11 April 2012

A story of a failed exam.

The Microsoft's SQL Server 2012 beta exam 71-464 has failed. Yes, the exam, or rather Prometric, not me.

Yesterday morning I went to an Authorized Prometric Testing Centre ready to sit the beta exam. It was all going well until the actual exam. The time has come, I clicked the 'start' button and an error message popped up.

Tuesday, 10 April 2012

T-SQL Tuesday #029 - Analytic Functions in SQL Server 2012

I took the last few days to review the new features of SQL Server 2012 in preparation for the beta exams  I will sit very soon. About many of them I read before but some still came as a nice surprise (better now than on the exam I suppose).

If I am to choose one useful feature of SQL Server 2012 to write about today it must be the Analytic Functions in T-SQL and improved OVER clasue. The LAG, the LEAD and the OVER which I would call a sapper tools for dealing with time bomb coding techniques, especially the triangular joins. Let me present

Wednesday, 21 March 2012

24 Hours of PASS 2012

Last 24 hours where the 24 hours of PASS tightly packed with very interesting sessions about many aspects of SQL Server. A lot of information, a lot of stories and unfortunately some technical problems too. Still a time well spent. 

My favourite presentations of the day in the order they happened were

Wednesday, 14 March 2012

Database table naming conventions

Opinions are like... belly buttons and so are naming conventions, right? Some people ignore them and some are ready to go to some lengths in proving superiority of theirs. And the truth is it doesn't really matter, unless your convention happens to be something as silly as Hungarian notation, for example.

When it comes to naming database tables there are two schools of thought: you should either use singular or plural nouns. Some people argue that singular looks better in projections and selections because you tend to think about an individual item there. Some people, and I am among them, think that the noun should be plural as it is semantically closer to what it is: a set.

But I am easy, I am not going to try to convince anybody that a table called sales will perform better then one called sale. It is just that sales fits better in the way I think about databases and in turn the way I think about them have on a few occasions proven to result in better performance ;)

Wednesday, 7 March 2012

How not to move locations

It will be a bit of storytelling this time. A story about trust and guts and things you cannot control.

Not so long ago a company that hosted their own servers in-house decided to move offices. All was planned in advance. The servers were to stay in the old location longer than everybody else until a decent internet connection will be installed in the new server room. The ISP was informed, a new line was ordered with specific instructions that it is not (what it was) an office move but that a completely new line is needed in the new location. A small trick to make sure everything goes right.

The plan was, that on Friday the new line will be installed and then on Saturday night the servers will be moved. The time has come and... nothing has happened.

Obviously ISP's support very kindly apologised from a very safe distance and said they are very busy and it will be done as soon as possible. The whole move was postponed until the following weekend.

Wednesday, 29 February 2012

ALTER COLLATION - deceptive simplicity

A database collation is something developers hardly ever think about. After all, most of the time, it is hidden deep below the surface and concerns itself with such trifling matters as character data types encoding and sorting.

As a developer you hardly ever have to concern yourself with it but it can occasionally bite with a 'Cannot resolve the collation conflict between X and Y in the equal to operation.' error. It may happen in comparison, look-ups or joins. That is because when two columns have different collations they cannot be compared directly and there is no way to do any sort of implicit conversion. Such problems can be easily solved by forcing the collation with COLLATE.

More surprising, perhaps, are problems with temporary objects as they use collation of the tempdb and not the database you are using. Solution is the same but here usually more cumbersome. For that reason administrators' rule of thumb is to keep all databases with the same collation as tempdb as long as it is possible, just to keep collations in their oblivion.

Wednesday, 22 February 2012

sub-tree performance - summary

Note to self in regards to sub-tree performance in SQL Server.

When working with hierarchical structures in SQL Server never use XML data. It is usually tricky to implement and performs poorly. CTE recursive queries perform reasonably well and are very elegant in implementation and as such are suitable for wide range of applications but when the tree is big, wide and deep and performance is critical HierarchyId should be used. On smaller data sets it not only cumbersome but also slower than other solutions and the cross-over point seems to be around 1500 nodes.

Old loop solution performs consistently regardless of data size but somehow lacks elegance.

Detailed reasons for the above can be found in the following posts:

Part 1 - the brief introduction to the problem
Part 2 - test environment setup with all four methods explained
Part 3 - testing, results and the conclusion