I am introducing a new series today. This series is called “Memory Lane.” From the last six years and 2,300 articles, there are fantastic articles I keep revisiting. Sometimes when I read old blog posts I think I should have included something or added a bit more to the topic. But for many articles, I still feel they are fantastic (even after six years) and could be read again and again.
I have also found that after six years of blogging, readers will write to me and say “Pinal, why don’t you write about X, Y or Z.” The answer is: I already did! It is here on the blog, or in the comments, or possibly in one of my books. The solution has always been there, it is simply a matter of finding it and presenting it again. That is why I have created Memory Lane. I will be listing the best articles from the same week of the past six years. You will find plenty of reading material every Saturday from articles of SQLAuthority past.
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.
Query to Display Foreign Key Relationships and Name of the Constraint for Each Table in Database
My blogging journey began with this blog post. As many of you know my journey began with creating a repository of my scripts. This was very first script which I had written to find out foreign key relationship and constraints. The same query was updated later on using the new SYS schema modification in SQL Server.
Version 1: Using sys.schema
Version 2: Using sys.schema and additional columns
Milestone Posts – 1 Year (365 blogs) and 1 Million Views
When I reached 1st week of Nov in 2007 SQLAuthority.com blog had around 365 blog posts and 1 Million Views. I was not obsessed with the statistics before but this was indeed an interesting moment for me as I was blogging for myself and did not realize that so many people are reading my blog. In year 2006 there were not many bloggers so blogging was new to me as well. I was learning it as I go.
Stored Procedure WITH ENCRYPTION and Execution Plan
If you have stored procedure and its code is encrypted when you execute it what will be displayed in the execution plan. There are two kinds of execution plans 1) Estimated and 2) Actual. It will be indeed interesting to know what is displayed in both the cases when Stored Procedure is encrypted. What is your guess? Now go ahead and click on here and figure out your answer.
If the user is not able to login into SQL Server due to any error or issues there were two different blog post addresses the same issue here and here.
It seems like Nov is the month of SQLPASS month. In 2009 on the same week I was in USA attending SQLPASS event. I had a fantastic experience attending the event. Here are the blog posts covering the subject Day 1, Day 2, Day 3, Day 4
Finding the last backup time for all the databases
This little script is very powerful and instantly gives details when was the last time your database backup performed. If you are reading this blog post – I say just go ahead and check if everything is alright on your server and you have all the necessary latest backup. It is better to be safe than sorrow.
Version 1: Above script was improved to get more details about the database
Version 2: This version of the script will include pretty much have all the backup related information in a single script. Do not miss to save it for future use.
Are you a Database Administrator or a Database Developer?
Three years ago I created a very small survey and the results which I have received are very interesting. The question was asking what is the profile of the visitor of that blog post and I noticed that DBA and Developers have balanced with little inclination towards Developers. Have you voted so far? If not, go ahead!
New Book Released – SQL Server Interview Questions And Answers
One year ago, on November 3, 2011 I published my book SQL Server Interview Questions and Answers. The book has a lot of great reviews, and we have even received emails telling us this book was a life changer because it helped get them a great new job. I don’t think anyone can get a job just from my book. It was the individual who studied hard and took it seriously, and was determined to learn something new. The book might have helped guide them and show them the topics to study, but they spent their own energy on it. It was their own skills that helped them pass the exam.
So, in this very first installment, I would like to thank the readers for accepting our book, for giving it great reviews and for using it and sharing it. Our goal in writing this book was to help others, and it seems like we succeeded.
Reference: Pinal Dave (https://blog.sqlauthority.com)
I have question regarding Partitioning to existing table.
We have three Database – SearchDb, MSDB1, MSDB2
SearchDB has table called – KeyTab and it has PK field called ID which has Identity and another column called AU_ID which is served as
Key Field to join another table and another table has same field.
We are planning to add new column called Group ID and make it as Parition Column, this group id will consist in a three Range/group
1, 2, 3 which will we our partition.
Once i add the column Group Id into table. I need to create the partition into this eisting table.
1) I will add the three file group into existing table using following:
2) Create the partition Function
3) Create the Partition Scheme
Now i need to create the procedure to Insert the data into different Partition using following criteria:
Check the AU_ID into SearchDB.KeyTab and compare with MSDB1.AUSUMTab and
if StartDate > today’s Date then Insert into Partition 1 of SearchDB Database of Partitioned Table KeyTab
if StartDate < today's Date then Insert into Partition 2 of SearchDB Database of Partitioned Table KeyTab
and another logic for EndDate i need to add as
if EndDate < today's Date then Insert into Partition 3 of SearchDB Database of Partitioned Table KeyTab
Could you please guide me that my above steps are right and also how i cn write the Procedure to Isert data into Partition?