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 (https://blog.sqlauthority.com)
