SQL SERVER – Weekly Series – Memory Lane – #003
- by pinaldave
Here is the list of curetted 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
This was the first year of my blogging and lots of new things I was learning as I go. I was indeed an infant in blogging a few years ago. However, as time passed by I have learned a lot. This year was year of experiments and new learning.
2007
Working as a full time DBA I often encoutered various errors and I started to learn how to avoid those error and document the same.
ERROR Msg 5174 Each file size must be greater than or equal to 512 KB
Whenever I see this error I wonder why someone is trying to attempt a database which is extremely small. Anyway, it does not matter what I think I keep on seeing this error often in industries. Anyway the solution of the error is equally interesting – just created larger database.
Dilbert Humor
This was very first encounter with database humor and I started to love it. It does not matter how many time we read this cartoon it does not get old.
Generate Script with Data from Database – Database Publishing Wizard
Generating schema script with data is one of the most frequently performed tasks among SQL Server Data Professionals. There are many ways to do the same. In the above article I demonstrated that how we can use the Database Publishing Wizard to accomplish the same. It was new to me at that time but I have not seen much of the adoption of the same still in the industry.
Here is one of my videos where I demonstrate how we can generate data with schema.
2008
Delete Backup History – Cleanup Backup History
Deleting backup history is important too but should be done carefully. If this is not carried out at regular interval there is good chance that MSDB will be filled up with all the old history. Every organization is different. Some would like to keep the history for 30 days and some for a year but there should be some limit. One should regularly archive the database backup history.
South Asia MVP Open Days 2008
This was my very first year Microsoft MVP. I had Indeed big blast at the event and the fun was incredible. After this event I have attended many different MVP events but the fun and learning this particular event presented was amazing and just like me many others are not able to forget the same.
Here are other links related to the event:
South Asia MVP Open Day 2008 – Goa
South Asia MVP Open Day 2008 – Goa – Day 1
South Asia MVP Open Day 2008 – Goa – Day 2
South Asia MVP Open Day 2008 – Goa – Day 3
2009
Enable or Disable Constraint
This is very simple script but I personally keep on forgetting it so I had blogged it. Till today, I keep on referencing this again and again as sometime a very little thing is hard to remember.
Policy Based Management – Create, Evaluate and Fix Policies
This article will cover the most spectacular feature of SQL 2008 – Policy-based management and how the configuration of SQL Server with policy-based management architecture can make a powerful difference. Policy based management is loaded with several advantages. It can help you implement various policies for reliable configuration of the system. It also provides additional administrative assistance to DBAs and helps them effortlessly manage various tasks of SQL Server across the enterprise.
SQLPASS 2009 – My Very First SQPASS Experience
Just Brilliant! I never had an experience such a thing in my life. SQL SQL and SQL – all around SQL!
I am listing my own reasons here in order of importance to me.
Networking with SQL fellows and experts
Putting face to the name or avatar
Learning and improving my SQL skills
Understanding the structure of the largest SQL Server Professional Association
Attending my favorite training sessions
Since last time I have never missed a single time this event. This event is my favorite event and something keeps me going. Here are additional post related SQLPASS 2009.
SQL PASS Summit, Seattle 2009 – Day 1
SQL PASS Summit, Seattle 2009 – Day 2
SQL PASS Summit, Seattle 2009 – Day 3
SQL PASS Summit, Seattle 2009 – Day 4
2010
Get All the Information of Database using sys.databases
Even though we believe that we know everything about our database, we do not know a lot of things about our database. This little script enables us to know so many details about databases which we may not be familiar with. Run this on your server today and see how much you know your database.
Reducing CXPACKET Wait Stats for High Transactional Database
While engaging in a performance tuning consultation for a client, a situation occurred where they were facing a lot of CXPACKET Waits Stats. The client asked me if I could help them reduce this huge number of wait stats. I usually receive this kind of request from other client as well, but the important thing to understand is whether this question has any merits or benefits, or not. I discusses the same in this article – a bit long but insightful for sure.
Error related to Database in Use
There are so many database management operations in SQL Server which requires exclusive access to the database and it is not always possible to get it. When any database is online in SQL Server it either applications or system thread often accesses them. This means database can’t have exclusive access and the operations which required this access throws an error. There is very easy method to overcome this minor issue – a single line script can give you exclusive access to the database.
Difference between DATETIME and DATETIME2
Developers have found the root reason of the problem when dealing with Date Functions – when data time values are converted (implicit or explicit) between different data types, which would lose some precision, so the result cannot match each other as expected. In this blog post I go over very interesting details and difference between DATETIME and DATETIME2
History of SQL Server Database Encryption
I recently met Michael Coles and Rodeney Landrum the author of one of the kind book Expert SQL Server 2008 Encryption at SQLPASS in Seattle. During the conversation we ended up how Microsoft is evolving encryption technology. The same discussion lead to talking about history of encryption tools in SQL Server. Michale pointed me to page 18 of his book of encryption. He explicitly gave me permission to re-produce relevant part of history from his book.
2011
Functions FIRST_VALUE and LAST_VALUE with OVER clause and ORDER BY
Some time an interesting feature and smart audience make a total difference in places. From last two days, I have been writing on SQL Server 2012 feature FIRST_VALUE and LAST_VALUE. I created a puzzle which was very interesting and got many people attempt to resolve it. It was based on following two articles:
Introduction to FIRST_VALUE and LAST_VALUE
Introduction to FIRST_VALUE and LAST_VALUE with OVER clause
I even provided the hint about how one can solve this problem. The best part was many people solved the problem without using hints! Try your luck!
A Real Story of Book Getting ‘Out of Stock’ to A 25% Discount Story Available
This is a great problem and everybody would love to have it. We had it and we loved it. Our book got out of stock in 48 hours of releasing and stocks were empty. We faced many issues and learned many valuable lessons. Some we were able to avoid in the future and some we are still facing it as those problems have no solutions. However, since that day – our books never gone out of stock. This inspiring learning story for us and I am confident that you will love to read it as well.
Introduction to LEAD and LAG – Analytic Functions Introduced in SQL Server 2012
SQL Server 2012 introduces new analytical function LEAD() and LAG(). This function accesses data from a subsequent row (for lead) and previous row (for lag) in the same result set without the use of a self-join . It will be very difficult to explain this in words so I will attempt small example to explain you this function. I had a fantastic time writing this blog post and I am very confident when you read it, you will like the same.
Reference: Pinal Dave (http://blog.sqlauthority.com)
Filed under: Memory Lane, PostADay, SQL, SQL Authority, SQL Query, SQL Server, SQL Tips and Tricks, T SQL, Technology
