Hope you guys are getting ready for Diwali Festival :-). Before you guys start with the festival event I thought of writing an article on performance. Couple of days ago one of the developer came to me and said he was using an INNER join in his query and he is getting delay in results, soon after he changes it to LEFT OUTER join he could see some improvement in getting the results. I was blinking for a second and I started arguing with him that this will change the business logic & asked him to explain me how’s OUTER join faster so we went into an argument which resulted in this article.
I was working in a project where we are using database mirroring as high availability solution. In the same instance we have multiple databases in which only critical databases are configured for database mirroring. As you know database mirroring scope is limited to database only which means dependent database are not failed over (which is now possible in SQL Server 2012 as Availability groups). In my case the dependent database is not even configured for mirroring. They have some procedures \ views which will pull the data from dependent database. Assume if the mirrored database is failed over to next node they won’t be able to pull the data from dependent database which means they are screwed and application will be down till we go back to principal node.
Today I’m going to discuss another new feature in SQL Server 2012. Pagination in result set is a new feature in SQL Server 2012. ORDER BY clause in SQL Server 2012 is enhanced with additional parameters that is OFFSET and FETCH. Using this parameter allows you to fetch data from a particular page from result set, you can also specify how many rows to retrieve. However an ORDER BY clause is compulsory to utilize this feature.
Microsoft released 3 new conversion function in SQL Server Denali. In which two functions will tell you whether the action is possible the third one will give you the result. Below are the three conversion functions
SQL Server Denali comes with one more new logical function which helps to pull data at the specified index from a list of values. CHOOSE() is the function that’s been added. Again this function exists in Access , so if you migrate the db from Access there is no need to rewrite your query using CASE statement. In simple this function is a short form of CASE statement.