SQL SERVER – Weekly Series – Memory Lane – #019

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

QUOTED_IDENTIFIER ON/OFF and ANSI_NULL ON/OFF Explanation
When creating or alter SQL object like Stored Procedure, User Defined Function in Query Analyzer, it is created with following SQL commands prefixed and suffixed. What are these – QUOTED_IDENTIFIER ON/OFF and ANSI_NULL ON/OFF? I explained the same in this blog post.

2008

Simple Example of Cursor – Sample Cursor Part 2
One of the most popular script on SQL Server Cursor – a very simple example.

Find Nth Highest Record from Database Table
It is very easy to find the top and bottom records but it is very difficult to find North Record in SQL Server. This blog post discusses how one can do the same.

2009

This is a very old photo of the year 2009 with my friends and I am still their friend and I talk with them regularly.

Pinal Dave, Abhishek Kant, Jacob Sebastian

Pinal Dave, Abhishek Kant, Jacob Sebastian

2010

Performance Comparison – INSERT TOP (N) INTO Table – Using Top with INSERT
This has been one of the most fun blog posts ever. Initially I had no clue that TOP (N) even works with INSERT but later I learned the same and found it very interesting. In this blog post I have compared the performance between TOP N with INSERT and INSERT with SELECT N.

Rollback TRUNCATE Command in Transaction
TRUNCATE is indeed logged operation and it can be rolled back. If you think this statement is incorrect, I suggest you go and check the blog post above here.

Order of Columns in Update Statement Does not Matter
Does ORDER of column matter when we update the statement. In this blog post I explain that in detail how the order works with update as well have listed associated blog post for additional information.

Improve Performance by Reducing IO – Creating Covered Index
Too many indexes, and in particular, too many covering indexes can hamper the performance. Again, indexes are very important aspect of performance tuning. In this post, I am demonstrating very limited capacity of the Index. We will create a covering index for query and see how its performance improves as IO reduces. Please note that do not run this script on production servers as they contain DBCC commands which can create performance issues.

2011

Demo Script – Keeping CPU Busy
In year 2011 I wrote a blog post where I explained how I can keep the CPU busy and can endanger your job. In year 2013 right after 2 years of writing original script I updated my script and wrote a new blog post where I explained how to keep our CPU busy efficiently.

Concurrency Problems and their Relationship with Isolation Level
Concurrency is simply put capability of the machine to support two or more transactions working with the same data at the same time. This usually comes up with data is being modified, as during the retrieval of the data this is not the issue. Most of the concurrency problems can be avoided by SQL Locks. There are four types of concurrency problems visible in the normal programming.

Running Multiple Batch Files Together in Parallel
Recently I was preparing a demo for my next technical session, I had to do run a SQL code in parallel. I decided to use Batch File to run the code. I am not the best guy to with command shell so I did it by following setup. I am still open for a better solution.

Difference between COUNT(DISTINCT) vs COUNT(ALL)
You can clearly notice from the result set that COUNT (ALL ColumnName) is the same as COUNT(ColumnName). The reality is that the “ALL” is actually  the default option and it needs not to be specified. The ALL keyword includes all the non-NULL values.

Stay Connected and Social Media
I think I have finally gotten back my faith in social media. There was a phase when I used to like it and later on I build a dislike for it. However, just like everything else I gain my faith back on social media once I learned how I should use it and how I should share it. All the best practices which I have learned I have blogged about them in this blog post.

Twitter | LinkedIn | Facebook

2012

SQL SERVER – Various Leap Year Logics
When I wrote this blog post, I had really fun to learn new things – this is interesting blog post where I discuss how many different ways to identify if any year is Leap Year or not.

T-SQL Errors and Reactions – Demo – SQL in Sixty Seconds #005 – Video
In this blog post Rick Morelan discusses how SQL Server reacts to when it encounters any error. In this video Rick explains in details how T-SQL errors works and how they react.

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

About these ads

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s