This is the 8th episode of the weekly series of memory lane. 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. My favorite articles this time are about the solution when the log file is very large as well as T-SQL script to find details about TempDB. Let me know which one of the following is your favorite article from memory lane.
2006
SQL SERVER – Fix : Error 14274: Cannot add, update, or delete a job (or its steps or schedules) that originated from an MSX server. The job was not saved
This is one error every single developer who have ever upgraded SQL Server to the latest version would have faced it – I was glad to have the solution of this out early in my career as I had upgraded my system around the same time from SQL Server 2000 to SQL Server 2005.
2007 – TempDB
SQL SERVER – Solution – Log File Very Large – Log Full
What do you do when your log is too full. This blog contained a quick advice, when I look at it back, I feel to add many more disclaimers to my own article. Well, I was beginner in the field and we all learn. Well, today I have opportunity so I am adding one disclaimer here – use the advice with care and always take full backup if you are not sure what you are doing. Rather do not do anything if you are not sure what you are doing – consult who know what they are doing. Production server is not something one should take chances with.
T-SQL Script to Find Details About TempDB
A Quick Script which gives you basic and fundamental details about TempDB, for example – Autogrowth, GrowthValue, etc.
DISTINCT Keyword Usage and Common Discussion
Jr. DBA asked me a day ago, how to apply DISTINCT keyword to only first column of SELECT. Distinct can not be applied to only few columns it is always applied to the whole column. This is one of the very interesting discussion I had in my early career.
Enabling Clustered and Non-Clustered Indexes – Interesting Fact
When a clustered index is disabled, all the non-clustered indexes on the same tables are auto disabled as well. The user does not need to disable non-clustered index separately. However, when a clustered index is enabled, it does not automatically enable non-clustered index. All the non-clustered indexes need to be enabled individually. I wondered if there is any short cut to enable all the indexes together. Index rebuilding came to my mind instantly. I ran T-SQL command of rebuilding all the indexes and it enabled all the indexes on the table.
Difference Between Index Rebuild and Index Reorganize Explained with T-SQL Script
This was a very quick blog post written in my early career. When I read it today, I can see how simple words I have explained the complex subject. However, after 5 years, I feel like expanding to this subject. May be I will write more in future blog posts.
2008
Find Collation of Database and Table Column Using T-SQL
Collations specify the rules for how strings of character data are sorted and compared, based on the norms of particular languages and locales. This script quickly finds the collation of the database and tables using T-SQL.
Interesting Interview Questions – Revisited
Read Interesting Interview Questions before continuing reading this article. This interview question was about user participation and about how good and how different you can come with your T-SQL script. What I really liked is that many users took this test seriously and did their best to answer. There are few great answers – however, I believe the same question can be improved today as well – do you want to give it a try?
Change Collation of Database Column – T-SQL Script
Earlier in the same week, I wrote about Find Collation of Database and Table Column Using T-SQL and I had received some good comments and one particular question was about how to change collation of database. It is quite simple to do so. In this quick script I have attempted to solve the same problem. Now here is the question back to you – is the trick mentioned in the blog efficient?
2009
Fillfactor, Index and In-depth Look at Effect on Performance
Creating Indices in SQL Server are one of the most important tasks of any SQL DBA. Performance of your database is directly depends on your skills and proficiency in creating and maintaining the right number and quality indices. Fill-factor is always an integer valued from 1 to 100. The fill-factor option is designed for improving index performance and data storage. By setting the fill-factor value, you specify the percentage of space on each page to be filled with data, reserving free space on each page for future table growth.
Comma Separated Values (CSV) from Table Column
It is a very common request to create List or CSV from Table Column. We are going to explore the same concept in this blog – I have tried to use XML to generate the solution of this issue. Here is the blog post I wrote for the same subject earlier: Comma Separated Values (CSV) from Table Column
2010
Server Side Paging in SQL Server Denali Performance Comparison
The real challenge of paging is all the unnecessary IO reads from the database. Network traffic was one of the reasons why paging has become a very expensive operation. I have seen many legacy applications where a complete result set is brought back to the application and paging has been done. As what you have read earlier, SQL Server 2011 offers a better alternative to an age-old solution. In this article I have compared the two tests on Performance and Paging
- Test 1: Performance Comparison of the Two Different Pages on SQL Server 2011 Method
- Test 2: Performance Comparison of the Two Different Pages Using CTE (Earlier Solution for SQL Server 2005/2008) and the New Method of SQL Server 2011
A Successful Community TechDays at Ahmedabad – December 11, 2010
We were fortunate to have such a legendary speaker line up in this single event – Jacob Sebastian, Vinod Kumar, Rushabh Mehta, Prabhjot Bakshi and yours truly. One of the greatest experience.
Index Created on View not Used Often – Limitation of the View 12
This was a follow up of my earlier series of Limitation of the Views. Index are great when used properly – they are like a silver bullet. The same is true for Views as well. However, if both of them are together and not used properly that is the recipe of the disaster. Here is the earlier series of 11 limitations.
- ORDER BY Does Not Work – Limitation of the View 1
- Adding Column is Expensive by Joining Table Outside View – Limitation of the View 2
- Index Created on View not Used Often – Limitation of the View 3
- SELECT * and Adding Column Issue in View – Limitation of the View 4
- COUNT(*) Not Allowed but COUNT_BIG(*) Allowed – Limitation of the View 5
- UNION Not Allowed but OR Allowed in Index View – Limitation of the View 6
- Cross Database Queries Not Allowed in Indexed View – Limitation of the View 7
- Outer Join Not Allowed in Indexed Views – Limitation of the View 8
- SELF JOIN Not Allowed in Indexed View – Limitation of the View 9
- Keywords View Definition Must Not Contain for Indexed View – Limitation of the View 10
- View Over the View Not Possible with Index View – Limitations of the View 11
Give me one more idea where views can be problematic! Let me start – Index Created on View not Used Often – Observation of the View, now your turn –
2011
2012 Auditing Enhancement – On Audit Log Failure Options – Maximum Rollover Files
You can see that in SQL Server 2012 they have added two more options for audit log failure. In earlier version the only option was to shut down the server when there was an audit log failure. Now you can fail the operation as well continue on log failure. This new option now gives finer control on the behavior of the audit failure scenario.
A Quick Trick about SQL Server 2012 CONCAT function and A Quick Trick about SQL Server 2012 CONCAT Function – PRINT
Above two blogs can be summarized with a single video!
Mastering the Basics – Igniting Learning – A Unique Learning Experience
It has been my lifelong dream to be an author, and recently I have seen that goal realized three times. One of the books I am most proud of is SQL Server Interview Questions and Answers. When I wrote SQL Server Interview Questions and Answers – I had very clear idea what my goals were for the book.
My goals were simple: 1. Mastering the Basics and 2. Igniting Learning
This is the eight editions of the Memory Lane – I am eagerly waiting for your feedback about this series – do let me know with your comments.
Reference: Pinal Dave (https://blog.sqlauthority.com)
