SQL SERVER – Weekly Series – Memory Lane – #053 – Final Post in Series

It has been a fantastic journey to write memory lane series for an entire year. This series gave me the opportunity to go back and see what I have contributed to this blog throughout the last 7 years. This was indeed fantastic series as this provided me the opportunity to witness how technology has grown throughout the year and how I have progressed in my career while writing this blog post.

This series was indeed fantastic experience readers as many joined during the last few years and were not sure what they have missed in recent years.

Let us continue with the final episode of the Memory Lane Series.

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.


Get Current User – Get Logged In User
Here is the straight script which list logged in SQL Server users.

Disable All Triggers on a Database – Disable All Triggers on All Servers
Question : How to disable all the triggers for a database? Additionally, how to disable all the triggers for all servers? For answer execute the script in the blog post.

Importance of Master Database for SQL Server Startup
I have received following questions many times. I will list all the questions here and answer them together.

What is the purpose of Master database?
Should our backup Master database?
Which database is must have database for SQL Server for startup?
Which are the default system database created when SQL Server 2005 is installed for the first time?
What happens if Master database is corrupted?

Answers to all of the questions are very much related.


DECLARE Multiple Variables in One Statement
SQL Server is a great product and it has many features which are very unique to SQL Server. Regarding feature of SQL Server where multiple variable can be declared in one statement, it is absolutely possible to do.


How to Enable Index – How to Disable Index – Incorrect syntax near ‘ENABLE’
Many times I have seen that the index is disabled when there is a large update operation on the table. Bulk insert of very large file updates in any table using SSIS is usually preceded by disabling the index and followed by enabling the index. I have seen many developers running the following query to disable the index.


List of all the Views from Database
Many emails I received suggesting that they have hundreds of the view and now have no clue what is going on and how many of them have indexes and how many does not have an index. Some even asked me if there is any way they can get a list of the views with the property of Index along with it.

Here is the quick script which does exactly the same. You can also include many other columns from the same view.

Minimum Maximum Memory – Server Memory Options
I was recently reading about SQL Server Memory Options over here. While reading this one line really caught my attention is minimum value allowed for maximum memory options. The default setting for min server memory is 0, and the default setting for max server memory is 2147483647. The minimum amount of memory you can specify for max server memory is 16 megabytes (MB).


Fundamentals of Columnstore Index
There are two kinds of storage in a database. Row Store and Column Store. Row store does exactly as the name suggests – stores rows of data on a page – and column store stores all the data in a column on the same page. These columns are much easier to search – instead of a query searching all the data in an entire row whether the data are relevant or not, column store queries need only to search a much lesser number of the columns.

How to Ignore Columnstore Index Usage in Query
In summary the question in simple words “How can we ignore using the column store index in selective queries?” Very interesting question – you can use I can understand there may be the cases when the column store index is not ideal and needs to be ignored the same. You can use the query hint IGNORE_NONCLUSTERED_COLUMNSTORE_INDEX to ignore the column store index. The SQL Server Engine will use any other index which is best after ignoring the column store index.


Storing Variable Values in Temporary Array or Temporary List
SQL Server does not support arrays or a dynamic length storage mechanism like list. Absolutely there are some clever workarounds and few extra-ordinary solutions but everybody can;t come up with such solution. Additionally, sometime the requirements are very simple that doing extraordinary coding is not required. Here is the simple case.

Move Database Files MDF and LDF to Another Location
It is not common to keep the Database on the same location where OS is installed. Usually Database files are in SAN, Separate Disk Array or on SSDs. This is done usually for performance reason and manageability perspective. Now the challenges comes up when database which was installed at not preferred default location and needs to move to a different location. Here is the quick tutorial how you can do it.

UNION ALL and ORDER BY – How to Order Table Separately While Using UNION ALL
If your requirement is such that you want your top and bottom query of the UNION resultset independently sorted but in the same result set you can add an additional static column and order by that column. Let us re-create the same scenario.

Copy Data from One Table to Another Table – SQL in Sixty Seconds #031 – Video


Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Weekly Series – Memory Lane – #052

Let us continue with the final episode of the Memory Lane Series.

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.


Set Server Level FILLFACTOR Using T-SQL Script
Specifies a percentage that indicates how full the Database Engine should make the leaf level of each index page during index creation or alteration. fillfactor must be an integer value from 1 to 100. The default is 0.

Limitation of Online Index Rebuld Operation
Online operation means when online operations are happening in the database are in normal operational condition, the processes which are participating in online operations does not require exclusive access to the database.

Get Permissions of My Username / Userlogin on Server / Database
A few days ago, I was invited to one of the largest database company. I was asked to review database schema and propose changes to it. There was special username or user logic was created for me, so I can review their database. I was very much interested to know what kind of permissions I was assigned per server level and database level. I did not feel like asking Sr. DBA the question about permissions.

Simple Example of WHILE Loop With CONTINUE and BREAK Keywords
This question is one of those questions which is very simple and most of the users get it correct, however few users find it confusing for the first time. I have tried to explain the usage of simple WHILE loop in the first example. BREAK keyword will exit the stop the while loop and control is moved to the next statement after the while loop. CONTINUE keyword skips all the statement after its execution and control is sent to the first statement of while loop.

Forced Parameterization and Simple Parameterization – T-SQL and SSMS
When the PARAMETERIZATION option is set to FORCED, any literal value that appears in a SELECT, INSERT, UPDATE or DELETE statement is converted to a parameter during query compilation. When the PARAMETERIZATION database option is SET to SIMPLE, the SQL Server query optimizer may choose to parameterize the queries.


Transaction and Local Variables – Swap Variables – Update All At Once Concept
Summary : Transaction have no effect over memory variables. When UPDATE statement is applied over any table (physical or memory) all the updates are applied at one time together when the statement is committed.

First of all I suggest that you read the article listed above about the effect of transaction on local variant. As seen there local variables are independent of any transaction effect.

Simulate INNER JOIN using LEFT JOIN statement – Performance Analysis
Just a day ago, while I was working with JOINs I find one interesting observation, which has prompted me to create following example. Before we continue further let me make very clear that INNER JOIN should be used where it cannot be used and simulating INNER JOIN using any other JOINs will degrade the performance. If there are scopes to convert any OUTER JOIN to INNER JOIN it should be done with priority.


Introduction to Business Intelligence – Important Terms & Definitions
Business intelligence (BI) is a broad category of application programs and technologies for gathering, storing, analyzing, and providing access to data from various data sources, thus providing enterprise users with reliable and timely information and analysis for improved decision making.

Difference Between Candidate Keys and Primary Key
Candidate Key – A Candidate Key can be any column or a combination of columns that can qualify as unique key in database. There can be multiple Candidate Keys in one table. Each Candidate Key can qualify as Primary Key.

Primary Key – A Primary Key is a column or a combination of columns that uniquely identify a record. Only one Candidate Key can be Primary Key.


Taking Multiple Backup of Database in Single Command – Mirrored Database Backup
I recently had a very interesting experience. In one of my recent consultancy works, I was told by our client that they are going to take the backup of the database and will also a copy of it at the same time. I expressed that it was surely possible if they were going to use a mirror command. In addition, they told me that whenever they take two copies of the database, the size of the database, is always reduced. Now this was something not clear to me, I said it was not possible and so I asked them to show me the script.

Corrupted Backup File and Unsuccessful Restore
The CTO, who was also present at the location, got very upset with this situation. He then asked when the last successful restore test was done. As expected, the answer was NEVER.There were no successful restore tests done before. During that time, I was present and I could clearly see the stress, confusion, carelessness and anger around me. I did not appreciate the feeling and I was pretty sure that no one in there wanted the atmosphere like me.


TRACEWRITE – Wait Type – Wait Related to Buffer and Resolution
SQL Trace is a SQL Server database engine technology which monitors specific events generated when various actions occur in the database engine. When any event is fired it goes through various stages as well various routes. One of the routes is Trace I/O Provider, which sends data to its final destination either as a file or rowset.

DATEDIFF – Accuracy of Various Dateparts
If you want to have accuracy in seconds, you need to use a different approach. In the first example, the accurate method is to find the number of seconds first and then divide it by 60 to convert it in minutes.

Dedicated Access Control for SQL Server Express Edition


Book Signing at SQLPASS


Who I Am And How I Got Here – True Story as Blog Post
If there was a shortcut to success – I want to know. I learnt SQL Server hard way and I am still learning. There are so many things, I have to learn. There is not enough time to learn everything which we want to learn. I am constantly working on it every day. I welcome you to join my journey as well. Please join me in my journey to learn SQL Server – more the merrier.

Vacation, Travel and Study – A New Concept
Even those who have advanced degrees and went to college for years, or even decades, find studying hard.  There is a difference between studying for a career and studying for a certification.  At least to get a degree there is a variety of subjects, with labs, exams, and practice problems to make things more interesting.

Order By Numeric Values Formatted as String
We have a table which has a column containing alphanumeric data. The data always has first as an integer and later part as a string. The business need is to order the data based on the first part of the alphanumeric data which is an integer. Now the problem is that no matter how we use ORDER BY the result is not produced as expected. Let us understand this with an example.

Resolving SQL Server Connection Errors – SQL in Sixty Seconds #030 – Video
One of the most famous errors related to SQL Server is about connecting to SQL Server itself. Here is how it goes, most of the time developers have worked with SQL Server and knows pretty much every error which they face during development language. However, hardly they install fresh SQL Server. As the installation of the SQL Server is a rare occasion unless you are a DBA who is responsible for such an instance – the error faced during installations are pretty rare as well.


Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Weekly Series – Memory Lane – #051

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.


Explanation and Understanding NOT NULL Constraint
NOT NULL is integrity CONSTRAINT. It does not allow creating of the row where column contains NULL value. Most discussed questions about NULL is what is NULL? I will not go in depth analysis it. Simply put NULL is unknown or missing data. When NULL is present in database columns, it can affect the integrity of the database. I really do not prefer NULL in the database unless they are absolutely necessary.

Three T-SQL Script to Create Primary Keys on Table
I have always enjoyed writing about three topics Constraint and Keys, Backup and Restore and Datetime Functions. Primary Keys constraints prevent duplicate values for columns and provides a unique identifier to each column, as well it creates clustered index on the columns.


Get Numeric Value From Alpha Numeric String – UDF for Get Numeric Numbers Only
SQL is great with String operations. Many times, I use T-SQL to do my string operation. Let us see User Defined Function, which I wrote a few days ago, which will return only Numeric values from Alpha Numeric values.

Introduction and Example of UNION and UNION ALL
It is very much interesting when I get requests from blog reader to re-write my previous articles. I have received few requests to rewrite my article SQL SERVER – Union vs. Union All – Which is better for performance? with examples. I request you to read my previous article first to understand what is the concept and read this article to understand the same concept with an example.

Downgrade Database for Previous Version
The main questions is how they can downgrade the from SQL Server 2005 to SQL Server 2000? The answer is : Not Possible.

Get Common Records From Two Tables Without Using Join
Following is my scenario, Suppose Table 1 and Table 2 has same column e.g. Column1 Following is the query,

1. Select column1,column2 From Table1
2. Select column1 From Table2

I want to find common records from these tables, but I don’t want to use the Join clause because for that I need to specify the column name for Join condition. Will you help me to get common records without using Join condition? I am using SQL Server 2005.

Retrieve – Select Only Date Part From DateTime – Best Practice – Part 2
A year ago I wrote a post about SQL SERVER – Retrieve – Select Only Date Part From DateTime – Best Practice where I have discussed two different methods of getting the date part from datetime.

Introduction to CLR – Simple Example of CLR Stored Procedure
CLR is an abbreviation of Common Language Runtime. In SQL Server 2005 and later version of it database objects can be created which are created in CLR. Stored Procedures, Functions, Triggers can be coded in CLR. CLR is faster than T-SQL in many cases. CLR is mainly used to accomplish tasks which are not possible by T-SQL or can use lots of resources. The CLR can be usually implemented where there is an intense string operation, thread management or iteration methods which can be complicated for T-SQL. Implementing CLR provides more security to the Extended Stored Procedure.


Comic Slow Query – SQL Joke

Before Presentation

After Presentation

Enable Automatic Statistic Update on Database
In one of the recent projects, I found out that despite putting good indexes and optimizing the query, I could not achieve an optimized performance and I still received an unoptimized response from the SQL Server. On examination, I figured out that the culprit was statistics. The database that I was trying to optimize had auto update of the statistics was disabled.

Recently Executed T-SQL Query
Please refer to blog post  query to recently executed T-SQL query on database.

Change Collation of Database Column – T-SQL Script – Consolidating Collations – Extention Script
At some time in your DBA career, you may find yourself in a position when you sit back and realize that your database collations have somehow run amuck, or are faced with the ever annoying CANNOT RESOLVE COLLATION message when trying to join data of varying collation settings.


Visiting Alma Mater – Delivering Session on Database Performance and Career – Nirma Institute of Technology
Everyone always dreams of visiting their school and college, where they have studied once. It is a great feeling to see the college once again – where you have spent the wonderful golden years of your time. College time is filled with studies, education, emotions and several plans to build a future. I consider myself fortunate as I got the opportunity to study at some of the best places in the world.

Change Column DataTypes
There are times when I feel like writing that I am a day older in SQL Server. In fact, there are many who are looking for a solution that is simple enough. Have you ever searched online for something very simple. I often do and enjoy doing things which are straight forward and easy to change.


Three DMVs – sys.dm_server_memory_dumps – sys.dm_server_services – sys.dm_server_registry
In this blog post we will see three new DMVs which are introduced in Denali. The DMVs are very simple and there is not much to describe them. So here is the simple game. I will be asking a question back to you after seeing the result of the each of the DMV and you help me to complete this blog post.

A Simple Quiz – T-SQL Brain Trick
If you have some time, I strongly suggest you try this quiz out as it is for sure twists your brain.


List All The Column With Specific Data Types in Database
5 years ago I wrote script SQL SERVER – 2005 – List All The Column With Specific Data Types, when I read it again, it is very much relevant and I liked it. This is one of the script which every developer would like to keep it handy. I have upgraded the script bit more. I have included few additional information which I believe I should have added from the beginning. It is difficult to visualize the final script when we are writing it first time.

Find First Non-Numeric Character from String
The function PATINDEX exists for quite a long time in SQL Server but I hardly see it being used. Well, at least I use it and I am comfortable using it. Here is a simple script which I use when I have to identify first non-numeric character.

Finding Different ColumnName From Almost Identitical Tables
Well here is the interesting example of how we can use sys.column catalogue views and get the details of the newly added column. I have previously written about EXCEPT over here which is very similar to MINUS of Oracle.

Storing Data and Files in Cloud – Dropbox – Personal Technology Tip
I thought long and hard about doing a Personal Technology Tips series for this blog.  I have so many tips I’d like to share.  I am on my computer almost all day, every day, so I have a treasure trove of interesting tidbits I like to share if given the chance.  The only thing holding me back – which tip to share first?  The first tip obviously has the weight of seeming like the most important.  But this would mean choosing amongst my favorite tricks and shortcuts.  This is a hard task.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Weekly Series – Memory Lane – #050

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.


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.

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 – 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.



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.

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.


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.


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 ComparisonSQL 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 learningsharing, 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.


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.


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)

SQL SERVER – Weekly Series – Memory Lane – #049

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.


Two Connections Related Global Variables Explained – @@CONNECTIONS and @@MAX_CONNECTIONS

Returns the number of attempted connections, either successful or unsuccessful since SQL Server was last started.

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 TRYCATCH 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 TRYCATCH. 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.


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.


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?


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.

Limited View

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.


Here are two DMV’s which are newly introduced in SQL Server 2012 and provides vital information about SQL Server.

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.


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


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.

  1. Login using the System Admin account
  2. Create a user without login
  3. Checking Access
  4. Impersonate the user without login
  5. Checking Access
  6. Revert Impersonation
  7. Give Permission to user without login
  8. Impersonate the user without login
  9. Checking Access
  10. Revert Impersonation
  11. Clean up

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Weekly Series – Memory Lane – #048

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.


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.


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.


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.


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.

Limited View


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


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


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)

SQL SERVER – Weekly Series – Memory Lane – #047

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.


List All The Constraint of Database – Find Primary Key and Foreign Key Constraint in Database
The script listed in the blog is very useful to know all the constraint in the database. I use this many times to check the foreign key and primary key constraint in database.

Difference Between UPDATE and UPDATE()
What is the difference between UPDATE and UPDATE()? The UPDATE is the syntax used to update the database tables or database views. UPDATE() is used in triggers to check update/insert to the database tables or database views.

UDF – Validate Positive Integer Function – Validate Natural Integer Function
Earlier I wrote SQL SERVER – UDF – Validate Integer Function. It was very interesting to write this and developers at my company started to use it. One Jr. DBA modified this function to validate only positive integers.

Rename Database to New Name Using Stored Procedure by Changing to Single User Mode
There are a few interesting facts to note when the database is renamed.

  • When renamed the database, filegroup name or filename (. mdf,. ldf) are not changed.
  • User with SA privilege can rename the database with following script when the context of the database is master database.



Execution Plan and Results of Aggregate Concatenation Queries Depend Upon Expression Location
The best practice is to avoid the usage of function in ORDER BY clause when string concatenation operations are executed.

The reason for this behavior is that the use of function in ORDER BY clause will change the order of query execution and create an unexpected output.

Introduction to Service Broker and Sample Script
The maintenance of Service Broker is easy and it is a part of the routine database administration procedure. This is because this functionality forms a part of the Database Engine. Service Broker also provides security by preventing unauthorized access from networks and by message encryption. Let us understand Service Broker with a simple script. The script contains necessary comments to explain what exactly script is doing.


SELECT * and Adding Column Issue in View – Limitation of the View 4
In this blog we talk about a very well known limitation of the view. Once the view is created and if the basic table has any column added or removed, it is not usually reflected in the view till it is refreshed.

How to Stop Growing Log File Too Big
With this method, you can restore your database at Point of Time if a disaster ever happens on your server.

Let us run an example to demonstrate this. In this case, I have done the following steps:

  1. Create Sample Database in FULL RECOVERY Model
  2. Take Full Backup (full backup is a must for taking subsequent backup)
  3. Repeat Following Operation
    1. Take Log Backup
    2. Insert Some rows
    3. Check the size of Log File
  4. Clean Up

COUNT(*) Not Allowed but COUNT_BIG(*) Allowed – Limitation of the View 5
One of the most prominent limitations of the View it is that it does not support COUNT(*); however, it can support COUNT_BIG(*) operator. In the following case, you see that if View has COUNT (*) in it already, it cannot have a clustered index on it. On the other hand, a similar index would be created if we change the COUNT (*) to COUNT_BIG (*).For an easier understanding of this topic, let us see the example in blog.


I have put up a quick guide here where I am writing all the 14 new functions linking them to my blog post as well Book On-Line for a quick reference.

SQLAuthority.com Book On-Line
Conversion functions
Date and time functions
Logical functions
String functions

I have personally bookmarked this post for my future reference.


SSMS Automatically Generates TOP (100) PERCENT in Query Designer
Question: I am trying to create a view in Query Designer (not in the New Query Window). Every time I am trying to create a view it always adds  TOP (100) PERCENT automatically on the T-SQL script. No matter what I do, it always automatically adds the TOP (100) PERCENT to the script. I have attempted to copy paste from notepad, build a query and a few other things – there is no success. I am really not sure what I am doing wrong with Query Designer.

Effect of Collation on Resultset – SQL in Sixty Seconds #026 – Video


SQL SERVER – 2 T-SQL Puzzles 

Why following code when executed in SSMS displays result as a * (Star)?


Replace a Column Name in Multiple Stored Procedure all together
How to replace a column name in multiple stored procedure efficiently and quickly? This blog tries to answer the same question.

Reference: Pinal Dave (http://blog.sqlauthority.com)