Are MS SQL Server stored procedures precompiled ?

A common interview question and a common answer:
Q: Why should you use Stored procedures not ad-hoc queries?
A (happily, he's sure that he knows how to answer this question): They perform better because they are precompiled at creation time, also ...(Some, other reasons that I don't really care about now)...

Is that true?

In fact, this is a common misconception that many users of SQL Server 7.0, 2000, and 2005 suffer, may be because this was the case in SQL Server 6.5. Also because inaccurate and not up to date articles, books ,and learning materials.

I quote this from msdn:

In SQL Server version 6.5 and earlier, stored procedures were a way to partially precompile an execution plan. At the time the stored procedure was created, a partially compiled execution plan was stored in a system table. Executing a stored procedure was more efficient than executing an SQL statement because SQL Server did not have to compile an execution plan completely, it only had to finish optimizing the stored plan for the procedure.

This is not the case in for SQL Server 7.0 and higher, again from msdn:

SQL Server 2000 and SQL Server version 7.0 incorporate a number of changes to statement processing that extend many of the performance benefits of stored procedures to all SQL statements. SQL Server 2000 and SQL Server 7.0 do not save a partially compiled plan for stored procedures when they are created. A stored procedure is compiled at execution time, like any other Transact-SQL statement.

Knowing this, it's common that arguments take place from time to another comparing using Stored procedures vs. ad hoc queries.

Personally, I prefer to use stored procedures because I find this more modular.

My advice is, whether you use Stored procedures or ad hoc queries, always use best coding practices when performing database operations to avoid SQL injection attacks. Be them parameterized queries, prepared statements, or whatever depending on the programming language and framework you use.

Inspiring speech by Steve Jobs (Apple CEO)

Steve Jobs speech to the graduates of Stanford University in 2005
I enjoyed it and learned from it. He gives great directions in life and work.

How to check programmatically if WSS or MOSS are installed

I came into a situation when I needed to check if a SharePoint installation exists on a system, and specifically, WSS (Windows SherePoint Servises) or MOSS (Microsoft Office SharePoint Server).

The two properties can be useful in this case, the idea depends on checking the registry.
Note that I extracted this code from the open source project SharePoint Solution Installer and made minor modifications.

To check for WSS we check the key:
[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Shared Tools\Web Server Extensions\12.0]
and the value "SharePoint" must be "Installed"

To check for MOSS we check the key:
[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Office Server\12.0]
"BuildVersion"="12.0.4518.1016"
and check that the version is 12

Here is the code:

public static bool IsWSSInstalled
{
get
{
string name = @"SOFTWARE\Microsoft\Shared Tools\Web Server Extensions\12.0";

try
{
RegistryKey key = Registry.LocalMachine.OpenSubKey(name);
if (key != null)
{
object val = key.GetValue("SharePoint");
if (val != null && val.Equals("Installed"))
{
return true;
}
}
return false;
}

catch (SecurityException ex)
{
throw new Exception("Access deined. Could not read the Windows Registry key '" + name + "'", ex);
}
}
}

public static bool IsMOSSInstalled
{
get
{
string name = @"SOFTWARE\Microsoft\Office Server\12.0";

try
{
RegistryKey key = Registry.LocalMachine.OpenSubKey(name);
if (key != null)
{
string versionStr = key.GetValue("BuildVersion") as string;
if (versionStr != null)
{
Version buildVersion = new Version(versionStr);
if (buildVersion.Major == 12)
{
return true;
}
}
}
return false;
}

catch (SecurityException ex)
{
throw new Exception("Access deined. Could not read the Windows Registry key '" + name + "'", ex);
}
}
}

and at the top of your class:
using Microsoft.Win32;
using System.Security;

Free Microsoft E-Learning: What's New in Microsoft SQL Server 2008

Microsoft e-learning site offers free online training for SQL Server 2008
I Hope this helps getting ready for the new release...

Query to get database relations in SQL Server 2005

It is a common need to get a list of foreign key relations in a database
This simple query does this:

Select a.Column_name as [FK Column],a.Constraint_Name as [FK],c.column_name [PK Column] ,Unique_Constraint_Name As [PK]
from INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE a
join INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS b on a.Constraint_Schema=b.Constraint_Schema and a.Constraint_Name=b.Constraint_Name
join INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE c on c.Constraint_Schema=b.Unique_Constraint_Schema and c.Constraint_Name=b.Unique_Constraint_Name

Things I liked about Visual Web Developer 2008 Express

Microsoft's decision of releasing free express editions of visual studio (starting from VS 2005) has proven to be a really right step. Beginners and enthusiastic developers found express editions a great start or a chance to look into the MS world of software tools.

I used 2005 Express editions and today I downloaded Visual Web Developer 2008 Express and found nice things that I'd like to share:

• Split view: Now you can view both HTML source and Design view of web pages, this feature makes design much easier, however you need to save or synchronize for HTML changes to reflect in the design view.
  

• New Project and Item types: WcfService and AjaxWcfService
  

• Targeting Framework version: You can choose which .net framework version to target.
  

• AJAX Extensions are pre-installed

These are few things that I liked, discover more yourself by downloading Express editions.

Comments on "Efficiently Uploading Large Files via Streaming" article on 15Seconds.com

In an article Efficiently Uploading Large Files via Streaming, published on 15seconds the author tries to give a solution for uploading huge files to ASP.NET applications.
The problem according to the author is:

When uploading a file to a web server, the upload process generally requires the incoming file to be stored in memory until the upload is complete. If an uploaded file is larger than the available memory, memory usage in general and performance in particular will suffer.

And the suggested solution was to read the uploaded data directly from PostedFile.InputStream as small chunks and store them in a database.

I believe the author has totally missed the point in this article.

First: Huge uploaded files are not kept in memory, they are buffered to the hard disk. at least this is true in ASP.NET 2.0 as stated in the msdn:

Files are uploaded in MIME multipart/form-data format. By default, all requests, including form fields and uploaded files, larger than 256 KB are buffered to disk, rather than held in server memory.

Second: The solution claims that reading the file from the InputStream will be a streaming solution that avoid caching the whole file on the server, which is again not true. The ASP.NET code is called after the whole request has already been submitted to the server.

I wished to clarify this by commenting on the article or by sending to the author, but unfortunately, the site does not allow this :( .

How granting execute permission on stored procedures made easier in SQL Server 2005

In SQL Server 2000, a common problem was faced by developers who design their data access to depend on stored procedures, SQL server had db_datareader and db_datareader roles. But no such db_executer role.
This caused trouble as the user used to execute stored procedure had to be granted execute permission on every stored procedure in the database. This can be automated using scripts that grant execute permissions to the required user. But imagine how much time you'll spend debugging your application after creating a new procedure just to discover that you forgot to run the script.
Another easy and dirty way was to add the user used by the application to the db_owner group and make all procedures owned by the dbo. No need to talk about why this is bad.

In SQL Server 2005, the concept of permissions, securables and schemas ,were introduced.
now you can have it all done using one statement. Assuming you have a user test and a schema named HR, test can have execute permissions on all stored procedures belong to HR schema:

GRANT EXECUTE on SCHEMA::HR to test

Now if you (as dbo) create a procedure like:
CREATE proc HR.GetAccData
as
Select * from dbo.acc
GO

The used test will have execute permission on the HR.GetAccData procedure.

Moving a form without a title bar

How to make a form without a title bar movable?

This is a common request in programming forums, and here is a simple solution that depends on handling mouse down and mouse move events:


public partial class Form1 : Form
{
int m_PrevX;
int m_PrevY;

public Form1()
{
InitializeComponent();
}

private void Form1_MouseMove(object sender, MouseEventArgs e)
{
if (e.Button != MouseButtons.Left)
return;

Left = Left + (e.X - m_PrevX);
Top = Top + (e.Y - m_PrevY);

}

private void Form1_MouseDown(object sender, MouseEventArgs e)
{
if(e.Button!=MouseButtons.Left)
return;

m_PrevX = e.X;
m_PrevY = e.Y;
}

MS SQL Server Views: Order By

If you create a view in SQL Server 2005 management studio and add an order by clause, run the query, the order by seems to take effect.
But when you reference the view in a simple query (with no order by) you find that the returned results are not as expected, records are not ordered..
check the definition of the created view :

sp_helptext view_name

you find that the management studio added a TOP clause to the view.
reviewing SQL Server Books Online:

SQL Server 2000:

The query defining the view cannot include the ORDER BY, COMPUTE, or COMPUTE BY clauses or the INTO keyword.

SQL Server 2005:

The SELECT clauses in a view definition cannot include the following:
.
.
An ORDER BY clause, unless there is also a TOP clause in the select list of the SELECT statement

This makes extreme sense to me, as views represent logical tables, so just like tables, the order in which you get data should not be assumed, unless explicitly requested using an order by in the query.

Posting dynamic data to a popup page

It's a common need to pass some JavaScript dynamically formatted data to a pop up page, this can be easy as you just need to append data to the URL of the pop up and open it using window.open() or simply by setting the src property of an anchor element, this is a get request.

But if the data is too big to be put in the query string (2kb+), or if you want to make it harder for the user to see and modify the passed parameters, you'll need to post this data.

this JavaScript function makes the job, I post the string ("real long data").

function PostToPopup()
{
window.open("about:blank","popup",null,null);

var hdn= document.createElement("Input");
hdn.type="hidden";
hdn.name="popupdata";

var frm= document.createElement("Form");

frm.target="popup";
frm.method="POST";
frm.action="popup.aspx";

document.body.appendChild(frm);
frm.appendChild(hdn);

hdn.value="real long data";

frm.submit();
}

You can read this value in the popup from the request..this is an ASP.NET example:
Response.Write(Request.Form["popupdata"]);

I passed this semester with 3 'A's

I my computer science diploma study at aast (Arab Academy for and technology and maritime transport), I passed the summer semester with grade A for the three courses I studied.
I studied Software Engineering, Database management systems ,and web programming.
The most one that I'm happy with is the web programming course. I made a Java application which is an online image album and editor.
What I liked about this project is that it utilized the client to make all the image effects (using a Java applet). The applet communicates with the server to get the image and to save it by calling a servlet. new thing that I've learned.
I wish to share the code of this project and publish it, maybe as an article.

ctrl+R to refresh SQL Server enterprise manager query results

A small tip that many people don't know..
You can run or refresh SQL Server 2000 enterprise manager query results without clicking the (run) toolbar button using the (ctrl+R) shortcut.

Misleading performance comparisons: C# vs. Java

In a blog post: The Ultimate Java Versus C# Benchmark the author tried to prove that Java outperforms C# in a real life benchmark.
The benchmark actually measures the performance of regular expressions in both languages (say frameworks).
He concludes the post saying that C# failed even to give the results, and consumed too much memory till an OutOfMemoryException was thrown.
I tried both code samples (C# using .net 2.0, Java using Java SE runtime version 1.6).
The Java version worked as mentioned in the blog post (took about 4031 milliseconds on my PC), the C# version took too long time so I had to terminate it.
I noticed that the whole benchmark is about comparing these lines of code:
C#:
Regex regexpr = new Regex(matchthis, RegexOptions.Compiled);
Boolean b = regexpr.IsMatch(_doc);
Java:
Pattern regexpr = Pattern.compile(matchthis); Matcher matcher = egexpr.matcher(_doc);
boolean b = matcher.find();

When I changed the C# code to:
Regex regexpr = new Regex(matchthis);
Boolean b = regexpr.IsMatch(_doc);

Execution took about 14672 milliseconds. Which is somehow acceptable, even if a lot longer that Java execution time. Note that this version does not compile the regular expression.

When I removed the regular expression comparison from both codes the result was:
C#: 891 milliseconds
Java: 1750 milliseconds

See the difference!!

Conclusion:

1. It's unfair to compare two versions of code written in a way that is optimized for one language but not for another. In .net, compiling regular expressions is good when you are going to reuse them.
2. Taking a single point of comparison (regular expression in this case) is not a valid measurement when you compare two huge frameworks like .net and Java.

I'm not trying to defend C#, and I'm not claim that .net outperforms Java generally. I just suggest that performance comparisons should not be performed by biased persons. The test should not be optimized for one language without the other.

Virtual methods: Java vs C++,C#

When I first learned about OOP, I thought that one of the challenging decisions in the design was to determine virtual functions (I started OOP using c++). However, this became not a big issue as I got more mature in OOD.

Java differs. In Java, all methods are virtual, unless explicitly declared as final.

I was thinking, which approach is better?
My initial thoughts are that Java's approach is better and easier. Maybe it's not good to apply in C++ because virtual tables are used and calling virtual functions cost more than non-virtual. (not sure about the case in Java).

I don't have a complete image yet. Thoughts and comments are welcome.

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.

Solve problems where they exist, don't forward them...

I was fixing a bug in a web form that takes input from user in form of drop down lists that were put inside a table in the same row, each in a table cell.
The form required to work in both directions (RTL , LTR), according to some configurations. So if the user chooses to display the form in RTL, the alignment of the input controls must to change to "right" and so the direction should change to RTL, quite simple..

For some reason, the developer that added this functionality did adjust the alignment but did not adjust the direction of the table. This can make the user get confused because he expects the input sequence to be from right to left in case the form is right-aligned.

He solved this problem by changing the logic of reading and saving user input to reverse data in case of RTL. And problems started.

Being a relatively complex module, this change in logic caused other errors that were hard to trace. I solved this bug simply by removing any special handling to RTL case in application logic and changed the direction of the table that contains the drop down list to RTL. This solved the problem and made the code clearer.

I'm fond of thinking about and studying the real causes of bugs and issues. One of them is what the fellow developer did...
The RTL behavior is clearly a UI function. Supporting this functionality should not go to other layers or places in the code.

A new lesson I must remember:

Solve problems where they exist, don't forward them...

Should I say whenever possible?

Tips for your next major deployment

Deployment is one of the most annoying steps in the software production process especially for developers. I hate it. A small missing configuration or permission can cause severe problems. worse can happen when applying a fix or an update.
I believe that software developers should not make the actual deployment. Software Implementors or system engineers are better at configuring things ;)

Here, I share some good points that can help making a peaceful deployment experience . Most of them apply mainly to web applications.

1. Write a good deployment guide and test it before you deploy on production
   
2. Create a brief yet complete check list.
3. Peer check your deployment: another implementor or developer should review the steps you made.
   
4. Automate the process of deployment as much as you can.
5. If you deploy on a production environment, backup live data and configurations. You should also take the application offline.
   
6. Use staging environments with the same configurations as the production servers.
7. Test your deployment.

sp_MS_marksystemobject - When an undocumented stored procedure helped me

During a maintenance of a system that used merge replication, I dropped and recreated the MSmerge_tombstone table which is used by replication.

After a period, the client sent me an email asking me if he can safely drop this table as he did not find it relevant to his data.
this was because the he used to hide system objects shown in SQL Server 2000 Enterprise manager. And when I recreated the table it was not marked as system.

I tried to discover where does SQL Server save the "system" property in system tables, but I've got no clear results.

After a search I found an undocumented stored procedure "sp_MS_marksystemobject" that did the work.
It uses some bit manipulation to the status field in sysobjects table to mark an object as "system"... Something you can't discover by luck.

The syntax for using it is :
sp_MS_marksystemobject 'object_name'

You can use sp_helptext sp_MS_marksystemobject to check the code.

MDC 2007 Prize!!

The closing session in MDC2007 was all about fun, there were many prizes and I've got one for being an MDC fan (I had 3 MDC registration cards)
The prize was a Microsoft wireless mouse and keyboard with finger print recognition.
I did not try it yet and I'll need to stick Arabic letters on the keyboard.
Another winner won a good mobile device (iMate I think) for having 4 cards.
I ask myself, why did I get rid of the 2005 card while I kept 2004 and 2006 cards?
Anyway, It's good to get something for free :)

SQL Server cursors are not always evil !!

In her blog post, Robyn Page reopened a discussion that took place many times. She provided a problem (calculating a running total) as a proof that in some cases using cursors can give better performance than using other T-SQL techniques, as (sub queries-joins etc.)
The claim that T-SQL cursors are bad and to be avoided by any means is well advocated for, and many developers assume that whatever a solution they come to using set-based statements is necessarily better than using cursors.
In this SQLTeam article the writer intended to use the same problem (calculating a running total) as a proof, but surprisingly he found that the results of using cursors were better.

So what is the bottom line? When are cursors good and when are they bad?

We can generalize the result of the above mentioned tests and say that, in general, operations that needs sequential processing are cases where we should consider using cursors.

I also faced a situation about 2 years ago when I needed to provide paging to query results, which required jumping to a specific recored in a result set. I applied a technique using dynamic cursors and it worked great even with thousands of records and . Later I found a codeproject article that proves this method to be recommended to a great extent, another case that cursors proved to be useful.

I'm not trying to count the cases when cursors are good. I just don't like taking assumptions for granted. But I tried to give some cases where it's a good idea to use try different techniques including cursors and judge by yourself.