Get the Last Run Query in SQL Server 2005

Have you ever run an update or delete query by mistake and you need to roll back or at least want to know the last run query to fix the problem? The following SQL statement can help in that.

SELECT deqs.last_execution_time AS [Time], dest.text AS [Query]
FROM sys.dm_exec_query_stats AS deqs
CROSS APPLY sys.dm_exec_sql_text(deqs.sql_handle) AS dest
ORDER BY deqs.last_execution_time DESC

This will retrieve the last run queries with its execution time.

Filtering Procedures.. Do you make it right?

All of us do filtering in their project. It’s one of the most repeated functionalities. But do we make it well? How do you make your filtering procedures? Ok.. Have a look on the following one:

CREATE PROCEDURE std_GetFiles
@fileCategory varchar(50),
@tag varchar(50)
AS
declare @SQL as nvarchar(200)

SET @sql = ‘SELECT * FROM tscoFileIndex WHERE ‘

if @fileCategory null
set @sql = @sql + ‘ FileCategory = ‘ + @fileCategory + ‘ and ‘

if @tag null
set @sql = @sql + ‘ tag = ‘ + @tag + ‘ and ‘

SET @SQL= LEFT(@SQL, LEN(@SQL) -4)

GO

This is the first way come to your mind when you trying to do filtering. But take care. Actually, all the string concatenation in this procedure make your data under threaten. As this allow procedure users to inject sql statements inside your concatenated query by passing unexpected paramaters through “fileName” and “tag” inputs. Also the many if conditions here affects your procedure performance.

The alternative, which is better than this, would be something like that:

CREATE PROCEDURE std_GetFiles
@fileCategory varchar(50),
@tag varchar(50)
AS

SELECT *
FROM tscoFileIndex
WHERE (@fileCategory is null OR FileCategory = @fileCategory ) AND (@tag is null OR tag = @tag)

GO

So, you match all rows if the parameter is null, and use the
condition when the parameter is not null.

Paging Implementation With SQL

When you see how the paging is a piece-of-cake now in .NET server controls (like GridView), you should imagine how much amount of work is needed to do paging in early days. On of the suggestions was to fetch all the data block and store them on a session variable or any such temporary allocation and just do your paging in you business layer! Ok..I hear someone say what a silly solution! That is true. This is really a mess when you working on huge data blocks. So, the best way is to do you paging in your data layer.

google_ad_client = “pub-6699943802796732″;google_ad_width = 728;google_ad_height = 90;google_ad_format =”728x90_as”;google_ad_type = “text_image”;google_ad_channel = “”;google_color_border = “f6f6f6”; google_color_bg = “f6f6f6”;google_color_link = “550296”;google_color_text = “000000”;google_color_url = “550296”;

You may ask? why I should consider paging while I have ready-to-use paging server controls? .. Well, Although, .NET provide ready-to-use controls with paging feature (like GridView), you still need to implement paging inline for other controls such as Repeaters and Datalists.

Recently, I was searching for doing paging using a single SQL statement. Unfortunatily, solutions differ from DBMS to another. For example, in MySQL, you can make the paging using a SQL statement like that:

SELECT fields FROM table LIMIT $offset,$pgsize

In the other side, SQL Server doesn’t have such keywords for paging. But still there is a solution. You can go around and use “TOP” keyword to do the paging using SQL. For example, if your page size is PAGESIZE, you can divide you data to several pages as following:

SELECT TOP PAGESIZE * FROM EMP WHERE ID NOT IN (SELECT TOP OFFSET ID FROM EMP)

PAGSIZE is constant,and you just change the OFFSET. For example, if your PAGESIZE is 20, then your offset should be: 0, 20, 40 and so on. I think this SQL statement is a piece of art! I really like it!