September 10, 2009 at 10:08 pm
Comments posted to this topic are about the item Changing Usernames
September 11, 2009 at 3:06 am
Great idea. Thanks folks.
--Jeff Moden
Change is inevitable... Change for the better is not.
September 11, 2009 at 3:27 am
Of all the databases I'd have expected to have the right constraints defined, this would have been top of the list. You'll be telling us it's all done with cursors next 😀
September 11, 2009 at 3:47 am
will you toss in a T for the one with the highest added number ? :w00t:
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
September 11, 2009 at 7:48 am
I'd love to see your corrective T-SQL Steve - good article material. I want to see timings using complex SQL, use of temporary tables and for Jeff M's benefit - use of cursors!
OK. Perhaps I HAVE lost the plot a little, but it gives me a buzz.
September 11, 2009 at 7:51 am
We used to have those constraints implemented! V1 and V2 of this site (2001 and 2004 respectively) were built by DBAs. So the ASP and ASP.NET code, the HTML wasn't great, but we had things in place in the db, normalized tables, lookups, no enums, etc.
v3 was a corporate application, under the same constraints and issues many of you face. Done by developers, not much DBA input, deadlines, etc. It came out well, but one of the things that was missing was a good model of community interaction. Some of that is because the forum software didn't have unique usernames, the devs didn't think it mattered.
We're slowly trying to correct things to make it better.
September 11, 2009 at 8:01 am
ALZDBA (9/11/2009)
will you toss in a T for the one with the highest added number ? :w00t:
You'll have to add your own "T"
September 11, 2009 at 8:18 am
:hehe:
declare @Tmp table (UserName varchar(128) not null, EmailAddress varchar(128) not null primary key, TsRegistration datetime not null)
Insert into @Tmp values ('alzdba', 'alzdba@dono.com', '2001-04-01 18:00:00.000')
Insert into @Tmp values ('alzdba', 'alzdba1@dono.com', '2002-04-01 18:00:00.000')
Insert into @Tmp values ('alzdba', 'alzdba2@dono.com', '2003-04-01 18:00:00.000')
Insert into @Tmp values ('alzdba', 'alzdba3@dono.com', '2004-04-01 18:00:00.000')
Insert into @Tmp values ('alzdba', 'alzdba4@dono.com', '2005-04-01 18:00:00.000')
Insert into @Tmp values ('alzdba', 'alzdba5@dono.com', '2006-04-01 18:00:00.000')
Insert into @Tmp values ('alzdbx', 'alzdbx@dono.com', '2001-04-01 18:00:00.000')
Insert into @Tmp values ('alzdbx', 'alzdbx1@dono.com', '2002-04-01 18:00:00.000')
Insert into @Tmp values ('alzdbx', 'alzdbx2@dono.com', '2003-04-01 18:00:00.000')
Insert into @Tmp values ('alzdbx', 'alzdbx3@dono.com', '2004-04-01 18:00:00.000')
Insert into @Tmp values ('alzdbx', 'alzdbx4@dono.com', '2005-04-01 18:00:00.000')
Insert into @Tmp values ('alzdbx', 'alzdbx5@dono.com', '2006-04-01 18:00:00.000')
Select EmailAddress, rank() over (partition by UserName order by UserName, TsRegistration)
from @Tmp
update T
set UserName = UserName + '_' + convert(varchar(10),RankNo)
from @Tmp T
inner join (Select EmailAddress
, rank() over (partition by UserName order by UserName, TsRegistration) as RankNo
from @Tmp ) Rnk
on T.EmailAddress = Rnk.EmailAddress
and Rnk.RankNo > 1
Select *
from @Tmp
order by UserName, TsRegistration
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
September 11, 2009 at 8:29 am
Very neat - sadly can't use RANK() in 2000 db but otherwise nice!
September 11, 2009 at 5:01 pm
Jamie Batiste (9/11/2009)
I'd love to see your corrective T-SQL Steve - good article material. I want to see timings using complex SQL, use of temporary tables and for Jeff M's benefit - use of cursors!
Sure! Don't forget to use an explicit transaction inside the loop with rollback code and all for maximum effect! Better yet, write it as a single update and incorporate an inequality in the SET statement to form a perfect triangular join to generate the sequential numbers so it looks set based. 😀
--Jeff Moden
Change is inevitable... Change for the better is not.
September 11, 2009 at 9:19 pm
Trust a respected DB newsletter not to constrain itself...
September 11, 2009 at 9:23 pm
Trust a respected DB newsletter not to constrain itself...
September 14, 2009 at 5:14 am
Ouch. It was the "Developers' fault", eh. Damn corporate projects and their corporate developers that don't talk to the DBA's.
I find it amazing that anyone thinks this is amazing --even DBA's may not decide that a unique constraint on user name matters and the BA's would be making snide remarks.
BTW the site is awesome and I visit every day. I wonder if my name will change.
September 14, 2009 at 5:47 am
Its a real world example of change that happens to us all - is useful in the future as a reference and I don't think anyone is worried about an additional number -except maybe Steve - wouldn't look good if he was Steve Jones 321 🙂
Alzdba's code is neat example and highlights rank() which I'm sure I'll use in the future. I probably would have used something a little like this to generate the ranks in 2000
declare @Tmp table (UserName varchar(128) not null, EmailAddress varchar(128) not null primary key, TsRegistration datetime not null, userrank int)
declare @userrank int
select @userrank=0
Insert into @Tmp values ('alzdba', 'alzdba@dono.com', '2001-04-01 18:00:00.000',-1)
Insert into @Tmp values ('alzdba', 'alzdba1@dono.com', '2002-04-01 18:00:00.000',-1)
Insert into @Tmp values ('alzdba', 'alzdba2@dono.com', '2003-04-01 18:00:00.000',-1)
Insert into @Tmp values ('alzdba', 'alzdba3@dono.com', '2004-04-01 18:00:00.000',-1)
Insert into @Tmp values ('alzdba', 'alzdba4@dono.com', '2005-04-01 18:00:00.000',-1)
Insert into @Tmp values ('alzdba', 'alzdba5@dono.com', '2006-04-01 18:00:00.000',-1)
Insert into @Tmp values ('alzdbx', 'alzdbx@dono.com', '2001-04-01 18:00:00.000',-1)
Insert into @Tmp values ('alzdbx', 'alzdbx1@dono.com', '2002-04-01 18:00:00.000',-1)
Insert into @Tmp values ('alzdbx', 'alzdbx2@dono.com', '2003-04-01 18:00:00.000',-1)
Insert into @Tmp values ('alzdbx', 'alzdbx3@dono.com', '2004-04-01 18:00:00.000',-1)
Insert into @Tmp values ('alzdbx', 'alzdbx4@dono.com', '2005-04-01 18:00:00.000',-1)
Insert into @Tmp values ('alzdbx', 'alzdbx5@dono.com', '2006-04-01 18:00:00.000',-1)
--yeah an rbar for Jeff M
while exists(select userrank from @Tmp where userrank=-1)
begin
update @Tmp
set @userrank=userrank=@userrank+1 --deliberate double equals
where username = (select top 1 username from @Tmp where userrank=-1 order by TsRegistration asc, username asc )
select @userrank=0
end
Select EmailAddress, userrank from @Tmp
update @Tmp
set username=username+cast(userrank as varchar(11))
where userrank>1
Select username, userrank from @Tmp
Should work in 2000
September 14, 2009 at 10:35 am
Heh... no loops, please... not even in 2k. 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply