After many months of work I'm proud to announce my latest project HertzFlyDrive.com. This website was designed from the ground up provide Hertz with a more effective way to manage their car rental booking information and help speed up the booking process for end users.

This site has a lot of new features in it that I had great fun with including Linq to XML for the XML calls to Hertz, jQuery UI date pickers, a brand new jQuery expander made specifically for this project, contacting the database using JSON and jQuery, multiple languages, etc. The list of new tech on the site is nearly endless! I'm proud of the speed of the site compared to their older site considering all of the extras we have added to the forms. By comparison our new site loads roughly twice as fast as the older Hertz site.

It's not only raw speed that helps the end user experience but also the ease of use of the site. We rose to the challenge and set about making the site a breeze to use for users. Our new car selection page actually combines two processes in one. On the old site the end user was required to go through to two separate pages for booking a car and picking the accessories that they might want. On our site this is all done on the same page. We have also included a very nice clean way for the user to upgrade their car choice without forcing them to go back a page and forward again. Steps like these all help speed up the booking process for end users and provide a much smoother experience.

It's in the admin tools that we've really gone to town with features. There are loads of snazzy extras we've built into this system. With the old site that Hertz had there were no stats for bookings or facilities to update offers on their site. Our brand new admin tools let Hertz quickly and easily add/update/delete/order any offer for any country and/or language.

There is also a brand new email marketing tool built for this site that allows admins to send and track emails sent to customers. The emails sent can be stored and stats like number of opens, click throughs or completed bookings from an email message are all store in our system. Customers can opt out of the email marketing at any time and there is even an option for them to easily opt back in if they decide to change their mind later on.

Finally, we have provided Hertz with enough stats to keep them busy for years to come! Everything from 'how many child seats were bought?' to 'whats our most popular destination?' is available to the site admins.

We're hoping that this new web system will help drive more sales for Hertz and that end users find the site a joy to use. I'd love to hear what you think about the site so let me know in the comments below.

I've only recently started playing around with SQL Server 2008 for a new project I'm working on. To date I've found it very easy to use, especially coming from SQL Server 2005. One thing that I noticed early on was my inability to edit any of my tables using design mode. Anytime I wanted to change the column to allow nulls or adjust a column type I would see this error message in SQL Server:

Saving changes is not permitted. The changes you have made require the following tables to be dropped and re-created. You have either made changes to a table that can't be re-created or enabled the option Prevent saving changes that require the table to be re-created.

To stop this message occurring all you need to do is go to Tools and Options from the SQL Server toolbar. A popup will open on screen. Simply select the Designers option from the left hand navigation within the popup window and uncheck the option that says 'Prevent saving changes that require table re-creation'. Click 'OK' and your done. If you now try to adjust your table using the table designer in SQL Server 2008 everything should work OK for you.

Usually when testing a database I fill it with loads of dummy data. This helps both me and the client see what their site will look like once real data is in their system. Just before the site goes live it's always a good idea to remove the test data, especially if you have user accounts with test as the username and password!

However, if you delete the contents of a table then you will notice that the primary ID values will continue to auto increment from the last number that was in your table. So if you had 100 rows of temp data and removed them then the next entry to the system will be 101. This should not be an issue for your database or code but sometimes it's nice to reset these values too. In SQL you can do this by running the following command:

DBCC CHECKIDENT (tablename, reseed, 0)

Tablename should obviously be the name of your table that you want to reset the primary key value in. It should be pointed out that this code isn't just for resetting to zero. You could put 350 as the number in place of 0 and SQL would start the next entry in your table to 351.

As you can see setting the value of a primary key is quite an easy thing to do within SQL.

The SQL Database publishing wizard is a great tool for exporting out your sql database tables, all its content and any stored procedures that you have into a T-SQL file. No more fiddling with text files or excel sheets.More...

In most databases today there are columns that contain thousands of characters of text. In some cases this information needs to be returned to an end user on a busy section of your site, for example a search feature.

If someone does a search on your site usually you will be displaying only a portion of that content when displaying the search results. You could very easily return the full content of all your columns to the end user and use your code to stip off the excess data but a much faster way is to strip off what you don't need in SQL before you pass back to your code. One way of doing this is to use the SubString method in SQL Server:

SUBSTRING(ColumnName, start position, end position)

Example below returns the value within PostDetails from the start position up to 150 characters long and calls this new 'field' PostSummary

SUBSTRING(PostDetails,1,150) as PostSummary

Full example:

Select PosterName, PostDate, SUBSTRING(PostDetails,1,150) as PostSummary
From Posts
Where <<search conditions entered from search page>>

You can easily call PostSummary just like you call PosterName or PostDate in your code view.

I recently had to do a Left Join on my SQL statement to get images relating to houses in my database. The database results had to give me the properties, regardless of whether they had images or not hence the Left Join as apposed to an inner join. The problem I had was that just doing something like below would not work. SQL would return all your properties but you get the same image for every house:

SELECT P.PropertyID, P.PropertyRef, PI.ImageFile
FROM Property AS P  
LEFT JOIN(
Select TOP 1 ImageFile, PropertyIDFK
From PropertyImages PI
) PI
P.PropertyID = PI.PropertyIDFK

What I needed to do in this situation was use an OUTER APPLY statement. This then allowed me to return all of my properties with the first image uploaded from the database. Very nice. For more information on the APPLY function in SQL Server 2005 you can read some blogs here or here.

Below is the end SQL statement for using TOP function on an OUTER JOIN:

SELECT P.PropertyID, P.PropertyRef, PI.ImageFile
FROM Property AS P  
OUTER APPLY (
Select TOP 1 ImageFile, PropertyIDFK
From PropertyImages PI
WHERE P.PropertyID = PI.PropertyIDFK
ORDER BY PI.PropertyImagesID
) PI