We all know backups are good but most of the time you won’t need a backup from a year ago. Just keep enough copies to recover from a possible failure and get rid of the rest. The other day I was working on cleaning up old security camera images which become meaningless very quickly. The images are uploaded to Amazon S3. My first approach was to delete the older ones by a scheduled script but then I discovered an easier and more effective way.

Let AWS do the work!

It’s possible to loop through thousands of objects and delete them but the alternative is to set an expiration date for each object. To activate this select the folder and make sure the properties panel is visible. Expand the Lifecycle section and click Add rule. Add a number of days for the expiration. Make sure “Apply to Entire Bucket” is checked so that any newly uploaded files comply with this rule. It’s easy as that!

S3 Lifecycle

One thing to note is that this process runs once a day. So don’t expect to get your bucket cleaned up immediately. But also don’t forget to check the next to ensure everything is working as expected!


Amazon Web ServicesDevelopment comments

Sometimes you need a microsite with no server-side code. All you need is to display a pretty-looking entry page. In such scenarios you don’t need to use your own servers and use your precious resources on such trivial sites. The 2 ways to achieve this (that I know about) are:

  • Using Amazon Web Services S3
  • Using GitHub Pages

Both methods are very well documented. You can find the links to the official tutorials.


First, my favourite IT company: Amazon! S3 method requires to create a bucket with the name of your domain or subdomain. In the properties enable Static Website Hosting and point to your index document. In order for this to work, you have to use AWS Route 53 as your DNS provider. In Route 53, all you have to do is define an A record as an alias and select the S3 bucket that contains your site. If you have multiple accounts make sure that Route 53 DNS records and S3 bucket are under the same account. Otherwise you cannot point to the bucket as en endpoint.

GitHub Pages

GitHub method is also quite easy. All it takes is create a public repository, create a branch called “gh-pages” and check in your source code. To let GitHub know that you want to host a site there, you have to create a file called CNAME which includes the domain name. And in your DNS settings you have to point your site to GitHub’s IP address. The downside of this method is, obviously, your site will stop working if GitHub decides to change their IP address.


Electronics comments

Playing around with the development boards and gadgets I was wondering about the differences between Microcontrollers (MCU) and Microprocessors (MPU). I found a nice concise and informative article (link in the resources section). For me the highlights of the document and the comparison drawn bettern MCU and MPU are:

  • MCU uses on-chip embedded memory, this leads way to a short start-up period. Downside of embedded memory is that there is limited space. MPUs use external memory.
  • MCUs need one single power rail whereas MPUs need several (for core, DDR etc.)
  • MPUs have much more processing power than MCUs. A full operating system’s requirements are more likely to be met by MPUs.
  • For numeric-based (number-crunching scientific type) applications and heavy user interfaces MPUs are more suitable
  • MCUs consume far less power than MPUs

So in a nutshell, MCUs are generally a better choice for low-cost, low-power projects. For example, for small devices like remote controls, smart meters MCUs are preferred.MPUs perform much better in more resource-demanding systems.


Gadget comments

If you like playing with gadgets like me you will love this one. It’s very tiny (as the name implies) and as of version 3.0 it is Arduino-compliant. If you install Teensyduino addon on top of Arduino IDE you can compile and upload your sketches to Teensy just like Arduino. This tiny things rocks a 32-bit ARM Cortex-M4 processor.


Also it is quite cheap and it’s size allows it to be used in a variety of projects. Strongly recommended for gadget-lovers.



Programming comments

I need a large dataset to practice on SQL server while preparing to exams. One idea is to generate random data but there is no fun in that. Because it is hard to create real-life scenarios but gibberish data. Then I found out that IMDB actually releases all their data and allows usage for non-commercial applications.

Data Analysis

Unfortunately, IMDB data doesn’t come in a very structured format. You have to deal with the exceptional cases and create your own data structures. I found some nice info in a Github repo called imdb-blaster (link in the resources section).

I started with the main file to parse: Movies.lst. This file contains movies, TV shows and their episodes. I want to store these in separate tables so I wanted to treat them separately. What they look like in a nutshell is as follows:

  • Movies: The Lord of the Rings: The Return of the King (2003)    2003
  • TV Shows: “Seinfeld” (1990)                    1990-1998
  • Episodes: “Seinfeld” (1990) {Male Unbonding (#1.4)}        1990

All TV Shows are enclosed with double-quotes. There are also some other optional attributes that need to be taken into account. Such as deleted items are marked with like “Seinfeld” (1990) {Good News, Bad News (#1.0)}     1989

Start parsing

I don’t like regular expressions too much as they are very hard to read. But they are very helpful and powerful in scenarios like this. The goal is validating and parsing it to groups so that it can be inserted easily to a relational database. So I developed 3 regular expression patterns for the type of data in the file. One thing I learned about regular expressions is that you can group parts by using parenthesises. This creates an unnamed group. If you want to name them you can use angle brackets and assign a name to group such as: (subexpression)

Parsing movies

As most regular expressions tend to wind up with hard-to-read complex queries this was no exception! Even if the structure is simple because of the optional parameters and exceptional cases it became very complex very soon. Here’s the sample code to run in LINQPad to parse a sample line for a movie:

string line = @"America the Punchline (2009) 		2009";
string pattern = @"(^.*)\((\d{4}|\?{4})(/I|II|III)?\)([ ]?)()(\(V\)|\(TV\))?\t*(\d{4})$";
Console.WriteLine(Regex.IsMatch(line, pattern));

MatchCollection matches = Regex.Matches(line, pattern);
foreach (Match match in matches)
	Console.WriteLine("Group count: {0}", match.Groups.Count);
	foreach (Group matchGroup in match.Groups)
		Console.WriteLine("{0}", matchGroup.Value);

And the output is:

Group count: 8
America the Punchline (2009)     2009
America the Punchline


The first group is the whole string. The second one is the title, the third is the year. There is an optional group because some titles come with I, II or III after the release date. I have no idea what that means but had to break it into a group to avoid an unmatch. The last two parameters are the type (V: Video, TV: Television, VG: Video game) and another date. I don’t know why the movies have two dates and they almost always match so I’ll ignore one of them. So the actual code is very simple:

if (regexMovie.IsMatch(line))
	Match match = regexMovie.Match(line);

	commandMovie.Parameters.AddWithValue("@Name", match.Groups[1].Value);
	commandMovie.Parameters.AddWithValue("@ProductionYear", match.Groups[2].Value);
	commandMovie.Parameters.AddWithValue("@IsDeleted", (match.Groups[5].Value == ""));
	commandMovie.Parameters.AddWithValue("@ReleasedFor", match.Groups[6].Value);

All the hard work is done by regular expressions already. All we have to do is to check is a line matches a movie definition and if it does just insert the data that is already broken up into groups.

Parsing the rest

I’ll publish the full source code in a Github repository and will update this post with the link. But I guess to start parsing the movies file the other two patterns suffice for now: TV Show Pattern: (^””.””) (((\d{4}|\?{4})(/I|II|III)?))\t.(\d{4}-\d{4}|\?{4})$ Episode Pattern: (^””.””) ((\d{4}|\?{4})(/I|II|III)?) {(.?)(#(\d).(\d))}\t*(\d{4}|\?{4})$


Electronics comments

I was trying to avoid soldering but looks like at some point it is mandatory to learn how to do it. Got myself a basic toolkit:

Soldering Tools

There are plenty of tutorials around. I enojyed Iyaz Akhtar’s KnowHow episode dedicated to soldering and EEVBlog’s tutorial. After spending some time around here’s the fruits of my efforts:

Soldered Circuit

Of course the components soldered on this board are meaningless and they don’t do anything. But it was a good practice.

I was surprised to find out that desoldering was an easy process. All you have to do is heat the iron, touch the solder and liquify it and then vacuum it using the desoldering pump.

Desoldered Resistor


ArduinoGadget comments

Basically the fun with playing gadgets such as Arduino, Gadgeteer and others comes from sensors. Because they are how your system interacts with the real world. And writing code that acts upon some readings from the real world is fascinating!

So let’s get started with using the motion sensor. I have a very generic sensor from Parallax and fortunate enough to find an article that explains exactly what I needed: Using motion sensor with Arduino. Check out the resources section down below to access the original article. Unlike the LCD, it’s quite simple to connect the motion sensor to Arduino. The sample code lights the onboard LED when the motion sensor detects a motion. Check out the video for a short demo. When the sensor “senses” motion, it goes red and the LED starts blinking.

I think the next step would be to connect both LCD and motion sensor to display the alarms on the display. Hope I can make that project happen without too much delay.


ArduinoGadget comments

I’ve been looking for a good tutorial to connect the LCD screen to Arduino. There are a lot of tutorials everywhere but Interestingly most of them just show the final version with all the cables jumbled up. After a lot of looking around I finally found a simple, step-by-step tutorial with clear illustrations: http://www.codingcolor.com/microcontrollers/connecting-a-lcd-to-arduino/

This is a great tutorial and I recommend it to anyone who wants how to connect LCD to an Arduino.

Arduino with LCD


ArduinoGadget comments

The most popular development boards is without a doubt Arduino. It is open-source and it’s been around since 2005. There are lots of different models and libraries. My goal for now is just get started and see it in action. To achieve this, first stop is the official site where you can download the drivers and IDE. After download simply unzip the contents to a folder.

A great place to start playing with it is Adafruit. You can find the link for Arduino tutorials in the resources section below. I simply started with Lesson 1 and changed the speed of the onboard LED.


So it takes minutes to get started with it and comes loaded with a bunch of examples so they made it very easy for beginners.


Error: stk500_getsync() not in sync resp=0x00 This error means that Arduino couldn’t be found in the COM port. To resolve the issue:

  • Use Device Manager and make sure Arduino is connected. It should be listed under Ports node. Note the COM port number
  • Then from Tools –> Serial Port make sure the correct port is selected.


DevelopmentGame comments

Kodu is basically a tool for kids to develop games without writing code. You can select form a bunch of items to place in your “world” and use the visual programming language to program the actions. The best part is you don’t have to start from scratch. You can browse the existing worlds play with it and start editing it right away. For example below is a screenshot of a game developed by California Institute of Technology:

Kodu Mars Rover

You can download any game, play and modify to your liking. It’s very easy and fun way to build games. Especially if you have young children or you are one!


.NET Gadgeteer comments

Soon after I started playing with Gadgeteer it became a bit messy. Components were dangling by the cables and there was no way of keeping them steady. But it is about to change as I have discovered the Tamiya Universal Plate.

Although it’s main purpose is not for Gadgeteer it works great with it. I bought mine from Proto-Pic for around £10 so it’s a small price to pay to keep my design tidy.

Here’s the link for it: http://proto-pic.co.uk/tamiya-70172-universal-plate-l-210x160mm/?gclid=CLT44pym1bgCFbHKtAoddxEAjw

And here’s how I can organize my Gadgeteer designs:



I bought 3mm screws and nuts to pin the components but saw some plastic long pins that can be used for the same purpose and would work better as screwing all components is a bit tedious. I’ll update the post if I find those pins.

DevelopmentNetworkingSecurity comments

HSTS (HTTP Strict Transport Security) is a security policy where a web server instructs the client that the website they are calling can be used with HTTPS only. By using tools like Wi-Fi PineApple and sslstrip a man-in-the-middle attack can be carried out easily. The attacker can redirect the victim to HTTP version of the site they are connecting to and after the login info is submitted in clear-text and captured the victim is redirected back to HTTPS. By using HSTS the client browser always makes the calls over HTTPS hence reducing the risk of MitM attack. It’s not bullet-proof but still an improvement over not using it.


HSTS is basically a response header. In an ASP.NET application it can be added in the web.config file as below:

        <add name="Strict-Transport-Security" value="max-age=31536000" />

And when you visit this site over HTTPS response looks like:


This header tells the browser to use only HTTPS from now on for 1 year. Also, the response header can be added in the BeginRequest event handler:

protected void Application_BeginRequest(object sender, EventArgs e)
	protected void Application_BeginRequest(object sender, EventArgs e)
		switch (Request.Url.Scheme)
			case "https":
				Response.AddHeader("Strict-Transport-Security", "max-age=31536000");
			case "http":
				var path = "https://" + Request.Url.Host + Request.Url.PathAndQuery;
				Response.Status = "301 Moved Permanently";
				Response.AddHeader("Location", path);

HSTS header only works with HTTPS. So you can not observe it HTTP responses. That’s why redirecting to HTTPS is required. Because if the client uses HTTP all the time and if there is nothing forcing the user to use HTTPS, even if you have HSTS turned on, the client will never receive those responses. So at this point even if an attacker is redirecting the user to HTTP, the browser will always make the requests over HTTPS.


  • Not all browsers support it. It works with Chrome and Firefox but not with IE and Safari
  • Cannot protect for the first call: If the user is making a call to a site for the first time and there is an attacker in between it wouldn’t work. At least it would start working after the attacker is finished with the attack and redirected the user back to HTTPS.


Electronics comments

If you’re going to do something you need the right tools. The last time I was dabbling with I didn’t quite enjoy it but that was mostly because I didn’t have the right tools. The most daunting part for me was cabling. With the jumper cable set I had it was just too painful.

Get your cables right

This time I’m prepared for all occasions: I have Male – Male, Male – Female and Female – Females cables which cover all combinations.



Multimeter: I realized a multimeter is an essential tool if you want to build your own circuits. I added a reference for an excellent tutorial which teaches you how to use one in a few minutes. By using it, I was able to measure voltage and resistor values in no time.

LCD Display: Also, I think it’s almost impossible to build a cool project without a display. It can be used for debugging and informing the user so very helpful to have it in the mix. They are so cheap that I ordered 5 of them for about £7 on eBay. So I guess there is no excuse for not buying one!

Resistors: Real resistors! I hated my old blue resistors with 5 bands. I couldn’t read the values. Colors were not clear. But now finally I got a bunch of standard 3-band yellow ones which make it easier to read. (Actually I have a multimeter now, so I can just use it to measure the resistance anyway.)

Breadboards: I had a big nice one but I think in order to run things in parallel I’ll need a few more of the smaller ones. Also I’m trying to learn how to solder so I now have a couple of solder boards to practice. Push buttons: I think to control the flow they are definitely needed.

New Components

Let’s get crackin’

I just built a simple circuit to light a LED when the push button is pressed. I know it’s not impressive by any means but I enjoyed building something from scratch. Familiarized myself with my tool shed and looking forward to build more complicated things.


Gadget comments

Yes!!! Finally it arrived. It looks very tiny and elegant. This is what the package and contents look like:

Leap Motion


Good thing it comes with 2 USB cables of different lengths. One is bout half a meter, probably for laptop users and the other is about 2 meters, better suited for desktop users like myself. It doesn’t come with any instructions or user guide but a card that tells you to go to leapmotion.com/setup. And that’s where I went.

The setup file is about 56MB. As this is a new file, Norton 360 didn’t like it and issued a warning. Another interesting note is right after I plugged it in Skype icon started blinking. I checked the window and it was saying “Your video works fine”.  as I don’t normally keep my webcam plugged in, the video doesn’t work. But it became happy to detect a camera! I’m not sure if it would work with Skype though. Probably not.

App Store

After the installation you have to login to Airspace which is the app store for Leap Motion. I logged in with my LeapMotion account. It comes with a few free apps. Cut the Rope didn’t run unfortunately. Didn’t bother to give an error message either. So I tried the others. Lotus looks like a weird psychedelic app. As far as I could understand you make music with your fingers. Here’s a little demo of how it looks:


What’s more interesting than downloading and using apps is developing one on my own! In order to do this you have to sign up for a developer account at: http://developer.leapmotion.com After the sign up you download their SDK. So far I managed to install the drivers, tried a few apps and downloaded the SDK and I’ll post the development experience in another post.

Final words

I’m not so sure if this gizmo will take off but as long as it’s an interface for the OS, not some custom apps, I don’t see much use for it. Keeping hands in the air for long tasks is tiring. I think it only might make some games more fun but that’s a very small market.

HardwareMobile comments

If you have an Android phone that supports NFC, you can save and change some settings very easily based on your location. All you need is some blank NFC tags like shown below, which are very cheap and a free app called NFC Task Launcher.

NFC Tags

NFC for the uninitiated

NFC stands for Near Field Communication. It’s a set of standards build on RFID that allows wireless communication between devices in a close proximity. It is commonly used in contactless payment systems.

Manage tasks with the task launcher

It’s a very intuitive and easy to use app. First, you select an action group (like WiFi On/Off, BlueTooth On/Off).

NFC Task Launcher

Then you configure the action (like Enable/Disable) and finally you touch to your tag to associate the task with it.

Secure your phone outside the house

After playing around with tools like WiFi PineApple I’m now even more afraid of wireless networks than ever. Thinking about it, I don’t connect to open networks on purpose and if my phone connects to one of them that means something fishy is going on. So why should I have WiFi when I’m not using my own? But of course turning it on and off every time I enter/leave the house is cumbersome. My solution is: doing it with NFC. Although it’s not completely automated, I just touch my phone to the red tag I stuck to my door when I leave the house and the WiFi is turned off. When I enter the house, I touch on the green one it is turned back on. Simple as that!

NFC Task Launcher


Gadget comments

I know they are about to release XBox One and they have already released Kinect for Windows but I still only have a Kinect for XBox for 360. I’m hoping to create a quick and sample application to test my Kinect and discover what’s in Kinect SDK so that I can remove it for a while from my gadgets-pending-to-be-tested-and-played-with list.

Until I buy a better and more recent hardware I just decided to utilise my existing sensor. I followed Channel9’s video tutorials and here is the code to display a video output of the sensor on the screen:


Source code:

using System.Windows;
using System.Windows.Media;
using System.Windows.Media.Imaging;
using Microsoft.Kinect;

namespace KinectDemo
	public partial class MainWindow : Window
		KinectSensor _sensor;

		public MainWindow()

		private void Button_Click(object sender, RoutedEventArgs e)

		private void Window_Loaded(object sender, RoutedEventArgs e)
			if (KinectSensor.KinectSensors.Count > 0)
				_sensor = KinectSensor.KinectSensors[0];

				if (_sensor.Status == KinectStatus.Connected)
					_sensor.AllFramesReady += _sensor_AllFramesReady;

		void _sensor_AllFramesReady(object sender, AllFramesReadyEventArgs e)
			using (ColorImageFrame colorFrame = e.OpenColorImageFrame())
				if (colorFrame == null)

				byte[] pixels = new byte[colorFrame.PixelDataLength];

				int stride = colorFrame.Width * 4;
				image1.Source = BitmapSource.Create(colorFrame.Width, colorFrame.Height, 96, 96, PixelFormats.Bgr32, null, pixels, stride);

		void StopKinect(KinectSensor sensor)
			if (sensor != null)

		private void Window_Closing(object sender, System.ComponentModel.CancelEventArgs e)

I think for this amount of code and the simplicity it is a pretty good result:


Probably I will continue my conquest of Kinect after I get the latest one, but for now I’ll just call it.


Gadget comments

I have a number of electronics kits and boards like Netduino, Arduino and Gadgeteer. Among all the cheapest one I’ve ever seen is Texas Instrument’s MSP430 LaunchPad. Last year I bough one of these just because it was so cheap but couldn’t spend much time on it. I recently found out it has dropped to £8 and I decided to buy the latest version. Now that I have 2 of these babies I definitely need to spend some time to play with them.

MSP 430

Looks like there are a few differences between the two. The one on the left in the picture above is the old version. The new version comes with better controllers and male pins on the side.


First thing to do is go to its official site and download Code Composer Studio to develop programs to run on it. It is an Eclipse-based IDE so I’m hoping it won’t take to long to get acquainted with it.

First Program

Good thing there are plenty of tutorials about it. I decided to follow one of the simplest ones which just blinks one of the on-board LEDs. To be honest, the code looks hideous:

unsigned int i = 0; 
void main(void)
  P1DIR |= 0x01;

  for (;;)
    P1OUT ^= 0x01;
    for(i=0; i&lt; 20000; i++);

I deleted the comments to keep it short. But even with detailed comments it looks hard to develop complex applications with this. Anyway, I’ll see how far I can get with it. So far, for this amound of work, the results are not so bad:


Gadget comments

After Raspberry Pi opened the way for low-cost small computers, a number of them started to show up in the market. One of the most recent ones gaining ground is BeagleBoard.

Beagle Logo

I just bought the cheap version which is called BeagleBone black which looks something like this:


Nice looking little fellow, right?

Getting Started

I followed the getting started guide which you can find on the home page. Also the link in the resources section. When I plugged it in to USB it is recognized as a removable device. Then downloaded and tried to install the drivers as suggested but no luck. Apparently the drivers are unsigned and Windows 8 didn’t allow me to install them. Googling a little bit I found a step-by-step guide to change the machine settings so that unsigned drivers can be installed. I followed the steps and it worked like a charm. The original URL is in the resources section also the steps are below.


To be able to install drivers on Windows 8 follow the steps below:

1. Press win+c on the keyboard to bring up the charms side bar (or move mouse to right top corner of the screen)
2. Click the Settings button.
3. Click the Change PC Settings at the bottom of the sidebar.
4. On screen that shows up, select the General option from the sidebar then scroll down the page that appears.
5. Click the Restart now button under the Advanced startup section.
6. You will momentarily see the restarting screen, then it will switch to a blue screen titled "Choose an option"
7. Click the Troubleshoot button.
8. Click Advanced options.
9. Click Startup Settings
10. Click Restart
11. You should then see a Startup Settings screen after your computer reboots.
12. Press 7 or F7 on your keyboard to Disable driver signature enforcement
13. Now Windows 8 will continue starting up.
14. Log-in as normal, and then run BONE_D64.exe again
15. Now you should see 4 warning dialogs about "unsigned driver installation", click OK for all of them.


So far so good:

BeagleBone Black

I can connect to the web server running by default. I’ll try to explore what I can do with it further. Let’s see what it is capable of.


Security comments


EMET (Enhanced Mitigation Experience Toolkit) is a free utility that helps prevent memory corruption vulnerabilities and is designed to prevent hackers from gaining access to a system. I haven’t used it before but according to the documentation the new features are:

  • Certificate Trust: Allows to detect Man in the Middle attacks that leverage fraudulent SSL certificates
  • ROP mitigations: Block exploit that leverage the Return Oriented Programming exploitation technique.
  • Early Warning Program: Allows enterprise customers and Microsoft to analyze the details of an attack and respond effectively.
  • Audit Mode: Provides monitoring functionalities for testing purposes.
  • Redesigned User Interface: Streamlines the configuration operations and provides accessibility.

Under the hood this tool performs very low-level operations and honestly I don’t know what they all mean. For me the biggest benefit was learning about advanced attacks and exploits and how to avoid them. I strongly recommend reading the user’s guide. Apparently there are many interesting attack techniques when it comes to exploiting the memory. EMET provides mitigation methods against these attacks. For instance:

  • Structured Exception Handler Overwrite Protection (SEHOP): This mitigation was shipped with Windows Vista SP1 but with EMET it can be used in previous versions as well.
  • Data Execution Prevention (DEP): This mitigation exists since Windows XP but applications need to be compiled with a special flag to opt-in to DEP. With EMET allows applications without that flag to opt as well.
  • Heapspray allocation: “When an exploit runs, it often cannot be sure of the address where its shellcode resides and must guess when taking control of the instruction pointer. To increase the odds of success, most exploits now use heapspray techniques to place copies of their shellcode at as many memory locations as possible.”
  • Mandatory Address Space Layout Randomization (ASLR): ASLR randomizes the address space of the loaded modules so that attackers cannot predict their locations. Like in DEP, EMET allows applications without the special flag to use this feature.


Certificate Trust

I was wondering about this feature as it’s more “visible” than the others. Basically what you do is define protected websites and pinning rules. Pinning Rule is essentially importing an SSL certificate’s thumbprint. Once you import it you create a protected website which is simply entering the domain name of the site and you associate the certificate with the site.


From now on if the certificate changes EMET displays a message like below:


Actually, I didn’t want to spend a great deal to test it first so the easiest way to test is running Fiddler (allow it to install it’s own certificate) and visit login.live.com. EMET comes with that site already configured so as soon as it notices the certificate doesn’t match its rule it fires the alarm. Looks like a nice feature but it only works with IE which I almost never use other than testing my code. Also I was expecting more fuss about it actually. It just displays the window for a few seconds and you can keep visiting the site. If for instance you were looking at something else in that brief period while the site is loading you may completely miss the fact you are being “Man-in-the-Middle”d. Overall, it’s better to have it than not having it at all I think.


CertificationSQL Server comments

This the Part 2 of my Study notes for 70-461 exam. You can find the Part 1 here

04. Aggregate Functions

  1. GROUPING SETS Using GROUPING SETS operator returns the same result set generated by using a GROUP BY, ROLLUP and CUBE operator. For example, this query generates the following result set:

     SELECT o.TerritoryID, YEAR(OrderDate) AS SaleYear, SUM(SubTotal) AS Total
     FROM Sales.SalesOrderHeader o
     GROUP BY GROUPING SETS ((), o.TerritoryID, YEAR(OrderDate))
     ORDER BY SaleYear ASC, o.TerritoryID ASC, Total ASC

    Output: Query Output

    Source: http://technet.microsoft.com/en-us/library/bb522495(v=sql.105).aspx

  2. RANK / DENSE_RANK / NTILE / ROW_NUMBER These functions may be quite helpful for reporting queries.

    RANK – DENSE_RANK: These functions help to order rows by assigning an incrementing number based on the criteria used in ORDER BY clause. The difference between them is rank skips the next value when there are two of the same one and dense_rank doesn’t.


     SELECT o.TerritoryID, YEAR(OrderDate) AS SaleYear, SUM(SubTotal) AS Total,
     FROM Sales.SalesOrderHeader o
     GROUP BY o.TerritoryID, YEAR(OrderDate)
     ORDER BY Rank, SaleYear ASC, o.TerritoryID ASC, Total ASC


    Query Output

  3. NTILE Works like rank but it takes an integer parameter (n) and partitions the results to n-pieces. For example, the query below with NTILE returns 40 rows. As n is 4 in this example, the first 10 rows got rank 1 and 11 – 20 got rank 2 and so forth. Example:

     SELECT o.TerritoryID, YEAR(OrderDate) AS SaleYear, SUM(SubTotal) AS Total,
     FROM Sales.SalesOrderHeader o
     GROUP BY o.TerritoryID, YEAR(OrderDate)
     ORDER BY Rank, SaleYear ASC, o.TerritoryID ASC, Total ASC


    Query Output

    ROW_NUMBER: Just increments the rank no matter what. Example:

     SELECT o.TerritoryID, YEAR(OrderDate) AS SaleYear, SUM(SubTotal) AS Total,
     FROM Sales.SalesOrderHeader o
     GROUP BY o.TerritoryID, YEAR(OrderDate)
     ORDER BY Rank, SaleYear ASC, o.TerritoryID ASC, Total ASC


    Query Output

Source: http://msdn.microsoft.com/en-us/library/ms189798.aspx

05. Analytic Functions

LAG / LEAD: When I first saw these I instantly loved them! They prevent an unnecessary costly self-join to access previous and next rows in a result set.

So for example check out the following query and its output:

SELECT o.TerritoryID, YEAR(OrderDate) AS SaleYear, SUM(SubTotal) AS Total,
LAG(SUM(SubTotal)) OVER(ORDER BY YEAR(OrderDate)) AS PrevYear,
LEAD(SUM(SubTotal)) OVER(ORDER BY YEAR(OrderDate))  AS NextYear
FROM Sales.SalesOrderHeader o
WHERE o.TerritoryID = 1
GROUP BY o.TerritoryID, YEAR(OrderDate)
ORDER BY SaleYear ASC, o.TerritoryID ASC, Total ASC


Query Output

Notice that every row has the previous row’s current value in its PrevYear column. Except the first one obviously as it doesn’t have a previous year. And the same logic applies to the NextYear value with the difference that LEAD function is used to obtain those values.

Source: http://msdn.microsoft.com/en-us/library/hh213234.aspx


One important topic to grasp is transaction isolation levels. I had a few questions related to this topic. Also it is crucial to create high-performance queries. The isolation levels you need to pay attention are:

READ UNCOMMITTED: This one is most loose one as it allows dirty reads. But much faster than the others as it doesn’t lock the rows.

READ COMMITTED: It doesn’t allow reading uncommitted data. This is the default level.

REPEATABLE READ: Prevents dirty reads. Concurrent operations cannot modify or delete data.

SERIALIZABLE: This level takes it one step further: In  addition to REPEATABLE READ it prevents concurrent operations from inserting data too.

SNAPSHOT: Specifies that data read by any statement in a transaction will be the transactionally consistent version of the data that existed at the start of the transaction.

Source: http://msdn.microsoft.com/en-us/library/ms173763.aspx