Row_number() Function with no specific order

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 Suppliers table from northwind database)

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

select row_number() over(order by order_col),companyname,address from
(
select companyname,address,0 as order_col from northwind..Suppliers
) as t

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

select row_number() over(order by (select 0)),companyname,address from northwind..Suppliers

Also refer Multipurpose Row_Number() Function

Filed under: , ,

Time Calculation on Numbers

In Forums sometimes I read questions on how to find out the difference between two times which are stored as characters

Here are some possible answers

Subtraction

declare @t1 varchar(5), @t2 varchar(5), @sum float

select @t1='12.56', @t2='7.58'

set @sum=cast(@t1 as float)-cast(@t2 as float)

--Method 1 : Convert to Datetime

--Method 2 : Number caculation1
select case when right(@sum,2)>=60 then @sum-0.4 else @sum end

--Method 3 : String caculation
select cast(left(@sum,charindex('.',@sum)-1)+case when right(@sum,charindex('.',reverse(@sum))-1)>60 then right(@sum,charindex('.',reverse(@sum))-1)-40 else right(@sum,charindex('.',reverse(@sum))-1) end/100.0 as decimal(4,2))

--Method 4 : Number calculation2
select cast(cast(@sum as int)+case when cast(@sum*100 as int)%100>=60 then cast(@sum*100 as int)%100-40 else cast(@sum*100 as int)%100 end/100.0 as decimal(4,2))

--Method 1: Date calculation

--Method 2: Number calculation

select @sum=cast(@t1 as decimal(4,2))+@t2

select case when right(@t1,2)*1+right(@t2,2)>=60 then @sum+0.4 else @sum end

SQL_VARIANT_PROPERTY function

If you want to know the datatype or length of a column, you can query on the system table syscolumns or
system
view INFORMATION_SCHEMA.COLUMNS

But you dont need to query on system objects if you use SQL_VARIANT_PROPERTY function

Example

SELECT
TOP 1
SQL_VARIANT_PROPERTY('column_name','basetype')
FROM
table_name

which gives you the datatype of the column_name

This is an example from Northwind..Orders table

SELECT
TOP 1
SQL_VARIANT_PROPERTY('orderid','basetype') as basetype,
SQL_VARIANT_PROPERTY('orderid','maxlength') as maxlength,
SQL_VARIANT_PROPERTY('orderid','precision') as precision,
SQL_VARIANT_PROPERTY('orderid','scale') as scale,
SQL_VARIANT_PROPERTY('orderid','collation') as collation
FROM
Northwind..Orders