Blog ala Vidar

SQL, AppFrame and other cool technologies

Monthly Archives: August 2009

AppFrame Podcast

I’ve been thinking about starting podcasts for quite a while now. The naming was the easiest: AppFrame Podcast. What’s left is starting the thing. I have some ideas who I’ll capture to get them talking, and some topics. But, is there any particular topics you’d like to hear about? Maybe you’d like to hear one specific guy talk about something, or you just want to hear more about a particular topic.

My thoughts was also to co-host it with someone and then invite “experts”. Then we’d have a couple of fixed columns like “tip of the day”, “tool of the day” or similar. So, the reason for me posting this blog is that I want YOUR suggestions. I already got a good idea by Kelly about not only talking to experts, but also PIMS-users. So, if you’ve got ANY idea, good or bad, please comment, or contact me (email or MSN) at vidar@omega.no

Oh, btw. This will be an audio podcast. On English, of course!

Advertisements

Download NDC 2009 videos

The videos of all sessions at NDC (Norwegian Developers Conference) 2009 is now available through a torrent. Illegal? Nope! They’re publicly available. Please add comments with your recommendations. I know I will…. And please seed when you’re finished downloading!

http://blogs.msdn.com/grothaug/pages/downloadable-ndc2009-videos.aspx

5 minutes interview #8 – Geir Ove Fjellstad

Did you know that Geir Ove was one of the first lucky guys to get AppFrame training from me? Now he’s the team leader for the BP-team in Stavanger. You see the connection?!

How was your first week? Did you actually learn anything at all?
Frankly, my first week was scary. AppFrame, R2, CR1, CR2, MarketMaker, PIMS – all these words that I just couldn’t figure out but felt I was expected to (not sure I really understand them today either:)
But friendly colleagues in a laid-back environment helped me relax, and one step at the time I eventually got familiar with "The Omega way" (I hope).

What did you do before you started in the BP team?
After finishing my Master in Australia in 2004, I returned to Norway to an IT job market pretty much "on hold". I therefore started working for Søral, an aluminum producer. In 2005 I decided to "do a Bill Gates" and started my own company offering IT/programming services. After a year or so I came to the conclusion that Bill probably was luckier with the timing than me, so I decided to apply for a real job instead. My timing was a lot better this time, because Omega had vacant positions and I was lucky to get one. I started in May 2006 and have never looked back.

Is there anything particular that you’ve found interesting while working in Omega?
Hard to put a finger on anything in particular because what I feel is interesting changes. A year ago I felt the most interesting thing in the world was to figure out fancy technical solutions to a problem at hand. Today however I find it even more interesting to try to "get under the skin" of the client, and try to figure out what the client really needs (which often is something completely different than what the client says he/she needs).

Do you have any hobbies, except computer stuff?"Computer stuff" is actually not a hobby for me. I like it a lot (tend to love it sometimes), but I always think of it as my profession only. I feel it’s healthier to have this attitude (and then I can book man-hours:) For hobbies I play golf once in a while, deer-hunting every autumn, scuba-diving occasionally, trip to the gym now and then, and looking at the girls all year around.

Azure

What does Azure mean? Blue sky. But what does Microsoft mean with this? You might have heard of “the cloud”. Storing data in the cloud etc. This is a term that is very popular nowadays. The point here is that your data (or applications) are stored “in the cloud”, which means that you don’t have a relationship to where it’s actually stored or executed. I just created a database in Microsoft’s SQL Azure. I have NO idea where this database is, but I can access it through management studio or similar. There are a lot of other things you can do “in the cloud”. You can store your pictures, make it do heavy calculations etc. Some have been dreaming about having a screen (TV like) where all the actual computing is done “in the cloud”. I think it’s really interesting, because most of our computers are idling most of the time anyways.

So, why am I writing about this? Arild posted a blog about SQL Azure CTP earlier which started a discussion if we should support it. That lead me to try to make AppFrame “Azure compatible”. After a couple of hours, I’ve made my decision: If we should go “Azure”, we need to do it from scratch. There are so many things that are not supported by the SQL Azure which are imho show stoppers. For starters, you can not connect to it using management studio. Correction, you CAN, but you don’t get the object browser, and it’s very limited. The unsupported features are a long list. This in it self isn’t a big problem, because we don’t use most of them, but here’s a list of those we do use, and a little description.

SUSER_SNAME() – This is used EVERYWHERE. Literally! Triggers, views, defaults etc
NEWSEQUENTIALID() – Can be replaced by NEWID(), but you loose performance
WITH (NOLOCK) – Can be removed, but can lead to performance issues and lock-problems
FullText Indexing – Can of course be replaced by normal WHERE-clauses, but this will be a performance issue.

This is the biggest problems I’ve found so far, and I’ve only scripted tables and views. In addition, you don’t have sysobjects, syslogins, sysusers etc. Making AppFrame Azure compatible IS possible, but it would in my mind be just as time consuming as making it MySQL compatible. I’m sure Microsoft will add more features. This is only the CTP, so I hope they’ll at least add SUSER_SNAME and NEWSEQUENTIALID. The others we CAN live without.

So, to summarize. Should we be thinking to “go to the cloud”? No, at least not yet. I see it as a MySQL replacer. It’s great for small applications, but when you want to do real enterprise-stuff, you run MSSQL Server Enterprise Edition 🙂 One other thing to keep in mind is where is my data stored? All companies on in the US needs to be SOX compliant. This might be a problem for Azure. Don’t get me wrong. I’m positive about Azure, but not as an enterprise database!

If you want to test out Azure SQL, go to http://connect.microsoft.com and sign up for Azure SQL.

NB! We’ve got two new bloggers (from TGE). Looking forward to their postings 🙂
Tim Bosinius
Florian Biermaier

Sparse Columns

You’ve probably seen this in a list of features in SQL 2008, but what exactly is it and how can you take advantage of it? A sparse column just means that it will not store NULL. When you have a NULLable field, NULLs are actually stored in normal columns. It doesn’t use much space, but still. So, why is this a problem? Well, if you have an index on a NULLable field, the index will have as many NULLs as the field and therefore not be particularly selectable. In some tables, like the Objects table in CMS there are MANY NULLable fields, which on most records are set to NULL.

A sparse column is stored as XML, so you only want to add this to fields that have at least 50% NULLs, since there’s an overhead for each field that has data. For example, an integer field non sparse uses 4 bytes. The same field with sparse, uses 8 bytes, but for non-NULL data. Let’s do a little experiment.

CREATE TABLE test1 (
	Field1 INT,
	Field2 INT SPARSE NULL,
	Field3 INT SPARSE NULL
)

CREATE TABLE test2 (
	Field1 INT,
	Field2 INT NULL,
	Field3 INT NULL
)

INSERT INTO test1
  (Field1, Field2, Field3)
SELECT TOP 100000
  ROW_NUMBER() OVER (ORDER BY C.id), NULL, NULL
	FROM sys.syscomments AS C
	CROSS JOIN sys.syscolumns

INSERT INTO test2
  (Field1, Field2, Field3)
SELECT TOP 100000
  ROW_NUMBER() OVER (ORDER BY C.id), NULL, NULL
	FROM sys.syscomments AS C
	CROSS JOIN sys.syscolumns

EXEC sp_spaceused 'test1'
EXEC sp_spaceused 'test2'

We create two tables, almost identical. The only thing different between these are that Field2 and 3 are SPARSE in test1. Then we populate both tables with 100000 records, and set NULL for Field2 and 3. This is the result I got from sp_spaceused:

This tells me that for 100.000 records, two NULLable fields will all saves me (about) 1 MB with data when using sparse. Doesn’t sound much, but if you compare it to the one without sparse it’s almost 50%. And that’s with only two NULLable fields and no indexes. In one of our customers CMS database there’s over 300.000 records in their Objects table. This table has 55 NULLable fields. Doing a SELECT * on this tables, I see that most of the NULLable fields are actually NULL more than 80% of the time. Five of them are indexed too.

So, what’s the downside of sparse columns? Well, for starters it uses the double amount of space to store data, when it’s not NULL. You can not use text, ntext, geometry and geography, you can’t have default values on them. It is also not supported by merge replication. But it’s supported by AppFrame R3, so start using it!

One thing I forgot to mention is column sets. If you add a column set to your table with sparse columns, the sparse columns will be stored in this column (remember, it’s all just XML). Keep in mind that using column sets have more restrictions. For instance, you can’t change a column set. If you have a sparse columns in your table and you want to add a column set, you need to re-create the table.

One thing that’s “cool” about column sets though is that when you select * you get the column set (with your sparse columns) as an XML:

CREATE TABLE test3 (
	Field1 INT,
	Field2 INT SPARSE NULL,
	Field3 INT SPARSE NULL,
	MySparseFields XML COLUMN_SET FOR ALL_SPARSE_COLUMNS
)

INSERT INTO test3
  (Field1, Field2, Field3)
VALUES
  (1, 1, 1),
  (2, NULL, NULL),
  (3, 3, 3),
  (4, NULL, NULL)

SELECT *, Field3
	FROM test3
	WHERE Field2 = 1

The result:

If you think this is something you should use, read more about it here:

My favorite bloggers at SQL Skills:

MSDN:

Using Sparse Columns

Using Column Sets

5 minutes interview #7 – Jekaterina Golouchova

Jekaterina, or Kate as everyone calls her, has been on my team since she started in Omega. Although she’s sitting in Lithuania we’ve had relative good communication via MSN. I’ve also been in Lithuania a couple of times to visit the guys (and girls). The thing I like the most about being in Lithuania is that I get to talk ALL the time. When I’ve been there we’ve had almost non-stop sessions about SQL, .NET and other interesting topics.

How did you start in Omega, and how was your first day?
In 2006 I finished the University and sent my CV to UAB Omega. After the interview I was sure I wouldn’t get the job, because I haven’t had enough experience. So I was very happy to receive an offer to work in UAB Omega as a trainee. On the first working day we had session about TeamDocuments and after that I started with AppFrame certification program.

What have you been working with?
When I have started in Omega I was working with CR1/CR2 bugs, AppFrame Reports and some SQL stuff. During that period one of the most interesting tasks was to create the "Dynamic Reports" concept in CR2. I learned a lot of new things while I was working on it. Now I’m working with CR3 (create new forms, fix bugs and etc.), reports and SQL-code.

What do you think is the most interesting thing to work with? Any examples?
Till now everything I’ve worked with have been very interesting for me. I like to work with forms (create new, make changes etc.), reports (reports with sub reports, writing script, reports with charts), fix bugs (figure out why this bug appears, how to fix it), work with SQL Server (create views, SP, work with triggers, overview what’s new) because every time when you work with all these stuff you learn something new.

Except computer stuff, do you have any hobbies?
I like to read (most often humorous fantasy). Not long ago I bought an e-book reader so now it’s more convenient to read books. Also I like to ride my bicycle (especially to the seaside). Sometimes when I’m not lazy I go in for sports (aerobics, swimming or other sport exercises). When I have a good mood and inspiration I like to cook. And also I spend a lot of time with my family.

Comma Separated List in SQL

What do you do if you got some records of for example users and you want to return them in a comma separated list? Most of you will probably do something like this:

SET NOCOUNT ON

CREATE TABLE MyTable (
	Login NVARCHAR(128)
)

INSERT INTO MyTable
  (Login)
VALUES
  ('Vidar'), ('Johnny'), ('Peter'), ('JanCB'), ('Georgij'), ('Tomas'), ('Torgeir')

DECLARE @Logins AS NVARCHAR(MAX)
DECLARE @Login AS NVARCHAR(128)

SELECT Login
	INTO #MyLogins
	FROM MyTable

WHILE EXISTS (SELECT * FROM #MyLogins)
BEGIN
	SELECT TOP 1
	  @Login = Login
		FROM #MyLogins

	IF @Logins IS NULL
	BEGIN
		SET @Logins = @Login
	END
	ELSE
	BEGIN
		SET @Logins = @Logins + ', ' + @Login
	END

	DELETE
		FROM #MyLogins
		WHERE Login = @Login
END

DROP TABLE #MyLogins
DROP TABLE MyTable

PRINT @Logins

Yes, I know. This is not best practice with creating two tables etc…. but you get my point. Looping through a record set and add a field to a variable. But, why not just do:

SET NOCOUNT ON

CREATE TABLE MyTable (
	Login NVARCHAR(128)
)

INSERT INTO MyTable
  (Login)
VALUES
  ('Vidar'), ('Johnny'), ('Peter'), ('JanCB'), ('Georgij'), ('Tomas'), ('Torgeir')

DECLARE @Logins AS NVARCHAR(MAX) = ''

SELECT @Logins = @Logins + Login + ', '
	FROM MyTable

DROP TABLE MyTable

PRINT SUBSTRING(@Logins, 1, LEN(@Logins) - 1)

This works in SQL Server 2005 too. You just have to SET @Logins = ‘’ after you declare it. And you need to split up the values to be inserted.

5 minutes interview #6 – Vadim Naroznij

Vadim is Omega’s chief in Lithuania. We’ve played tennis several times and he and his brother are always nice to me when I’m visiting Lithuania. He and Loretta also made a very cool game when we had a teambuilding in Klaipeda.

Do you remember your first day in Omega?
The next day after receiving my master diploma at university (in July 2005) I came to Norway to work for Omega AS. I was assigned to the Drift department that Eiolf Berge was at the time running. This was actually not my first meeting with Omega and AppFrame – I have been communicating with Eiolf for a half of year over email and had already completed the CR1 certification program, and read quite many documentation files by that time. So I was at ones assigned to solve a nasty bug inside CR2 client (which was quite fresh back then). Coming from web development background, this was a tough challenge.

I remember you did the first eFaktura (electronic invoices) in Omega. What else have you been doing?
That’s right – eFaktura application was a web service client for submitting invoices to Statoil directly from AppFrame. Statoil was so impressed how fast and neat we did the implementation that I’ve got a year-long assignment in their IT department in Stavanger (working remotely from Ølensvåg) to help other suppliers implementing and debugging similar solutions. This job made me learn many interesting standards and technologies like E2B, xCBL, PIDX, XSD, WSDL, BizTalk. A bit later I have been assigned to a project integrating PIMS 2000 with the production database in Ølen Betong. Being transferred to the Technology department I was somewhat involved into system world development and documentation, created a new AppFrame certification program for newcomers. Apart of that I was involved in many internal applications development including Travel Expenses, Group SMS, Telephony, My CV, Timesheet, CR3 email application, mail server services and eLearning courses. Over the time I had a chance to participate in some PIMS modules development – such as CMS, ResourceMgmt, DocMgmt, QuantityMgmt, ProjectPortals. Since 2006 when Omega established an office in Klaipeda (Lithuania), I have been leading the daughter company and mostly working from here.

If anyone visit Klaipeda, which places should they see?
Let’s start with that if you plan to visit, it’s best to look into the events calendar on e.g. Klaipeda.lt web page – than you might come just in time for some cool events like the yearly international Jazz festival or a Midsummer festival or maybe the famous Sea festival, which by the way was held on the last weekend and was topped with arrival of the whole "Tall ships races Baltic 2009" fleet (there were also two sail ships from Norway :). If you happen to be in Klaipeda when no events are happening, I’m pretty sure that there will be someone from our friendly local team who will show you around: old town streets, panorama from 21st floor lounge bar, sea/clock/amber museums, white sandy beaches and introduce you to some Lithuanian cuisine specials 😉

What hobbies do you have, except computer stuff?
Apart of being a geek, I also like fishing, hiking and shooting (that’s why I like staying in Norway – there’s plenty of either). I enjoy casual sports activities like cycling, volleyball or jogging (latest addition: skiing). After spending almost the whole day at the PC, I hate reading, but I like fiction books… – my solution is audio books (note: don’t get too involved while cycling).

Tool belt

Everyone has a couple of applications that they can not live without. Here’s mine:

7zip
A free (both as of cost and as in open source) (de)compression tool that handles all the formats worth mentioning, including zip, gzip, bz2 and rar.

Fences
A great tool which enables you to create “fences” on your desktop. This means that’ you’re grouping icons together like I’ve done on the screenshot

Filezilla
Open source FTP client which supports FTP, FTPS, FTPES and SFTP.

Live Mesh
Great tool which enables you to share folders between computers, and users. You can also access these folders from the web, if you’re for instance sitting in a internet café. I’ve written a blog post about this. You can read it here.

TreeSize
A little tool which lets you easily view in a tree how much space every folder uses like in the screenshot to the right.
 

Virtual CloneDrive
This is a tool to mount ISO files. I’ve been using daemon tools for many years, but this is truly better. For starters, you don’t need to restart either on desktops or servers.

You see any tools that I should be using? Please comment!
For the record, Visual Studio, SQL Server, Office etc aren’t tools you can have in your tool belt. They’re too big 🙂

Social Networks

A couple of weeks ago I removed my accounts at facebook, MySpace, LinkedIn etc. Why? Because I’m sick and tired of getting mail with invites for stupid things that I don’t care about. The only social network I’m now a member of is twitter. This is different. For starters, there’s almost no “kiddies” there. Most of you probably know what twitter is but for you that don’t, read here. At first I didn’t want a twitter account, because I don’t care what everyone is doing all the time. But, I found out that this is not the primary usage. At least not for IT-guys. It’s very good to for example share interesting links.

At twitter, there are a couple of things you should know. When using a hash tag (#), you’re referring to a topic which is searchable, so when a user search for “windows7”, all your tweets with #windows7 in it will show. You can also click on these hash tags and see other people’s tweets about this topic. When referring to a user you use the @. For example my user is known as @vidarnordnes, so if you want to write something to me, you’ll write: “@vidarnordnes: Good morning”, and if I want to ReTweet, I write: “RT @johnnyvik: Good morning”. These are all public messages, but you can also send direct messages which only can be read by the recipient.

So, who’s worth following? You can go here to find the persons I’m following. Here’s a list of (in)active Omega twitters:

And here’s a list of my favorites:

 

BTW! Please rate blog post. If you think this blog post was stupid, set it to 1. If you loved it set it to 5. That way we know what topics to keep posting blogs about 🙂