SQL SERVER – Weekly Series – Memory Lane – #014

Here is the list of selected articles of SQLAuthority.com across all these years. Instead of just listing all the articles I have selected a few of my most favorite articles and have listed them here with additional notes below it. Let me know which one of the following is your favorite article from memory lane.

2007

Query Analyzer Short Cut to display the text of Stored Procedure
In my early career I picked up the shortcut to display the text of the stored procedure and believe me I am using the same shortcut till today. It is like learning how to ride bike, once we learn it we never forget it.

ReIndexing Database Tables and Update Statistics on Tables
This blog post written with keeping SQL Server 2000 in mind and uses older syntax of DBREINDEX. SQL SERVER 2005 uses ALTER INDEX syntax to reindex database. SQL SERVER 2005 supports DBREINDEX but it will be deprecated in future versions.

2008

Find All The User Defined Functions (UDF) in a Database
This is another blog post which straight heads towards script instead of any other writing.

2009

Generate A Single Random Number for Range of Rows of Any Table – Very interesting Question from Reader
Generating random number has been always fun to me. Though the word says random, nothing is truly random if you look at it. To generate the random number has had to use logic and make sure that the number is really random and not predictable. In this blog post, I go over a couple of methods of how to generate random numbers.

Rebuild Every Index of All Tables of Database – Rebuild Index with FillFactor
Fillfactor is a very important concept but I have often spotted that many developers and DBA have no idea about how to select the best value of the FillFactor. However, by spending a little time understanding the business as well understanding how data modeling is done, an experienced DBA often can come up with appropriate value for the FillFactor. The real challenge is how to alter all the indexes and adjust them all with the new FillFactor. In this blog post, I provide the script which does the same task.

SQL SERVER – T-SQL Script for FizzBuzz Logic
Write a program that prints the numbers from 1 to 100. But for multiples of three print “Fizz” instead of the number and for the multiples of five print “Buzz”. For numbers which are multiples of both three and five print “FizzBuzz”. Now try to resolve it yourself or read my solution in the blog post.

2010

Several Readers Questions and Readers Answers
I often get questions on the blog and many times I even get answers from readers as well.

• How the records of a table can be scripted in the INSERT INTO statements?
• How can I track the changes or identify the latest insert-update-delete from a table?
• I am getting collation conflict error when joining columns of two tables?
• How can I get data from a database on another server?
• My Multilanguage data are not inserting correctly and returning as ?  How can I store Multilanguage data in a table?

Location of Resource Database in SQL Server Editions
While working on a project of database backup and recovery, I found out that my client was not aware of the resource database at all. The Resource database is a read-only database that contains all the system objects that are included with SQL Server. This is a very important database and it should be properly backed up.

2011

What is a Technology Evangelist?
Technology evangelists are individuals who, professionally or in their spare time, spread the news about the latest new products.  Sounds like a salesperson, right?  No they are absolutely different. Salespeople also keep up to date with a large number of people, and like to convince others to buy their product – and some will go to any lengths to sell!  An evangelist, on the other hand, is brutally honest about the product, even if sometimes it means not making a sale.  An evangelist is out there to tell the TRUTH.  A salesperson needs to make sales.

Introduction to SEQUENCE – Simple Example of SEQUENCE
SQL Server 2012 contains one of the very interesting feature called SEQUENCE. I had waited for this feature for a really long time. I am glad it is here finally. SEQUENCE allows you to define a single point of repository where SQL Server will maintain in memory counter. In this blog post, I have described what is sequencing in very simple words.

SEQUENCE is not IDENTITY
SQL Server 2012’s SEQUENCE is often confused with IDENTITY, which is not true. They are absolutely different and are built for different purposes. In this blog post, I explain the difference between SEQUENCE and IDENTITY.

Wait Stats – Wait Types – Wait Queues – Day 0 of 28
This was beginning of my one of the most ambitious project. I have been working with SQL Wait Stats for more than 2 years and I had collected some real insight on this subject. I decided to write a blog post series of 28 days where I discuss SQL Wait Stats and their various usability. When I was writing this blog post, I really had no idea where I am heading with this series. My humble attempt was to collect all the knowledge I had on this subject and put in front of everyone. However, destiny had better plans for it. Later that year, I come up with a book on this subject (and very first book in the industry) on SQL Wait Stats. It is still not late, if you are not caught up on this subject, you can still learn this subject from the beginning.

2012

Jan 2012 was a quiz month and we had a great time asking interesting questions and have received fantastic responses. Do you know the answers of the questions – if yes, fantastic if no, you still have time to go over them and refresh your memory.

• Common Gotcha’s Associated with Common Table Expressions (CTE) – Quiz – Puzzle – 26 of 31
• Lots of Date Functions – Find Right One to Use – Quiz – Puzzle – 27 of 31
• Reclaiming Space Back from Database – Quiz – Puzzle – 28 of 31
• Data Collector Usage – Quiz – Puzzle – 29 of 31
• Advantages of Partitioning – Quiz – Puzzle – 30 of 31
• What is Slowly Changing Dimension – Quiz – Puzzle – 31 of 31

Additionally, the same week I wrote a story that how I created a movie from my book. I visited my grandma after publishing my third book and she asked me if she will be able to see the movie from the book. Well, it was not exactly possible to do as this was technical book, however, I got success to achieve the same goal with Pluralsight by building a course for them.

Read the inspirational story here.

Reference: Pinal Dave (https://blog.sqlauthority.com)