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
This was the year I started to understand the importance of the blogging and how it is changing my life. I started to make new friends and everything I learned now had permanent repository – this was indeed exciting.
2008
Time Out Due to Executing DELETE on Large RecordSet
There are many differences between truncate and delete but I have seen people using them interchangeably without thinking much. In this blog post, the difference is explained with the help of other similar articles. It is not a good idea to use TRUNCATE right away if you DELETE is timing out. The best practice should be to understand the difference. It is also a good idea to delete the data in smaller batch instead of larger batch.
What is – DML, DDL, DCL and TCL – Introduction and Examples
These three statements are absolutely different and not everyone knows the difference between them. I had fun writing this small blog post where I explained the difference between them with a simple example.
2009
Find Row Count in Table – Find Largest Table in Database – T-SQL
There is more than one way to find the information from the table regarding how many total rows it contains. In this blog post, we go over another method which is very rarely used but when used it gives very accurate data very quickly. If you are the one who loves new interesting details, this blog post is specifically for you.
Find Number of Rows and Disk Space Reserved – Using sp_spaceused Interesting Observation
We can find the row count using another system SP sp_spaceused. This SP gives additional information regarding disk space reserved on the database as well. Well, when I ran the SP on AdventureWorks first time, I suspected that database SP is not providing me correct results. However, it was not the case, there was an additional parameter to pass, which solved the problem for me. I described my experience in the blog post.
Remove Duplicate Entry from Comma Delimited String – UDF
User Defined Function has very simple logic behind it. It takes a comma delimited string and then converts it to the table and runs the DISTINCT operation on the table. DISTINCT operation removes duplicate value. After that it converts the table again into the string and it can be used. I would suggest that this UDF should be kept handy to perform this tedious task of removing duplicate entry from comma delimited string easily.
Difference Between Index Scan and Index Seek
I have explained the concept of Index Scan and Index Seek earlier but I keep on receiving the same question again and again. SQL Server uses search predicates to make a decision right before applying indexes to any given query. Index Scan happens when an index definition can not point close to a single row satisfy search predicates. In this case SQL Server has to scan multiple pages to find a range of rows which satisfy search predicates. In case of Index Seek SQL Server finds single row matching search predicates using index definition.
2010
Find Busiest Database
In my early career I was asked to how to find which is the busiest database in any SQL Server Instance. What he really meant by this is which database was doing lots of read and write operation. To find the answer to this question I decided to look into the DMV which contains all the details of the executed query. From the DMV sys.dm_exec_query_stats I found three most important columns to determine busiest database.
SSMS Query Command(s) completed successfully without ANY Results
Has it ever happened to you when you attempt to run a query and the query runs successfully but displays no result. Well, it happened to my friend that every single time when he ran query – he was getting answers that the command completed successfully but he did not get any results. When he attempted to do this from another machine, it worked all fine. After a few diagnosis we found the reason for the problem. I suggest you read the blog post for the interesting story!
2011
A Quick Note on DB_ID() and DB_NAME() – Get Current Database ID – Get Current Database Name
Quite often a simple thing makes experienced DBA to look for a simple thing. Here are few things which I used to get confused couple of years ago. Now I know it well and have no issue but recently I see one of the DBA getting confused when looking at the DBID from one of the DMV and not able to relate that directly to Database Name.
Performance tuning is an art and there are many aspects to the same. One has to learn many things beyond index to master performance tuning. For example I consider following various topic one need to understand for performance tuning.
- Logical Query Processing
- Efficient Join Techniques
- Query Tuning Considerations
- Avoiding Common Performance Tuning Issues
- Statistics and Best Practices
- TempDB Tuning
- Hardware Planning
- Understanding Query Processor
- Using SQL Server 2005 and 2008 Updated Feature Sets
- CPU, Memory, I/O Bottleneck
- Index Tuning (of course)
- Many more…
Shrinking Database is Bad – Increases Fragmentation – Reduces Performance
Shrinking is indeed a bad thing but not everyone knows. Many know about it but still keep on practicing it. In this blog post, I tried to explain why Shrinking is bad with practical example and code. Look at the irony of the Shrinking database. One person shrinks the database to gain space (thinking it will help performance), which leads to increase in fragmentation (reducing performance). To reduce the fragmentation, one rebuilds index, which leads to the size of the database to increase way more than the original size of the database (before shrinking). Well, by Shrinking, one did not gain what he was looking for usually.
2012
Jan 2012 was a quiz month and we had a great time asking interesting questions and have received fantastic responses. Do you know the answers of the questions – if yes, fantastic if no, you still have time to go over them and refresh your memory.
- SQL SERVER – DACPAC and SQL Azure – Quiz – Puzzle – 12 of 31
- SQL SERVER – Debate – Table Variables vs Temporary Tables – Quiz – Puzzle – 13 of 31
- SQL SERVER – Cases When Stored Procedure RECOMPILE – Quiz – Puzzle – 14 of 31
- SQL SERVER – Difference between CHAR, VARCHAR, NVARCHAR and VARCHAR(MAX) – Quiz – Puzzle – 15 of 31
- SQL SERVER – CHECKPOINT Behavior and Database Recovery Models – Quiz – Puzzle – 16 of 31
- SQL SERVER – Various Ways to Create Constraints – Quiz – Puzzle – 17 of 31
- SQL SERVER – Importance of Resource Database – Quiz – Puzzle – 18 of 31
Reference: Pinal Dave (https://blog.sqlauthority.com)
