SQL SERVER – Weekly Series – Memory Lane – #030

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

ASCII to Decimal and Decimal to ASCII
I still use this script many times in my daily work. It works and it is cool.

Script/Function to Find Last Day of Month
A simple trick but we are often lazy to write, a script like this from the archives can immediately help.

Comparison EXCEPT operator vs. NOT IN
The EXCEPT operator returns all of the distinct rows from the query to the left of the EXCEPT operator when there is no matching rows in the right query. The EXCEPT operator is equivalent of the Left Anti Semi Join. EXCEPT operator works the same way NOT IN. EXCEPTS returns any distinct values from the query to the left of the EXCEPT operand that do not also return from the right query.

2005 Explanation Left Semi Join Showplan Operator and Other Operator
The Left Semi Join operator returns each row from the first (top) input when there is a matching row in the second (bottom) input. If no join predicate exists in the Argument column, each row is a matching row. The Left Anti Semi Join operator returns each row from the first (top) input when there is no matching row in the second (bottom) input. If no join predicate exists in the Argument column, each row is a matching row.

NorthWind Database or AdventureWorks Database – Samples Databases
Where can you find Northwind database and how can you install it – this is very simple basic blog which can do the same.

Disadvantages (Problems) of Triggers
I personally do not prefer to use triggers in my daily work as there are many disadvantages associated with it. I only use it in the case of debugging. In this blog post, I have listed few of the disadvantages of triggers.

Change Default Fill Factor For Index
Fill factor is one of the most important parts of the index and I have seen many database getting slower or under performing due to they have very incorrect fill factor. In this blog post I explain how one can change fill factor to good value.

2008

SQL SERVER – PIVOT Table Example
In this blog in very simple words I have attempted to explain how PIVOT works and also have included the script along with it.

T-SQL Script to Devide One Column into Two Column
This is indeed a strange question – why would you divide one column into two columns but there are reasons for doing the same. We often faced a situation where one column in the database contained two values which were separated by comma. We wanted to separate these two values in their own columns. It was interesting that the value of the column was variable and something dynamic needed to be written.

2009

Download PDF SQL Server Cheat Sheet
You can download SQL Server Cheat Sheet and print it on a glossy paper yourself. I request you all to spread the words and pass this cheat sheet to your friends. Bookmark it, reprint it, and distribute it! Due to copyright issues, you are not allowed to host it online anywhere other than its original location listed below.

Fix connection error to SQL Server
There are many reasons for user not able to connect to SQL Server. In this blog post, I went over in detail about the reasons how user can’t connect to SQL Server and what is the workaround for the same. I also built a video for the same, where I demonstrate how one can resolve it in 60 seconds.

2010

Simple Example of Snapshot Isolation – Reduce the Blocking Transactions
Snapshot Isolation was introduced in SQL Server in 2005. However, the reality is that there are still many software shops which are using the SQL Server 2000, and therefore cannot be able to maintain the Snapshot Isolation. Many software shops have upgraded to the later version of the SQL Server, but their respective developers have not spend enough time to upgrade themselves with the latest technology. “It works!” is a very common answer of many when they are asked about utilizing the new technology, instead of backward compatibility commands.

2011

Connecting to Server Using Windows Authentication by SQLCMD
In this simple blog post I explain how to connect to SQL Server using SQLCMD.

Running SSIS Package in Scheduled Job
Can we run SSIS package from schedule Job? Absolutely, we can. Additionally we can also run an SSIS package from the command line.

2012

Saturday Fun Puzzle with SQL Server DATETIME2 and CAST
Here is the puzzle with DATETIME2

Renaming Index – Index Naming Conventions
Is NCI and CI prefixed required to additionally describe the index names. I have once received suggestion to even add fill factor in the index name – which I do not recommend at all. What do you think should be an ideal name of the index, so it explains all the most important properties? Additionally, you are welcome to vote if you believe changing the name of the index is just a waste of time and energy.

SmallDateTime and Precision – A Continuous Confusion
This question once again brings up another ancient question:  “Do we need a database designer?” I often come across databases which are facing major performance problems or have redundant data. Normalization is often ignored when a database is built fast under a very tight deadline. Often I come across a database which has table with unnecessary columns and performance problems. While working as Developer Lead in my earlier jobs, I have seen developers adding columns to tables without anybody’s consent and retrieving them as SELECT *.

Standard Reports from SQL Server Management Studio – SQL in Sixty Seconds #016 – Video

A Puzzle – Fun with NULL – Fix Error 8117
It’s always fun to ask this question during interviews, because in every interview, I get a different answer. NULL is often confused with false, absence of value or infinite value. Honestly, NULL is a very interesting subject as it bases its behavior in server settings. There are a few properties of NULL that are universal, but the knowledge about these properties is not known in a universal sense.

A Puzzle – Fun with SEQUENCE in SQL Server 2012 – Guess the Next Value
See if you can figure out why there is an error due to UNION.

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