Although CTEs (Common Table Expressions) have been around since SQL 2005 I have found that most people are still not using them. Basically, CTEs can be used in two ways. First they can be used to return a temporary result set when nested inside another SQL statement. Second, they can be used in recursive queries.

Recursive queries can be a little bit difficult to master. However, when used properly they are very powerful. Recursive queries are a often used with hierarchical data. The most classical example or a recursive query is when you are given an organization structure and you need to represent the same structure in a relational database and run queries against it. Here is the syntax of a CTE:

   1:  WITH cte_name (optional column list) AS 

   2:  (   

   3:     Cte_query_1   

   4:     UNION ALL   

   5:     Cte_query_2 ) 

   6:  Statement that uses the above CTE

   7:  OPTION (MAXRECURSION n)

From the syntax above you can see that the CTE consists of 2 queries combined with a UNION ALL. The first query is referred to as the anchor member and it molds the result set. The second query is referred to as the recursive member. The recursive member forms the next part of the result set using the anchor member as input.  OK, enough with the technical jargon, lets do an example using the AdventureWorks sample database:

   1:  USE AdventureWorks;

   2:  GO

   3:   

   4:  WITH DirectReports(LoginID, ManagerID, EmployeeID) AS 

   5:  (    

   6:     SELECT LoginID, ManagerID, EmployeeID    

   7:     FROM HumanResources.Employee    

   8:     WHERE ManagerID IS NULL    

   9:     UNION ALL    

  10:     SELECT e.LoginID, e.ManagerID, e.EmployeeID    

  11:     FROM HumanResources.Employee e    

  12:     INNER JOIN DirectReports d    

  13:     ON e.ManagerID = d.EmployeeID )

  14:   

  15:  SELECT * FROM DirectReports;

  16:  GO

Lets break down the query:

• First we setup the result set with the anchor query. This is the line SELECT LoginID, ManagerID, EmployeeID FROM HumanResources.Employee WHERE ManagerID IS NULL. In plain english, this query selects all employees who are top level managers.
• Then we setup the recursive member to find the employee(s) who work for each top level manager by using the ManagerID key and referencing the DirectReports CTE. This is the line: SELECT e.LoginID, e.ManagerID, e.EmployeeID FROM HumanResources.Employee e INNER JOIN DirectReports d ON e.ManagerID = d.EmployeeID
• Finally, we return the result set by issuing the statement SELECT * FROM DirectReports

The result of this query is:

CTEs are very powerful, in the old days you would have to use a combination of cursors and temporary tables to accomplish the same feat. The syntax is fairly easy to understand and best of all, it is ANSI SQL 99 compliant.

Got a laptop or netbook without a DVD drive that you want to format and install a new operating system on? Well, don't rush out to the store to spend $50 on an external USB drive. Just whip out that ol' thumb drive and use that instead! Here are some step by step directions for installing an OS with a thumb drive.

Pre-Requisites

• A thumb drive large enough to hold the OS you want to install. The faster the bigger. Many people recommend the Apacer 4GB Handy Steno USB Flash Drive HT203, 200X Hi-Speed USB 2.0. It's the fastest USB 2.0 Flash Drive that we've found - it has a read speed of 25MBytes/sec. & a write speed-14MBytes/sec
• A bios that supports booting from a USB device
• A secondary machine that you can use to xcopy the contents of a DVD to the thumb drive. Alternatively you could mount an ISO image using DVD virtualization software to stage the install files.

Instructions Run CMD.EXE and type the following:

diskpart

select disk 1
clean
create partition primary
select partition 1
active
format fs=fat32
assign
exit

Copy the content of your install DVD to the root of the Flash Drive. I would recommend using the xcopy command. Here is an example:

xcopy d:\*.* /s/e/f e:\

Believe it or not, you are done. Stick in the USB drive and reboot your machine! If you did everything correctly you should be able to start the install.

SQL Server 2008 offers several new data types. Some of the changes are really just improvements on existing data types. For example the four new date time data types will offer more range, precision and flexibility than the traditional datetime data type.

Also, we have access to some brand new capabilities. SQL 2008 will introduce spatial data, the HierarchyID and the Filestream data type. These capabilities will help to further increase SQL Servers stronghold in the database market by giving developers more power and flexibility in how they store and retrieve their data.

Four New Date Time Data Types:

• datetime2 - allows for increased range for the year and accuracy down to 100 nanoseconds. The min date is 0001-01-01 and the max date is 9999-12-31.
• date - Just the date and only the date. No time.
• time - The time only.

• datetimeoffset - Defines a date that is combined with a time of a day that has time zone awareness and is based on a 24-hour clock. Same range and precision as datetime2 and also allows for a time zone offset of +/- 14 hours.

The Hierarchy ID Data Type:

The Hierarchy id data type allows you to construct relationships among data elements within a table, specifically to represent a position in a hierarchy. This is a perfect fit for representing tree-like structures such as organizational charts. In order to manipulate this data several new functions have been created:

• GetLevel() -gets the level of the current node.
• GetRoot() - returns the root of the tree.
• IsDescendent() - returns true/false if the node is a descendent of the parent.
• Parse() - converts the canonical string representation of a hierarchyid to a hierarchyid value.
• Read() - reads binary representation of HierarchyId from the passed-in BinaryReader and sets the SqlHierarchyId object to that value.
• Write() - writes out a binary representation of SqlHierarchyId to the passed-in BinaryWriter.
• Reparent() - Used to re-parent a node.

• ToString() - self explanatory

Spatial Data:

Spatial data types are the backbone of Geographical Information Systems (GIS).  GIS technology can be used for scientific investigations, resource management, asset management, archaeology, environmental impact assessment, urban planning, cartography, criminology, geographic history, marketing, logistics, and other purposes. Google Earth is a good example of a Geographical Information System.

Geography and Geometry

The Geography data type uses the same methods but the data type reflects the fact the we live on a curved 2D surface popularly known as the earth.

The Geometry data type supports a flat 2D surface with XY coordinates for points.  Points can be on lines, on line string and mark the edges of polygons.  Here are some of the new functions that were created to be used on Geometry and Geography Data Types:

• STIntersects() - Returns 1 if a geometry instance intersects another geometry instance. Returns 0 if it does not.
• STArea() - Returns the total surface area of a geography instance.

• STDistance() - Returns the shortest distance between a point in a geography instance and a point in another geography instance.

FileStream Data Type:

Allows large binary data to be stored in the file system and yet remain an integral part of the database with transactional consistency. This enables scale-out of large binary data to be managed by the database and yet stored on cheaper storage and file systems. This would be perfect for document management systems like SharePoint. Hopefully, this is one of the driving forces behind Microsoft creating it.

Applications can use a new storage type VARBINARY(MAX) FILESTREAM to define table columns and store large binary data as files in the file system instead of as a SQL BLOB. Applications can use T-SQL SELECT/INSERT/UPDATE/DELETE statements to query and modify FILESTREAM data.

If you are working on multiple computers then it can be a hassle to unify your toolset. Wouldn't it be great to have all your favorites apps with you all of the time! Well you can! Just install them on your thumb drive. Anyway, here are some things that you can do with your thumb drive:

1. Install PortableApps - This is first in the list for a reason. This application will install a menu similar to the start menu on windows but it will run directly from your thumb drive. Then you can download software and launch applications from the menu. Some of the apps you will find on their website include Firefox, GIMP, 7-Zip, OpenOffice and FileZilla. By the way everything at portable apps is 100% free.
2. Install MAME - This program is for playing those retro arcade games. In order to be legal you must own the original game before you download the ROM. Over 5800 ROMS can be found at mameromlinks.com.
3. Run Linux - Damn Small Linux, Xubuntu, Fedora Live and Puppy Linux can run directly from your thumb drive. They include a bunch of productivity applications and again they are free.
4. Use MojoPac - is a software virtualization product that turns any USB 2.0 storage device into a portable computing environment. The no-restrictions version isn't free ($29.99 for a license), but there's a free version that gives you a virtual XP desktop—and that might be just what you need.
5. Run XAMPP - XAMPP is an easy to install Apache distribution containing MySQL, PHP and Perl. To run it on a thumb drive, extract the files onto to the main directory (i.e. F:\xampplite\). Then to start XAMPP simply open the main directory, and double click xampp-control.exe
6. Install RockXP - A great tool which will allow you to recover windows passwords or keys, change keys, display system password, and more.
7. Restore Files with Restoration - Restoration is an easy to use and straight forward tool to undelete files that were removed from the recycle bin or directly deleted from within Windows
8. Download Torrents with UTorrent - Hands down the best torrent client you will ever find for Windows.
9. Store important documents with TrueCrypt - a free open-source disk encryption software that works with Windows Vista/XP, Mac OS X, and Linux
10. Store important password with Keepass - Today you need to remember many passwords. You need a password for the Windows network logon, your e-mail account, your homepage's FTP password, online passwords (like website member account), etc. etc. etc. The list is endless. Also, you should use different passwords for each account. Because if you use only one password everywhere and someone gets this password you have a problem... A serious problem. The thief would have access to your e-mail account, homepage, etc. Unimaginable.KeePass is a free open source password manager, which helps you to manage your passwords in a secure way. You can put all your passwords in one database, which is locked with one master key or a key file. So you only have to remember one single master password or select the key file to unlock the whole database.

If you don't have a thumb drive then head over to newegg. You can pick up a 8GB thumb drive for about $24.

For years, Flash has been the forerunner for developing cross-platform web applications. When Silverlight was released many people dismissed it as an alternative because it was lacking cross-platform support. However, today that has all changed. I am pleased to announce that version 1.0 of Moonlight, a Linux-based Silverlight plug-in, has been officially released.

The Moonlight project was a precursor to the Mono project which was aimed at porting the .NET framework to *nix systems. Both projects are sponsored by Novell who is committed to making Linux a first-class platform for multimedia and Rich Internet Applications. The Moonlight plug-in will be available for all major Linux distributions including openSUSE, SUSE Linux Enterprise, Fedora, Redhat and Ubuntu. The release is a result of the technical relationship established by Microsoft and Novell which has helped to provide increased inoperability between Windows and Linux. In addition to the Moonlight plug-in, Microsoft is also offering a set of codecs which will allow Linux users to use a wide variety of WMV and WMA formats in addition to MP3.

The Mono project founder and Developer vice president at Novell, Miguel de Icaza, stated "Moonlight brings the benefits of Silverlight's popular multimedia content to Linux viewers. This first release delivers on the goal of breaking down barriers to multimedia content and creating parity in the user's viewing experience regardless of whether the user is on Windows or Linux."

The Moonlight plug-in is currently available for download as a Firefox plug-in and it can be installed using only a single click. The plug-in is a Silverlight 1.0 implementation which means that Javascript does all the heavy lifting in the release. A Silverlight 2.0 compliant release will mean the implementation of a ECMA CLR execution engine which means a lot of sleep less nights for Miguel de Icaza. If you are interested in test-driving Moonlight then you can head over to Novell's website and download the Live CD.