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 FREEPROCCACHEDBCC 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.
[youtube=http://www.youtube.com/watch?v=mekifucLXYI]
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.
[youtube=http://www.youtube.com/watch?v=nOC__FohR-A]
Reference: Pinal Dave (https://blog.sqlauthority.com)
