SQL SERVER – Weekly Series – Memory Lane – #050
- 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
Executing Remote Stored Procedure – Calling Stored Procedure on Linked Server
In this example we see two different methods of how to call Stored Procedures remotely.
Connection Property of SQL Server Management Studio SSMS
A very simple example of the how to build connection properties for SQL Server with the help of SSMS.
Sample Example of RANKING Functions – ROW_NUMBER, RANK, DENSE_RANK, NTILE
SQL Server has a total of 4 ranking functions. Ranking functions return a ranking value for each row in a partition. All the ranking functions are non-deterministic.
T-SQL Script to Add Clustered Primary Key
Jr. DBA asked me three times in a day, how to create Clustered Primary Key. I gave him following sample example. That was the last time he asked “How to create Clustered Primary Key to table?”
2008
2008 – TRIM() Function – User Defined Function
SQL Server does not have functions which can trim leading or trailing spaces of any string at the same time. SQL does have LTRIM() and RTRIM() which can trim leading and trailing spaces respectively. SQL Server 2008 also does not have TRIM() function. User can easily use LTRIM() and RTRIM() together and simulate TRIM() functionality.
http://www.youtube.com/watch?v=1-hhApy6MHM
2009
Earlier I have written two different articles on the subject Remove Bookmark Lookup. This article is as part 3 of original article. Please read the first two articles here before continuing reading this article.
Query Optimization – Remove Bookmark Lookup – Remove RID Lookup – Remove Key Lookup
Query Optimization – Remove Bookmark Lookup – Remove RID Lookup – Remove Key Lookup – Part 2
Query Optimization – Remove Bookmark Lookup – Remove RID Lookup – Remove Key Lookup – Part 3
Interesting Observation – Query Hint – FORCE ORDER
SQL Server never stops to amaze me. As regular readers of this blog already know that besides conducting corporate training, I work on large-scale projects on query optimizations and server tuning projects. In one of the recent projects, I have noticed that a Junior Database Developer used the query hint Force Order; when I asked for details, I found out that the basic concept was not properly understood by him.
Queries Waiting for Memory Allocation to Execute
In one of the recent projects, I was asked to create a report of queries that are waiting for memory allocation. The reason was that we were doubtful regarding whether the memory was sufficient for the application. The following query can be useful in similar cases. Queries that do not have to wait on a memory grant will not appear in the result set of following query.
2010
Quickest Way to Identify Blocking Query and Resolution – Dirty Solution
As the title suggests, this is quite a dirty solution; it’s not as elegant as you expect. However, it works totally fine.
Simple Explanation of Data Type Precedence
While I was working on creating a question for SQL SERVER – SQL Quiz – The View, The Table and The Clustered Index Confusion, I had actually created yet another question along with this question. However, I felt that the one which is posted on the SQL Quiz is much better than this one because what makes that more challenging question is that it has a multiple answer.
Encrypted Stored Procedure and Activity Monitor I recently had received questionable if any stored procedure is encrypted can we see its definition in Activity Monitor.Answer is - No. Let us do a quick test. Let us create following Stored Procedure and then launch the Activity Monitor and check the text.
Indexed View always Use Index on Table
A single table can have maximum 249 non clustered indexes and 1 clustered index. In SQL Server 2008, a single table can have maximum 999 non clustered indexes and 1 clustered index. It is widely believed that a table can have only 1 clustered index, and this belief is true. I have some questions for all of you. Let us assume that I am creating view from the table itself and then create a clustered index on it. In my view, I am selecting the complete table itself.
2011
Detecting Database Case Sensitive Property using fn_helpcollations()
I received a question on how to determine the case sensitivity of the database. The quick answer to this is to identify the collation of the database and check the properties of the collation. I have previously written how one can identify database collation. Once you have figured out the collation of the database, you can put that in the WHERE condition of the following T-SQL and then check the case sensitivity from the description.
Server Side Paging in SQL Server CE (Compact Edition)
SQL Server Denali is coming up with new T-SQL of Paging. I have written about the same earlier.SQL SERVER – Server Side Paging in SQL Server Denali – A Better Alternative, SQL SERVER – Server Side Paging in SQL Server Denali Performance Comparison, SQL SERVER – Server Side Paging in SQL Server Denali – Part2 What is very interesting is that SQL Server CE 4.0 have the same feature introduced. Here is the quick example of the same. To run the script in the example, you will have to do installWebmatrix 4.0 and download sample database. Once done you can run following script.
Why I am Going to Attend PASS Summit Unite 2011
The four-day event will be marked by a lot of learning, sharing, and networking, which will help me increase both my knowledge and contacts. Every year, PASS Summit provides me a golden opportunity to build my network as well as to identify and meet potential customers or employees.
2012
Manage Help Settings – CTRL + ALT + F1
This is very interesting read as my daughter once accidently came across a screen in SQL Server Management Studio. It took me 2-3 minutes to figure out how she has created the same screen.
Recover the Accidentally Renamed Table
“I accidentally renamed table in my SSMS. I was scrolling very fast and I made mistakes. It was either because I double clicked or clicked on F2 (shortcut key for renaming). However, I have made the mistake and now I have no idea how to fix this. If you have renamed the table, I think you pretty much is out of luck. Here are few things which you can do which can give you an idea about what your table name can be if you are lucky.
Identify Numbers of Non Clustered Index on Tables for Entire Database
Here is the script which will give you numbers of non clustered indexes on any table in entire database.
Identify Most Resource Intensive Queries – SQL in Sixty Seconds #029 – Video
Here is the complete complete script which I have used in the SQL in Sixty Seconds Video. Thanks Harsh for important Tip in the comment.
http://www.youtube.com/watch?v=3kDHC_Tjrns
Advanced Data Quality Services with Melissa Data – Azure Data Market
For the purposes of the review, I used a database I had in an Excel spreadsheet with name and address information. Upon a cursory inspection, there are miscellaneous problems with these records; some addresses are missing ZIP codes, others missing a city, and some records are slightly misspelled or have unparsed suites. With DQS, I can easily add a knowledge base to help standardize my values, such as for state abbreviations. But how do I know that my address is correct?
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
