SQLAuthority News – Blog Subscription and Comments RSS

Quite often I get email where many readers ask me how to get email from SQLAuthority.com blog. Today very quickly I will go over few standard practices of this blog using you can stay connected with SQLAuthority.com

First the most important is search: I received hundreds of emails and hundreds of comments every day. I try to answer each of them but if you have any urgent question I strongly suggest to search in my custom SQLAuthority.com Search. It searches in all the blogs as well in the comments.

Search @ SQLAuthority.com

If you want to stay connected with SQLAuthority.com using your RSS reader here are RSS feed for the same.

RSS of all the Posts: http://blog.sqlauthority.com/feed/

RSS of all the Comments posted on blog: http://blog.sqlauthority.com/comments/feed/

There are ways you can also subscribe to emails of the blog.

Subscribe to email from Feedburner: http://www.feedburner.com/fb/a/emailverifySubmit?feedId=872428

Subscribe to email from WordPress: (Look for following box in Right Side Nav)

Subscribe to SQLAuthority.com

Subscribe to SQLAuthority.com

Additionally, each post on this blog has subscription available for comments of that post only. At the bottom of each post there is following features which can help you to subscribe to only that post related details.

Post Detail Subscription on SQLAuthority.com

Post Detail Subscription on SQLAuthority.com

Reference: Pinal Dave (http://blog.SQLAuthority.com)

SQLAuthority News – Converting a Delimited String of Values into Columns

This blog post is about two great bloggers and their excellent series of blog posts. It was quite unusual to see two bloggers posting articles that are supporting each other and constantly improving the articles to the next level.

Two blogs which I am going to mention here are as follows: SELECT Blog FROM Brad.Schulz CROSS APPLY SQL.Server() – Brad Schulz and Demystifying SQL Server – Adam Haines. Before continuing this blog post, I suggest you all to bookmark these blogs for future reference.

The whole thing started when Adam tried to answer the question “How to transform a delimited values into columns?” on MSDN SQL Forum. Adam made the first blog post here http://jahaines.blogspot.com/2009/06/converting-delimited-string-of-values.html and then Brad and Adam bounced a few ideas off the wall. Then, Adam started his series on concatenating column values http://jahaines.blogspot.com/2009/06/concatenating-column-values-part-1.html, and http://jahaines.blogspot.com/2009/07/concatenating-column-values-part-2.html.  In part two of the series, Brad wanted to get a deeper understanding of why the results came out the way they did. Adam then started another series on unpacking or parsing out delimited characters, http://jahaines.blogspot.com/2009/11/splitting-delimited-string-part-1.html and here http://jahaines.blogspot.com/2009/12/splitting-delimited-string-part-2.html.  From here, Brad started digging even deeper into the internals of the XML method. Brad has a comprehensive list of the back and forth on this post, http://bradsruminations.blogspot.com/2009/12/delimited-string-tennis-anyone.html and a final post here http://bradsruminations.blogspot.com/2010/01/delimited-string-tennis-again-final.html.

The final outcome is that both the XML method and number table can perform equally, but the XML method has to be coded in a very particular way; otherwise performance will be horrendous. Also, the permanent number table shows consistently more performance than the inline number table; however, on smaller string sizes, the difference here is that most of the inline number tables require more CPU and RAM, whereas the permanent number table requires more IO. For an average developer, the number table solution is probably the easiest of the solutions to implement.

Overall, I strongly suggest to go through the abovementioned posts; you will love it and become their fan. Please note these posts are very easy to understand and if you know a bit of XML only, you will still be able to understand them very well. The above description has been taken with proper consent of the Adam and Brad.

Hats Off to you Guys! You guys inspire me and many SQL enthusiasts!

Reference: Pinal Dave (http://blog.SQLAuthority.com)

SQLAuthority News – Microsoft in India – Leadership Profiles

Recently when I have attended SQLAuthority News – MVP Open Day South Asia – Jan 20, 2010 – Jan 23, 2010 I got the chance to attend the keynote by Srini Koppolu Corporate Vice President & Managing Director (Microsoft India Development Center). It was great keynote and very interesting. We all were listening it carefully to the master who developed IDC. While listening him I realize that I do not know other pillars of Microsoft India. Just searching a bit online on MS site I came across following site which has list of all the leaders of the Microsoft India.

I am sure just like me there are many others who wonder about the top leadership of Microsoft India.

I have found following link on MS site where they have listed all the top leaders along with their bio.

MS Leadership

MS Leadership

Microsoft in India » Leadership Profiles

Reference: Pinal Dave (http://blog.SQLAuthority.com)

SQLAuthority News – Featured on Channel 9

This blog was featured on Channel 9 MSDN over here : TWC9: Scott Hanselman, Jon Galloway, Bing, parallel unit tests, more. I was very proud that this blog was discussed for more than 5 mins (from min 18 to min 23) on my favorite online show. Scott Hanselman, Jon Galloway along with Dan Fernandez make this show very live and very very entertaining.

The article which was featured in the show is SQL SERVER – Comma Separated Values (CSV) from Table Column.

Here are few screenshot from the show.

Reference: Pinal Dave (http://blog.SQLAuthority.com)

SQLAuthority News – A Daily Doze of Technology – Alvin Ashcraft’s Morning Dew

A common question that I receive is regarding how I keep myself updated with latest information about technology and what is going on at present. I read lots of blogs and books. I am usually traveling 4 days in my any regular work week. I read physical books at the time. I prefer to read the books in hard copy and not on the computer screen. If you ever spot me reading books, quite often you can see me with a fiction book rather than a SQL Book.

Ok… So the question is what do I read to keep myself updated with latest knowledge? Well, the answer is very simple; I read the daily post by Alvin Ashcraft. Everyday, he makes a list of the best of the best posts of different technologies and lists them category-wise in a single post. You might have came across many technology bloggers like him earlier but just like any other regular blogs, many have either stopped blogging and some stop updating their blogs at all. Well Alvin has been blogging for long time and has been consistently updating on different technologies. The quality of the links Alvin has been including is just getting better day by day.

I must admit that I have been addicted to his blog from long time. Every evening (Indian time), I wait for his blog to be updated; this way, I can read what has happened in the previous day. Alvin is from Philadelphia – also a husband, dad, atheist and geek – and has about 14 years of experience with software development.

Read Alvin Ashcraft’s Morning Dew. No matter when you are reading this post, I am sure that visiting his blog will bring up the latest news updates from technology.

Reference: Pinal Dave (http://blog.SQLAuthority.com)

SQLAuthority News – Subscribe to Blog – Search a Blog

Quite often I get request if I send blog post in newsletter or through email. Here are few important links.

You can for sure get email of my post, however, I strongly suggest to visit blog as if there are any updates in my post they are reflected on blog.

Subscribe to blog post through email

Subscribe SQLAuthority Feed

Search SQLAuthority – This is very powerful search. Give it a try.

Follow me on Twitter

Reference: Pinal Dave (http://blog.SQLAuthority.com)

SQL SERVER – 2005 2008 – Backup, Integrity Check and Index Optimization By Ola Hallengren

Script of Backup, Integrity Check and Index Optimization are the most important scripts for any developer. SQL Expert and true SQL enthusiast Ola Hallengren is known for his excellent scripts.

Please try it out and let me know what you think. The documentation is available on http://ola.hallengren.com/Documentation.html and the script can be downloaded from http://ola.hallengren.com.

Here is brief documentation sent by Ola himself for his script in his own words.

Backup Maintenance

I think that most of you have experienced the error messages “BACKUP LOG cannot be performed because there is no current database backup.” and “Cannot perform a differential backup for database “”, because a current database backup does not exist.”.

This usually happens when you have created a new database or when you have changed recovery model of a database from Simple to Full.

The consequence is that the database is not getting backed up, until a full backup (or a differential backup for the BACKUP LOG error message if that can be done) has been performed. It will also create some noise in your monitoring system.

The solution is to check if a differential or transaction log backup can be performed before doing the backup. This can be done by checking sys.master_files.differential_base_lsn and sys.database_recovery_status.last_log_backup_lsn.

The backup solution that I have developed has a parameter called @ChangeBackupType.

EXECUTE dbo.DatabaseBackup @Databases = 'USER_DATABASES', @Directory = 'C:\Backup', @BackupType = 'LOG', @ChangeBackupType = 'Y'

If it’s a new database in Full recovery model, the backup type for that database and job run will be changed to full. If it’s a database that was newly changed to Full recovery model, then the backup type for that database and job run will be changed to differential. The next time the job runs a transaction log backup will be performed for that database.

New databases start getting backed up quickly and no more “BACKUP LOG cannot be performed because there is no current database backup.” and “Cannot perform a differential backup for database “”, because a current database backup does not exist.”.

Index Optimization

The design idea is categorize all indexes based on their fragmentation level (High, Medium or Low) and whether there are columns with LOB (Large Object) data types. For each category you can define an action. The possible actions are to rebuild indexes online or offline, reorganize indexes, update statistics, reorganize indexes and update statistics or to do nothing.

Here’s an example.

EXECUTE dbo.IndexOptimize
@Databases = 'USER_DATABASES',
@FragmentationHigh_LOB = 'INDEX_REBUILD_OFFLINE',
@FragmentationHigh_NonLOB = 'INDEX_REBUILD_ONLINE',
@FragmentationMedium_LOB = 'INDEX_REORGANIZE',
@FragmentationMedium_NonLOB = 'INDEX_REORGANIZE',
@FragmentationLow_LOB = 'NOTHING',
@FragmentationLow_NonLOB = 'NOTHING',
@FragmentationLevel1 = 5,
@FragmentationLevel2 = 30,
@PageCountLevel = 1000

Indexes with a fragmentation above 30% are to be rebuilt, online if possible (no LOB columns), otherwise offline (LOB columns). Indexes with a fragmentation between 5% and 30% are to be reorganized. Indexes with a fragmentation below 5% or a size below 1000 pages are not to be touched.

If you are using partitioning IndexOptimize has a parameter, @PartitionLevel to do index rebuilds and reorganizations on the partition level. If you prefer to do sort operations in tempdb you can do that with the parameter @SortInTempdb and if you would like to set a fillfactor you can do that with the parameter @FillFactor.

Reference: Pinal Dave (http://blog.SQLAuthority.com)

Please try it out and let me know what you think. The documentation is available on http://ola.hallengren.com/Documentation.html and the script can be downloaded from http://ola.hallengren.com.