March 2009 - Posts

Filling random passwords - Set based approach


this post, I showed a way to generate 8 characters random password


If you have table with data and add a column for password and want to fill that column with random passwords, you can use this approach. I have seen the usage of a function that generates random password, and use that function in the update statement.

The following updates the
column with random password in a set based approach

declare @password varchar(max), @number as int

set @password=''

select @password=@password+char(n) from
        select top 10000 t1.number as n from master..spt_values t1 cross join master..spt_values as t2 
        where t1.type='p' and t1.number between 48 and 122
order by newid()
) as t

set @number=0

declare @t table(i int, pwd varchar(8))

insert into @t(i)
select top 100 number from master..spt_values
where type='p' and number>0

select * from @t

update @t
set pwd=substring(@password,@number*8,8),@number=@number+1

select * from @t

Posted by Madhivanan with no comments

Which columns uniquely identify a row?

Without seeing the table structure,If you are interested in knowing which columns uniquely identify a row in a table, you can use this system procedure 

EXEC sp_special_columns table_name


Posted by Madhivanan with no comments

Beware of the Usage of SET ROWCOUNT

This is used to set the number of rows affected for DML

Declare @test table(number int)

set rowcount 1

insert into @test
select 11 union all select 10
set rowcount 0

select number from @test

The above inserts only one row to the table variable @test

But beware that this wont be supported in Future versions of SQL Server

Here is the Remarks from BOL, SQL Server help file

Using SET ROWCOUNT will not affect DELETE, INSERT, and UPDATE statements in the next release of SQL Server. Avoid using SET ROWCOUNT together with DELETE, INSERT, and UPDATE statements in new development work, and plan to modify applications that currently use it. Also, for DELETE, INSERT, and UPDATE statements that currently use SET ROWCOUNT, we recommend that you rewrite them to use the TOP syntax.

Posted by Madhivanan with no comments
Filed under: , ,