SQL SERVER – Weekly Series – Memory Lane – #015

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

Primary Key Constraints and Unique Key Constraints
There is a big difference between Primary Key and Unique Key, however, quite common they are confused and mixed up. Well, this blog post explains the concept in very simple words and explains the scripts as well.

2008

Simple Example of WHILE Loop with BREAK and CONTINUE
One of the most popular blog posts of my blog has been from this week. Lots of people do not know how to run the WHILE loop in SQL Server and this blog post precisely describes the same.

SQL Joke, SQL Humor, SQL Laugh – Funny Microsoft Quotes
This is an interesting post – Instead of writing more, I suggest you head towards this blog post and read what I have written there. It is funny quotes which I had heard in Microsoft World.

Import CSV File Into SQL Server Using Bulk Insert – Load Comma Delimited File Into SQL Server
Over 700 comments and people are still loving it. I personally love how simple words I had blogged about it. This blog post answer following questions – How to import CSV file into SQL Server? How to load CSV file into SQL Server Database Table? How to load comma delimited file into SQL Server? Let us see the solution in quick steps.

2009

Observation – Effect of Clustered Index over Nonclustered Index

A query which is not using nonclustered index to retrieve results used nonclustered index when a clustered index created on the column which is retrieved. The reason for this happening is that every nonclustered index refers to clustered index internally. When a clustered index is created on the table it reorganizes the table in the physical order of the clustered index. When there was no clustered index created on the table at that time all nonclustered index points to data in the table to retrieve the data, however once clustered index is created all the nonclustered indexes are reorganized and they point to clustered index.

Quickest Way to – Kill All Threads – Kill All User Session – Kill All Processes
More than a year ago, I wrote how to kill all the processes running in SQL Server. Just a day ago, I found the quickest way to kill the processes of SQL Server. While searching online I found very similar methods to my previous method everywhere. Today in this article, I will write the quickest way to achieve the same goal.

Introduction to Force Index Query Hints – Index Hint
When any query is running SQL Server Engine determines which index has to be used. SQL Server makes use Index which has lowest cost based on performance. An index which is the best for performance is automatically used. There are some instances when Database Developer is best judge of the index used. The DBA can direct SQL Server which index to be used to execute a query.

• Introduction to Force Index Query Hints – Index Hint
• Introduction to Force Index Query Hints – Index Hint – Part2

2010

MVP Open Day South Asia – Jan 20, 2010 – Jan 23, 2010 – Review Part Fun
Here is a photo which is almost 3 years old. It is very surprising that how time passes by and our daughter is now almost 3.5 years old. In the photograph she is only few months old.

Get the List of Object Dependencies – sp_depends and information_schema.routines and sys.dm_sql_referencing_entities
I have quite often seen that information_schema. Routines give proper dependency relationship where assp_depends returns an incorrect answer. However, as per book online sp_depends will be deprecated, and instead, sys.dm_sql_referencing_entities and sys.dm_sql_referenced_entities are recommended. Let us quickly see where sp_depends fail and other solutions work fine.

Stream Aggregate Showplan Operator – Reason of Compute Scalar before Stream Aggregate
Stream Aggregate is a quite commonly encountered showplan operator. I have often found it in very simple COUNT(*) operation’s execution plan. The Stream Aggregate operator group rows by one or more columns and then calculate one or more aggregate expression returned by the query.

Find the Size of Database File – Find the Size of Log File
A simple straight to the script blog post!

Brief Note about StreamInsight – What is StreamInsight
StreamInsight is a new event processing platform introduced in upcoming version SQL Server 2008 R2. Similar to other components such as SSIS, SSAS or Service Broker, it also needs to be installed along with the SQL Server. Read this blog post for basic introductory information. 

2011

In the year 2011 February I wrote a month long blog series on the subject SQL Wait Stats, which eventually converted to mega successful book SQL Wait Stats.

• Introduction to Wait Stats and Wait Types – Wait Type – Day 1 of 28
• Signal Wait Time Introduction with Simple Example – Wait Type – Day 2 of 28
• DMV – sys.dm_os_wait_stats Explanation – Wait Type – Day 3 of 28
• DMV – sys.dm_os_waiting_tasks and sys.dm_exec_requests – Wait Type – Day 4 of 28
• Capturing Wait Types and Wait Stats Information at Interval – Wait Type – Day 5 of 28
• CXPACKET – Parallelism – Usual Solution – Wait Type – Day 6 of 28
• CXPACKET – Parallelism – Advanced Solution – Wait Type – Day 7 of 28
• SOS_SCHEDULER_YIELD – Wait Type – Day 8 of 28
• PAGEIOLATCH_DT, PAGEIOLATCH_EX, PAGEIOLATCH_KP, PAGEIOLATCH_SH, PAGEIOLATCH_UP – Wait Type – Day 9 of 28

2012

An Incredible Successful SQL Saturday #116 Event – First SQL Saturday in India
Here a single photo which summarizes this landmark event in India.

Finding Count of Logical CPU using T-SQL Script – Identify Virtual Processors
One of my old friends wanted to know how many virtual processors are there available for SQL Server. He already had a script for SQL Server 2008 but was mainly looking for SQL Server 2000. He made me go to my past. I found a script from my old emails and modified it for him. 

Convert Subquery to CTE – SQL in Sixty Seconds #001 – Video
It has been one year since I started this series – I am so happy that we have come a long way since the series started. This series has been the most popular series on my blog. I am very proud how good the response I have received in this series. Here is the very first video which I am still happy that the most popular video of this series.

[youtube=http://www.youtube.com/watch?v=Z7VuYBXX2dg]

INNER JOIN Returning More Records than Exists in Table
Can inner join return more rows than the rows exists in either of the table. Absolutely! Read this blog for excellent conversation and few interesting pointers why this can happen. I strongly recommend this blog post! There can’t be any better blog post than this one to end today’s series.

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