Alternatives to SSMS

I always wanted a small query analyzing tool to test my code snippets,  found the following links useful. 

http://www.albahari.com/queryexpress.aspx
Neat thing is the author, Mr.Joseph Albahari has provided the source code as well.

http://www.linqpad.net/
Amazing tool to write code snippets in TSQL, LINQ, C#, F# by the none other than Mr. Joeshph Albahari

Get rows between any two arbitrary numbers

A week ago, I was asked to write a query to return rows between 30 and 40 of a particular ID column of a particular table and the result set should be sorted. There are 4 different ways of achieving this:
For this example, I took the products table from Northwind database.

Method 1: Using the between keyword

select productid, productname from Products where ProductID between 30 and 40

Method 2: Using the except keyword (set theory)

select productid, productname from Products where ProductID < 41 except
select productid, productname from Products where ProductID < 30 order

Method 3: Using ranking function and a subquery

select temp1.rownum, temp1.ProductName from (select ROW_NUMBER()
over(order by productId desc) as rownum, ProductName
      from Products) as temp1 where temp1.rownum between 30 and 40

Method 4: Using CTE and ranking function

with temp as
(
      select ROW_NUMBER() over(order by productId desc) as rownum, ProductName
      from Products
)
select * from temp where rownum between 30 and 40

To do: 
Analysis the performance on a large dataset and compare efficiencies

Create Database Adventurework2012

1. Get the Adventureworks 2012 OLTP mdf file from
http://msftdbprodsamples.codeplex.com/downloads/get/165399

2. Copy the mdf file to
C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA'

3.  Run the following script to create the database

CREATE DATABASE AdventureWorks2012
ON (FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\AdventureWorks2012_Data.mdf')
FOR ATTACH_REBUILD_LOG

Since there is no log file, the database engine will create one for us and the engine will display the following message.

File activation failure. The physical file name "C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\AdventureWorks2012_Log.ldf" may be incorrect.


New log file 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\AdventureWorks2012_log.ldf' was created.
Converting database 'AdventureWorks2012' from version 705 to the current version 706.
Database 'AdventureWorks2012' running the upgrade step from version 705 to version 706.

Refresh the Databases folder to see Adventureworks2012