Year 2007 in February I was still learning how to blog and I was trying to get a grasp of the whole blogging thing, I still remember my old day and wonder how many naive I was and I had so much long way to go.
How to Escape Single Quotes
In this example there is a direct script which explains how to escape single quotes in SQL Server.
UDF to Return a Calendar for Any Date for Any Year
I am not sure why do I need this but it was fun to write a script. I encourage all of you to try this out and see if you can come up with a better solution.
Find Current Location of Data and Log File of All the Database
As I am doing lots of experiments on my SQL Server test box, I sometimes get too many files in SQL Server data installation folder – the place where I have all the .mdf and .ldf files are stored.
Observation – Effect of Clustered Index over Nonclustered Index
In our example we have one query which is not using any index. On the same table there is already non-clustered index created, which is also not being used. Now when we created clustered index on the same table, our query suddenly started to use a non-clustered index which was so far it is not using. The interesting part of this is that the query is using non-clustered index when a clustered index is created on the same.
Plan Recompilation and Reduce Recompilation – Performance Tuning
Recompilation process is same as the compilation and degrades server performance. In SQL Server 2000 and earlier versions, this was a serious issue but in SQL server 2005, the severity of this issue has been significantly reduced by introducing a new feature called Statement-level recompilation. When SQL Server 2005 recompiles stored procedures, only the statement that causes recompilation is compiled, rather than the entire procedure.
Recompile Stored Procedure at Run Time
The RECOMPILE hint is used with a query and recompiles only that particular query. However, if the parameters are used in many statements in the stored procedure and we want to recompile all the statements, then instead of using the RECOMPILE option with all the queries, we have one better option that uses WITH RECOMPILE during stored procedure creation or execution.
IF EXISTS(Select null from table) vs IF EXISTS(Select 1 from table)
When I wrote article about SQL SERVER – Stored Procedure Optimization Tips – Best Practices. I received lots of comments on particular blog article. In fact, almost all the comments are very interesting. If you have not read all the comments, I strongly suggest to read them. Click here to read the comments.
In the year 2011 February I wrote a month long blog series on the subject SQL Wait Stats, which eventually converted to mega successful book SQL Wait Stats.
- WRITELOG – Wait Type – Day 17 of 28
- LOGBUFFER – Wait Type – Day 18 of 28
- PREEMPTIVE and Non-PREEMPTIVE – Wait Type – Day 19 of 28
- MSQL_XP – Wait Type – Day 20 of 28
- Guest Posts – Feodor Georgiev – The Context of Our Database Environment – Going Beyond the Internal SQL Server Waits – Wait Type – Day 21 of 28
- Guest Post – Jacob Sebastian – Filestream – Wait Types – Wait Queues – Day 22 of 28
- OLEDB – Link Server – Wait Type – Day 23 of 28
Solution Part 2 – A Quick Puzzle on JOIN and NULL – SQL Brain Teaser
Some questions are timeless and they never grow old; no matter how much they grow old their interest never dies. Earlier, I asked a simple puzzle based on a conversation on SQLAuthority Page, and have received an overwhelming response from readers. I still get emails related to this puzzle every day.
Case Sensitive Database and Database User
Interesting Question from Blog Reader: – Here is the scenario. I have two databases: AdventureWorks and MyAdventureWorks. I have a user called SQLAuthority in both databases. Now when I try to grant access the user in one database it works fine, but when I try to do the same thing in another database, it gives me an error. I am stunned as both the users are same. As a matter of fact, I had just created those users recently, and now I am not able to gain necessary permission.
Disable Guest Account – Serious Security Issue
I welcome guests as much as any other Indian does; however, I am strongly opinionated about guest users in SQL Server. I like to keep it disabled unless there is a special need of it. If there is some persistent need of a guest user, I suggest to create a separate account. Again, there are always special cases where there is a need of this guest account. But in other cases, a guest account is not necessary.
Force Removing User from Database
One of the user created a guest user on the server even though I warned user not to do so. Well, this blog has a solution of the problem where if user does not follow my advice and create a random solution.
Detecting guest User Permissions – guest User Access Status
Earlier I wrote the blog post Disable Guest Account – Serious Security Issue, and I got many comments asking questions related to the guest user.
1) How do we know if the guest user is enabled or disabled?
2) What is the default for guest user in SQL Server?
Reference: Pinal Dave (http://blog.sqlauthorit