SQL SERVER – Weekly Series – Memory Lane – #020

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

DBCC RESEED Table Identity Value – Reset Table Identity
My early career blog discusses about Table Identity Value and how to reset them to the original value.

2008

How to Retrieve TOP and BOTTOM Rows Together using T-SQL 

It is easy to select Top 2 records but it is not easy to select only top and bottom records from the table. Here are three different blog posts which talks about various solutions to do the same.

Part 1 | Part 2 | Part 3

Find Nth Highest Record from Database Table – Using Ranking Function ROW_NUMBER
This is one of the most interesting blog posts as I have received a very innovative solution from Nicholas where we find Nth Highest records from database. I encourage you to try out this script and compare with your existing script. If your script is optimized for performance do send it to me and I will update the blog post.

Retrieve Processes Using Specified Database
A simple straight to script blog post where we discuss how to retrieve the processes accessing database in SQL Server.

Retrieve Any User Defined Object Details Using sys.objects Database
Another straight to retrieve script where we can use size. objects to find any user defined objects from the database.

2009

Pad Ride Side of Number with 0 – Fixed Width Number Display
In this blog post, I answered following question -

I have a situation where I need to display my numbers in fixed format. Like

1 as 0000001
109 as 0000109

0987 as 0000987

Is there any string function in SQL Server which can do this?

Difference Between Union vs. Union All – Optimal Performance Comparison
Difference between Union and Union All is very much known fact however, it is interesting to know what is the optimal performance of either of the syntax. Here is a quick blog post which describes the same. A UNION statement effectively does a SELECT DISTINCT on the results set. If you know that all the records returned are unique from your union, use UNION ALL instead, it gives faster results.

Interesting Observation of ON Clause on LEFT JOIN – How ON Clause affects Resultset in LEFT JOIN
When I want to filter records in a query, I usually put the condition in the WHERE clause. When I make an inner join, I can put the condition in the ON clause instead, giving the same result. But with left joins this is not the case.

2010

Force Index Scan on Table – Use No Index to Retrieve the Data – Query Hint
In this blog post I tried to answer two of the very important questions I keep on encountering with related to Indexing.
Question 1: I have a unique requirement where I do not want to use any index of the table; how can I achieve this?
Question 2: Currently my table uses clustered index and does seek operation; how can I convert seek to scan?

MAXDOP Settings to Limit Query to Run on Specific CPU
This is very simple and known tip. Query Hint MAXDOP – Maximum Degree Of Parallelism can be set to restrict query to run on a certain CPU. Please note that this query cannot restrict or dictate which CPU to be used, but for sure, it restricts the usage of number of CPUs in a single batch.

Quick Note of Database Mirroring
While I was at a meeting I took quick notes about database mirroring. This was very interesting meeting as I was able to note a few things about Database Mirroring.

2011

SQL SERVER – Pending IO request in SQL Server – DMV
“How do we know how many pending IO requests are there for database files (.mdf, .ldf) individually?” Very interesting question and indeed answer is very interesting as well. Here is the quick script which I use to find the same. It has to be run in the context of the database for which you want to know pending IO statistics.

2012

SQL Server 2012 – Microsoft Learning Training and Certification
In this single blog post I have listed all the SQL Server 2012 certificate details from Microsoft. If you are interested in SQL Server 2012 certification and examination, this is a single blog which needs to be bookmarked.

SQL Server Performance: Indexing Basics – SQL in Sixty Seconds #006 – Video
I do not have any words for following video – I think you just have to watch it to understand it. There are already 22,000 views on this blog post.

Install Samples Database Adventure Works for SQL Server 2012
Though, looks very simple but it is not that easy for everyone to install SQL Server Sample Database AdventureWorks. In this blog post, I explain with the script how one can do this efficiently as well have attached a video along with it. This video is the second most viewed video of the whole series.

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