SQL Server Journey with SQL Authority

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

DBCC RESEED Table Identity Value – Reset Table Identity
My early career blog discusses about Table Identity Value and how to reset them to the original value.

2008

How to Retrieve TOP and BOTTOM Rows Together using T-SQL 

It is easy to select Top 2 records but it is not easy to select only top and bottom records from the table. Here are three different blog posts which talks about various solutions to do the same.

Part 1 | Part 2 | Part 3

Find Nth Highest Record from Database Table – Using Ranking Function ROW_NUMBER
This is one of the most interesting blog posts as I have received a very innovative solution from Nicholas where we find Nth Highest records from database. I encourage you to try out this script and compare with your existing script. If your script is optimized for performance do send it to me and I will update the blog post.

Retrieve Processes Using Specified Database
A simple straight to script blog post where we discuss how to retrieve the processes accessing database in SQL Server.

Retrieve Any User Defined Object Details Using sys.objects Database
Another straight to retrieve script where we can use size. objects to find any user defined objects from the database.

2009

Pad Ride Side of Number with 0 – Fixed Width Number Display
In this blog post, I answered following question -

I have a situation where I need to display my numbers in fixed format. Like

1 as 0000001
109 as 0000109
0987 as 0000987

Is there any string function in SQL Server which can do this?

Difference Between Union vs. Union All – Optimal Performance Comparison
Difference between Union and Union All is very much known fact however, it is interesting to know what is the optimal performance of either of the syntax. Here is a quick blog post which describes the same. A UNION statement effectively does a SELECT DISTINCT on the results set. If you know that all the records returned are unique from your union, use UNION ALL instead, it gives faster results.

Interesting Observation of ON Clause on LEFT JOIN – How ON Clause affects Resultset in LEFT JOIN
When I want to filter records in a query, I usually put the condition in the WHERE clause. When I make an inner join, I can put the condition in the ON clause instead, giving the same result. But with left joins this is not the case.

2010

Force Index Scan on Table – Use No Index to Retrieve the Data – Query Hint
In this blog post I tried to answer two of the very important questions I keep on encountering with related to Indexing.
Question 1: I have a unique requirement where I do not want to use any index of the table; how can I achieve this?
Question 2: Currently my table uses clustered index and does seek operation; how can I convert seek to scan?

MAXDOP Settings to Limit Query to Run on Specific CPU
This is very simple and known tip. Query Hint MAXDOP – Maximum Degree Of Parallelism can be set to restrict query to run on a certain CPU. Please note that this query cannot restrict or dictate which CPU to be used, but for sure, it restricts the usage of number of CPUs in a single batch.

Quick Note of Database Mirroring
While I was at a meeting I took quick notes about database mirroring. This was very interesting meeting as I was able to note a few things about Database Mirroring.

2011

SQL SERVER – Pending IO request in SQL Server – DMV
“How do we know how many pending IO requests are there for database files (.mdf, .ldf) individually?” Very interesting question and indeed answer is very interesting as well. Here is the quick script which I use to find the same. It has to be run in the context of the database for which you want to know pending IO statistics.

2012

SQL Server 2012 – Microsoft Learning Training and Certification
In this single blog post I have listed all the SQL Server 2012 certificate details from Microsoft. If you are interested in SQL Server 2012 certification and examination, this is a single blog which needs to be bookmarked.

SQL Server Performance: Indexing Basics – SQL in Sixty Seconds #006 – Video
I do not have any words for following video – I think you just have to watch it to understand it. There are already 22,000 views on this blog post.

Install Samples Database Adventure Works for SQL Server 2012
Though, looks very simple but it is not that easy for everyone to install SQL Server Sample Database AdventureWorks. In this blog post, I explain with the script how one can do this efficiently as well have attached a video along with it. This video is the second most viewed video of the whole series.

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

Read Full Post »

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

QUOTED_IDENTIFIER ON/OFF and ANSI_NULL ON/OFF Explanation
When creating or alter SQL object like Stored Procedure, User Defined Function in Query Analyzer, it is created with following SQL commands prefixed and suffixed. What are these – QUOTED_IDENTIFIER ON/OFF and ANSI_NULL ON/OFF? I explained the same in this blog post.

2008

Simple Example of Cursor – Sample Cursor Part 2
One of the most popular script on SQL Server Cursor – a very simple example.

Find Nth Highest Record from Database Table
It is very easy to find the top and bottom records but it is very difficult to find North Record in SQL Server. This blog post discusses how one can do the same.

2009

This is a very old photo of the year 2009 with my friends and I am still their friend and I talk with them regularly.

Pinal Dave, Abhishek Kant, Jacob Sebastian

2010

Performance Comparison – INSERT TOP (N) INTO Table – Using Top with INSERT
This has been one of the most fun blog posts ever. Initially I had no clue that TOP (N) even works with INSERT but later I learned the same and found it very interesting. In this blog post I have compared the performance between TOP N with INSERT and INSERT with SELECT N.

Rollback TRUNCATE Command in Transaction
TRUNCATE is indeed logged operation and it can be rolled back. If you think this statement is incorrect, I suggest you go and check the blog post above here.

Order of Columns in Update Statement Does not Matter
Does ORDER of column matter when we update the statement. In this blog post I explain that in detail how the order works with update as well have listed associated blog post for additional information.

Improve Performance by Reducing IO – Creating Covered Index
Too many indexes, and in particular, too many covering indexes can hamper the performance. Again, indexes are very important aspect of performance tuning. In this post, I am demonstrating very limited capacity of the Index. We will create a covering index for query and see how its performance improves as IO reduces. Please note that do not run this script on production servers as they contain DBCC commands which can create performance issues.

2011

Demo Script – Keeping CPU Busy
In year 2011 I wrote a blog post where I explained how I can keep the CPU busy and can endanger your job. In year 2013 right after 2 years of writing original script I updated my script and wrote a new blog post where I explained how to keep our CPU busy efficiently.

Concurrency Problems and their Relationship with Isolation Level
Concurrency is simply put capability of the machine to support two or more transactions working with the same data at the same time. This usually comes up with data is being modified, as during the retrieval of the data this is not the issue. Most of the concurrency problems can be avoided by SQL Locks. There are four types of concurrency problems visible in the normal programming.

Running Multiple Batch Files Together in Parallel
Recently I was preparing a demo for my next technical session, I had to do run a SQL code in parallel. I decided to use Batch File to run the code. I am not the best guy to with command shell so I did it by following setup. I am still open for a better solution.

Difference between COUNT(DISTINCT) vs COUNT(ALL)
You can clearly notice from the result set that COUNT (ALL ColumnName) is the same as COUNT(ColumnName). The reality is that the “ALL” is actually  the default option and it needs not to be specified. The ALL keyword includes all the non-NULL values.

Stay Connected and Social Media
I think I have finally gotten back my faith in social media. There was a phase when I used to like it and later on I build a dislike for it. However, just like everything else I gain my faith back on social media once I learned how I should use it and how I should share it. All the best practices which I have learned I have blogged about them in this blog post.

Twitter | LinkedIn | Facebook

2012

SQL SERVER – Various Leap Year Logics
When I wrote this blog post, I had really fun to learn new things – this is interesting blog post where I discuss how many different ways to identify if any year is Leap Year or not.

T-SQL Errors and Reactions – Demo – SQL in Sixty Seconds #005 – Video
In this blog post Rick Morelan discusses how SQL Server reacts to when it encounters any error. In this video Rick explains in details how T-SQL errors works and how they react.

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

Read Full Post »

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.

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.

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

Read Full Post »

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

Year 2007 in February I was still learning how to blog and I was trying to get a grasp of the whole blogging thing, I still remember my old day and wonder how many naive I was and I had so much long way to go.

2008

How to Escape Single Quotes
In this example there is a direct script which explains how to escape single quotes in SQL Server.

UDF to Return a Calendar for Any Date for Any Year
I am not sure why do I need this but it was fun to write a script. I encourage all of you to try this out and see if you can come up with a better solution.

2009

Find Current Location of Data and Log File of All the Database
As I am doing lots of experiments on my SQL Server test box, I sometimes get too many files in SQL Server data installation folder – the place where I have all the .mdf and .ldf files are stored.

Observation – Effect of Clustered Index over Nonclustered Index
In our example we have one query which is not using any index. On the same table there is already non-clustered index created, which is also not being used. Now when we created clustered index on the same table, our query suddenly started to use a non-clustered index which was so far it is not using. The interesting part of this is that the query is using non-clustered index when a clustered index is created on the same.

2010

Plan Recompilation and Reduce Recompilation – Performance Tuning
Recompilation process is same as the compilation and degrades server performance. In SQL Server 2000 and earlier versions, this was a serious issue but in SQL server 2005, the severity of this issue has been significantly reduced by introducing a new feature called Statement-level recompilation. When SQL Server 2005 recompiles stored procedures, only the statement that causes recompilation is compiled, rather than the entire procedure.

Recompile Stored Procedure at Run Time
The RECOMPILE hint is used with a query and recompiles only that particular query. However, if the parameters are used in many statements in the stored procedure and we want to recompile all the statements, then instead of using the RECOMPILE option with all the queries, we have one better option that uses WITH RECOMPILE during stored procedure creation or execution.

IF EXISTS(Select null from table) vs IF EXISTS(Select 1 from table)
When I wrote article about SQL SERVER – Stored Procedure Optimization Tips – Best Practices. I received lots of comments on particular blog article. In fact, almost all the comments are very interesting. If you have not read all the comments, I strongly suggest to read them. Click here to read the comments.

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.

• WRITELOG – Wait Type – Day 17 of 28
• LOGBUFFER – Wait Type – Day 18 of 28
• PREEMPTIVE and Non-PREEMPTIVE – Wait Type – Day 19 of 28
• MSQL_XP – Wait Type – Day 20 of 28
• Guest Posts – Feodor Georgiev – The Context of Our Database Environment – Going Beyond the Internal SQL Server Waits – Wait Type – Day 21 of 28
• Guest Post – Jacob Sebastian – Filestream – Wait Types – Wait Queues – Day 22 of 28
• OLEDB – Link Server – Wait Type – Day 23 of 28

2012

Solution Part 2 – A Quick Puzzle on JOIN and NULL – SQL Brain Teaser
Some questions are timeless and they never grow old; no matter how much they grow old their interest never dies. Earlier, I asked a simple puzzle based on a conversation on SQLAuthority Page, and have received an overwhelming response from readers. I still get emails related to this puzzle every day.

Case Sensitive Database and Database User
Interesting Question from Blog Reader: - Here is the scenario. I have two databases: AdventureWorks and MyAdventureWorks. I have a user called SQLAuthority in both  databases. Now when I try to grant access the user in one database it works fine, but when I try to do the same thing in another database, it gives me an error. I am stunned as both the users are same. As a matter of fact, I had just created those users recently, and now I am not able to gain necessary permission.

Disable Guest Account – Serious Security Issue
I welcome guests as much as any other Indian does; however, I am strongly opinionated about guest users in SQL Server. I like to keep it disabled unless there is a special need of it. If there is some persistent need of a guest user, I suggest to create a separate account. Again, there are always special cases where there is a need of this guest account. But in other cases, a guest account is not necessary.

Force Removing User from Database
One of the user created a guest user on the server even though I warned user not to do so. Well, this blog has a solution of the problem where if user does not follow my advice and create a random solution.

T-SQL Constructs – Declaration and Initialization – SQL in Sixty Seconds #003 – Video

Detecting guest User Permissions – guest User Access Status
Earlier I wrote the blog post Disable Guest Account – Serious Security Issue, and I got many comments asking questions related to the guest user.

1) How do we know if the guest user is enabled or disabled?
2) What is the default for guest user in SQL Server?

Reference: Pinal Dave (http://blog.sqlauthorit

Read Full Post »

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

FIX : Error 15023: User already exists in the current database
One of the most popular errors when SQL Server 2000 is migrated to SQL Server 2005.

2008

SQL SERVER – Get Current Database Name
A quick script which will give user current database name.

Introduction to Performance Monitor – How to Use Perfmon
Performance Monitoring is a very important concept and here is how you can turn on Perfmon.

1. Type “start perfmon” at the command prompt.
2. Go to Start | Programs | Administrative Tools | Performance Monitor.
3. Go to Start | Run | Perfmon.

2009

Start SQL Server Instance in Single User Mode
To start SQL Server in single user mode is a very simple procedure as displayed below. Go to SQL Server Configuration Manager and click on  SQL Server 2005 Services. Click on the desired SQL Server instance and right click go to properties. On the Advance table enter param ‘-m;‘ before existing params in Startup Parameters box.

Two excellent blog posts about working with XML -

• Simple Example of Creating XML File Using T-SQL
• Simple Example of Reading XML File Using T-SQL

Restore Master Database – An Easy Solution
It is really unusual to have need of restoring the master database. In very rare situations this need should arise. It is important to have a full backup of the master database, without full backup file of master database it can not be restored.

Reasons to Backup Master Database – Why Should Master Database Backed Up
Master database contains all the system level information of the server. Information about all the login account, system configurations and information required to access all the other database are stored in the master database. If master database is damaged, it will be difficult to use any other database in SQL Server and that makes it most important database of the SQL Server.

2010

Difference Between Update Lock and Exclusive Lock
I have often received this question on this blog as well in different SQL Training.

What is the difference between Update Lock and Exclusive Lock?

In this blog post, the same subject is discussed in brief words.

Stored Procedure Optimization Tips – Best Practices
Best Practices never get old. In this blog post, we go over many efficiency tricks of the performance tuning. There are some of the best practices which are as old as database system is old. Quickly read the blog post and see if there are any tips which you have yet not adopted.

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.

• IO_COMPLETION – Wait Type – Day 10 of 28
• ASYNC_IO_COMPLETION – Wait Type – Day 11 of 28
• PAGELATCH_DT, PAGELATCH_EX, PAGELATCH_KP, PAGELATCH_SH, PAGELATCH_UP – Wait Type – Day 12 of 28
• FT_IFTS_SCHEDULER_IDLE_WAIT – Full Text – Wait Type – Day 13 of 28
• BACKUPIO, BACKUPBUFFER – Wait Type – Day 14 of 28
• LCK_M_XXX – Wait Type – Day 15 of 28
• Guest Post – Jonathan Kehayias – Wait Type – Day 16 of 28

2012

 A Quick Puzzle on JOIN and NULL – SQL Brain Teaser
It seems that we all love to solve puzzles. On SQLAuthority Page, we have been playing the number game and those who are playing with us know how much fun we are having. Sometimes, the answers are so innovative and informative that they open up those aspects of the technology which I have not thought of.

Here is the solution of the puzzle: Solution – A Quick Puzzle on JOIN and NULL – SQL Brain Teaser

What is Big Data – An Explanation in Simple Words
The way the amount of the data has grown so wild that a relational database is not able to handle the processing of this amount of the data. Conventional RDBMS faces challenges to process and analysis data beyond certain very large data. Big Data is a large amount of the data which is difficult or impossible for traditional relational database. Current moving target limits for Big data is terabytes, exabytes and zettabytes.

T-SQL Errors and Reactions – SQL in Sixty Seconds #002 – Video

Be Different – Be A Leader – An Interactive Journey – Questions and Answers – Book and Video
Here is something which only an adult would think. Just like me – there has been many who thought this was the wrong trend. He should not have climbed up the wrong side of the slide as that can lead to accidents and can hurt all the kids involved. Of course – we are correct but the kids were not thinking like that – they were happy and (fortunately) they were safe after playing for a few minutes. I saw that the kids enjoyed this new exercise. They learned how to walk on a slippery slope. Maybe it was a good thing for them if ever they face another situation like this. I once again want to give everything the benefit of the doubt before calling it wrong.

Reference: Pinal Dave (http://blog.sqlauthorit

Read Full Post »