SQL SERVER – Weekly Series – Memory Lane – #029

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

List all the database
A Simple script which list all the database from the server.

Function to Parse AlphaNumeric Characters from String
Following function keeps only Alphanumeric characters in string and removes all the other character from the string. This is a very handy function when working with Alphanumeric String only. I have used this many times.

Query to Find First and Last Day of Current Month
The blog contains the query, which will run respective on today’s date. It will return Last Day of Previous Month, First Day of Current Month, Today, Last Day of Previous Month and First Day of Next Month respective to current month.

Binary Sequence Generator – Truth Table Generator
Run script from the blog in the query editor to generate truth table with its decimal value and binary sequence. The truth table is 512 rows long. This can be extended or reduced by adding or removing cross joins respectively.

Disable Index – Enable Index – ALTER Index
A straight to script blog where I demonstrate how to enable and disable index.

2008

SQL SERVER – Comparison : Similarity and Difference #TempTable vs @TempVariable – Part 2
There is lots of confusing going on about difference between TempTable and TempVariable. In this blog I take another attempt about how to find difference between TempTable and TempVariable.

SQL SERVER Database Administrator Job Description
I have received quite a lot of requests to update it or post something similar. Writing SQL Articles are easier than writing Job description for DBA. I have read many job description and job posting at Best SQL Jobs and found the following job description.

2009

This year, I have attended my very first TechEd and presented very first session ever. Here is a photo with my wife Nupur at this event.

How to Drop Temp Table – Check Existence of Temp Table
A quick direct to script blog post.

2010

Understanding ALTER INDEX ALL REBUILD with Disabled Clustered Index
The difference between ALTER INDEX ALL REBUILD and ALTER INDEX REBUILD along with the disabled clustered index. Instead of writing a big theory, we will go over the demo right away. Here are the steps that we intend to follow.

1) Create Clustered and Non-clustered Index
2) Disable Clustered and Non-clustered Index
3) Enable – a) All Indexes, b) Clustered Index

Find Most Expensive Queries Using DMV
I was asked in recent query tuning consultation project, if I can share my script which I use to figure out which is the most expensive queries are running on SQL Server. This script is very basic and very simple, there are many different versions are available online.

List All the DMV and DMF on Server
“How many DMVs and DVFs are there in SQL Server 2008?” – this question was asked to me in one of the recent SQL Server Trainings. The answer is very simple in this blog post.

2011

Import CSV File into Database Table Using SSIS
It is a very frequent request to upload CSV file to database or Import CSV file into a database. I have previously written an article how one can do this using T-SQL over here  Import CSV File Into SQL Server Using Bulk Insert. One of the request which I quite often encounter is how I can do the same using SSIS package and automate it. Today we will understand the same using images.

Restart Remote Computer – Shutdown Remote Computer
Here is a quick trick which I use almost everyday. I have more than one computer at my desk and I manage multiple instances from a single monitor. Some of my computers are in a different location and I have to physically walk there. In this blog post, I demonstrated a quick method where I can use CMD prompt to restart and shutdown remote computer.

2012

Columnstore Index and sys.dm_db_index_usage_stats
Regular SQL Server DMV does not give information for usage stats for columnstore indexes and for that one has to use different DMV. In this blog post I explain the same in detail with DMV script. I also describe my journey how I come across this DMV.

Get Schema Name from Object ID using OBJECT_SCHEMA_NAME
Sometime a simple solution have even simpler solutions but we often do not practice it as we do not see the value in it or find it useful. Well, today’s blog post is also about something which I have seen not practiced much in codes. We are so much comfortable with an alternative usage that we do not feel like switching how we query the data.

SQL in Sixty Seconds – 5 Videos from Joes 2 Pros Series
Here are the five videos which Rick and I have built for Joes 2 Pros series.

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

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

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

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

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

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