Combining Columns Ignoring Null Columns

Let’s start with an example.

You will see that MiddleName column has NULL values. If you want to combine all four columns and display them in one column you will write following query.

Result is,

You will realize the obvious problems from the above result set. To avoid this what you should is, use the ISNULL function.

Though the above will solve your problem, as a developer you need to keep an eye on nullability of the column.

With Denali, you have a new function called CONCAT.

And the result is,

which is what we were looking for.

Next question is, how about the performance.

To answer that I will display query execution plans for two queries, one with CONCAT and the other with ISNULL function.

So there is no additional cost with the new function.

SQL Server 2012 Editions

SQL Server 2012 users will experience new SQL Server editions called Business Intelligence  to the addition of existing editions, Enterprise, Standard, Web , Developer, Express and Evolution.

If you wish for more news about editions and licensing click it here.

TRY_CONVERT

I am sure you would have used CONVERT function many times.

If you see the above data set, last date value is an invalid date. If you are integrating with legacy systems, you will these types of records.

They you might need to convert these into a date time value. Naturally, you will use, CONVERT function.

But it will fail.

In Denali or SQL Server 2012 (as per CTP3) you have new function called TRY_CONVERT.

With this new function, you won’t get the error as well as you will be able to find out the errors as well.

Following query will return all the rows where you have an invalid date value.

You can use this for other data types as well.

Download the code from here

SQL Server 2008 SP3 is now available!

Microsoft SQL Server Sustained Engineering group is pleased to announce the release of SQL Server 2008 Service Pack 3 (SP3). Both the Service Pack and Feature Pack updates are available for download on the Microsoft Download Center. As part of our continued commitment to software excellence for our customers, this upgrade is free and doesn’t require an additional service contract.

Download it from here

Failing of alter Table statement

Replication Agent will fail when attempting to alter a column in the publisher which is part of an index.

Here is the faq of mine.

දත්ත මූලිකයන්හි සිංහලෙන් දත්ත ගබඩා කරමු

මෙය මවිසින් ලියන ලද දෙවැනි ලිපියයි.

මෑතකදී, විශ්ව විද්‍යාලයක සිසුන් පිරිසක් තම පාඨමාලවේ පර්යේෂන වියාපෘතියකට ශ්‍රි ලංකා පොලීසිය සදහා ලැබෙන සුළු අපරාධ විශ්ලේශණය කිරීම තෝරා ගෙන තිබුනි. ඒ සදහා ඔවුන් සිංහලෙන් දත්ත ගබඩා කිරිමට තෝරා ගෙන තිබිනි. එයට මූලික හේතුව වී තිබුනේ, ලැබෙන දත්ත භාවිතා කරන අයට ඉංග්‍රීසි භාවිතයට වඩා සිංහල පහසු නිසාය. බොහෝ ශ්‍රි ලංකාවේ රාජ්‍ය ආයතයන් හි මෙම තත්වය ඇති බැවින්, ඒ පිළිබදව කිසියම් විස්තරයක් කිරීම මෙම ලිපියෙහි අරමුණයි.

දත්ත මූලිකයන්හි සිංහලෙන් දත්ත ගබඩා කරමු

Change the case of the Logical Name of a SQL Server Database

When you create a database, by default data file and log file will be added to the database. For logical name it will be, Databasename_log. Let us say you want to change it to Database_Log (where L is in upper case).

ALTER DATABASE [TailLog]
MODIFY FILE (NAME=N'TailLog_log', NEWNAME=N'TailLog_Log')
GO

Then you will get an following error.

Msg 1828, Level 16, State 3, Line 1
The logical file name "TailLog_Log" is already in use. Choose a different name.

Let us try to do this from the SSMS.

So basically you are not allow to do this.

What you have to do is, first set it to another name.

ALTER DATABASE [TailLog]
MODIFY FILE (NAME=N'TailLog_log', NEWNAME=N'TailLog_LogTemp')
GO

The file name 'TailLog_LogTemp' has been set.

Then change it back to TailLog_Log so that you can achieve your target.

ALTER DATABASE [TailLog]
MODIFY FILE (NAME=N'TailLog_LogTemp', NEWNAME=N'TailLog_Log')
GO

Disk Usage Report in SSMS

Reports in SSMS are very helpful when monitoring SQL Server. But today I encounter an issue with one of the reports, which is Disk Usage Report.

 

Above is the Transaction Log space usage report taken for same database but for different times. Actually, second one was after taking a log backup. After seeing this report my immediate reaction was log backup has not cleared the log. So I ran another log backup and results are similar. But when you analyze these graphs carefully you would see that in the first graph, Green is Used and in the second Green is Unused and Blue is vice-versa. (Oops).

I did few more research into this to find out the tipping point and this is happening after 50% percent. See the image below.

Now it is 50-50 and next one is,

You can clearly see the change. I verified this is the same behavior in SQL Server 2008 R2 as well.

Using $IDENTITY function

You may have used many different Identity functions like SCOPE_IDENTITY(), @@IDENTITY etc. but have you used $IDENTITY function.

Let us say you have a table where identity property is set in one of the columns.

if you run the following query,

Following is the result,

This means, you don’t have to explicitly mention the identity column instead you can use $IDENTITY.  You can use this for all aggregations ( AVG ($IDENTITY), MAX($IDENTITY) etc) as well as in grouping columns.

පූර්ණ කාලීන දත්ත මූලික කළමනාකරුවන් (DBA) අවශ්‍ය ඇයි? (si-LK)

මෙය මා විසින් ලියන ලද පළමු සිංහල blog සටහනයි. බොහෝ කලක පටන් පැවති අදහසක් "දැවන්ත" කාර්යයකින් පසු මෙසේ සටහනක් බවට පත්විය. මේ වෙනුවෙන් මට සහාය වු, Fiqriට මගේ ස්තුතිය.

පූර්ණ කාලීන දත්ත මූලික කළමනාකරුවන් (DBA) අවශ්‍ය ඇයි? (si-LK)