Row_number() Function with no specific order

You know that Row_number() function is used to generate row number to rows based on the column which is ordered

What if you want to generate row number without ordering any column

Here is the method

Consider the following dataset

use tempdb 

create table temp(dates datetime)
insert into temp
select '20090114' union all
select '20080131 10:10:10' union all
select '20070111' union all
select '20090330' union all
select '20120229' union all
select '20100228'

Now you need to use row_number function without order dates column

Here are two methods

1 Assign dummy column with literal 0 and order it by that column

       row_number() over(order by order_col) as sno ,
       dates from 
        select dates,0 as order_col from temp
) as t

2 Assign dummy column with literal 0 directly on the Order by clause

      row_number() over(order by (select 0)) as sno ,

Both the queries will return the following result

sno                  dates
-------------------- -----------------------
1                    2009-01-14 00:00:00.000
2                    2008-01-31 10:10:10.000
3                    2007-01-11 00:00:00.000
4                    2009-03-30 00:00:00.000
5                    2012-02-29 00:00:00.000
6                    2010-02-28 00:00:00.000

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))
Select @test_param
EXEC test1 'test'
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

       'This has more than thirty characters' as varchar
          ) as data

The result is

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=''
    @result=@result+case when number like '[0-9]' then number else '' end 
         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


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


  • 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


  • 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


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


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.


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.