SQL SERVER – Weekly Series – Memory Lane – #023

SQL SERVER - Weekly Series - Memory Lane - #023 memorylane 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.


TempDB is Full. Move TempDB from one drive to another drive
Move TempDB from one drive to another drive. There are major two reasons why TempDB needs to move from one drive to another drive.
1) TempDB grows bigger and the existing drive does not have enough space.
2) Moving TempDB to another file group which is on a different physical drive helps to improve database disk read, as they can be read simultaneously.

T-SQL Paging Query Technique Comparison – SQL 2000 vs SQL 2005
I was paging in SQL Server 2000 using Temp Table or Derived Tables. I decided to check out new function ROW_NUMBER () in SQL Server 2005. ROW_NUMBER() returns the sequential number of a row within a partition of a result set, starting at 1 for the first row in each partition. I have compared both the following query in SQL Server 2005.


Find Nth Highest Salary of Employee – Query to Retrieve the Nth Maximum value
This question is quite a popular question and it is interesting that I have been receiving this question every other day. I have already answered this question here. “How to find the Nth Highest Salary of Employee”.

15 Best Practices for Better Database Performance
I have written 14 best practices here, read them all and let me know what is as per your opinion should be the 15th best practice.


IntelliSense Does Not Work – Enable IntelliSense
While I was working with SQL Server 2008 IntelliSense, I realized that it was not functioning as I expected. Even after I had enabled IntelliSense it was still not opening any suggestions at all. After a while, I figured out some vital information regarding how to make sure IntelliSense smoothly works all the time without you giving any trouble.

Reseed Identity of Table – Table Missing Identity Values – Gap in Identity Column
Some time ago I was helping one of my Junior Developers who presented me with an interesting situation. He had a table with Identity Column. Because of some reasons he was compelled to delete few rows from the table. On inserting new rows in the table he noticed that the rows started from the next identity value which created gap in the identity value. His application required all the identities to be in sequence, so this was certainly not a small issue for him.

Very Powerful and Feature-Rich Backup, Zip and FTP Utility SQLBackupAndFTP
SQLBackupAndFTP is MS SQL Server backup software. This tool performs several tasks such as running scheduled backups of SQL Server or SQL Server Express databases, zipping the backups, storing them on a network or on a FTP server, removing old backups, and finally, sending an e-mail confirmation on job’s success or failure. Besides, SQLBackupAndFTP Free is freeware, which is free from any spyware. Best of all, this ingeniously simple tool is extremely affordable for all. You just need to install it on the server and you can get up and running really fast.


Simple Installation of Master Data Services (MDS) and Sample Packages – Very Easy
First of all, go to your SQL Server 2008. Install self-extracted folder and find the .msi file for C:\1033_enu_lp\x64\setup\masterdataservices.msi. Once you clicked on the file, follow the image tour below. You can ask me any questions in case you are still confused with any of the steps of the installation.

Difference Between GRANT and WITH GRANT
The difference between these options is very simple. In case of only GRANT, the username cannot grant the same permission to other users. On the other hand, with the option WITH GRANT, the username will be able to give the permission after receiving requests from other users.

Enable Identity Insert – Import Expert Wizard
Enabling the  property “Enable Identity Insert” by checking the checkbox allows the values to be inserted in the identity field. This way, the exact identity values are moved from source database to the destination table.

Introduction to Snapshot Database – Restore From Snapshot
A Database Snapshot is a read-only, static view of a database (the source database). Multiple snapshots can exist on a source database and can always reside on the same server instance as the database. Each database snapshot is consistent, in terms of transactions, with the source database as of the moment of the snapshot’s creation. A snapshot persists until it is explicitly dropped by the database owner.


A Simple Example of Contained Databases
A Contained Database is a database which contains all the necessary settings and metadata, making database easily portable to another server. This database will contain all the necessary details and will not have to depend on any server where it is installed for anything. You can take this database and move it to another server without having any worries.


sp_describe_first_result_set New System Stored Procedure in SQL Server 2012
SQL Server never stops to amaze me. Here is the example of it sp_describe_first_result_set. I stumbled upon it when I was looking for something else on BOL. This new system stored procedure did attract me to experiment with it. This SP does exactly what its names suggests – describes the first result set. Let us see a very simple example of the same. Please note that this will work on only SQL Server 2012.

A Puzzle – Illusion – Confusion – April Fools’ Day
Run following in SQL Server Management Studio and observe the output:

SELECT 30.0/(-2.0)/5.0;
SELECT 30.0/-2.0/5.0;

Here are a few questions for you:

1) What will be the result of the above two queries?
2) Why?

Use ROLL UP Clause instead of COMPUTE BY
COMPUTE BY clause is replaced by ROLL UP clause in SQL Server 2012. However there is no direct replacement of the code, user have to re-write quite a few things when using ROLL UP instead of COMPUTE BY. The primary reason is that how each of them returns results.

#TechEdIn – TechEd India 2012 Memories and Photos
There are many different memories in the link above.

SQL SERVER - Weekly Series - Memory Lane - #023 TechEd05
Pinal, Shaivi and Nupur – 3 of us!

Performance: Indexing Basics – Interview of Vinod Kumar by Pinal Dave
Here is a 200 second interview of Vinod Kumar I took right after completing the course. There are many people who said they would like to read the transcript of the video. Here I have generated the same.

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

Memory Lane
Previous Post
SQL SERVER – Group by Rows and Columns using XML PATH – Efficient Concating Trick
Next Post
SQL SERVER – Pass One Stored Procedure’s Result as Another Stored Procedure’s Parameter

Related Posts

Leave a Reply