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

Search Text Field – CHARINDEX vs PATINDEX
Both functions take two arguments. With PATINDEX, you must include percent signs before and after the pattern, unless you are looking for the pattern as the first (omit the first %) or last (omit the last %) characters in a column. For CHARINDEX, the pattern cannot include wildcard characters. The second argument is a character expression, usually a column name, in which Adaptive Server searches for the specified pattern.

Explanation of TRY…CATCH and ERROR Handling
SQL Server provides the TRY…CATCH construct, which is already present in many modern programming languages. TRY/CATCH helps to write logic separate the action and error handling code. The code meant for the action is enclosed in the TRY block and the code for error handling is enclosed in the CATCH block. In case the code within the TRY block fails, the control automatically jumps to the CATCH block, letting the transaction roll back and resume execution. In addition to this, the CATCH block captures and provides error information that shows you the ID, message text, state, severity and transaction state of an error.

UDF – User Defined Function to Extract Only Numbers From String
This is straight to script blog post where I had written about UDF which extracts only numbers from strings.

Disable Triggers – Drop Triggers
Till today I use it extensively when I have to disable or drop trigger.

Stored Procedures Advantages and Best Advantage
Here is a very old but still today relevant blog post where I discuss why one should use stored procedures. I just can’t believe that such a short article which I wrote 6 years ago is still very valid article and relevant.

2008

Change Order of Column In Database Tables
How to change the order of the column in a database table? It happens many times table with few columns is already created. After a while there is need to add new column to the previously existing table.

Restore Database Using Corrupt Datafiles (.mdf and .ldf) – Part 2
A straight to script kind of blog post!

2009

Maximum Columns per Primary Key
Maximum columns per Primary Key Index is 16. In fact, 16 is the limit for columns per Foreign Key and Index Key. You cannot have more than 16 columns per Index Key, Primary Key or Foreign Key. So, reduce the columns in those columns to less than or equal to 16 columns.

Restore or Attach Database Without .NDF or .MDF is Not Possible
All the .mdf and .ndf files are mandatory to attach or restore database successfully.  Even though  you have all the transactions stored in .ldf you will not be able to restore the database completely.

Interesting Observation of DMV of Active Transactions and DMV of Current Transactions

1) sys.dm_tran_active_transactions – Returns information about transactions for the instance of SQL Server.

2) sys.dm_tran_current_transaction – Returns a single row that displays the state information of the transaction in the current session.

Introduction to JOINs – Basic of JOINs
I have tried to explain the fundamentals of the join using following quick method. I used diagram and simple script which are quite popular.

2010

Configure Management Data Collection in Quick Steps – T-SQL Tuesday #005
The three most important components of any computer and server are the CPU, Memory, and Hard disk specification. This post talks about  how to get more details about these three most important components using the Management Data Collection. Management Data Collection generates the reports for the three said components by default. Configuring Data Collection is a very easy task and can be done very quickly.

2011

Add New Column With Default Value
Adding default value to column is very easy task but not everyone knows the secret and they often write long script do so. In this to the point script you can see how easy it is to do it.

Query to Recent Query on Server with Execution Plan Function to Get SQL
The APPLY operator allows you to invoke a table-valued function for each row returned by an outer table expression of a query. The table-valued function acts as the right input and the outer table expression acts as the left input. The right input is evaluated for each row from the left input and the rows produced are combined for the final output.

2012

DMV sys.dm_exec_describe_first_result_set_for_object – Describes the First Result Metadata for the Module
DMV sys. dm_exec_describe_first_result_set_for_object returns a result set which describes the columns used in the stored procedure. Here is the quick example. Let us first create stored procedure.

Social Media Series – Twitter and Myself

Follow me on Twitter

Social Media Series – Facebook and Google+

Like me on Facebook |Add me to Google+ 

Social Media Series – YouTube and Movies

Subscribe us on YouTube

Social Media Series – LinkedIn and Professional Profile

Connect me with LinkedIn

Installing AdventureWorks Sample Database – SQL in Sixty Seconds #010 – Video
Many SQL Books and online blogs and articles there are scripts written by using AdventureWork database. I often received request that where people can get sample database as well how to restore sample database.

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

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.

2008

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.

2009

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.

2010

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.

2011

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.

2012

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.

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 (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

@@IDENTITY vs SCOPE_IDENTITY() vs IDENT_CURRENT – Retrieve Last Inserted Identity of Record
This was one of the most interesting blog posts I have ever written. This blog post I wrote as I have been receiving lots of questions related to identity. To avoid the potential problems associated with adding a trigger later on, always use SCOPE_IDENTITY() to return the identity of the recently added row in your T SQL Statement or Stored Procedure.

Difference between DISTINCT and GROUP BY – Distinct vs Group By
A DISTINCT and GROUP BY usually generate the same query plan, so performance should be the same across both query constructs. GROUP BY should be used to apply aggregate operators to each group. If all you need is to remove duplicates then use DISTINCT. If you are using sub-queries execution plan for that query varies so in that case you need to check the execution plan before making decision of which is faster.

Index Seek Vs. Index Scan (Table Scan)
Index Scan retrieves all the rows from the table. Index Seek retrieves selective rows from the table.

Index Scan:
Since a scan touches every row in the table whether or not it qualifies, the cost is proportional to the total number of rows in the table. Thus, a scan is an efficient strategy if the table is small or if most of the rows qualify for the predicate.

Index Seek:
Since a seek only touches rows that qualify and pages that contain these qualifying rows, the cost is proportional to the number of qualifying rows and pages rather than to the total number of rows in the table.

2008

Simple Puzzle Using Union and Union All
What will be the output of following two SQL Scripts. First try to answer without running this two script in Query Editor.

Script 1
SELECT 1
UNION ALL
(SELECT 1
UNION
SELECT 2)
GO

Script 2
(SELECT 1
UNION ALL
SELECT 1)
UNION
SELECT 2
GO

Here is the blog post with the answer of the puzzle listed above.

Introduction to sys.dm_exec_query_optimizer_info
sys.dm_exec_query_optimizer_info returns detailed statistics about the operation of the SQL Server query optimizer. You can use this view when tuning a workload to identify query optimization problems or improvements. For example, you can use the total number of optimizations, the elapsed time value, and the final cost value to compare the query optimizations of the current workload and any changes observed during the tuning process. All occurrence values are cumulative and are set to 0 at system restart. All values for value fields are set to NULL at system restart.

List All Column With Indentity Key In Specific Database
A to the point blog post where I write a script which provides the answer right away to the question in the title.

Introduction to Heap Structure – What is Heap?
If the data of the table is not logically sorted, in other word there is no order of data specified in a table it is called as Heap Structure. If the index is created on a table, the data stored in the table are sorted logically and it is called as clustered index. If the index is created as a separate structure pointing location of the data it is called non clustered index.

2009

Fix : Error : There is already an object named ‘#temp’ in the database
Recently, one of my regular blog readers emailed me with a question concerning the following error:

Msg 2714, Level 16, State 6, Line 4
There is already an object named ‘#temp’ in the database.

This reader has been encountering the above-mentioned error, and he is curious to know the reason behind this.

2010

Generate Report for Index Physical Statistics – SSMS
A user asked me a question regarding if we can use similar reports to get the detail about Indexes. Yes, it is possible to do the same. There are similar types of reports are available at Database level, just like those available at the Server Instance level. You can right click on Database name and click Reports. Under Standard Reports, you will find following reports.

Introduction to Extended Events – Finding Long Running Queries
One of the many advantages of the Extended Events is that it can be configured very easily and it is a robust method to collect the necessary information in terms of troubleshooting. There are many targets where you can store the information, which include XML file target, which I really like.

In the following Events, we are writing the details of the event at two locations: 1) Ringer Buffer; and 2) XML file. It is not necessary to write at both places, either of the two will do.

World Shapefile Download and Upload to Database – Spatial Database
One of the most popular blog posts where I explain how to use Spatial Database feature of SQL Server as well where to download the shape file of the world. If you have not read this one blog post, I suggest you to read it, I am sure it will for sure be a fun read.

2011

SQL SERVER 2012 – Improvement in Startup Options
I often work with advanced features of the SQL Server and this really led me to change how SQL Server is starting up. Recently I was changing the startup options in SQL Server and I was very delighted when I saw the startup option screen in Denali. It has really improved and is very convenient to use. Now I realized that the more I use SQL Server 2012, the more I love it.

2012

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.

Right Aligning Numerics in SQL Server Management Studio (SSMS)
SQL Server Management Studio is my most favorite tool and the comfort it provides for users is sometime very amazing. Recently I was retrieving numeric data in SSMS and I found it is very difficult to read them as they were all right aligned. Please pay attention to following image, you will notice that it is not easy to read the digits as we are used to read the numbers which are right aligned.

T-SQL Constructs – *= and += – SQL in Sixty Seconds #009 – Video
My friend Vinod came up with this new episode where he demonstrates how dot net developer can write familiar syntax using T-SQL constructs. T-SQL has many enhancements which are less explored. In this quick video we learn how T-SQL Constructions work. We will explore Declaration and Initialization of T-SQL Constructions. We can indeed improve our efficiency using this kind of simple tricks. I strongly suggest that all of us should keep this kind of tricks in our toolbox.

Difference between DATABASEPROPERTY and DATABASEPROPERTYEX
Earlier I asked a simple question on Facebook regarding difference between DATABASEPROPERTY and DATABASEPROPERTYEX in SQL Server. You can view the original conversation there over here. The conversion immediately became very interesting and lots of healthy discussion happened on Facebook page. The best part of having conversation on Facebook page is the comfort it provides and leaner commenting interface.

Online Index Rebuilding Index Improvement in SQL Server 2012
Have you ever faced a situation where you see something working but you feel it should not be working? Well, I had similar moments a few days ago. I knew that SQL Server 2008 supports online indexing. However, I also knew that I could not rebuild index ONLINE if I used VARCHAR(MAX), NVARCHAR(MAX) or a few other data types. While I was strongly holding on to my belief, I came across with that situation where I had to go online and do a little bit of reading at Book Online.

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

SQL Commandments – Suggestions, Tips, Tricks
Earlier I come across an interesting article where author has written 25 commandments for other database technology. I re-wrote the same article for SQL Server and it is still very much relevant.

Stored Procedure – Clean Cache and Clean Buffer
In this article I explained the difference between two of the important DBCC commands. Use them only when you need it, if you use it without understanding it, you may damage your server.

DBCC FREEPROCCACHE
DBCC DROPCLEANBUFFERS

2008

Add Column With Default Column Constraint to Table
I prefer to specify my constraint name as per my coding standards. It is very easy to add columns and specify default constraints. I have seen many examples where the constraint name is not specified, if constraint name is not specified SQL Server will generate a unique name for itself.

Introduction to Live Lock – What is Live Lock?
A Live lock is one, where a request for exclusive lock is denied continuously because a series of overlapping shared locks keeps on interfering each other and to adapt from each other they keep on changing the status which further prevents them to complete the task.

Find Highest / Most Used Stored Procedure
While doing performance tuning , it is important to know which stored procedure is used a lot and which stored procedure is least used. In this blog post, I have demonstrated a query which discusses the same.

2009

Find All Servers From Local Network – Using sqlcmd – Detect Installed SQL Server on Network
This is a very handy trick. I often forget the servers which I have installed on my network and I use this trick to identify if they are online or not. This helps me to also know if the server is up or not.

Optimize for Ad hoc Workloads – Advance Performance Optimization
Every batch (T-SQL, SP etc.) when ran creates an execution plan which is stored in system for re-use. Due to this reason a large number of query plans are stored in the system. However, there are plenty of plans which are only used once and have never re-used again. One time ran a batch plans wastes memory and resources. SQL Server 2008 has feature of optimizing ad hoc workloads. Before we move to it, let us understand the behavior of SQL Server without optimizing ad his workload. Let us understand the same.

2010

Difference Between ROLLBACK IMMEDIATE and WITH NO_WAIT during ALTER DATABASE
Today, we are going to discuss about something very simple, but quite commonly confused two options of ALTER DATABASE. The first one is ALTER DATABASE …ROLLBACK IMMEDIATE and the second one is WITH NO_WAIT. Many people think they are the same or are not sure of the difference between these two options. Before we continue our explanation, let us go through the explanation given

Fix : Error : 3117 : The log or differential backup cannot be restored because no files are ready to rollforward
If the database is online, it means it is active and in operational mode. It will not make sense to apply further log from backup if the operations have continued on this database. The common practice during the backup restore process is to specify the keyword RECOVERY when the database is restored. When RECOVERY keyword is specified, the SQL Server brings back the database online and will not accept any further log backups.

Enumerations in Relational Database – Best Practice
Excellent blog post by my friend Marko Parkkolla - This is a subject which is a very basic thing in relational databases but often not very well understood and sometimes badly implemented. There are of course many ways to do this but I concentrate only two cases, one which is “the right way” and one which is definitely the wrong way.

2011

Declare and Assign Variable in Single Statement
Many of us are tend to overlook simple things even if we are capable of doing complex work. In SQL Server 2008, inline variable assignment is available. This feature exists from last 3 years, but I hardly see its utilization. One of the common arguments was that as the project migrated from the earlier version, the feature disappears. I totally accept this argument and acknowledge it. However, my point is that this new feature should be used in all the new coding – what is your opinion?

Log File Growing for Model Database – model Database Log File Grew Too Big
The model database is used as the template for all databases created on an instance of SQL Server. Any object you create in the model database will be automatically created in the subsequent user database created on the server. In this blog post we learn how it impacts the other databases.

Zoom Query Editor
SQL Server next version 2012 is coming up with a very neat feature which can be used while presentations, group discussion or for people who prefers large fonts.

Object ID in Negative – Local TempTable has a Negative Object ID
SQL Server 2012 have negative object id for temp tables and it is very interesting to learn about the same and share it over here.

2012

Finding Shortest Distance between Two Shapes using Spatial Data Classes – Ramsetu or Adam’s Bridge
When you run this script SQL Server finds out the shortest distance between two shapes and draws the line. We are using STBuffer so we can see the connecting line clearly.

Table Variables and Transactions – SQL in Sixty Seconds #007 – Video
Quite often I have seen people getting confused with certain behavior of the T-SQL. They expect SQL to behave a certain way and SQL Server behave differently. This kind of issue often creates confusion and frustration.

Using MAXDOP 1 for Single Processor Query – SQL in Sixty Seconds #008 – Video
There are always special cases when it is about SQL Server. There are always few queries which gives optimal performance when they are executed on single processor and there are always queries which gives optimal performance when they are executed on multiple processors.

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

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 »