SQL SERVER – Weekly Series – Memory Lane – #049
- 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
Two Connections Related Global Variables Explained – @@CONNECTIONS and @@MAX_CONNECTIONS
@@CONNECTIONS
Returns the number of attempted connections, either successful or unsuccessful since SQL Server was last started.
@@MAX_CONNECTIONS
Returns the maximum number of simultaneous user connections allowed on an instance of SQL Server. The number returned is not necessarily the number currently configured.
Query Editor – Microsoft SQL Server Management Studio
This post may be very simple for most of the users of SQL Server 2005. Earlier this year, I have received one question many times – Where is Query Analyzer in SQL Server 2005? I wrote small post about it and pointed many users to that post – SQL SERVER – 2005 Query Analyzer – Microsoft SQL SERVER Management Studio. Recently I have been receiving similar question.
OUTPUT Clause Example and Explanation with INSERT, UPDATE, DELETE
SQL Server 2005 has a new OUTPUT clause, which is quite useful. OUTPUT clause has access to insert and deleted tables (virtual tables) just like triggers. OUTPUT clause can be used to return values to client clause. OUTPUT clause can be used with INSERT, UPDATE, or DELETE to identify the actual rows affected by these statements. OUTPUT clause can generate a table variable, a permanent table, or temporary table. Even though, @@Identity will still work with SQL Server 2005, however I find the OUTPUT clause very easy and powerful to use. Let us understand the OUTPUT clause using an example.
Find Name of The SQL Server Instance
Based on database server stored procedures has to run different logic. We came up with two different solutions. 1) When database schema is very much changed, we wrote completely new stored procedure and deprecated older version once it was not needed. 2) When logic depended on Server Name we used global variable @@SERVERNAME. It was very convenient while writing migrating script which depended on the server name for the same database.
Explanation of TRY…CATCH and ERROR Handling With RAISEERROR Function
One of the developers at my company thought that we can not use the RAISEERROR function in new feature of SQL Server 2005 TRY… CATCH. When asked for an explanation he suggested SQL SERVER – 2005 Explanation of TRY… CATCH and ERROR Handling article as excuse suggesting that I did not give example of RAISEERROR with TRY…CATCH. We all thought it was funny. Just to keep records straight, TRY… CATCH can sure use RAISEERROR function.
Different Types of Cache Objects
Serveral kinds of objects can be stored in the procedure cache:
Compiled Plans: When the query optimizer finishes compiling a query plan, the principal output is compiled plan.
Execution contexts: While executing a compiled plan, SQL Server has to keep track of information about the state of execution.
Cursors: Cursors track the execution state of server-side cursors, including the cursor’s current location within a resultset.
Algebrizer trees: The Algebrizer’s job is to produce an algebrizer tree, which represents the logic structure of a query.
Open SSMS From Command Prompt – sqlwb.exe Example
This article is written by request and suggestion of Sr. Web Developer at my organization. Due to the nature of this article most of the content is referred from Book On-Line. sqlwbcommand prompt utility which opens SQL Server Management Studio. Squib command does not run queries from the command prompt. sqlcmd utility runs queries from command prompt, read for more information.
2008
Puzzle – Solution – Computed Columns Datatype Explanation
Just a day before I wrote article SQL SERVER – Puzzle – Computed Columns Datatype Explanation which was inspired by SQL Server MVP Jacob Sebastian. I suggest that before continuing this article read the original puzzle question SQL SERVER – Puzzle – Computed Columns Datatype Explanation.The question was if the computed column was of datatype TINYINT how to create a Computed Column of datatype INT?
2008 – Find If Index is Being Used in Database
It is very often I get a query that how to find if any index is being used in the database or not. If any database has many indexes and not all indexes are used it can adversely affect performance. If the number of indices are higher it reduces the INSERT / UPDATE / DELETE operation but increase the SELECT operation. It is recommended to drop any unused indexes from table to improve the performance.
2009
Interesting Observation – Execution Plan and Results of Aggregate Concatenation Queries
If you want to see what’s going on here, I think you need to shift your point of view from an implementation-centric view to an ANSI point of view. ANSI does not guarantee processing the order. Figure 2 is interesting, but it will be potentially misleading if you don’t understand the ANSI rule-set SQL Server operates under in most cases. Implementation thinking can certainly be useful at times when you really need that multi-million row query to finish before the backup fire off, but in this case, it’s counterproductive to understanding what is going on.
SQL Server Management Studio and Client Statistics
Client Statistics are very important. Many a times, people relate queries execution plan to query cost. This is not a good comparison. Both parameters are different, and they are not always related. It is possible that the query cost of any statement is less, but the amount of the data returned is considerably larger, which is causing any query to run slow. How do we know if any query is retrieving a large amount data or very little data?
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
SQL SERVER – Get Query Running in Session
I was recently looking for syntax where I needed a query running in any particular session. I always remembered the syntax and ha d actually written it down before, but somehow it was not coming to mind quickly this time. I searched online and I ended up on my own article written last year SQL SERVER – Get Last Running Query Based on SPID. I felt that I am getting old because I forgot this really simple syntax.
Find Total Number of Transaction on Interval
In one of my recent Performance Tuning assignments I was asked how do someone know how many transactions are happening on a server during certain interval. I had a handy script for the same. Following script displays transactions happened on the server at the interval of one minute. You can change the WAITFOR DELAY to any other interval and it should work.
2011
Here are two DMV’s which are newly introduced in SQL Server 2012 and provides vital information about SQL Server.
DMV – sys.dm_os_volume_stats – Information about operating system volume
DMV – sys.dm_os_windows_info – Information about Operating System
SQL Backup and FTP – A Quick and Handy Tool
I have used this tool extensively since 2009 at numerous occasion and found it to be very impressive. What separates it from the crowd the most – it is it’s apparent simplicity and speed. When I install SQLBackupAndFTP and configure backups – all in 1 or 2 minutes, my clients are always impressed.
Quick Note about JOIN – Common Questions and Simple Answers
In this blog post we are going to talk about join and lots of things related to the JOIN. I recently started office hours to answer questions and issues of the community. I receive so many questions that are related to JOIN. I will share a few of the same over here. Most of them are basic, but note that the basics are of great importance.
2012
Importance of User Without Login
Question: “In recent version of SQL Server we can create user without login. What is the use of it?”
Great question indeed. Let me first attempt to answer this question but after reading my answer I need your help. I want you to help him as well with adding more value to it.
Preserve Leading Zero While Coping to Excel from SSMS
Earlier I wrote two articles about how to efficiently copy data from SSMS to Excel. Since I wrote that post there are plenty of interest generated on this subject. There are a few questions I keep on getting over this subject. One of the question is how to get the leading zero preserved while copying the data from SSMS to Excel. Well it is almost the same way as my earlier post SQL SERVER – Excel Losing Decimal Values When Value Pasted from SSMS ResultSet. The key here is in EXCEL and not in SQL Server.
Solution – 2 T-SQL Puzzles – Display Star and Shortest Code to Display 1
Earlier on this blog we had asked two puzzles. The response from all of you is nothing but Amazing. I have received 350+ responses. Many are valid and many were indeed something I had not thought about it.
I strongly suggest you read all the puzzles and their answers here - trust me if you start reading the comments you will not stop till you read every single comment. Seriously trust me on it. Personally I have learned a lot from it.
Identify Most Resource Intensive Queries – SQL in Sixty Seconds #028 – Video
http://www.youtube.com/watch?v=TvlYy-TGaaA
Importance of User Without Login – T-SQL Demo Script
Earlier I wrote a blog post about SQL SERVER – Importance of User Without Login and my friend and SQL Expert Vinod Kumar has written excellent follow up blog post about Contained Databases inside SQL Server 2012. Now lots of people asked me if I can also explain the same concept again so here is the small demonstration for it. Let me show you how login without user can help. Before we continue on this subject I strongly recommend that you read my earlier blog post here.
In following demo I am going to demonstrate following situation.
Login using the System Admin account
Create a user without login
Checking Access
Impersonate the user without login
Checking Access
Revert Impersonation
Give Permission to user without login
Impersonate the user without login
Checking Access
Revert Impersonation
Clean up
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
