Returning random records.. MS SQL Server vs. MySQL

Sometimes it's useful to return a number of random records from a database. For example a "tip of the day" functionality or displaying random products in an e-commerce site.
In MS SQL Server, this can be done using a technique that depends on the newid() function that returns a GUID . This technique is well described in this SQLTeam article.
I tried to use the same technique in MySQL using a query like:

select name from country order by uuid() limit 10;

Thinking that the uuid() function will do the same job as newid() in MS SQL Server.
for my astonishment, this could not do the job at all, the same 10 records are always returned.
After changing the query to :

select name,uuid() from country limit 10;

the results where:

+------------+--------------------------------------+
| name       | uuid()                               |
+------------+--------------------------------------+
| Anguilla   | 85f55cb0-2620-102a-9f3b-3e89f326e405 |
| Argentina  | 85f55cf4-2620-102a-9f3b-3e89f326e405 |
| Australia  | 85f55d0f-2620-102a-9f3b-3e89f326e405 |
| Austria    | 85f55d2a-2620-102a-9f3b-3e89f326e405 |
| Belgium    | 85f55d64-2620-102a-9f3b-3e89f326e405 |
| Brazil     | 85f55d73-2620-102a-9f3b-3e89f326e405 |
| Canada     | 85f55d80-2620-102a-9f3b-3e89f326e405 |
| Chile      | 85f55d8d-2620-102a-9f3b-3e89f326e405 |
| China      | 85f55d9b-2620-102a-9f3b-3e89f326e405 |
| Costa Rica | 85f55da8-2620-102a-9f3b-3e89f326e405 |
+------------+--------------------------------------+

Looking at the results above, the uuid() function seems to return sequential unique identifier. which is not the intended behavior by any means (in my case of course).

So..I tried another technique that would fail in SQL Server(As I may explain why later)

select name from country order by rand() limit 10;

and this worked great!! Each time I run this query, different 10 country names are returned.

Why does using rand() to get random records in SQL Server fail? And how to overcome this?

Ok, maybe I'll talk about this later in another post. I want to sleep.

Editing SQL Server 2000's DTS packages in SQL Server 2005

In MS SQL Server 2005, DTS was replaced with SSIS.
Fortunately, Microsoft appreciates the cost and effort spent by companies and DBAs to create DTS packages. So SSIS has (Execute DTS 2000 Package) task.
This is great, But what if you want to make a small edit to the DTS package after migrating to the new version of SQL Server?
You have 2 options:

1. Import the DTS package to SSIS, it's relatively easy. And although I had minor problems when trying this, the process was successful.
   
2. If you don't want to migrate to SSIS, Microsoft provided a good download in its SQL Server 2005 feature pack called Microsoft SQL Server 2000 DTS Designer Components. which can be used to edit and save DTS packages using the same interface used in SQL Server 2000 and save it. A really good one from Microsoft!!.
   

A Singleton pattern variation

Singleton pattern is one of the famous creational design patterns in software.
The intent of this design pattern according to GoF is to:

ensure a class has only one instance, and provide a global point of access to it.

This is a simple implementation of this pattern in C#:
public class SingletonClass
{
private static SingletonClass _instance=null;
protected SingletonClass()
{
//
// TODO: Add constructor logic here
//
}

public static SingletonClass Instance()
{
if (_instance == null)
{
_instance = new SingletonClass();
}
return _instance;
}
}
This implementation depends on a static instance that is reused by all components within the same Application Domain. Sometimes, especially in web applications (which are multi threaded by nature), this can lead to problems, or it simply can be an undesirable behavior. For example, you may want to share this instance between all classes in the same request only.

To show that above technique will not give the required behavior, I a DateTime field to the class and set it in the constructor, then read it in a ASP.NET page code and display it.
public class SingletonClass
{
private static SingletonClass _instance=null;

private DateTime _creationTime;
protected SingletonClass()
{
_creationTime = DateTime.Now;
}

public DateTime GetCreationTime()
{
return _creationTime;
}

public static SingletonClass Instance()
{
if (_instance == null)
{
_instance = new SingletonClass();
}
return _instance;
}
}
And print this value in the Page_Load Event handler:
protected void Page_Load(object sender, EventArgs e)
{
SingletonClass c = SingletonClass.Instance();
Response.Write(c.GetCreationTime());
}
Try this page in different browser instances, notice that the response is always the same.

To make an instance shared at the request level, we have some candidates where to store it.
One is using thread local storage, which is an idea that I don't recommend after some research.
Another is to store the instance in the Http Context Items collection. This code is based on this idea:
public class SingletonClass
{
private DateTime _creationTime;
protected SingletonClass()
{
_creationTime = DateTime.Now;
}

public DateTime GetCreationTime()
{
return _creationTime;
}

public static SingletonClass Instance()
{
SingletonClass _instance=null;
if(!HttpContext.Current.Items.Contains(typeof(SingletonClass)))
{
_instance=new SingletonClass();
HttpContext.Current.Items.Add(typeof(SingletonClass),_instance);
}
else
{
_instance = (SingletonClass) HttpContext.Current.Items[typeof(SingletonClass)];
}
return _instance;

}
}
This code shows the technique in action:
protected void Page_Load(object sender, EventArgs e)
{
SingletonClass a = SingletonClass.Instance();
System.Threading.Thread.Sleep(1000);
SingletonClass b = SingletonClass.Instance();

Response.Write(a.GetCreationTime().Ticks);

Response.Write(" , ");
Response.Write(b.GetCreationTime().Ticks);
}

Note that the same instance is used in the life time of the request, and when you open the page in a new browser instance or refresh the same page,you'll see that a new instance was created for the new request.

Dynamic declaration of cursors in MS SQL Server

Sometimes it's required to declare and use cursors to apply some logic on the results of queries that return the same columns, but using different criteria.
For example, you format a query based on user's selection in a search screen. Then you need to apply some logic that requires cursors on the result. This logic can be paging logic (as it was the case the first time I use this technique) or whatever logic defined by your application.

This dynamically generated code can look like:

declare @sql varchar(1000)
Set @sql='Select ID,Name,JoinDate Fom Employee Where ID>10

Now, the question is, how to declare a cursor for such a dynamic query and use this cursor in static code within a stored procedure for example to apply the required logic.

This technique cames to work great:

Create Proc CursorLogic(@SQL varchar (1000))
as
declare @id int
declare @name varchar(1000)
declare @joindate datetime

Set @sql='declare cur cursor for '+ @sql
exec (@SQL)
open cur

fetch next from cur into @id,@name,@joindate
while @@FETCH_STATUS=0
Begin

print @name
-- Cursor logic goes here

fetch next from cur into @id,@name,@joindate
End
close cur
deallocate cur
Go

This creates a stored procedure that takes the @sql variable which is a SQL query.
It inserts the declare cursor statement then uses this cursor inside the stored procedure logic, just as if it was declared regularly within the stored procedure code.

This stored procedure can be called as:

declare @sql varchar(1000)
Set @sql='Select ID,Name,JoinDate Fom Employee Where ID>10
Exec CursorLogic @SQL

Set @sql='Select ID,Name,JoinDate Fom Employee Where Name like 'M%'
Exec CursorLogic @SQL


Paging in MS SQL Server: Pain ends here

Paging in SQL Server was a great pain, it does not have a built-in paging mechanism or keywords. (MySQL has the Limit keyword which makes it easy to page results but it's not standard).

Developers had to go either 2 ways:

1. Page records after retreiving from DB (using ASP.NET datagrid paging for example)
2. Make a custom paging solution to page results in the server side.

The first solution was easy and supported by the tools, but was not efficient with really large datasets.

The second option can be applied using many techniques, each has its pros and cons, a good discussion about these techniques is:
Paging of Large Resultsets in ASP.NET

In sql server 2005, things made easier using Common table expressions and the new ranking functions, especially: row_number()
Using row_number and Common table expression you can make an efficient SQL Server paging for resultsets.

For example, assume you want to return only the page #30 of the employees table sorted by name, and the page size is 10:

With EmployeesCTE
as
(
Select row_number() over (order by Name) as rownum,*
from Employees
)
Select * from EmployeesCTE
Where rownum between 291 and 300
Order by Name

The first part: (With EmployeesCTE..) creates a common table expression, which can be referenced in subsequent T-SQL statements and you can consider it as an (on the fly) view.
The row_number function returns the record number based on the order specified in the OVER clause.

The second part is the easy one, now you have a simple view (EmployeesCTE) that contains the
record number, you can easily get the range of records you need using: Between x and y.