Many times when I am just bored I surf Book On Line for SQL Server 2005. Almost all the time I find something new which makes me believe that I have lot to learn and there are so many things I am not aware of. Today I found system catalog view sys.dm_exec_query_optimizer_info. I just enjoyed reading about it and now I will share this with you.
SQL SERVER – Add Column With Default Column Constraint to Table
Just a day ago while working with database Jr. Developer asked me question how to add column along with column constraint. He also wanted to specify the name of the constraint. The newly added column should not allow NULL value. He requested my help as he thought he might have to write many lines to achieve what was requested.
SQL SERVER – 2005 – What is CLR?
CLR is Common Language Runtime.
Here is the diagram which explains the architecture of the CLR.
SQL SERVER – How to Retrieve TOP and BOTTOM Rows Together using T-SQL – Part 2 – CTE
Please read SQL SERVER – How to Retrieve TOP and BOTTOM Rows Together using T-SQL before continuing this article. I had asked users to come up with an alternate solution of the same problem. In this blog post we will see solution with the help of CTE.Â
SQL SERVER – Find Nth Highest Record from Database Table
I had previously written SQL SERVER – Query to Retrieve the Nth Maximum value. I just received an email that if I can write this using AdventureWorks database as it is a default sample database for SQL Server 2005 and the user can run the query against it and understand it better. Let us see how we can find highest record from database.
SQL SERVER – How to Retrieve TOP and BOTTOM Rows Together using T-SQL
Just a day ago, while working with some inventory related projects, I faced one interesting situation. I had to find TOP 1 and BOTTOM 1 record together. I right away that I should just do UNION but then I realize that UNION will not work as it will only accept one ORDER BY clause. If you specify more than one ORDER BY clause. It will give an error. Let us see how we can retrieve top and bottom rows together.
SQL SERVER – SELECT 1 vs SELECT * – An Interesting Observation
Many times I have seen issue of SELECT 1 vs SELECT * discussed in terms of performance or readability while checking for existence of rows in table. I ran quick 4 tests about this observed that I am getting same result when used SELECT 1 and SELECT *. I think smart readers of this blog will come up the situation when SELECT 1 and SELECT * have different execution plan when used to find existence of rows.