SQL SERVER – Weekly Series – Memory Lane – #018

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

Restore Database Backup using SQL Script (T-SQL)
This is one of my most popular blog posts where I explained how to take backup using SQL Script in a few T-SQL statement. There are more than 500 comments on this blog so far.

T-SQL Script to find the CD key from Registry
A Quick script which can help you find the CD Key from the registry.

Delete Duplicate Records – Rows
Find and Delete Duplicate Records – an extremely popular script and video.

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

2008

SELECT 1 vs SELECT * – An Interesting Observation
Many times I have seen the issue of SELECT 1 vs SELECT * discussed in terms of performance or readability while checking for the existence of rows in the table. I ran quick 4 tests about this observed that I am getting same result when used SELECT 1 and SELECT *. I think smart readers of this blog will come up the situation when SELECT 1 and SELECT * have a different execution plan when used to find existence of rows.

Dynamic Case Statement – FIX : ERROR 156 : Incorrect syntax near the keyword
This blog post explains a quick resolutions about how to resolve the issue when there is Dynamic Case Statement in the query.

Transfer The Logins and The Passwords Between Instances of SQL Server 2005
This is one of the most popular blog post where I demonstrated how to move login from one server to another server.

How to Retrieve TOP and BOTTOM Rows Together using T-SQL
I had to find TOP 1 and BOTTOM 1 record together. I right away that I should just do UNION but then I realize that UNION will not work as it will only accept one ORDER BY clause. If you specify more than one ORDER BY clause. It will give error. In this blog post, I explain how the same can be achieved with simple T-SQL script.

2009

Find Relationship of Foreign Key and Primary Key using T-SQL – Find Tables With Foreign Key Constraint in Database
A Quick and effective script which does what it says!

2010

Introduction to Rollup Clause
In this article we will go over basic understanding of the Rollup clause in SQL Server. ROLLUP clause is used to do aggregate operation on multiple levels in hierarchy. Let us understand how it works by using an example.

INSERT TOP (N) INTO Table – Using Top with INSERT

Note that there are two different techniques to limit the insertion of rows into the table.

Method 1:

INSERT INTO TABLE …
SELECT TOP (N) Cols…
FROM Table1

Method 2:

INSERT TOP(N) INTO TABLE …
SELECT Cols…
FROM Table1

Data and Page Compressions – Data Storage and IO Improvement
In SQL Server data compression is implemented at two levels: ROW and PAGE. Even page compression automatically implements row compression. Tables and indexes can be compressed when they are created by using the CREATE TABLE and CREATE INDEX statements.

Here is one of my photo from Sri Lanka TechEd 2010

2011

In the year 2011 February I wrote a month long blog series on the subject SQL Wait Stats, which eventually converted to mega successful book SQL Wait Stats.

• 2000 – DBCC SQLPERF(waitstats) – Wait Type – Day 24 of 28
• 2011 – Wait Type – Day 25 of 28
• Guest Post – Glenn Berry – Wait Type – Day 26 of 28
• Best Reference – Wait Type – Day 27 of 28
• Summary of Month – Wait Type – Day 28 of 28

Here is one of my interview taken by Michael J Swart. It was indeed too much fun to do the interview!

Pinal Dave: Blogger, MVP and now Interviewee by Michael J Swart

2012

guest User and MSDB Database – Enable guest User on MSDB Database
Disable the guest user in the user-created database. Additionally, I have mentioned that one should let the user account become enabled in the MSDB database. I got many questions asking if there is any specific reason why this should be kept enabled, questions like, “What is the reason that the MSDB database needs guest user?” Honestly, I did not know that the concept of the guest user will create so much interest in the readers. So now let’s turn this blog post into questions and answers format.

A Cool Trick – Restoring the Default SQL Server Management Studio – SSMS
This blog post is dedicated to all the beginners in SQL Server. It is extremely simple to reset the SSMS layout to default layout. The default layout involves 2 major things 1) Object Explorer on left side 2) Query Windows on the right side (80% screen estate). Personally I am so used to this as well that if there is any other changes in the same, I do not enjoy working on the environment.

Identifying guest User using Policy Based Management
One of the requests I received was whether we could create a policy that would prevent users unable guest user in user databases. Well, here is a quick tutorial to answer this. Let us see how quickly we can do it.

Detecting Leap Year in T-SQL using SQL Server 2012 – IIF, EOMONTH and CONCAT Function
This blog post is dedicated to February 29, the date which shows up at every four years. I wrote a blog post where I explained how I to detect this rare date.

Function: Is Function – SQL in Sixty Seconds #004 – Video
This quick video explains how to detect if any year is Leap year or not.

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

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