Creating series '22-47' '23-48' '24-49' in incremental order

Today I got a question in one of the SQL Forum, the problem statement was

“Hi, I have string like '21-46' i need output like '22-47' '23-48' '24-49'incremental order. etc...plz help me..”

This reminds me the Pl/SQL session we use to have in our college days and my dbms faculty, pretty Snehal Mam…

So here he is the answer for the same.

create proc sp_NoSeries

@howLong int,

@strNo varchar(50)

as

begin

 try

     declare @cntr int

     declare @no1 int

     declare @no2 int

    declare @msg varchar(100)

   

     set @howLong = 6

     set @cntr = 0

     while (@cntr<@howLong)

     begin

           print @strNo

           set @no1=cast(substring(@strNo,0,charindex('-',@strNo)) as int) + 1

           set @no2=cast(substring(@strNo,charindex('-',@strNo)+1,len(@strNo)) as int) + 1

          

           set @strNo=cast(@no1 as varchar(10)) +'-' + cast(@no2 as varchar(10))

          

           set @cntr = @cntr +1

     end

 end try

 begin catch

     print 'Error occur that is '

     set @msg = (select error_message())

     print @msg

 end catch

go

Lets execute the procedure.

exec sp_NoSeries 6,'21-46'

21-46

22-47

23-48

24-49

25-50

26-51

exec sp_NoSeries 6,'21-df46'

21-df46

Error occur that is

Conversion failed when converting the varchar value 'df46' to data type int.

To be happy always, be a coder, to learn happiness, love coder! J

 

Post Reference: Vikram Aristocratic Elfin Share

What does TimeStamp/Rowversion does?

Rowversion /Timestamp is a datatype which stores 8byte binary number. Each time any insert/update operation performed on a table having Rowversion column, the value of row version for inserted or updated rows changes to the latest counter of Rowversion.

A table can have maximum of only one column as rowversion datatype.

Timestamp is synonym to the Rowversion data type.

Lets see with an example, how we can use rowversion as a column in our table.

create table RowVersion_TB

(col1 int, col2 int, timestamp);

Command(s) completed successfully.

Now we have our table created with  rowversion datatype as one of the column. Lets insert few records in it.

insert into RowVersion_TB (col1, col2)

select 1,100 union all

select 2,200 union all

select 3,300

Lets query the table to see what values appears in the rowversion field.

select * from RowVersion_TB

col1        col2        timestamp

----------- ----------- ------------------

1           100         0x00000000000007D4

2           200         0x00000000000007D5

3           300         0x00000000000007D6

(3 row(s) affected)

Alright , the value of rowversion column is in increamented order. Lets update a row say where col1=3 and see how it affect rowversion data.

update RowVersion_TB

set col2=400

where col1=3

select * from RowVersion_TB

col1        col2        timestamp

----------- ----------- ------------------

1           100         0x00000000000007D4

2           200         0x00000000000007D5

3           400         0x00000000000007D7

(3 row(s) affected)

See the rowversion value changed when we change the data of col2 to 07D7

Lets again update the first row of the table to see, the changed value of Rowversion

update RowVersion_TB

set col2=10

where col1=1

select * from RowVersion_TB

col1        col2        timestamp

----------- ----------- ------------------

1           10          0x00000000000007D8

2           200         0x00000000000007D5

3           400         0x00000000000007D7

(3 row(s) affected)

Ah, notice that col1 1 has most updated Rowversion value. So most recently updated record has latest rowversion information.

Coder always have reason to avoid outing and shoping! J

Distinct vs Group By, Which is faster?

Group By with all the columns in the group by behaves much the same as DISTINCT when no aggregation function is involved.

But my strong recommendation is

“Use Distinct, if you want distinct record set because it is meant for finding Distinct records, and since Group By is associated with aggregate function, use it when you have to do some aggregation.”

A famous quote which I remember. “You can use knife as a screw driver, but when you have screw driver don’t use knife” but at the same time see also the execution time for both the alternative before choosing your alternative.  

Here in this post, we will conclude which is the fastest way to find distinct record in table. For demonstration we are taking a sample table with few rows in it.

create table table1

(id int identity(1,1),

name varchar(10),

l_name varchar(10))

insert into table1

select 'Babu','Mohanty' union all

select 'Rinny','Das' union all

select 'Binny','Mohapatra' union all

select 'Babu','Mohanty' union all

select 'Binny','Mohapatra'

Now since our table is ready, we can do some odd of practical to bring some concrete on this case.

CASE 1:

We are trying to find the distinct name and l_name list.

select name,l_name from table1

group by name,l_name

select distinct name,l_name from table1

Both result the same distinct records. Let’s see the execution plan of both the query.

So we saw both the query produce exactly similar plan, so this case anyone alternative can be chosen.

CASE 2

In the below example we are again trying to find the distinct records with two alternative.

select name,

       getdate(),

from table1

group by name

select distinct

       name,

       getdate()

from table1

Lets see the execution plan for both the query to decide which one will be more effective.

So here we can see the query are producing different plan, but when it comes to cost both are taking exactly same cost to execute. So again you can use any alternative as far this example n environment is concern.

CASE 3

Again we are trying to find the distinct record with two kindda alternative.

select name,

        rank() over(order by name) as RowNumber

from table1

group by name

select distinct

       name,

       rank() over(order by name) as RowNumber

from table1

Lets see the execution plan to find which is more efficient way to find the distinct records.

So here we saw again it produce different plan but both taking same cost to execute, so again we can choose anyone in this respect.

Conclusion: It all on your various parameter which may affect the outcome cost of query, so better approach would be check the execution plan and select your alternative.

A coder has thousand reason to laugh but zero reason to cry! J

 

Post Reference: Vikram Aristocratic Elfin Share