Paging is one of the most important optimization techniques about bandwidth usage between SQL Server and IIS. How can we let a user navigate through a 2 million rows length datatable? Get all 2 million rows to IIS and bind it to GridView for ASP.NET based paging? That’s totally a bad idea to let IIS get all 2 million rows for each page view. We need to get only the data that our user want to see. Paging should be handled on SQL Server in order to get only visible data from SQL to IIS. Additionally this technique will provide faster page process.

First of all let’s think about what we were doing on MSSQL 2000 to be able to page our data on server side. One of the possibilities can be to generate the needed SQL Query in a stored procedure and execute it. A more proper solution is to build row numbers and query them. On SQL 2000 some developers were creating temporary tables to generate row numbers. For those using SQL 2000 I will suggest the following code.

SELECT Product_ID, Product_Name, Price,
(SELECT COUNT(*) FROM Products e2 WHERE e2.Product_ID <= e.Product_ID) AS RowNumber
FROM Products e
ORDER BY Product_ID

You can get this query as a View and query it again to get specific records. The problem is when you just want to order your data by other fields except PK. SQL 2005 has a new feature for this problem.

SELECT Product_ID, Product_Name,
Price, Row_Number() OVER (ORDER BY Price ASC) AS RowNumber
FROM Products

This is a very cool function. We are not allowed to get row numbers depending on any specific order column. You can set two or more columns for order rules and you can get row numbers without depending on any PK. So how do we query the result and get only specific rows?

WITH NumberedRows AS (
SELECT Product_ID, Product_Name,
Price, Row_Number() OVER (ORDER BY Price ASC) AS RowNumber
FROM Products)
SELECT * FROM NumberedRows
where RowNumber > 2 and RowNumber < 5

Say welcome to another new feature of SQL2005 :) Common Table Expression. Actually this feature is creating kind of temporary table on the fly and we are allowed to query it instantly. Here it is :) You have your page data.

“A deadlock is a situation wherein two or more competing actions are waiting for the other to finish, and thus neither ever does. It is often seen in a paradox like 'the chicken or the egg'.” says Wikipedia and yes :) they are right. In SQL world deadlock refers to a specific condition when two or more queries are waiting for each other to release a datatable or a row. Actually we can’t prevent a deadlock in many situations but we can handle them by SQL 2005 TRY/CATCH blocks.

RETRY:
BEGIN TRY
    BEGIN TRANSACTION
        -- DO SOME BAD ACTION CAUSING DEADLOCK
    COMMIT TRANSACTION
END TRY
BEGIN CATCH
    SET @Err = @@ERROR
    IF @Err = 1205
        ROLLBACK TRANSACTION
        INSERT INTO ErrorLog (ErrID, ErrMsg)
        VALUES (@Err, 'Deadlock recovery attempt.')
        WAITFOR DELAY '00:00:10'
        GOTO RETRY
    IF @Err = 2627
        SET @ErrMsg = 'PK Violation.'
    IF @ErrMsg IS NULL
        SET @ErrMsg = 'Other Error.'
    INSERT INTO ErrorLog (ErrID, ErrMsg)
    VALUES (@Err, @ErrMsg)
END CATCH

TRY/CATCH block will catch errors and IF blocks will check what kind of error is happened. If that is a deadlock error we cancel our current transaction and use WAITFOR function to wait for 10 seconds to retry our query. This is actually an endless loop till there is no deadlock error. Notice that we are logging each error to an error table to check what’s happening.

In conclusion, deadlocks are easy to handle and be logged in SQL 2005 with TRY/CATCH blocks. In the other hand I would suggest you to check logs and analyze what’s causing deadlock and prevent getting deadlocks if possible without TRY/CATCH blocks :) for better performance.

This was always the main problem of web programmers using shared hosting plans with shared SQL databases. If the company you are working doesn’t provide any back-up solution or just provides some with manual interaction :) like you need to send a mail telling them to back-up your database the scenario gets dramatic. We need absolutely our own back-up system.

The first step is to get the remote data to our local machine. First we need to have an SQL Server 2005 Express Edition installed on our own local PC and a SQL Server 2005 Standart to just create our SSIS data transfer package file. The data transfer should work like in my article “How to schedule and run database transfers in SQL Express like in SQL Agent Job Scheduling Service in SQL Server 2005 Standard”. After having scheduled the data transfer Job our second job is to schedule a back-up for our local database.

The SQL Command to back-up our database is below;

BACKUP DATABASE [MyDatabaseName] TO  DISK = N'C:\MyBackUpFile.bak' WITH  RETAINDAYS = 10, NOFORMAT, NOINIT,  NAME = N'Any Description like Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO

The parameter “RETAINDAYS” is very important. 10 means that back-ups will not be stored in our back-up file if they are older than 10 days. This way our back-up file will include the last 10 days back-ups. We need to save this SQL command into any TXT file and store somewhere safe with .sql extansion.

We need to save this SQL command into any TXT file and store somewhere safe with .sql file extansion. Now time to lunch our back-up from command prompt.

sqlcmd -S "MyMachine\MySQLInstance" -E -i "C:\MySQLBackUp_CommandFile.sql"

You can now schedule this command with windows scheduler and have your back-up of remote SQL with two scheduled tasks.

This article will describe how Integration Services Packages can be scheduled on a PC running SQL Server Express Edition. However Integration Services Packages can’t build on SQL Server Express Edition Installed PCs because Express Editions software packages do not include Integration Services. You need to build packages on a SQL Server Standard Edition installed PC and copy package files. So why do we need an alternative solution for scheduling if we have got Standard Edition installed? Doesn’t it come with an agent too? Yes, it does. But what if you need to schedule these packages on a remote machine? You need to install SQL Standard again, and buy extra license? We don’t want to buy an extra licence, so we need an alternative.

MS SQL Server Express Edition comes in two different editions;

• SQL Server 2005 Express Edition
• SQL Server 2005 Express Edition with Advanced Services

Our main problem is that SQL Express Edition or -with Advanced Services do not include SQL Agent Job Scheduling Service. The agent is only available in SQL Server Standart Edition and -Enterprise. Let’s say you created your “Integration Services Package” with “Business Intelligence Development Studio” coming with “SQL Server 2005 Standart Edition”. While creating your package choose "User Password Protected". Copy your file to the root on your remote machine like "C:\MyPackage.dtsx". This PC should have installed "SQL Server 2005 Express Edition with Advanced Services" and "Microsoft SQL Server 2005 Express Edition Toolkit". Those are all free to download on MS web site. The toolkit includes "Connectivity Components", so we can use our DTSX. Open the command line and run;

   dtexec /f "C:\MyPackage.dtsx" /de MyPassword

That's all :) You just started the package. You can get more information about dtexec Utility on MSDN.

Scheduling?! What about using Windows Scheduler? :)

One of the best features in SQL2005 is the integrated CLR .NET languages support. What does it mean? You can use your common .NET programming language to code stored procedures in SQL 2005. In SQL2000 it was possible only with standard SQL statements. Let’s create our first Stored Procedure.

Launch Visual Studio 2005 and open a new SQL Server Project. Add a new Stored Procedure to your project.

        Using cnn As New SqlClient.SqlConnection("context connection=true")
             cnn.Open()
             Dim cmd As SqlClient.SqlCommand = New SqlClient.SqlCommand("SELECT * FROM Table_1", cnn)
             SqlContext.Pipe.ExecuteAndSend(cmd)
        End Using

Be carefull about our Conntection String. We do not define any specific connection string. Our stored procedure is already in SQL server, so we dont need to connect it again, we just use the default context connection.

Now Build your project and deploy it with "Build / Deploy" menu in VS2005.

      Msg 6263, Level 16, State 1, Line 4
      Execution of user code in the .NET Framework is disabled. Enable "clr enabled" configuration option.

This is the next error you will get. By default .NET Framwork is disabled in SQL2005. Explore "SQL Server 2005/Configuration Tools/Surface Area Configuration" in your Start menu. Select "Surface Area Configuration for Features", You will find "CLR Integration" option, activate it and save.

Done :) You can now just run your new stored procedure.

      EXECUTE [dbo].[StoredProcedure1]

The first run will be a little slow; because CLR will compile your stored procedure but next runs will be more effective.