Blog ala Vidar

SQL, AppFrame and other cool technologies

Monthly Archives: March 2010

Mix10

Microsoft’s MIX10 conference is over, and all their sessions have been uploaded. These are high quality sessions about web and windows phone related development and design. You can download the videos at http://live.visitmix.com/Videos

Advertisements

Handling strings in SQL Server

In .NET, if you have a string it’s:
VB: Dim vMyString As String
C#: string vMyString;

In SQL Server you’ve got more than one choice. varchar, nvarchar (with or without MAX), char, nchar, text and ntext. That’s why SQL Server is better than .NET. No, just kidding. When you’ve got a String in .NET, what’s the equivalent? nvarchar. If you’re trying to put varchar in .NET, you’ll end up with an nvarchar (since the text is changed to Unicode).

SQL is built to store large amounts of data and retrieving it faster than lightning. It’s not built to handle strings, but sometimes you need to do it on the SQL Server. That’s when functions like SUBSTRING, LEFT, RIGHT, REVERSE and PARSENAME are useful! The last one, PARSENAME, is a function that’s saved me A LOT of time. The purpose of this function is to retrieve table names, schema names etc from a string. For example a string like “Server.Database.Schema.Table”. If you put PARSENAME and set the second parameter to 1, you’ll always get the Table. 2 will always give you Schema etc. Now, how can we use this other than retrieving object-names?

DECLARE @Name AS NVARCHAR(100)

SET @Name = 'Nordnes, Vidar Stangeland'
SET @Name = REPLACE(@Name, ',', '.')

SELECT
  PARSENAME(@Name, 1) AS FirstName,
  PARSENAME(@Name, 2) AS LastName

The @Name variable contains a name where you’ve got “LastName, FirstName (MiddleName)”. Replacing comma with a period enables you to use PARSENAME. The cool thing about this is that it returns NULL if it doesn’t have any values. If you didn’t use PARSENAME, you’d have to do something like:

DECLARE @Name AS NVARCHAR(100)

SET @Name = 'Nordnes, Vidar Stangeland'

SELECT
  CASE WHEN CHARINDEX(',', @Name) > 0
	THEN SUBSTRING(@Name,
			CHARINDEX(',', @Name) + 2,
			LEN(RIGHT(REVERSE(@Name),
				CHARINDEX(',',
					REVERSE(@Name)))))
	ELSE @Name
  END AS FirstName,
  CASE WHEN CHARINDEX(',', @Name) > 0
	THEN LEFT(@Name, CHARINDEX(',', @Name) - 1)
	ELSE @Name
  END AS LastName

WAY more time consuming, for doing something that should be pretty easy. REVERSE is also a great function to know about. As is REPLICATE, STUFF, ISNULL (or COALESCE), LTRIM and RTRIM.

While I’m at it, “asdf “ (with trailing space) isn’t always the same as “asdf” (without trailing space). Just take a look at this example:

SELECT '1' WHERE 'asdf' = ' asdf'
UNION ALL
SELECT '2' WHERE 'asdf' = 'asdf '
UNION ALL
SELECT '3' WHERE 'asdf ' = ' asdf'
UNION ALL
SELECT '4' WHERE 'asdf ' = 'asdf'
UNION ALL
SELECT '5' WHERE 'asdf' LIKE ' asdf'
UNION ALL
SELECT '6' WHERE 'asdf' LIKE 'asdf '
UNION ALL
SELECT '7' WHERE 'asdf ' LIKE ' asdf'
UNION ALL
SELECT '8' WHERE 'asdf ' LIKE 'asdf'

Before you run the code, think about what you THINK will be returned. 1-4 is the same as 5-8 except the = vs LIKE. My first guess was 0 records. My second guess was 2 and 6. None of my guesses was correct though.

Remember also that a nvarchar(MAX) field can contain very much data. Returning this to the client could be very slow, especially if you include this field in other fields (for instance one field returning HTML-data, and one without the HTML-formatting), or as we often see here in Omega, a SearchColumn which is used by our framework for filtering. This field normally consist of all the (n)varchar-fields in the table.

5 minutes interview #22 – Rune Grothaug (Microsoft)

Rune is NNUG’s contact in Microsoft, since his title is "Developer Community Manager" in Microsoft Norway. He’s responsible for communication between developers and Microsoft and has been responsible for events such as MSDN Live and NDC (Norwegian Developer Conference). He’s leaving Microsoft soon, and I’m sure we’ll miss him. He’s been a great resource for NNUG, and has done a wonderful job with planning various tech-events. I bet this will be hard task for whoever’s replacing him!

What’s the coolest thing about working for Microsoft?
The coolest thing about working for Microsoft (and especially for the Developer Platform Evangelism team) is that we usually get to learn about new tech some time before it becomes publicly available. In addition I work with some pretty smart people who I learn a lot from. I do not think I’ve ever experienced the same amount of brain conditioning anywhere else in my life. Most of my coworkers work really hard and put in many hours to try to cover as much ground as possible. It’s not always easy being the world’s smallest DPE team, but we still deliver some of the best results in the world (really!). Working for Microsoft is different from many other jobs. Like Coca-Cola, Google, IBM and other large brands, most people have an opinion about Microsoft, and that allows for some really good discussions. Both when I’m officially working and on my own time. Especially after a few drinks people are VERY eager to come at me with all kinds of questions and weird allegations. I say bring it on!

Who’s your favorite speaker(s)?
Uh oh. This is tricky. I should definitely just point out all the speakers on this year’s NDC roster. I´m afraid it’s a bit more complicated. To be honest there are so many great speakers out there and I’ve been fortunate enough to have met many of them in person. If i have to mention a few, I´d like to point to Scott Hanselman for his incredible capacity, humor and being so knowledgeable about a huge variety of topics. Then there’s Bob Martin (Uncle Bob) for being able to make so many people really think about the effort they put into their work. Rafal Lukawiecki for his clarity and in-depth understanding of BI, Data Mining and Security (and being able to actually explain complex matters in a easy-to-understand way). I would have to point to both Luca Bolognese and Mads Torgersen for their supernatural understanding of programming languages and for explaining the choices made by the language team in Redmond in an engaging way. They both drew a good crowd at the last two NDC’s. Roy Osherove for being so focused on TDD and having inspired thousands of people to test better (and for playing a whole slew of geek songs during TechEd, NDC and in other conferences). David Chappell for being so eloquent and pleasant to listen to when he gives his renowned talks. Then there’s Ted Neward, Juval Löwy, Michele Bustamante and so many more who make our lives difficult by submitting a whopping >20 abstracts last year. Not easy to choose from that pile of great talks. Ian Griffiths is also a very good speaker and there are many more I should list up but this will have to suffice for now 🙂

You’re switching job. What are you going to do now, and what have you been doing before you started in Microsoft?
I recently decided to leave Microsoft. Not an easy choice but I´m taking on the role as team lead for the .NET guys in Avenir, one of Norway’s largest consultant businesses. I’ll have 35 directs so I’ll be busy busy. I´d have liked to stay with Microsoft longer but couldn’t pass up this great career opportunity. Maybe I’ll find me way back at some point – if they’ll have me 😉 Before I joined Microsoft I worked as a developer for 6+ years. First I did mostly web projects, like http://www.bmw.no and many other small and large portal solutions, working for a company called Electric Farm. It’s been out of business since 2004/2005 I think. Then I went on to do mostly Java programming with a company called Lawson. They had an ERP system huge as a house that needed continuous work and maintenance. I found it too boring in the end and started looking for a job that´d allow me to work more with people. That’s when I found the Microsoft job. The Developer Community Manager title is one I made up so people could understand better what it is I do. When I took on this job I had only one goal in my mind and that was to contribute to making it more fun to be a .NET developer in Norway. Some people say I’ve succeeded. I hope that is true. I will for sure miss all the good talks I’ve had with developers literally around the world, but first and foremost in Norway. I truly believe that the average Norwegian developer is very skilled. However I’ll still be part of the community as a NNUG member, so I won’t disappear completely. I´d like to thank everybody who’s been to MSDN Live, NDC and all the other events we’ve put on the last four years.

Do you have any hobbies, except computer stuff?
I’m an avid cook. Never get tired of pushing the limits. I love good food and don’t know anyone that’s as fixated as myself. So I had to start learning how to cook a good while back. Today I master many things in the kitchen but I am still work in progress. One can never learn enough. In addition I tend to geek out with just about everything. Lately cars and that’s kind of expensive. So maybe I should take to collecting match boxes or beer coasters to keep the cost down. My wife says I can’t buy everything just because they´re shiny objects. I beg to differ. Computers have always been on my mind, and these days my 1 year old is trying to write his first pieces of code. It doesn’t go as well as I had hoped. Today he sat himself down on the display – good thing it didn’t break. I think we currently have 8 PCs in our house (two are Macs). Oh yeah, and then I try to take pictures with my digital SLR camera. It sometimes turns out ok.

Links:
http//blogs.msdn.com/grothaug
@grothaug
@runegrothaug
http://flickr.
com/grothaug