Wednesday, October 31, 2012

SQL Server 2012 IIF function may replace CASE expression?


Hi
Did you install already SQL Server 2012? Well, see a new T-SQL function named IIF you can use sometimes instead of CASE expression.
http://msdn.microsoft.com/en-us/library/hh213574.aspx


CREATE TABLE #t (id INT)

INSERT INTO #t VALUES (1),(2),(3)

DECLARE @a int = 3;
DECLARE @b int = 2;

SELECT * FROM #t WHERE id= IIF ( @a > @b, @a, @b )

Returns Id=3.

You can even using nested IIF commands.
SELECT * FROM #t WHERE id= IIF ( @a > @b, IIF(@a>0,@b,0), @b )

Monday, June 18, 2012

Identity property remains gaps

Let say you have a table and use identity property. It is ok, but I really hope that you are aware of the bug that remains gaps in identity if you restart MS SQL Server service. Please vote http://connect.microsoft.com/SQLServer/feedback/details/739013/alwayson-fail
over-results-in-reseed-of-identity
Thanks

Monday, April 23, 2012

Successful upgrade to SQL Server 2012

Hi everyone. Today I successfully upgraded our production database to the new version -SQL Server 2012. Actually everything went ok, and after running Upgrade Advisor and restored the database into a new server. The "challenge" was to upgrade existing SSRS reports and SSIS packages. What I would recommend is to open a new project in SQL Data Tools (yes BIDS is gone) and adding report by report to the project. SQL Server automatically upgraded them for first time I run them. Another thing is that now you can much easily to configure SSRS and even if you specified not to "configure" during the installation. I have not noticed any performance degradation since we moved from SQL Server 2005.So lets enjoy new features that were introduced and happy working with SQL Server 2012.

Tuesday, March 6, 2012

SQL Server 2012 has released to manufacturing (RTM)

Microsoft announced that SQL Server 2012 has released to manufacturing (RTM). Customers and partners can download an evaluation of the product today (http://www.microsoft.com/sqlserver/en/us/default.aspx) and can expect general availability to begin on April 1.

Wednesday, February 1, 2012

LIKE operator "issue"

Hi
Please consider the below script

create table #t ( col varchar(40))

insert into #t values ('[Untitled].pdf')
insert into #t values ('Untitled].pdf')
insert into #t values ('^Untitled].pdf')
insert into #t values ('|Untitled].pdf')

Now the client runs the below SELECT statement

select * from #t where col like col

Ok, you ask why col LIKE col and not col=col. Well I really simplified the the query because it takes a parameter and needs to search sub string within a col base on CASE Expression.So as you can see the above script does not return the first row [Untitled].pdf. After some investigation it turns out than we cannot get rid of left bracket. The only real solution is the below script (thanks to Peter Larsson)

select * from #t where col like replace(col, '[', '[[]')

Tuesday, January 10, 2012

Must read that blog

Happy New Year to everyone!

Just found great web site about SQL Server maintained by Remus Rusanu (MS Employee)
It contains great info not only about published releases (SQL Server 2005/2008/2008R2) but also new features,command about SQL Server 2012

http://rusanu.com/2011/08/05/