SQL SERVER – Weekly Series – Memory Lane – #025

10 years ago

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

CASE Statement/Expression Examples and Explanation
CASE expressions can be used in SQL anywhere an expression can be used. Example of where CASE expressions can be used include in the SELECT list, WHERE clauses, HAVING clauses, IN lists, DELETE and UPDATE statements, and inside of built-in functions.

This is very old series but very relevant none the less. I later on published a book on this subject and one of the most popular one. You can download the sample chapters over here

Sample Chapters

SQL Server Interview Questions and Answers -Introduction
SQL Server Interview Questions and Answers – Part 1
SQL Server Interview Questions and Answers – Part 2
SQL Server Interview Questions and Answers – Part 3
SQL Server Interview Questions and Answers – Part 4
SQL Server Interview Questions and Answers – Part 5
SQL Server Interview Questions and Answers – Part 6
SQL Server Interview Questions and Answers Complete List Download

2008

Row Constructors – Load Temp Tables From Stored Procedures
Though I use following feature every day now a days when it was newly introduced it was quite a novelty for me. This feature existed in other database but was not available with SQL Server was I was very much delighted to have it.

Generate Foreign Key Scripts For Database
This script generates all the foreign key addition script for your database. Many times there are situations where one need to drop all the foreign key and add them back. This SQL Script can be used for the same purpose.

2009

Check if Current Login is Part of Server Role Member
Most of the time, I get a login and DBA from my clients. However, sometimes I face login-related problems, primarily because my clients forget to confer admin rights on me. Anticipating this situation I perform a simple task, which saves me time and saves me from exasperation. Whenever I receive a server login I run the following query to verify if the client has assigned me the role of system admin or not.

2010

What is Spatial Database? – Developing with SQL Server Spatial and Deep Dive into Spatial Indexing
A spatial database is a database that is optimized to store and query data related to objects in space, including points, lines and polygons. While typical databases can understand various numeric and character types of data, additional functionality needs to be added for databases to process spatial data types.

SELECT TOP Shortcut in SQL Server Management Studio (SSMS)
This tool is pretty old, yet always comes as a handy tip. I had a great trip at TechEd in India. And, during one of my presentations, I was asked if there are any shortcuts to SELECT only TOP 100 records from SSMS. I immediately told him that if he explores the table in SSMS, he can just right click on it and SELECT TOP 1000 records. If he wanted only 100 records, then he could edit that 1000 to 100 by means of going to Options.

Find Most Active Database in SQL Server – DMV dm_io_virtual_file_stats
A quick DMV script which finds the most active database in SQL Server. A must bookmark.

Find Max Worker Count using DMV – 32 Bit and 64 Bit
To address the previous discussion, adding more CPU does not necessarily double the Worker Count. In fact, the logic behind this simple principle is as follows:

For x86 (32-bit) upto 4 logical processors max worker threads = 256
For x86 (32-bit) more than 4 logical processors max worker threads = 256 + ((# Procs – 4) * 8)
For x64 (64-bit) upto 4 logical processors max worker threads = 512
For x64 (64-bit) more than 4 logical processors max worker threads = 512+ ((# Procs – 4) * 8)

2011

Transaction Log Impact Detection Using DMV – dm_tran_database_transactions
A straight to script blog post where I explain using DMV how to detect transaction log.

Finding Location of Log File when Primary Datafile is Crashed
Quick Quiz:Do you need the primary data file available to backup your transaction log after a crash?

This question can have multiple answers. While he asked the question on blog, I was sitting very next to him and he asked what do I think about it. We had less than 10 minutes during the lunch break after which we had to get back on work.

Making Database to Read Only – Changing Database to Read/Write
A simple but effective script about making database read only.

Applying NOLOCK Hint at Query Level – NOLOCK for whole Transaction
How do I apply NOLOCK hint to my whole query. I know that I can use NOLOCK at every table level but I have many tables in my query and I want to apply the same to all the tables. Read the answer to the question in this blog post.

Sudden Death of SSD on my Laptop – A Warning for SSD Users
An entertaining story of how my SSD had sudden death and I struggled to get back online.

2012

Working with FileTables in SQL Server 2012 – Part 1 – Setting Up Environment
Filestream is a very interesting feature, and an enhancement of FileTable with Filestream is equally exciting. Today in this post, we will learn how to set up the FileTable Environment in SQL Server. The major advantage of FileTable is it has Windows API compatibility for file data stored within an SQL Server database. In simpler words, FileTables remove a barrier so that SQL Server can be used for the storage and management of unstructured data that are currently residing as files on file servers. Another advantage is that the Windows Application Compatibility for their existing Windows applications enables to see these data as files in the file system.

Part 1 – Setting Up Environment | Part 2 – Methods to Insert Data Into Table

I suggest you to read above two

Cheatsheet – Released for SQL Server 2012 Edition
SQL Server Cheatsheet has been extremely popular download from my blog. There is plenty of request for me to update it with SQL Server 2012 features. I have finally upgraded the cheat sheet with SQL Server 2012 features. The new cheat sheet has following updates