SQL SERVER – Weekly Series – Memory Lane – #048
- by Pinal Dave
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.
2007
Order of Result Set of SELECT Statement on Clustered Indexed Table When ORDER BY is Not Used
Above theory is true in most of the cases. However SQL Server does not use that logic when returning the resultset. SQL Server always returns the resultset which it can return fastest.In most of the cases the resultset which can be returned fastest is the resultset which is returned using clustered index.
Effect of TRANSACTION on Local Variable – After ROLLBACK and After COMMIT
One of the Jr. Developer asked me this question (What will be the Effect of TRANSACTION on Local Variable – After ROLLBACK and After COMMIT?) while I was rushing to an important meeting. I was getting late so I asked him to talk with his Application Tech Lead. When I came back from meeting both of them were looking for me. They said they are confused. I quickly wrote down following example for them.
2008
SQL SERVER – Guidelines and Coding Standards Complete List Download
Coding standards and guidelines are very important for any developer on the path of a successful career. A coding standard is a set of guidelines, rules and regulations on how to write code. Coding standards should be flexible enough or should take care of the situation where they should not prevent best practices for coding. They are basically the guidelines that one should follow for better understanding.
Download Guidelines and Coding Standards complete List Download
Get Answer in Float When Dividing of Two Integer
Many times we have requirements of some calculations amongst different fields in Tables. One of the software developers here was trying to calculate some fields having integer values and divide it which gave incorrect results in integer where accurate results including decimals was expected.
Puzzle – Computed Columns Datatype Explanation
SQL Server automatically does a cast to the data type having the highest precedence. So the result of INT and INT will be INT, but INT and FLOAT will be FLOAT because FLOAT has a higher precedence. If you want a different data type, you need to do an EXPLICIT cast.
Renaming SP is Not Good Idea – Renaming Stored Procedure Does Not Update sys.procedures
I have written many articles about renaming a tables, columns and procedures SQL SERVER – How to Rename a Column Name or Table Name, here I found something interesting about renaming the stored procedures and felt like sharing it with you all. The interesting fact is that when we rename a stored procedure using SP_Rename command, the Stored Procedure is successfully renamed. But when we try to test the procedure using sp_helptext, the procedure will be having the old name instead of new names.
2009
Insert Values of Stored Procedure in Table – Use Table Valued Function
It is clear from the result set that , where I have converted stored procedure logic into the table valued function, is much better in terms of logic as it saves a large number of operations. However, this option should be used carefully. The performance of the stored procedure is “usually” better than that of functions.
Interesting Observation – Index on Index View Used in Similar Query
Recently, I was working on an optimization project for one of the largest organizations. While working on one of the queries, we came across a very interesting observation. We found that there was a query on the base table and when the query was run, it used the index, which did not exist in the base table. On careful examination, we found that the query was using the index that was on another view. This was very interesting as I have personally never experienced a scenario like this. In simple words, “Query on the base table can use the index created on the indexed view of the same base table.”
Interesting Observation – Execution Plan and Results of Aggregate Concatenation Queries
Working with SQL Server has never seemed to be monotonous – no matter how long one has worked with it. Quite often, I come across some excellent comments that I feel like acknowledging them as blog posts. Recently, I wrote an article on SQL SERVER – Execution Plan and Results of Aggregate Concatenation Queries Depend Upon Expression Location, which is well received in the community.
2010
I encourage all of you to go through complete series and write your own on the subject. If you write an article and send it to me, I will publish it on this blog with due credit to you. If you write on your own blog, I will update this blog post pointing to your blog post.
SQL SERVER – ORDER BY Does Not Work – Limitation of the View 1
SQL SERVER – Adding Column is Expensive by Joining Table Outside View – Limitation of the View 2
SQL SERVER – Index Created on View not Used Often – Limitation of the View 3
SQL SERVER – SELECT * and Adding Column Issue in View – Limitation of the View 4
SQL SERVER – COUNT(*) Not Allowed but COUNT_BIG(*) Allowed – Limitation of the View 5
SQL SERVER – UNION Not Allowed but OR Allowed in Index View – Limitation of the View 6
SQL SERVER – Cross Database Queries Not Allowed in Indexed View – Limitation of the View 7
SQL SERVER – Outer Join Not Allowed in Indexed Views – Limitation of the View 8
SQL SERVER – SELF JOIN Not Allowed in Indexed View – Limitation of the View 9
SQL SERVER – Keywords View Definition Must Not Contain for Indexed View – Limitation of the View 10
SQL SERVER – View Over the View Not Possible with Index View – Limitations of the View 11
2011
Startup Parameters Easy to Configure
If you are a regular reader of this blog, you must be aware that I have written about SQL Server Denali recently. Here is the quickest way to reach into the screen where we can change the startup parameters. Go to SQL Server Configuration Manager >> SQL Server Services >> Right Click on the Server >> Properties >> Startup Parameters
2012
Validating Unique Columnname Across Whole Database
I sometimes come across very strange requirements and often I do not receive a proper explanation of the same. Here is the one of those examples. For example “Our business requirement is when we add new column we want it unique across current database.” Read the solution to this strange request in this blog post.
Excel Losing Decimal Values When Value Pasted from SSMS ResultSet
It is very common when users are coping the resultset to Excel, the floating point or decimals are missed. The solution is very much simple and it requires a small adjustment in the Excel. By default Excel is very smart and when it detects the value which is getting pasted is numeric it changes the column format to accommodate that.
Basic Calculation and PEMDAS Order of Operation
Read this interesting blog post for fantastic conversation about the subject.
Copy Column Headers from Resultset – SQL in Sixty Seconds #027 – Video
http://www.youtube.com/watch?v=x_-3tLqTRv0
Delete From Multiple Table – Update Multiple Table in Single Statement
There are two questions which I get every single day multiple times. In my gmail, I have created standard canned reply for them. Let us see the questions here.
I want to delete from multiple table in a single statement how will I do it?
I want to update multiple table in a single statement how will I do it?
Read the answer in the blog post.
Reference: Pinal Dave (http://blog.sqlauthority.com)
Filed under: Memory Lane, PostADay, SQL, SQL Authority, SQL Query, SQL Server, SQL Tips and Tricks, T SQL, Technology
