July 2008 - Posts

Outputting DBCC results

Sometimes it may be useful to reuse the result of the DBCC commands. If the DBCC command resturns a resultset, it can be outputted to a table.

Consider the following command

DBCC useroptions

It returns a single resultset. To copy the resultset to a table, you can use the following

Create table #dbcc_useroptions ([set option] varchar(100), [value] varchar(100))

insert into #dbcc_useroptions
exec('DBCC useroptions')

select * from #dbcc_useroptions

drop table #dbcc_useroptions

Note that it is possible with dynamically executing DBCC commands and only the DBCC commands that return a resultset can be used

Posted by Madhivanan with no comments

Ordering Interger values stored in Varchar column

I have seen many newbies asking "How do I sort the numbers stored in varchar columns?"

Here are some methods

declare @t table(data varchar(15))

insert into @t
select '6134' union all
select '144' union all
select '7345' union all
select '109812' union all
select '100074'union all
select '1290' union all
select '45764'

--Method 1

select data from @t
order by cast(data as int)

--Method 2

select data from @t
order by data+0

--Method 3

select data from @t
order by len(data),data

--Method 4

select data from @t
order by replace(str(data),' ','0')

--Method 5

select data from @t
group by data
order by replicate('0',len(data)),data

--Method 6

select data from @t
order by replicate('0',(select max(len(data+0)) from @t)-len(data))+data

--Method 7

select data from @t
cross join
(
        select len(max(data+0)) as ln from @t
) as t
order by replicate('0',ln-len(data))+data

Posted by Madhivanan with 2 comment(s)
Filed under: , ,

Varchar(max) datatype and Replicate function

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 of maximum size 8000 when you dont convert the expression to specific type

Consider the following example

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

Note that the result is not 50000 but 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)

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

Posted by Madhivanan with no comments
Filed under: , ,