Blog ala Vidar

SQL, AppFrame and other cool technologies

Monthly Archives: December 2010

Podcast Survey

Once again I’m thinking of starting a podcast and I’d like to hear your opinion. It would be very helpful if everyone could please answer these 4 questions. Thanks for your 30 seconds! NB! No registration, login or similar required!

Create your free online surveys with SurveyMonkey, the world’s leading questionnaire tool.

Advertisements

Buying my first Mac

Yes. I have sold my soul to the devil; Apple. After using Linux for several years, I got “forced” over to the Microsoft world after starting at Omega. I realized that Linux really wasn’t that great, so I stopped using it as my desktop. I still use it for servers sometimes, but I tend to prefer the Microsoft platform on servers too.

Last year I traded my HTC HD2 with an iPhone, and a couple of weeks ago I bought an iPad. These are the best products I’ve used in these categories (phone and tablet), so I figured I’d try out the Mac.

I think I’ll like it for some things, but I don’t think I’ll exchange my main desktop with a Mac. Even if I wanted to, I’m too dependent on Microsoft products like Management Studio and Visual Studio. I know you can run a virtual machine inside OS X, but what’s the point if you spend most of your day in that virtual machine anyways?

I’ve been trying to find a place where I “need” the Mac, because people kept telling me that spending 10.000 NOK just to test it out is stupid. Last week I found a use-case! I’ll use it to record music with my band, and as a TV-PC. So I went to Apple.com and ordered a Mac Mini, a wireless keyboard, the Magic Mouse, a Trackpad and a 1TB Time Capsule. I’m really looking forward to testing it out so be sure to check back in a couple of weeks for the second part.

The OVER clause

To use the ROW_NUMBER, RANK, DENSE_RANK or NTILE functions you need to use the OVER clause, but did you know you can use it on other functions as well? Like SUM, AVG, MAX etc. In the example under I want to list all timesheet records, but I want to know if the person has 7.5 hours each day, so I’ve made a “SumDailyManHours” column in the SELECT. This is easily done with SUM(ManHours) OVER (PARTITION BY PersonID, TimesheetDate). In the “old” days I’d do something like the second SELECT statement.

CREATE TABLE #Timesheet (
	PersonID NVARCHAR(8),
	ProjectID NVARCHAR(10),
	TimesheetDate DATE,
	ManHours DECIMAL(4, 2)
)

INSERT INTO #Timesheet
  (PersonID, ProjectID, TimesheetDate, ManHours)
VALUES
  ('Vidar', 'Customer1', '2010-12-01', 1),
  ('Vidar', 'Customer2', '2010-12-01', 5),
  ('Vidar', 'Customer1', '2010-12-02', 1),
  ('Vidar', 'Customer2', '2010-12-02', 6.5),
  ('Werner', 'Customer1', '2010-12-01', 7.5),
  ('Werner', 'Customer2', '2010-12-02', 7.5)

-- New way of doing it
SELECT
  *,
  SUM(ManHours) OVER (PARTITION BY PersonID, TimesheetDate) AS SumDailyManHours
	FROM #Timesheet

-- Old way of doing it
SELECT
  *,
  (SELECT SUM(ManHours)
		FROM #Timesheet
		WHERE PersonID = T.PersonID
		  AND TimesheetDate = T.TimesheetDate) AS SumDailyManHours
	FROM #Timesheet AS T

DROP TABLE #Timesheet

Here’s the result for both SELECT statements:

PersonID ProjectID TimesheetDate ManHours SumDailyManHours
Vidar Customer1 2010-12-01 1.00 6.00
Vidar Customer2 2010-12-01 5.00 6.00
Vidar Customer1 2010-12-02 1.00 7.50
Vidar Customer2 2010-12-02 1.00 7.50
Werner Customer1 2010-12-01 7.50 7.50
Werner Customer2 2010-12-02 7.50 7.50

Apple iPad

Tuesday this week (Nov. 30th), Apple released iPad to Norway. I once again had to discuss with myself whether or not to buy one for myself. My arguments were:

 

Pros

  • It’s a ”little laptop”, meaning that I can bring it where-ever I go and still have most of my things with me.
  • Angry Birds – yes, I’m addicted!
  • It has the best twitter client there is; TwitBird Pro, with support for Read it Later.
  • It’s got apps for more or less everything; spreadsheets, presentations, games and even RDP (Remote Desktop Protocol)!

Cons

  • Apple. I love their products, but hate their attitude and their fan-boys which take all criticism to Apple (or their products) personally.
  • I don’t really need it.
  • It’s not a full-worthy computer, so I can’t replace any of my computers with it.

 

As you probably figured out by now, I’m a gadget geek, so I went out and bought one on Wednesday. The one I went for were 16GB, with 3G. The reason for this is that I will probably not use it for media-stuff (music, video etc.), and the reason that I choose 3G were that I actually might use this when I’m at a customer. My handwriting is so horrible that I sometimes don’t understand my own writing. Now I can bring my iPad instead, and take notes on that, and even if I don’t get access to the customer’s Wi-Fi, I still got internet access via 3G.

Now, I’ve used it very heavily every day, playing games, watching videos (I love TED), taking notes in meetings, tweeting and lots more, and I love it. I really love it!

I think this is an awesome device, and I am going to recommend it to all of my family members. If you’re in the market for a new laptop, and you’re using it for Facebook, Internet bank, MSN and surfing, this is without a doubt a perfect device in my eyes. Note that Java, and flash, doesn’t run on it, so to do Internet banking, you need an app from your bank. Most of the popular banks in Norway already got apps, either for the iPhone (which runs on the iPad) or specially designed for the iPad (often called “HD version”).

Oh, and just to be clear. This isn’t a device created for developers. It’s created for the end user, like my mom. You can’t run Visual Studio on it. You can RDP to a server, but coding on a touch-screen? I wouldn’t do that at least.

 

At last I’ve got a list of apps you might want to check out:

  • Dropbox – Enables you to sync files between all your devices, including Windows, Mac, Linux, iPad, iPhone and Android.
  • TVGuiden – Norwegian TV Guide.
  • TwitBird Pro – The. Best. Twitter. Client. Ever!
  • Read it Later – Great “extension” to TwitBird, which enables you to read websites after tweeting.
  • Evernote – Sync your notes between all your devices.
  • TED – Ideas worth spreading. 3-20 minutes videos about new technology, thinking and more.
  • Spotify – Music streaming.
  • Kindle – Read your e-books on your iPad. Read my review on the Kindle here.
  • Air Video – Enables you to stream video from your home server.
  • Angry Birds – Addictive game.
  • Air Hockey – Fun two-player game.
  • Civilization Revolution – If you like the Civilization game series, you’ll love this!

SQL Server 2011 – Denali

Almost a month ago, Microsoft released the first CTP (Community Technology Preview) version of their upcoming SQL Server 2011, code-named Denali. I immediately downloaded and installed it on all my machines, and I’ve been using it ever since. It works perfectly with both 2005 and 2008, and of course with 2011.

Now, what’s new in this release? The biggest difference is that Management Studio is built into Visual Studio. Or, more correct, Management Studio has got a Visual Studio 2010-shining. I think this is a good call. For instance, I HATE the intellisense in 2008. It’s the first thing I remove when I’ve installed it, but in 2011, I haven’t turned it off yet, because it’s not in my way. It’s actually helping me write queries! The other big new UI related feature is snippets. Now you can, finally, have them inside Management Studio. I’m going to create a library of my scripts and make a snippet folder to share with all that’s interested. I’ll post it here on my blog when it’s ready.

When it comes to T-SQL enhancements, there’s actually a couple of new features that looks very interesting:

ORDER BY has some new features. With the following query, you get 10 rows. Not the first 10, but from row 10 (OFFSET 10) to 19 (FETCH NEXT 10). I think this will be easier to use for paging than using ROW_NUMBER() in 2008.

SELECT *
	FROM sys.sysobjects
	ORDER BY name
	OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY

Notice that in books online they specify that FETCH FIRST and FETCH NEXT are synonyms and that they’re there to provide ANSI compatibility.

CREATE SEQUENCE is a completely new T-SQL feature. The following query makes a sequence that you can poll.

CREATE SEQUENCE test
	AS INT
	START WITH 1
	INCREMENT BY 1

As you see, the script is not refering to any tables, which means you can use this sequence anywhere by using NEXT VALUE FOR MySequence. For instance:

INSERT INTO MyTable
  (Field1, Field2)
VALUES
  (NEXT VALUE FOR MySequence, 'Value2')

You can also use this feature as a default constraint. This could be helpful when identity-columns isn’t good enough.