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.
2006
In year 2006 I started to blog and honestly I had no idea what is the blogging? It was just a collection of the bookmarks and I had a great time writing them up. I always thought I will read it when I need them. Today when I often read my old blog post – I feel nostalgic and also realize that I have improved a lot technically as well professionally.
2007
SQL SERVER 2008 – New DataTypes DATE and TIME
SQL Server 2008 introduced a new data type of DATE and TIME and with the introduction to this new datatypes lot of people got the answer to the following question – why SQL Server does not have only DATE or TIME datatypes? Now you can store DATE and TIME independent of each other and use them with various operators.
Mirrored Backup Introduction and Explanation
SQL Server 2005 introduced a new feature of Mirrored backup and after so many years, I have yet not seen the major implementation of this feature. Mirroring a media set increases backup reliability by adding redundancy of backup media which effectively reduces the impact of backup-device failing. While taking backup of database, same backup is taken on multiple media or locations. If you are using mirrored backup, I would like to know more about it from you.
TRUNCATE Can’t be Rolled Back Using Log Files After Transaction Session Is Closed
Wrote Statement – TRUNCATE can’t be rolled back! Well, there are lots of conversation we can have on this subject, matter of the fact, we had a similar conversation already in year 2007. I encourage all of my readers to go back to the blog post and read the comments, they are fantastic comments and some interesting learning for all of us with regards to how log file, delete as well truncate works.
Difference Between Quality Assurance and Quality Control – QA vs QC
A very confusing question but here is the quick answer.
Quality Assurance: A set of activities designed to ensure that the development and/or maintenance process is adequate to ensure a system will meet its objectives. Example : project audits , process checklists
Quality Control: A set of activities designed to evaluate a developed work product. Example : testing the process
Change Password of SA Login Using Management Studio
System Admin is very important login with the perspective of the security and the password should be kept very much safe. However, if you forget the password for SA account, you can very quickly reset it. On very side note: This blog post when I wrote I had no idea that I have made a factual error with one of the detail. I correct it as soon as I realize it. However, till today there are plenty of emails sent to me on this subject.
2008
During this week, security and System Admin account was my major focus. Here are few of the articles related to security.
- DISABLE and ENABLE user SA
- Fix : Error : msg 15151, Level 16, State 1, Line 2 Cannot alter the login ‘sa’, because it does not exist or you do not have permission
- Add Any User to SysAdmin Role – Add Users to System Roles
- Fix : Msg 15151, Level 16, State 1, Line 3 Cannot drop the login ‘test’, because it does not exist or you do not have permission
Now when I go over my above article, I realize that I need to check a few things on my own production server. Make sure that your production server is up to date with various security patches.
2009
ORDER BY Clause and TOP WITH TIES
Let us start with a simple example – suppose we have 100 rows in the table and out of that 50 rows have the same value in column which is used in ORDER BY; when you use TOP 10 rows, it will return you only 10 rows, but if you use TOP 10 WITH TIES, it will return you all the rows that have the same value as that of the last record of top 10 — which means a total of 50 records. This is the precise reason why one should use TOP WITH TIES.
Does CDC feature capture the data during the truncate operation? Answer: It is not possible or not applicable. Truncate is an operation that is not logged in the log file, and if one tries to truncate the table that is enabled for CDC, it will right away throw an error. This article describes how and why this is not possible. If you are wondering what is CDC, it is Change Data Capture.
Difference Temp Table and Table Variable – Effect of Transaction
If you search online the difference between Temp Table and Table Variable you will find various articles which describes their difference with related to performance. In this article I talk about them but with regards to the Transactions. I am very confident that not all of us know the difference between them.
Get Date of All Weekdays or Weekends of the Year
A simple and effective script!
2010
Index Created on View not Used Often – Observation of the View – Part 2
This blog post is an interesting test case. Let us see the details:
- Create a Table
- Create a View
- Create Index On View
- Write SELECT with ORDER BY on View
- Create Index on the Base Table
- Write SELECT with ORDER BY on View
After doing the last two steps, the question is “Will the query on the View utilize the Index on the View, or will it still use the Index of the base table?”
Server Side Paging in SQL Server 2012 – Part2
Earlier I wrote about SQL SERVER – Server Side Paging in SQL Server Denali – A Better Alternative. A very popular article on that subject. I had used variables for “number of the rows” and “number of the pages”. Blog reader sends me email asking in their organizations these values are stored in the table. Is there any the new syntax can read the data from the table. Absolutely YES!
ORDER BY ColumnName vs ORDER BY ColumnNumber
I read one of the blog post where blogger compared the performance of the two SELECT statement and come to conclusion that ColumnNumber has no harm to use it. Let us understand the point made by first that there is no performance difference. My preference is to use ColumnName in ORDER BY clause. Read this article to understand the reason.
Plan Cache and Data Cache in Memory
A straight to kind of blog post – how to find Plan Cache and Data Cache in memory!
2011
A Quick Script for Point in Time Recovery – Back Up and Restore
Point-in-time recovery (PITR) in the context of computers is a system whereby a set of data or a particular setting can be restored or recovered from a time in the past. In this blog post, I explain how to restore database to the point in time recovery option. Honestly, disaster happens quite frequently and we need to make sure those kind of user mistakes does not happen where we have to restore the database to correct the mistake. I strongly encourage all of the user to document the scenarios where they have to restore the database and see how they can be avoided in future.
Target Recovery Time of a Database – Advance Option in SQL Server 2012
Target Recovery Time of a Database is extremely not known option of the database. This option is related to Indirect Checkpoint. I suggest you read the complete blog post to understand how this option works and where and when you can use it. I have yet to see this option modified in any of the location where I have provided my consultancy services.
Effect of Compressed Backup Setting at Server Level on Database Backup
Database Backup can be huge in size as well. When the database backup is small it is easy to move it around on the network or ship it to the remote location. However, when the size is very big this can be a big issue. In this quick blog post we explore the same and understand some philosophy around database backup.
Year End Brain Teaser – Disabled Login and Associated User Without Disabled User Red Arrow
In this puzzle – I had two questions 1) Technical and 2) Non-Technical. Here is the non-technical question –
Find “The Hidden Tiger”
Reference: Pinal Dave (https://blog.sqlauthority.com)
