Categories
.NET SQL

Pagination Query for SQL Server

In recent past, we got few problem when we need to do Pagination with SQL Server. I specially got frustrated when I saw that MySQL server has “limit” word that does pagination work in MySQL in best known manner for me, BUT microsoft didn’t consider that option yet. But today I found a method for SQL server that does the pagination in similar way as MySQL limit works. I am not very sure if it runs on SQL Server 2000 or earlier but it runs on SQL Server 2005. The magic word here is “ROW_NUMBER()” function that provides a Serial No. kind of Numbering to each record. So to do paging all you need to do is, make Query calling ROW_NUMBER() and you are done.

Here is sample of such query

SELECT * FROM
    (SELECT ROW_NUMBER()
        OVER (ORDER BY EmployeeName) AS Row,
        EmployeeId, EmployeeName, Salary
    FROM Employees) AS EMP
WHERE Row BETWEEN 2 AND 4

I took this sample and knowledge from a great resources for .NET developer. Website URL is http://www.openwinforms.com/row_number_to_sql_select.html

Hope you like the solution. Any alternative is welcome.

Categories
Server Configuration SQL

MySQL “Server has gone away” error

A useful like to know the reason of “Server has gone away” error of MySQL.

http://blog.taragana.com/index.php/archive/mysql-tip-mysql-server-has-gone-away-or-lost-connection-to-server-during-query-fix/

It has very good information on why it happens, so you can check and correct the error.

Categories
Links Programming SQL

Comparing Date Range (Date Interaction checking through SQL)

As title says, I was looking to compare User give date range with date range saved in mysql Database. I search internet and found a great  small function that solve this problem for me, though they create a function for some programming language but I use same function in mysql (as per mysql syntax) and its all done

select * from  TableName where
(‘2008-04-16’ = start_date) or
if((‘2008-04-16’ > start_date),(‘2008-04-16′<=end_date),(start_date<=’2008-08-20’))

here 2008-04-16 is user enter date, and 2008-08-20 is user end end date for date range I collect from web form, and start_date and end_date is field in my table that define date range.

you can read about function here http://ryanfarley.com/blog/archive/2004/08/19/966.aspx

Categories
Code Snippets SQL

Creating SQL Table from another table

Often we need to take quick backup of database with data before we can make changes in table. for this I create the image of table within same database, as it is the fastest method. It is one of the reason why one need to create image a table.. or copy one table from another.

It can be achieved in all RDBMS

Here is SQL for MS SQL

select * into newTable from OldTable

However, their is another command (it works for me on mysql)

create table newtable as (select * from oldtable)

Best of luck with backup and server transfers.

Categories
Code Snippets Programming SQL

Setting/Resetting Auto increment value in MSSQL 2005

Once you create a table and what to reset autoincrement value of a table to your desire number, you need to set some system table values, which are not available for direct editing. To perform this you need to do following:

SELECT OBJECT_ID,OBJECT_NAME(OBJECT_ID) AS TABLENAME, NAME AS COLUMNNAME, SEED_VALUE, INCREMENT_VALUE, LAST_VALUE, IS_NOT_FOR_REPLICATION FROM SYS.IDENTITY_COLUMNS ORDER BY 2

This statement simply show all table in your select table with auto increment value. Once this gives you good information, you can run following command to change value

DBCC CHECKIDENT(‘mytable’,RESEED,[new numeric value])

If this runs perfect give something like this:

Checking identity information: current identity value ’14’, current column value ’30’.DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Otherwise it give an error. (In above message 14 and 30 will be replaced with your values.

MSDN Link for more details : http://msdn2.microsoft.com/en-us/library/ms176057.aspx