Author: Latheesh NK

SQL

IIF() in SQL Server
Today, we are going to see IIF function in SQL Server. Honestly speaking, I have never used this function in any of my code so far just because I am very much good with CASE WHEN expressions. However, let us look at some of the usages of IIF in details as part of our learning.

Syntax – IIF(condition,value_if_condition_is_true,value_if_condition_is_false) 

Declare @Age int =18
Select IIF(@Age >= 18, 'Adult','Kid')

--Equivalent CASE WHEN representation
Select CASE WHEN @Age >= 18 THEN 'Adult' ELSE 'Kid' END

Remarks
    1. SQL Server internally converts IIF to CASE WHEN expression, hence the rules applies to CASE WHEN expression applies to IIF function as well.

    2. It is to be noted that either of 2nd and 3rd parameter should be a NON NULL value.

I’d like to grow my readership. If you enjoyed this blog post, please share it with your friends!

SQL

QUOTENAME() in SQL Server
QUOTENAME function returns a unicode string with bracket by default.

Let us look at a simple example as below. 

Select db_name(),quotename(db_name())

I personally observed many people are using this function for enclosing the db name or schema name or server name with brackets. This makes life easier in multiple occasions for me.

But, there are other ways that you can use this function to replace other characters as well. The synatx is QUOTENAME(string, quote_char). There are some set of quote_char which are allowed to use as below in the screenshot. 

Select db_name(),quotename(db_name())
Select Quotename(db_name(),'''') -- ' character
Select Quotename(db_name(),'"') -- " character
Select Quotename(db_name(),'[') -- [ character
Select Quotename(db_name(),'{') -- { character
Select Quotename(db_name(),'(') -- ( character
Select Quotename(db_name(),'<') -- < character

I’d like to grow my readership. If you enjoyed this blog post, please share it with your friends!

SQL

How to recover “Recover Pending” database in SQL Server
One of my colleague has reached out to me to recover a database which has a state as “Recover Pending”. He tried few methods explained in another blog post, however it was not a successful effort.

While I was trying to get the database with restore…with recovery, ended up with the below error message.

Msg 3148, Level 16, State 3, Line 2 This RESTORE statement is invalid in the current context. The ‘Recover Data Only’ option is only defined for secondary filegroups when the database is in an online state. When the database is in an offline stafte filegroups cannot be specified. Msg 3013, Level 16, State 1, Line 2 RESTORE DATABASE is terminating abnormally.

Finally, we used the below method to get the database online. Since its a testing environment and a small amount of data loss is not an issue for us, we used DBCC command to Repair_allow_data_loss. Caveat: We would not recommend to use this method for Production environment, probably, we need to restore the database from a valid backup until the point in time recovery. 

ALTER DATABASE dbname SET EMERGENCY;
GO
ALTER DATABASE dbname set single_user
GO
DBCC CHECKDB (dbname, REPAIR_ALLOW_DATA_LOSS) WITH ALL_ERRORMSGS;
GO
ALTER DATABASE dbname set multi_user

I’d like to grow my readership. If you enjoyed this blog post, please share it with your friends!

SQL

NULLIF() in SQL Server
NULLIF – (Definition by Microsoft) returns NULL if the two expressions are equal and if the expressions are different then returns the first expression.

A simple representation as below: 

Select NULLIF(100,100),NULLIF(100,101)

Another way of representing

Case when Expression1 = Expression2 Then NULL Else Expression1 End

It is important to be aware that non-deterministic functions may have an impact since it evaluates two times and returns different values. Eg. NEWID(), RAND() etc.

I’d like to grow my readership. If you enjoyed this blog post, please share it with your friends!