Author Archives: nakulvachhrajani

About nakulvachhrajani

Nakul Vachhrajani is a SQL Server MCTS, TOGAF certified Technical Architect with Capgemini having a total IT experience of more than 12 years. Nakul is an active blogger, and has been a guest columnist for SQLAuthority.com and SQLServerCentral.com. Nakul presented a webcast on the “Underappreciated Features of Microsoft SQL Server” at the Microsoft Virtual Tech Days Exclusive Webcast series (May 02-06, 2011) on May 06, 2011. He is also the author of a research paper on Database upgrade methodologies, which was published in a CSI journal, published nationwide. In addition to his passion about SQL Server, Nakul also contributes to the academia out of personal interest. He visits various colleges and universities as an external faculty to judge project activities being carried out by the students. Disclaimer: The opinions expressed herein are his own personal opinions and do not represent his employer’s view in anyway.

#0407 – SQL Server – Clearing out the list of servers in SSMS


Today, I will talk about a very common question that I see in the forums. When you  work with a lot of SQL Server instances, the list of servers seen on the login screen in the SQL Server Management Studio (SSMS) becomes quite long, raising the question:

How to clear out the list of SQL Server instance names in SSMS?

SQL Server 2014 and above

Clearing out servers that no longer exist or to which you no longer need to connect to is quite simple in SQL Server 2014 and above. All you need to do is:

  1. Open SSMS
  2. In the login window,  expand the list of available SQL Server instances
  3. Use the keyboard’s down arrow or use the mouse to scroll down to the instance that needs to be deleted
  4. Once the required instance is selected in the list, just press “Delete” on the keyboard
SSMS_LoginWindow

Just select the appropriate SQL Server instance and press “Delete” to remove it from the SSMS login history

If you are still using an older version of SSMS due to various reasons, there is a manual workaround to this as shown below.

SSMS for SQL Server 2012 and below

  1. Close all open instances of SSMS on your workstation
  2. Depending upon your version of the SSMS client tools, navigate to and delete the files as shown in the table below:
  3. Launch SSMS
  4. It might take a little while to launch, as it recreates the “SqlStudio.bin”/”mru.dat
    • Once launched, you will see that the entire SSMS history is gone
SSMS Client Tools Version Path File to Delete
SQL 2012 %USERPROFILE%AppDataRoamingMicrosoftSQL Server Management Studio11.0 SqlStudio.bin
SQL 2008 %USERPROFILE%AppDataRoamingMicrosoftMicrosoft SQL Server100ToolsShell SqlStudio.bin
SQL 2005 %USERPROFILE%AppDataRoamingMicrosoftMicrosoft SQL Server90ToolsShell mru.dat

Until we meet next time,

Be courteous. Drive responsibly.

#0406 – SQL Server – Remember that spaces and blank strings are the same


It was recently brought to my attention that a particular script was passing spaces when it should not. Here’s an example:

DECLARE @spaceCharacter NVARCHAR(1) = N' ';
DECLARE @blankCharacter NVARCHAR(1) = N'';

--Confirm that we are looking at different values
--The ASCII codes are different!
SELECT ASCII(@spaceCharacter) AS ASCIICodeForSpace,
       ASCII(@blankCharacter) AS ASCIICodeForBlankString;

--Compare a blank string with spaces
IF (@spaceCharacter = @blankCharacter)
    SELECT 'Yes' AS IsSpaceSameAsBlankString;
ELSE 
    SELECT 'No' AS IsSpaceSameAsBlankString;
GO

/* RESULTS
ASCIICodeForSpace ASCIICodeForBlankString
----------------- -----------------------
32                NULL

IsSpaceSameAsBlankString
------------------------
Yes
*/

01_Symptom

We then checked the LENGTH and DATALENGTH of both strings and noticed something interesting – the check on the LENGTH was trimming out trailing spaces whereas the check on the DATALENGTH was not.

DECLARE @spaceCharacter NVARCHAR(1) = N' ';
DECLARE @blankCharacter NVARCHAR(1) = N'';

--Check the length
SELECT LEN(@spaceCharacter) AS LengthOfSpace, 
       LEN(@blankCharacter) AS LengthOfBlankCharacter,
       DATALENGTH(@spaceCharacter) AS DataLengthOfSpace, 
       DATALENGTH(@blankCharacter) AS DataLengthOfBlankCharacter;
GO

/* RESULTS
LengthOfSpace LengthOfBlankCharacter DataLengthOfSpace DataLengthOfBlankCharacter
------------- ---------------------- ----------------- --------------------------
0             0                      2                 0
*/

02_LengthAndDataLength

Often, we loose sight of the most basic concepts – they hide in our subconscious. This behaviour of SQL Server is enforced by the SQL Standard (specifically SQL ’92) based on which most RDBMS systems are made of.

The ideal solution for an accurate string comparison was therefore to also compare the data length in addition to a normal string comparison.

DECLARE @spaceCharacter NVARCHAR(1) = N' ';
DECLARE @blankCharacter NVARCHAR(1) = N'';

--The Solution
IF (@spaceCharacter = @blankCharacter) 
   AND (DATALENGTH(@spaceCharacter) = DATALENGTH(@blankCharacter))
    SELECT 'Yes' AS IsSpaceSameAsBlankString;
ELSE 
    SELECT 'No' AS IsSpaceSameAsBlankString;
GO

/* RESULTS
IsSpaceSameAsBlankString
------------------------
No
*/

03_Solution

Further Reading

  • How SQL Server Compares Strings with Trailing Spaces [KB316626]

Until we meet next time,

Be courteous. Drive responsibly.