Recently, someone in the team faced a fairly common requirement – to generate a comma-separated string from values stored in a table. This being the last post of the year, I thought of sharing the 2 most-commonly used methods I know of implementing this requirement.

Do you know any other? If you share it on this post, I will publish it with due credit on my blog.

----------------------------------------------------
--WARNING: THIS SCRIPT IS PROVIDED AS-IS AND WITHOUT
--         WARRANTY.
--         FOR DEMONSTRATION PURPOSES ONLY       
----------------------------------------------------
--Step 01: Generate Temp table to store source data
DECLARE  @NamesTable TABLE (Id INT,
                            Name NVARCHAR(50))

--Step 02: Generate test data
INSERT INTO @NamesTable VALUES (1,'A'),
                               (2,'D'),
                               (2,'C'),
                               (3,'E'),
                               (3,'H'),
                               (3,'G')

--Option 01: My favourite
DECLARE @listStr VARCHAR(MAX) --DO NOT initialize this one!

SELECT @listStr = COALESCE(@listStr + ',' ,'') + nt.Name
FROM @NamesTable nt

SELECT @listStr

--Option 02: Using XML
; WITH CommaSeparatedXML (CommaSeparatedXML)
AS (SELECT CAST((SELECT (',' + nt.Name)
                 FROM @NamesTable nt
                 FOR XML PATH('')) AS NVARCHAR(MAX))
   )
SELECT SUBSTRING(CommaSeparatedXML, 2, LEN(CommaSeparatedXML))
FROM CommaSeparatedXML
GO

Until we meet next time,

Be courteous. Drive responsibly.

As 2011 comes to a close, I have expanded my participation in Social Media on the web. You can now follow me on the following:

Twitter: Tweet to @nakulv_sql

Google+: +Nakul

Facebook: https://www.facebook.com/nakulvachhrajani 

For other options on how to stay in touch with me, please visit my post: SQL Server – How to stay in touch with me – Blog, RSS Feed and E-mail

Have a Very Happy New Year 2012!!

Until we meet next time,

Be courteous. Drive responsibly.

Subscribe to my posts via E-mail: Subscribe here | Read my posts via your favourite RSS reader: Click Here!

Ever since I started working with Microsoft SQL Server, I have heard and read a lot about the coveted “Microsoft Certified Professional” status. I had yet to get my first certification even after working with SQL Server for the last 7 years. As the year 2011 dawned, I had made a resolution that no matter what happens I need to pass the entry level MCTS examination 70-432.

As 2011 comes to a close, I am happy to announce that last week, I achieved the MCTS certification!

Today, I will try to summarize my learning and examination experience with all.

Preparations

I have always enjoyed the preparation phase of any examination – the simple reason being that I never look at things from an examination perspective. I always look at the preparation as an opportunity to learn new things.

Microsoft Learning has identified a great set of preparation materials on the examination’s home page – http://www.microsoft.com/learning/en/us/exam.aspx?id=70-432. Because of the work schedule, the self-paced training kit was the best option for me, and therefore, I bought a copy of the book – “MCTS Self-Paced Training Kit (Exam 70-432): Microsoft® SQL Server® 2008 – Implementation and Maintenance” by Mike Hotek (Microsoft | Amazon | Flipkart).

TIP: For those in India, make sure you get the “Eastern Economy Edition” when ordering it online.

As I mentioned, I have been working with SQL Server for quite some time now, and therefore, did not go through the complete prescribed reading syllabus. Apart from backup-restores, I have not had real-time, practical exposure on Disaster Recovery plans & techniques. So, as soon as I got the book, I started reading up on the areas to which I only had a cursory exposure.

TIP: Online resources are one of the best resources that can help in the preparations. Apart from the online resources listed on the examination’s home page, make sure you subscribe to blogs and newsletters of stalwarts like Pinal Dave (SQLAuthority.com), Vinod Kumar (Extreme Experts), Jacob Sebastian (BeyondRelational.com), and SQLServerCentral.com. Even outside of the preparations , these are some of the blogs & newsletters I read daily and find them to be enriched with knowledge & information.

That’s pretty much it. The end of the year was fast approaching, and after 6 months of slowly reading selected sections of the book, I decided to give the examinations a go!

Examinations

The examinations for Microsoft certifications are conducted through a network of authorized test centers operated by Prometric. One can go to their web-site, search for a convenient test center location, pay online and schedule an examination on a date of their choice. The online registration process is simple and smooth.

I reached the examination centre about 45 minutes before the scheduled time (they require you to come at least 30 minutes before time), and the check-in process was immediately started. They already knew I was coming, and I was given a couple of forms and non-disclosure agreements to sign on. I was required to produce 2 forms of photographic identifications (passport & driver’s license are the most common) and all pockets were to be emptied. All personal belongings need to go into a standard locker, and therefore, they recommend that one should not bring backpacks or any other oversized item to the test center.

After the sign-in process, I was given a set of 2 erasable boards, marker pens and was explained the examination process by a very kind and patient test administrator. I was then assigned a cubicle where once again a few terms & conditions were to be accepted. Once done, the test began. Personally, I found no reason to hurry – I was given a time of more than 2 hours to complete the examination. On an average, I had about 3-4 minutes per question and so, I immediately relaxed – I had time to think the questions through.

I finished the examination well within the prescribed time, and was immediately provided an embossed score sheet by the administrator certifying that I had passed the examination! After that, it was time for some feedback forms and that’s it – it was time to go!

The next day, I received an E-mail from Microsoft giving me my very own MCP number and access to the MCP website from where I could view my certificates, download them and even order a print copy if required.

I was officially, MCTS Certified! A new-year resolution was completed by me for the first time!

My Recommendations

I found the examination completely practical oriented. If you are going to appear for the exam after only reading through the theory, please do not do so. The secret to clearing the examination is – practice, practice & practice. The questions require you to have faced complex, practical issues in SQL Server implementation, maintenance & administration.

My recommendations would be to read through the theory, and reinforce it with practical experience without fail. The questions and practice exercises within the book are great way to get started on the practical side of the preparation. In addition, go through the content and practice tests in the CD/DVD accompanying the book – the practice tests on the CD/DVD simulate the test environment to a real world level and I strongly recommend that you pass at least 3 consecutive times on the simulated tests before you decide to appear for the exams.

I would recommend that anybody who foresees themselves working on Microsoft technologies should prepare and get themselves Microsoft certifications. The certification has tremendous value. For me, the next step is another MCTS certification (70-433: SQL Server 2008: Database Development), but that would be next year. I am exhausted for now!

Until we meet next time,

Be courteous. Drive responsibly.

Subscribe to my posts via E-mail: Subscribe here | Read my posts via your favourite RSS reader: Click Here!

I was playing around with my SQL 2012 (SQL 11/Denali) CTP03 instance the other day, and noticed a great usability enhancement to the Intellisense available in the Query editor.

I was adding a reference to a particular web-page on a query that I was working on. Lo and behold, Intellisense was able to recognize the hyperlink within a comment block, and upon hovering over the link, allowed me to visit the web-page by using the Ctrl+Click combination!

The Christmas week-end is coming up, and if you are working, I hope that that you have an easy end to the week and get to spend quality time with your family.

Have a Merry Christmas!

Until we meet next time,

Be courteous. Drive responsibly.

Subscribe to my posts via E-mail: Subscribe here | Read my posts via your favourite RSS reader: Click Here!

The Merriam-Webster dictionary describes a book (http://www.merriam-webster.com/dictionary/book) as “something that yields knowledge or understanding” and that it is the source of the “the total available knowledge and experience that can be brought to bear on a task or problem”.
I just completed reading a book that does true justice to the definition above. The book was “SQL Server Interview Questions and Answers”, authored by Pinal Dave (blog) and Vinod Kumar (blog), both of whom have been a source of inspiration to the entire SQL Server community and enthusiasts like myself.

First Impressions

The book arrived at my door a couple of weeks ago, and the first thing that surprised me was the size of the book. It’s a nice little book – convenient enough to carry around wherever you go.  Just skimming through the 207 odd pages brought another surprise. The pages are of good quality, and if you are in a habit like me to scribble little notes in the margins, you will be surprised with the large “notes” space that you get. The fonts are large and comfortable to read, preventing eye-strain. These are the advantages of a printed book!

The book was already on its way to becoming a winner.

Mastering the basics

Although the name contains the word “Interview”, this book is much more than just an interview questions guide. As you start reading the book, what you realize immediately is that this small package covers almost everything under the SQL Server umbrella, and caters to multiple audiences of multiple proficiency levels.

Audience

This is not a book just for learners; it’s not supposed to be. I would recommend that everyone working with Microsoft SQL Server for more than half of their day should get a copy of this book. Why? Think of it as a quiz. As you start answering the questions from this book, you begin to get a deeper understanding of the basics of Microsoft SQL Server. To aid the discovery process, there are reference links that are available where one can go to for additional information. Most of them lead to Pinal’s blog, which we all know is a goldmine in itself!

For engineers already having practical, hands-on experience on SQL Server, this book is a refresher course on the basics. It is a ready-reckoner for every SQL Server developer & administrator.

Simple, yet power packed content

For those who have had the privilege to listen to Pinal & Vinod presenting various sessions at Tech-Ed & Community Tech Days or for those who read their posts regularly, the simplicity of the language used in the book will not surprise you. It is really hard to keep things simple, and the hard-work of both the authors deserves a round of applause – they have kept things simple enough for the rookie, while packing enough content to excite the professional. (Spoiler alert!) For example, you can read the answer to the question – “Why can there be only one clustered index on a table?” which can be found on page #92. The concept is a complex one, but the way it has been treated makes it so simple to understand.

Book Organization

Book is organized into the following major sections or chapters:

1. Database Concepts With SQL Server
2. Common Generic Questions & Answers
3. Common Developer Questions
4. Common Tricky Questions
5. Miscellaneous Questions On Sql Server 2008
6. DBA Skills Related Questions
7. Data Warehousing Interview Questions & Answers
8. General Best Practices

What I liked about each section was:

• A very inspiring quote at the start of the section
• A “points to ponder” section at the end of the section
  • Quick references to Joes 2 Pros books (parts 1 through 4)
  • If given proper thought, this part is more than enough to gain reasonable expertise on SQL Server
• Links to Pinal’s posts for further reading
• Tips and things to keep in mind (for example, ready reference DBCC commands, or edition limitations) are provided in-line, which helps establish a context with the content at hand

Where can you get a copy?

Visit the book’s home pages for information on how you can get a copy for yourself:

• Page On Beyondrelational.com
• Page on SQLAuthority.com

(The only thing I found lacking on the book’s home pages is a link to an errata section)

Overall score

The book takes it all! A 5/5 rating!

With real world questions and practical answers, this is one book you simply cannot afford to miss. Happy reading!

Until we meet next time,

Be courteous. Drive responsibly.

Subscribe to my posts via E-mail: Subscribe here | Read my posts via your favourite RSS reader: Click Here!