Database MySQL

MySQL WorkBench : New GUI

I was looking at and found a new GUI, which came as replacement of old MYSQL administrative GUI. My impression was not good to learn that they have something new, as I thought it might be just that they get their old interface opensourced or something. But when I install this application, hmm .. it looks really good, a decently good managed user interface. Meet all modern application GUI stuff, still keep it professional. Though I download a Beta version but so far so good, it does all basic work I need to perform in manner I am habitual. Looks like I can relay on this interface in future.


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

        OVER (ORDER BY EmployeeName) AS Row,
        EmployeeId, EmployeeName, Salary
    FROM Employees) AS EMP

I took this sample and knowledge from a great resources for .NET developer. Website URL is

Hope you like the solution. Any alternative is welcome.

Server Configuration SQL

MySQL “Server has gone away” error

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

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

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

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.

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:


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 :