Hrubaru's blog

MCTS certification

noreply@blogger.com (Hrubaru) — Sun, 21 Feb 2010 18:29:00 +0000

I have been quiet lately with blog posts and that's because I didn't have time to write. I prepared for my MCTS Sql Server 2008 Database Development certification, plus I was very busy on my job.

In a previous post regarding new year's resolutions, the first thing on my list was this certification planned for February and I am very happy to announce that I passed the exam (70-433) pretty easily with a good score in my opinion (925/1000). I didn't buy any Microsoft training kit or practice tests, the study method was based solely on Books Online.

As Microsoft says, to provide others with official verification of my certification achievements:
1)use the online transcript link
2)use Transcript ID (890519) and the Access Code (ionuthrubaru)
It should provide you my exam transcript.

About the questions, I think they put too much accent on XML and Full Text. The first questions were XML only, at which point I thought there is no way I would pass if they keep going like this. I noticed other having the same issue, for example Brent Ozar who noticed the same XML emphasis.

I will come back now at my usual 1-2 posts a week.

Gaining new skills

noreply@blogger.com (Hrubaru) — Wed, 27 Jan 2010 10:32:00 +0000

I have decided to check some basic stuff about developing apps for mobile platforms. I have not decided yet upon the platform, the options are Iphone, Android or Windows Mobile.

Iphone seems already a crowded market with small chances to break trough. Developing the app is probably the easy part, while marketing is the tricky part. Also the approval process from Apple is a pain.
Since my professional orientation is to Sql Server and .NET, it would seem natural to go with Windows Mobile but it seems that right now WM is far behind. Even Microsoft CEO Steve Ballmer criticized Windows Mobil e :). People are waiting for Windows Mobile 7 which has been pushed back to the last quarter of 2010.

At this point Android attracts me a lot. Google's Android market has still flaws but on the long term it seems to offer good perspectives.

Sql Server bug when using NULL with LEFT and CHARINDEX functions?

noreply@blogger.com (Hrubaru) — Sat, 23 Jan 2010 16:56:00 +0000

I have the following query:

select LEFT(null,CHARINDEX('.',NULL))

My server:
Microsoft SQL Server 2005 - 9.00.4035.00 (X64)

The query should return NULL, in my opinion. But it constantly throws an error:
Msg 536, Level 16, State 1, Line 1
Invalid length parameter passed to the left function.

It's not working on SQL Server 2008 also:

Microsoft SQL Server 2008 R2 (CTP) - 10.50.1092.20 (X64)

This message occurs if you try to pass a negative value to the LEFT function in sql server: SELECT LEFT('abc',-1).
But this is not the case here since CHARINDEX should return NULL: if either expression1 or expression2 is NULL, CHARINDEX returns NULL when the database compatibility level is 70 or higher.

When I am breaking the query everything works as expected:
select CHARINDEX('.',NULL)
select LEFT(null,null)
both return NULL.

Initially I thought that it was a 64 bit issue, but I managed to reproduce it (harder, I admit) on 32 bit also.
You might even encounter the situation where it will work for an indefinte runs and all of a sudden crash. I have posted it on some sql server forums but I couldn't find a reasonable explanation. And going to Microsoft with it is not always easy. :)

O/R-Ms - the Vietnam of computer science

noreply@blogger.com (Hrubaru) — Tue, 19 Jan 2010 19:03:00 +0000

The phrase is not mine but I totally agree with it. I found it on Jeff Atwood's blog, Coding Horror, one of the best programming blogs in my opinion.

I guess most of us (DBAs) have been in the situation to have more or less contradictory debates on object relational mapping (that is, the best solution to map a set to an object in an OO language like Java/C# etc).
Most of the developers that interact with a database, have mixed feelings about the SQL: some try to avoid it as much as possible (O/R-Ms mappings), some are really against it, very few try to learn at least some basic syntax.

I remember having this kind of conversion with an application architect. Even though he was an application architect, he considered SQL useless and thus, replaceable. He even advised me and my coworker to re-think our long term goals and career because SQL will disappear. Luckily for him, he was a friend of ours and also paid the check :)

Here is a great blog post from Jeremiah Peschka, which tries to stays reasonable about the debate, and will offer good arguments for these kinds of discussions: O/R-Ms: Panacea or Polio Braces?

String concatenation issues

noreply@blogger.com (Hrubaru) — Sun, 17 Jan 2010 10:52:00 +0000

I had talked before about a string concatenation issues in Sql Server. It's not about issues Sql Server has, it's about issues that a developer can have if he doesn't know how string concatenation and datatype precedence work.
I thought of re-analyzing the scenarios (and correct some minor mistakes) using SQL Server 2008.

Most issues occur when concatenationg strings used for generating dynamic sql.
In my stored procedures I use it a lot because of the way the app is designed: processes generate new tables so our sps get table name, column names as parameters. The final string to execute can get quite large. The examples in this post us nvarchar datatype (same applies to varchar which has the character limit double - 8000 instead of 4000). Of course all examples are discussed in the context where the final string variable (the result) is nvarchar(max). At some point truncation occurs.

Scenario 1 - when concatenating string constants, the result will be truncated to 4000 (nvarchar), if all the constants are less than or equal to 4000.

declare @sql nvarchar(max)
select @sql = replicate(N'a',3000) + replicate(N'b', 3000)
select len(@sql) --4000

The result is 4000, even though @sql is nvarchar(max). The truncation occurred because we are concatenating constants less than the limit of 4000.
If one constant/variable is > 4000 then truncation doesn't occur:

declare @a nvarchar(max),@sql nvarchar(max)
set @a = N'a'
select @sql = replicate(@a,4000) + replicate('a',1)
select LEN(@sql) --4001

Scenario 2 - when concatenating varchar to nvarchar, datatype precedence and implicit
conversion occur which can lead to string truncation. To avoid implicit conversion (which can also lead to poor performance in some cases like filtering on a column), same datatype should be used:

declare @sql varchar(max)
select @sql = replicate('a',3000) + replicate('b', 3000)
select len(@sql)
--result is 6000

declare @sql varchar(max), @sql1 nvarchar(max)
set @sql1 =N''
select @sql = replicate('a',3000) + replicate('b', 3000) + @sql1
select len(@sql)
--result is 4000

Here implicit conversion from varchar(6000) to nvarchar(4000) occurred even though @sql1 is nvarchar(max). A flavour of this example is in the next section.
See, one might wonder what would be the above result if I do one small change:

declare @sql varchar(max), @sql1 nvarchar(max)
set @sql1 =N'a'
select @sql = replicate('a',3000) + replicate('b', 3000) + @sql1
select len(@sql)

The result is 4001 (more than the limit) but truncation still occurred and it's more difficult to detect than in the other cases. See below why.

Scenario 3 - concatenating multiple string constants will lead to string truncation if the intermediary results (starting from left to right in the expression) exceed 4000, but the final result can be more than 4000 of one of the variables is of type nvarchar(max)

To illustrate:
declare @sql nvarchar(max), @str1 nvarchar(128), @str2 nvarchar(max)
SET @str1 = N'x'
set @str2 = N'y'
set @sql = REPLICATE(N'a',4000) + @str1 + @str2

select len(@sql)
select @sql

The result is 4001. Oh, it’s greater than the 4000 limit so you would think everything is ok right? Not really. The @sql result ends up with 'ay', and in fact the length should be 4002 right? So, where did the 'x' character go?
Well, sql server evaluates the expressionfrom left to right, one step at a time: first replicate(N'a', 4000) + @str1. @str is nvarchar(128) so it assumes the result should be a constant of maximum 4000, thus it truncates the ‘x’. Next it adds that to @str2 which is a nvarchar(max) so it assumes the result is a nvarchar(max), thus it adds the 'y'.

Conclusion and fix

The basic rule here is:
@sql = @str1 + @str2 + @str3 + ........+ @strn
Assuming they are all of same datatype (nvarchar), Sql Server will truncate everything starting with @str1 if any of the intermediary results of concatenation is above 4000 until it reaches a nvarchar(max) type variable. Starting from there truncation will not occur, regardless of the legth of the variables/intermediary results. This is why you can end up with results greater than 4000 limit, and still have truncation!

The solution to avoid truncation is pretty obvious: when concatenation start all the time with a nvarchar(max) variable. I prefer initializing the final string to execute like this:

DECLARE @sql nvarchar(max)
SELECT @sql = N''
SELECT @sql = @sql + @str1 + @str2 + ......@strn

It might help sometimes to work only with nvarchar(max) variables, but this will not work if you have mixes of constants and variables (and your concatenations starts with constants). As far as SQL Server 2008 goes, no difference between it and 2005 version in this matter.

Also, pay attention to strings-in-strings, meaning dynamic sql inside another dynamic sql which can lead to pretty ugly scenarios and should be avoided. More in a future post.

New year's resolutions

noreply@blogger.com (Hrubaru) — Sun, 10 Jan 2010 11:06:00 +0000

I've seen this on other blogs and I believe it can help.
Here are my objectives for 2010:

Professional
1)MCTS certifications:
MCTS: SQL Server 2008, Database Development - I believe I can take this in February
MCTS: SQL Server 2008, Implementation and Maintenance - sometimes before May 1st
MCTS: SQL Server 2008, Business Intelligence Development and Maintenance - I am not sure about completion date for this one as it seems the most difficult one for me at this moment. I will jut put sometimes in 2010.

2)I want to become more involved in the online Sql Server community. This means blogging (at least once a week), helping people on sql forums (SqlServer Central, SqlTeam, PASS, MSDN, Romanian Sql Server User group (sqlserver.ro)
- Present a topic at a conference - CodeCamp.ro seems very interested in having someone to present stuff on Sql Server. Things look promising and I will start working for a presentation and send it to codecamp. I also want to contact our ro sql server group and offer my help in whatever they need.
- Publishing - start writing longer articles and send it to websites for publishing - the goal is to publish 4 articles in 2010;
- Know at least 5 new people

3)Get back in touch with the academic community - I had some initial discussion with my DB college teacher. There might be an opportunity for me to teach some Oracle classes to students. So, the goals are:
- Teach Oracle class;
- start working again with Oracle.

4)Learn a client-side language - I will probably focus on .NET technology and work mainly with web technologies like ASP.NET . The measurable goal here is to develop one website. The design part (HTML, CSS, Javascript) will be done by my brother.

5)Getting familiarized with Windows Server 2008 R2 & Sharepoint 2010.

Seems a little busy, considering that I already am full-time employed :)
Also I don't want to neglect my "hobbies": stock market and the Internet in general (SEO, SEM, blogging)

Personal side

Well, this year I am getting married on May 1st. Lot of preparation to do. After that we will want to stop paying rent and buy an apartment/house. My future wife wants us to have kids very fast, but that's something still to be discussed for this year :)

Another topic is my brother. I want him to be able to switch from the stuff that he learned in college (biochemistry - he has a very hard time in finding a job) to IT. I believe he is already on a good track but there is still much to do.

Looking forward to using it

noreply@blogger.com (Hrubaru) — Thu, 24 Sep 2009 08:28:00 +0000

I haven't yet used the RC. In fact, I am always a fan of using the final product.
All I can say for the moment is that I am excited about the hardware requirements here:

- 1 gigahertz (GHz) or faster 32-bit (x86) or 64-bit (x64) processor
- 1 gigabyte (GB) RAM (32-bit) or 2 GB RAM (64-bit)
- 16 GB available hard disk space (32-bit) or 20 GB (64-bit)

Nothing big here, so updating from XP won't require to upgrade my PC or even worse to buy a new one

in reference to: Welcome to Windows 7 (view on Google Sidewiki)

Ma gandesc sa castig un monitor

noreply@blogger.com (Hrubaru) — Sat, 08 Aug 2009 11:30:00 +0000

Simt ca sunt intr-o perioada norocoasa. Am mai castigat anul acesta un laptop. Asa ca m-am gandit ca particip la concursul lui Bobby Voicu si LG ce are ca premiu un monitor LG M2294 cu tunner TV incorporat si diagonala de 22 inch.

Follow up dupa picnic

noreply@blogger.com (Hrubaru) — Mon, 27 Apr 2009 16:49:00 +0000

Pe seara, dupa ceva whiskeane si multe beri m-am aruncat in Ciric ca trompeta. Cu inca vreo 2 terminati. Atunci parea o idee destul de buna. Rezultatul? O raceala faina de tot, ceva febra si o zi lipsa la servici.

Hai noroc ca stiu ce urmeaza: vreo 2-3 saptamani de pauza si lucru intens.

Somn de dimineata si nise locale

noreply@blogger.com (Hrubaru) — Sun, 26 Apr 2009 08:00:00 +0000

Tare bun mai e somnul de dimineata. In weekend dorm frate pana la 11,12 fara probleme. Simt si eu ca am o zi libera.

In alta ordine de idei, incepe sa imi placa frisky tot mai mult. E al unui fost coleg. Am sa-i recomand sa-l contacteze pe krumel pentru o idee doua si poate pentru un mic review.

Piata niselor regionale incepe sa ia avant si Iasul e doar un exemplu.

Azi mergem la un picnic cu firma. Sper sa nu patesc ca Arhi care zice in unul din filmuletele lui ca se gandeste si cand merge la gratar la ce sa scrie pe blog.

E sambata

noreply@blogger.com (Hrubaru) — Sat, 25 Apr 2009 12:01:00 +0000

Si m-am inscris pe blogspot. Mahmureala aproape a disparut. Ma joc cu blogul si imi place mai mult decat wordpress