Question: When we drop primary key on the column, does it automatically drop a non-clustered index on the same column (if created)?
Before we see answer of this question, I suggest you read following three blog posts, they discuss various similar aspects related to the primary key and clustered index.
Question: How to drop clustered index, which is created on primary key?
Answer: If you thought the answer as simple as following script, you are wrong.
DROP INDEX PK_Table1_Col1
ON Table1
GO
Question: When we drop clustered index on the column, does it automatically drop primary key on the same column (if created)?
Question: When we drop primary key on the column, does it automatically drop a clustered index on the same column (if created)?
Answer: This is one of the most popular questions, whenever I ask this to any candidate, they usually give an answer in yes or no. However, if I ask them follow up question to confirm, I have noticed most of the time they change the reply or accept that they really do not know the answer. It is totally fine. As when I was beginning with SQL, I even did not know the answer of it. I learn doing various experiments and doing various interviews.
Recently in one of the webcast, while I was presenting about statistics and performance tuning, I was asked by one of the attendees that if there is anyway to find out all the outdated statistics based on the dates from the server. This is indeed a very interesting question and I will be happy to explain you here.
It is surprising to see that lots of people do not know that SQL Server Fill Factor 0 (which is the default value when SQL Server is installed) is equal to value as 100.
A few days ago, I sent emails to everyone who subscribed to my newsletter (if you have not subscribed, you are missing out some real fun) that they can send me one performance problem of theirs like and I will help them solve it in 24 hours. I received over 1082 responses in the first 24 hours and honestly, I could not keep my commitment to reply each in 24 hours (even though I answered emails for 18 hours straight). It took me over 4 days to respond to EVERY SINGLE EMAIL, and on the top of it, I was still getting more emails. Finally, I caught up with everything on the fifth day and I had received a total of 2187 performance problems in the email. I have responded to each of them. During this marathon of answering emails, I saw that many users do not know how to send the execution plan in an email.