Beware of data truncation – Replicate function with Varchar(max) datatype

As you know, Replicate function is used to repeat a character expression for a specified number of times. But by default the result is converted to varchar with maximum size of 8000 when you don’t convert the expression to specific type

Consider the following example

declare @v varchar(max)
set @v=replicate('a',50000)
select len(@v),datalength(@v)

What do you think the result would be? 50000 Right? But the result is 8000 because by default the result is limited to the maximum size of 8000 for varchar/char datatype.

To get a correct result, you need to convert the expression to the type of varchar(max) as shown below

declare @v varchar(max)
set @v=replicate(convert(varchar(max),'a'),50000)
select len(@v),datalength(@v)

Now the result is 50000 as expected.

So you need to be aware of this implicit convertion when using Replicate function to assingn value to the column of varchar(max) datatype

Character column length vs Data length

When you use character datatypes (char,varchar,nchar,etc),always make sure that column has enough length to have data. Otherwise sometimes you would get errors and sometimes you won’t. 
 
Consider the following example

Declare @t table(id int, test_col varchar(2))
Insert into @t select 1,'test'
Select id,test_col from @t

 
Now, You get the following error

Msg 8152, Level 16, State 14, Line 4
String or binary data would be truncated.
The statement has been terminated.

Because the column length of test_col is not enough to have the value ‘test’
But only when you insert data to a table, you get this error.
In other cases you dont

Declare @v varchar(2)
set @v='test'
select @v

Now there is no error but the value is truncated and
only first two characters are assigned to the variable.
 
The same case is applied when you use a stored procedure with input parameter create

procedure test(@test_param varchar(2))
as
Select @test_param
Go
EXEC test1 'test'
Go
drop procedure test

Now see what happens when you don’t specify the length

Declare @v varcharset @v='test'
select @v

By default the length is 1

Consider another example

Select 
      cast(
       'This has more than thirty characters' as varchar
          ) as data

The result is

data
-------------------------------
This has more than thirty char

 
When you cast a literal to character type without specifying the length,
by default it is 30.

Depends on the usage, if the column does not have enough length
1 Error is generated
2 Data is truncated
3 Default length is assumed
 
So you need to specify enough column length when you use character datatypes

Extract only numbers from alphanumeric string

I see few questions related to this in forums on how to extract only numbers from an alphanumeric string.

Well there can be many methods to do this. One method is to use number table and extract them using a regular expression

Declare @s varchar(100),@result varchar(100)
set @s='as4khd0939sdf78'
set @result=''
select
    @result=@result+case when number like '[0-9]' then number else '' end 
    from
    (
         select substring(@s,number,1) as number from
        (
            select number from master..spt_values where type='p' and 
                   number between 1 and len(@s)
        ) as t
    ) as t
select @result as only_numbers

The result is

only_numbers
-------------------
4093978

How to recover virus affected MDF files?

This is the Guest post by my friend and SQL Server DBA Andrew Jackson

Where there is increase in technology, there is also addition of new computer threats in the list of virus attacks. One such threat is Ransomware. This is a type of malicious virus designed to encrypt files of victim’s machine until and unless a certain amount of money is not paid for decryption. When any encrypted file is having .wallet extension with it, this means that a standard Ransomware pattern is used by the attacker whose major aim is to force victim to pay the ransom.

What Is Ransomware Wallet Virus ?

It is a form of Ransomware attack that enters into one’s computer and encrypts few data files, which are stored on it. This encryption makes file unreadable on different existing programs of the local machine that means users will be unable to open or access such files. The Wallet Ransomware is most common Trojan type, infecting a machine where files of the victim gets encrypted and victim has to pay the con artist, who is hidden behind Wallet Ransomware attack for decrypting files. Paying con artists their desired amount is not a solution for the problem because it is not guaranteed that they will definitely decrypt file after getting money.

This attack uses an asymmetric cryptography technique for encryption in which the file-encoder appends ‘.wallet’ extension with the name of each encrypted file. The asymmetric cryptography technique involves generation of two keys i.e., public key for encryption and a private key for decryption. The private key is located on the remote server by developers therefore, only known to the attacker. A certain amount of money is demanded by cyber criminals for purchasing this private key. A Dharma’s text file comprises of a very short message pointing out the fact that the victim’s machine is unprotected, and users need to resolve the problem by restoring encrypted files.

A common way of executing this threaten activity is by being included in corrupted email attachments. The money requester or attacker will send an email message, which seems as if it is sent by a known user. By opening attachment of the mail, one is going to invite a threat i.e., Wallet Ransomware on their machine. This is the reason why email users are said to be extra conscious while opening emails on their machine.

Tip: One should keep in mind following two safety tips to avoid a bitter situation affecting you and your wallet:

  • Properly authenticate the email sender entity before opening any attachment
  • Timely create backup of your data to completely avoid Ransomware infection

Instead of ignoring problem for the time being, one should eliminate Wallet Ransomware from their machine right away. Therefore, in following section, we are going to learn a .wallet ransomware removal technique from the SQL Server database files.

Recover Affected MDF Files From Wallet Ransomware Virus

Consider a following scenario:

On my PC, the Ransomware attack is caused in the folder where I have placed all the database files i.e., MDF files with their associated LDF files. The files are encrypted with .wallet file extension and the only solution left is to restore files from a healthy backup. Unfortunately, I am not having backup of the databases on a separate machine and am feeling helpless to resolve my problem. Please someone help me out!!

In such scenario, SQL server users can go with the below-explained solution for starting recovery of affected MDF files:

  • Open Run prompt window on your machine by pressing Windows + R key and type msc command. Press Enter to continue
  • Select SQL server service and then right click on it. Hit on Stop button for terminating the SQL server service
  • Navigate towards the location where the server saves its entire database files i.e., primary, secondary, and log files. For example: location of the database files in SQL server 2014 is C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\
  • Choose the MDF files that are encrypted in .wallet extension and then rename it with .mdf file extension
  • Copy and locate the affected MDF files into a healthy system where similar/upgraded edition of the SQL Server is pre-installed
  • Now its time to recover data from affected files in healthy state. Therefore, launch free version of SysTools SQL Recovery software on a healthy systems1
  • Hit on Open button for selecting the affected MDF files from machine

s2

  • As per the convenience, either choose Quick or Advance scan mode and then select the version of SQL server from which MDF file is created. If in case you do not know the version then, enable Auto detect SQL server file (.mdf) version option

s3.png

  • After loading file successfully into SysTools SQL recovery software, the scanning procedure will get started. At this stage, tool will recover data from affected MDF file and after completion of the scanning procedure, application will generate a scanning report of available database objects in that file

s5.png

  • Entire data of affected MDF file will get loaded on the preview window of recovery tool where you can take look on complete data including tables, triggers, views, etc.

s6

Note: once the tool previews the database objects like Table, Trigger, Views, Stored Procedures, Functions etc of affected database file then one can go with the export option by purchasing the full licensed version of the tool.

Conclusion

It is a recommendation to the software user that they should implement above procedure on a healthy system with live SQL Server platform. One will definitely be able to regain the database from MDF files, which were affected from Ransomware attack. The solution will prove itself an helpful approach to recover the database from an encrypted .wallet files.

Question of the month February 2017 – What is the fastest way to transfer data between servers?

Assume that you have two identical tables in different servers. The table in production server has 100 million rows and the table in test server is empty and you want to transfer data from production server into test server to do some analysis based on that data. 

What is your preferred approach to transfer data between servers? 

Understanding Single quotes

Often I see that newbies struggle to understand how single quotes work in SQL Server. The problem happens when you try to add text data which has single quote as part of it to a table.

I have seen newbies worrying why the following doesn’t work

SELECT columns from mytable where col ='Lifco's'

When you specify a value which has single quote, you need to double it

SELECT columns from mytable where col ='Lifco''s'

The following may be helpful (Run and see the result)

SELECT '','''','''''','''''''',''''''''''

When you use a static sql and express a value in a single quote then first and last single quotes specify that the value is a string. Then within those single quotes every double single quotes represent a single single quote

When you use a Dynamic sql then first and last single quotes specify that it is a dynamic sql. Then within those single quotes every double single quotes specify that it is a string.Then within those single quotes every four single quotes represent a single single quote

Execute the following code and see the result

EXEC
(
'SELECT 
'''','''''''','''''''''''','''''''''''''''',
'''''''''''''''''''''
)

Fun with GO

In SQL Server, GO is the default Batch Separator. Here is a Fun when you use GO as Stored Procedure name

Create the following stored Procedure with the name GO

CREATE PROCEDURE GO
(
    @I INT
)
AS
    SELECT @I AS number

The procedure is created and let us try to execute with the following statement

GO 2

What we see is

Command(s) completed successfully

Because it becomes a syntax to execute a batch for two times

EXECUTE GO 2 

Result is 2

Now I let you to try running the following statements to understand what happens

Execution 1

[GO] 2

Execution 2

[GO] 2
GO 2

Execution 3

[GO] 2
GO
EXECUTE GO 2
GO

Execution 4

[GO] 2
GO 2
GO 2

Execution 5

[GO] 2
GO
GO

Execution 6

EXECUTE GO 2
EXECUTE GO 2
GO 2

Execution 7

[GO] 2
GO
GO 2
GO 2
EXECUTE GO 2
GO
GO 2
GO 2
GO 2 
Execution 8
GO;

Execution 9

GO;2

Execution 10

GO 2;

DROP IF EXISTS with multiple tables

SQL Server 2016 has introduced new DDL statement DROP IF EXISTS which will drop the table if exists.

In my earlier post on Dropping multiple tables with one DROP statement I have shown the method on how you can use single DROP statement to drop multiple tables

In the similar way, the new DDL also supports dropping multiple tables if they exists

create table #t1(id int);
create table #t2(id int);
create table #t3(id int);

DROP TABLE IF EXISTS #t1, #t2, #t3

 

Dropping multiple tables with one DROP statement

Do you know that you can drop multiple tables using a sing DROP statement?

Assume that in your code or procedure you use many temporary tables and at the end you want to drop all of them. One method is to drop them one by one

Assume the following temporary tables

create table #t1(id int);
create table #t2(id int);
create table #t3(id int);

Usual method of dropping them is

drop table #t1
drop table #t1
drop table #t1

However, simply you can use single DROP statement with each table names seperated by comma

drop table #t1,#t2,#t3

How many of you know this?