New enhancement in SSMS in SQL Server

SSMS in SQL 2016 has come up with lots of new features. One of the features is enhanced scrollbar in SSMS. I personally believe the new enhancement in scrollbars will definitely help developers to work with SSMS with ease.

Let us look at the below picture to have an insight:

They are different color bocks on the right side of SSMS query window that shows the changes:
a. Green blocks show the saved lines of Query part in a window to disk
b. Yellow blocks show the query lines of unsaved/changed lines since last save to disk
c. Red blocks show any syntax errors in a query window
d. Blue block shows the current cursor location

It is definitely good to know to developers who plays around with SSMS very frequently.

Advertisements

Extending sp_helpindex to get more useful information about index in SQL Server

This post is to help people who had tough time to get information about index and its included columns in SQL Server. Whenever I need, I always depend on object explorer and get the definition of the index.So I thought of compiling a snippet as below that can provide these information handy.

I created a procedure called – sp_helpindexExtended as below and the Tablename as parameter(this is an optional parameter).

Procedure Definition


Create Proc sp_helpindexExtended (@TableName sysname = NULL) 
as 
Begin 
	  ;With cte as 
	  (
		 Select Object_name(A.object_id) objectname,A.name index_name,
			Lower(type_desc) + Case When IS_Unique =1 then ', unique' Else '' end +
			Case when is_primary_key =1 Then ', primary key' Else '' End+
			Case when IS_Unique_Constraint = 1 Then ',unique key' Else '' End  index_description,
			Case when is_included_column=0 then c.name  + '('+
            Case When System_type_id = User_Type_id Then Type_Name(System_type_id) Else Type_Name(User_Type_id) End +')'  Else NULL end ColumnName,
            Case when is_included_column=1 then c.name  + '('+
            Case When System_type_id = User_Type_id Then Type_Name(System_type_id) Else Type_Name(User_Type_id) End +')'  Else NULL end IncludedColumnnames
            ,Is_Unique,Is_Primary_Key,IS_Unique_Constraint,Fill_factor,key_ordinal
    From sys.indexes A  
    Inner Join sys.index_columns B On A.object_id = B.object_id And A.index_id = B.index_id 
    Inner Join sys.columns C On c.object_id = B.object_id  And C.column_id  = B.column_id 
	Where A.Object_ID = Case when @TableName is not null Then OBJECT_ID(@TableName) Else A.object_id End
	)	SELECT objectname,index_name,index_description,
       stuff(( SELECT ','+ColumnName AS [text()] FROM cte p2
          WHERE p2.objectname = p1.objectname and p2.index_name = p1.index_name
          ORDER BY key_ordinal asc FOR XML PATH('') ), 1, 1,'') AS index_keys,
		stuff( ( SELECT IsNull(','+IncludedColumnnames ,'')  FROM cte p2
          WHERE p2.objectname = p1.objectname and p2.index_name = p1.index_name
          ORDER BY key_ordinal asc FOR XML PATH('') ), 1, 1,'')  AS included_keys
		  ,fill_factor 
      FROM cte p1 GROUP BY objectname,index_name ,index_description,fill_factor 
	 order by objectname asc
End

Procedure Usage

-- To get for all tables
exec sp_helpindexextended
-- To get only for a table
exec sp_helpindexextended 'tablename'

How to extract ONLY numbers from a string in SQL Server

Here is a script to extract *ONLY* numbers from a string.


DECLARE @Sample TABLE(string VARCHAR(2000));
INSERT INTO @Sample
Values( 'hhjjj12345hhhkk'),('-7655'),('asd5-5dffgdfg105'),('hhjjj12345hhhkk'),('a1b2c3d4ee55fff666gggg7777h'),('Al1')
,('2Bob$'),('!Carl%'),('%D#?:"{}|\][+_)(*&^	%$#@!~`_+?>a*vi()(d&*(&*#?:"{}|\][J+o_n)es(*&^%$#@!~`_+?><,./;:"' ),('    Eddie     ')
;WITH Num1 (n) AS (SELECT 1 UNION ALL SELECT 1),
Num2 (n) AS (SELECT 1 FROM Num1 AS X, Num1 AS Y),
Num3 (n) AS (SELECT 1 FROM Num2 AS X, Num2 AS Y),
Num4 (n) AS (SELECT 1 FROM Num3 AS X, Num3 AS Y),
Nums (n) AS (SELECT ROW_NUMBER() OVER(ORDER BY n) FROM Num4),
FinalOutput AS (
SELECT string, (SELECT CASE WHEN SUBSTRING(string, n, 1) NOT LIKE '[^0-9]'
                       THEN SUBSTRING(string, n, 1)
                      ELSE ''
                  END + ''
           FROM Nums
           WHERE n <= LEN(string)
           FOR XML PATH('')) AS stringout
FROM @Sample)
SELECT string, stringout FROM FinalOutput;

How to replace first occurrence of a word in a sentence in SQL Server

Have you ever thought of How REPLACE is working? It just replaces all the occurrences of a word in the sentence.

Lets look at an example.


CREATE TABLE #StringTable ( id INT, string VARCHAR(128) )

INSERT #StringTable VALUES (1,'It was a pleasant morning, and he was the only kid in the garden')
,(1,'There is no match word')
Declare @searchstring varchar(100)='was',@Replacestring varchar(100)='is'

select id,string,
	Replace(string,@searchstring,@replacestring)
	from #StringTable
		cross apply (select (charindex(@searchstring, string))) as Search1(Pos)

Drop table #StringTable

In the above example, there are two occurrences of the word “was” for the first record. And the REPLACE function results in replacing all occurrences.

Suppose, if you want to only replace the first occurrence, How do you do?

First approach with CROSS APPLY and SUBSTRING:


CREATE TABLE #StringTable ( id INT, string VARCHAR(128) )

INSERT #StringTable VALUES (1,'It was a pleasant morning, and he was the only kid in the garden')
,(1,'There is no match word')

Declare @searchstring varchar(100)='was',@Replacestring varchar(100)='is'

select id,string,
	case when Search1.Pos != 0 then 
				substring(string, 0,Search1.Pos )+ @Replacestring +
				substring(string, Search1.Pos+LEN(@searchstring), LEN(string) )
	else string end ChangedText
	from #StringTable
		cross apply (select (charindex(@searchstring, string))) as Search1(Pos)

Drop table #StringTable

Another method with STUFF and PATINDEX:


CREATE TABLE #StringTable ( id INT, string VARCHAR(128) )

INSERT #StringTable VALUES (1,'It was a pleasant morning, and he was the only kid in the garden')
,(1,'There is no match word')

Declare @searchstring varchar(100)='was',@Replacestring varchar(100)='is'

Select id, string, Case when charindex(@searchstring, string) != 0 Then
				Stuff(string, patindex('%'+@searchstring+'%',string),len(@searchstring),@Replacestring) 
				Else string End ChangedText
	from #StringTable

Drop table #StringTable

ALTER DATABASE failed because a lock could not be placed on database ‘DBNAME’. Try again later. in SQL Server

At times, we may need to take a database offline. And there is a chance that we encounter an error message like below.
How do we solve this issue and take the database offline?


Msg 5061, Level 16, State 1, Line 1
ALTER DATABASE failed because a lock could not be placed on database 'DBNAME'. Try again later.

Solution:
If there are any connections open, then there is a lock on the database while getting the database into Single user mode/taking offline.
To resolve the issue, as a first step you need to kill all the sessions on the database followed by the ALTER database statement.


USE master;

DECLARE @killSessions varchar(8000) = '';  
SELECT @killSessions = @killSessions + 'kill ' + CONVERT(varchar(5), spid) + ';'  
FROM master..sysprocesses  
WHERE dbid = db_id('dbname')
EXEC(@killSessions); 

ALTER DATABASE dbname SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
GO
ALTER DATABASE dbname SET OFFLINE WITH ROLLBACK IMMEDIATE
GO
ALTER DATABASE dbname  SET MULTI_USER;

The number of row value expressions in the INSERT statement exceeds the maximum allowed number of 1000 row values.

One of my colleague was facing an issue while inserting 2000 values to a table variable as below:


 DECLARE @EMPLOYEEDETAILS TABLE
(OLDEMPLOYEECODE NVARCHAR(30), NEWEMPLOYEECODE NVARCHAR(30))

INSERT INTO @EMPLOYEEDETAILS(OLDEMPLOYEECODE, NEWEMPLOYEECODE)
VALUES
('93466','0000007'),
('93467','0000010'),
...
...(2000 Records)
...
('93467','00002000')

Error Message:
Msg 10738, Level 15, State 1, Line 1006
The number of row value expressions in the INSERT statement exceeds the maximum allowed number of 1000 row values.

This is a self explanatory error message as SQL Server does not support row value expressions for more than 1000 values.
To overcome the issue, the code has been modified as below and the query has been executed successfully.

Solution:


DECLARE @EMPLOYEEDETAILS TABLE
(OLDEMPLOYEECODE NVARCHAR(30), NEWEMPLOYEECODE NVARCHAR(30))

INSERT INTO @EMPLOYEEDETAILS(OLDEMPLOYEECODE, NEWEMPLOYEECODE)
SELECT * FROM (VALUES
('93466','0000007'),
('93467','0000010'),
...
...(2000 Records)
...
('93467','00002000')
) A(Col1, Col2)

You may share any other alternatives/thoughts….

Could not load assembly ‘Microsoft.SqlServer.Connectioninfo, Version=10.0.0.0, Culture=netural, PublicKeyToken=89845dcd8080cc91’ or one Of its dependencies. The system cannot find the file specified.

Problem Statement:

When trying to migrate SQL Server 2008 R2 to SQL Server 2016, we identified an issue with the following error:

Could not load assembly ‘Microsoft.SqlServer.Connectioninfo, Version=10.0.0.0, Culture=netural, PublicKeyToken=89845dcd8080cc91’ or one
Of its dependencies. The system cannot find the file specified.

As a knee jerk reaction, we initially thought it was an issue with SQL Server 2016 installation and need to install the Feature Pack for SQL Server 2016. Once applied the feature Pack, we again tested and found the same issue unfortunately(but really fortunate one!!!).

Analyzing further(I mean, reading the error message clearly, It is found the version 10.0.0 that represents Microsoft® SQL Server® 2008.
As we do not have SQL Server 2008 installed in the environment and the code(.NET application) has some reference to the version mentioned, this was not able to find the dlls in the specified path(C:\windows\assemblies\).

To proceed with our testing, we installed SQL Server 2008 R2 Feature pack and resumed our testing. Finally, it worked like a treat!!!

The learning is we always need to go to the facts(error message) than blindly presume things!!!

please find the below information for various version feature pack references:

Microsoft® SQL Server® 2016 Feature Pack (Version: 13.0.1601.0)
https://www.microsoft.com/en-us/download/details.aspx?id=52676
Microsoft® SQL Server® 2014 SP2 Feature Pack (Version: 12.0.5000.0)
https://www.microsoft.com/en-us/download/details.aspx?id=53164
Microsoft® SQL Server® 2014 Feature Pack (Version: 12.0.0.0)
http://www.microsoft.com/en-us/download/details.aspx?id=42295
Microsoft® SQL Server® 2012 SP1 Feature Pack (Version: 11.0.0.0)
http://www.microsoft.com/en-us/download/details.aspx?id=35580
Microsoft® SQL Server® 2012 Feature Pack (Version: 11.0.2100.60)
https://www.microsoft.com/en-us/download/details.aspx?id=29065
Microsoft® SQL Server® 2008 R2 SP2 Feature Pack (Version: 10.50.4000.0)
https://www.microsoft.com/en-us/download/details.aspx?id=30440
Microsoft® SQL Server® 2008 R2 SP1 Feature Pack (Version: 10.50.2500.0)
http://www.microsoft.com/en-us/download/details.aspx?id=26728
Microsoft® SQL Server® 2008 R2 Feature Pack (Version: 10.50.1600.1)
https://www.microsoft.com/en-us/download/details.aspx?id=16978
Microsoft SQL Server 2008 Service Pack 4 Feature Pack (Version: 10.0.6000.29)
https://www.microsoft.com/en-in/download/details.aspx?id=44277
Microsoft SQL Server 2008 Service Pack 3 Feature Pack (Version: 10.00.5500.00)
https://www.microsoft.com/en-us/download/details.aspx?id=27596
Microsoft SQL Server 2008 Service Pack 2 Feature Pack (Version: 10.00.4000.00)
https://www.microsoft.com/en-us/download/details.aspx?id=6375
SQL Server 2008 Service Pack 1 (Version: 10.00.2531.00)
https://www.microsoft.com/en-in/download/details.aspx?id=20302