Numbers in ORDER BY Clause

One of my friends asked me “What does it mean when a number is used in the order by clause?” An example is

Order by 1

Well. Numbers in ORDER BY clause denote two different meanings based on how they are used. Let us create the following simple data set

create table #customers(cust_id int, cust_name varchar(100))
insert into #customers (cust_id,cust_name)
select 1,'Balaji' union all
select 2,'Madhivanan' union all
select 3,'Arumugam'

Now consider the following query

select cust_id,cust_name from #customers Order by 1

The result is

cust_id      cust_name
-------      ------------------
1            Balaji
2            Madhivanan
3            Arumugam              

consider another query

select cust_id,cust_name from #customers Order by 2

The result is

cust_id      cust_name
-------      ------------------
3            Arumugam
1            Balaji
2            Madhivanan

What it means that when you specify the number in ORDER by clause, the result set is sorted based on the ordinal position of the columns specified in the SELECT statement. In the first example it is sorted by cust_id and in the second example it is sorted by cust_name.

But it is entirely different if the numbers are used in CASE expression in ORDER BY clause.

Consider the following example

select cust_id,cust_name from customers 
order by 
case when cust_name='Madhivanan' then 1 else 2 end

The result is

cust_id      cust_name
-------      ------------------
2            Madhivanan
3            Arumugam
1            Balaji

The above indicates that if the cust_name is Madhivanan, make it as first row and others in any order.

So numbers in ORDER BY clause refer the ordinal position of the columns if specified directly or assign the numbers as part of result set and sort it based on the numbers if specified in CASE expression.

Question of the month September 2016 – Find out incorrect CREATE TABLE statement

Consider the following statements

create table #t1   (i int)   -- Statement 1
create table .[#t2]  (i int)   -- Statement 2
create table ..#t3 (i int)   -- Statement 3
create table ...#t4(i int)   -- Statement 4

Find out the incorrect statement

Best Practices – Use Unambiguous Date formats

I see that lot of sql developers do not understand how to effectively express Date/Datetime values in the queries. Many of them tend to use regional date formats.

Let us see what happens when regional date format is used. In India the regional date format is dd/MM/yyyy

declare @date datetime
set @date='10/06/2016'
select @date as date

The result is

date
-----------------------
2016-10-06 00:00:00.000

When you run the following

declare @date datetime
set @date='16/06/2016'
select @date as date

The result is

Msg 242, Level 16, State 3, Line 6
The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.

As you see, the input is given in the form dd/MM/yyyy which means 10th June 2016, but it is actually stored as 6th October,2016. The second code throws an error because 16 is not a valid month. Where is the problem? The problem is using the regional date formats. The default date format is mdy and regional inputs should be expressed in that format only. But if the date format is different, you need to change the expression again.

set dateformat dmy
declare @date datetime
set @date='10/06/2016'
select @date as date

The result is

date
-----------------------
2016-06-10 00:00:00.000

Now it works fine. So based on the server’s date format you need to change the expression. How do we solve this problem? Use Unambiguous date format.

In SQL Server, there are three Unambiguous date formats

YYYYMMDD
YYYYMMDD HH:MM:SS
YYYY-MM-DDTHH:MM:SS

Now run the following and see the result

set dateformat dmy
declare @date datetime
set @date='20160610'
select @date as date
GO
set dateformat mdy
declare @date datetime
set @date='20160610'
select @date as date
GO
set dateformat ymd
declare @date datetime
set @date='20160610'
select @date as date

The result is

date
-----------------------
2016-10-06 00:00:00.000

So it does not matter what the server’s date format is, the unambiguous date formats always work as expected. So you should always express these formats to avoid error/unexpected result

How to Recover Database From Restoring State Reference?

This is the Guest post by my friend and SQL Server DBA Andrew Jackson

SQL Server being a reliable database management system has become the choice of millions of the users worldwide. However, the technical issues can arise anytime in such a heavy server which may lead to server breakdown or database corruption at any moment. The users who depend much on the database cannot afford to lose data, hence they prefer to create a backup of the data at regular interval of time. The database files can be easily recovered on the system in such fatal situations. The in-built recovery utility generally assures a smooth recovery of the database without introducing any data loss. However, some times users face an annoying situation in which the recovery process gets stuck up in the restoring phase for a long time. Keeping in mind the inconvenience faced by the users, we have focused to provide the manual solution for recovering a database that is in the restoring state reference for much time and is not responding to the recovery process.

How SQL Database Stuck in Restoring State ?

 Generally, the SQL users attempt to restore the backup files using the SQL query. The database gets easily restored on the system within few minutes depending upon the size of the backup file.

Consider, we need to restore MyData database from MyData.bak backup file. The following SQL query can be run on SQL command prompt to restore the database:

RESTORE DATABASE MyData
FROM DISK = 'MyData.bak'
WITH REPLACE

Generally, the database gets restored on the system within few minutes. However, the REPLACE option used within the query leads to forced restore of the given database.

Moreover, it overwrites the existing database with the newly recovered database files. But, in some situations, the SQL Server database recovery progress is too slow that the restore process goes into the stuck mode for an indefinite time. Even if the user press the Stop button to cancel the restore process, he gets annoyed with SQL Server database restoring forever situation. So, in this situation neither the database can be conveniently restored nor the user knows how much estimated time would it take in recovering a database that is in the restoring state reference. This creates an annoyance for SQL users as they become unable to restore the crucial database, even after having the proper backup of the entire database.

How to Recover Database From Restoring State Reference?

 Only a small trick can help the users in recovering a database that is in the restoring state reference for any point of time. The following SQL query can help the users to easily restore the database from the backup file without facing any stuck up situation. This script replaces the existing database and restores the database from the specified backup file.

RESTORE DATABASE MyData
FROM DISK = 'MyData.bak'
WITH REPLACE, RECOVERY

However, if the user does not want to replace the existing database then the Replace option should be eliminated from the query. The following SQL query must be run in order to backup the database without overwriting the existing database:

RESTORE DATABASE MyData
FROM DISK = 'MyData.bak'
WITH RECOVERY

Running the SQL query in this manner will not let the database to stuck up in the restore process and will allow a smooth recovery process.

NOTE: Users can choose to view the progress status of the recovery process. This feature can be adopted in order to have a continuous eye on the ongoing database recovery. If any unexpected time lag is noticed, then an immediate error detection can be performed to carry out the recovery of SQL database smoothly.

RESTORE DATABASE MyData
FROM DISK = 'MyData.bak'
WITH RECOVERY, Stats=1

The query just needs to add an additional parameter for viewing the status of recovery time to time.

Conclusion

The database backup is a very crucial task and needs to be carried out attentively by the user. Users must keep it a point to always add the transaction log files for the specified database backup. If the log files are missing, then SQL Server may keep on waiting for the missing log files and turns into stuck up of restore process. We can observe that a slight change in the query can figure out much changes in the SQL processing. We hope that the blog has provided an efficient manual solution that would help in recovering a database that is in the restoring state reference.