Post reply

Changing Usernames

  • September 10, 2009 at 10:08 pm

    #63821

    Comments posted to this topic are about the item Changing Usernames

  • September 11, 2009 at 3:06 am

    #1052141

    Great idea. Thanks folks.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • September 11, 2009 at 3:27 am

    #1052146

    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

    #1052155

    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

    #1052271

    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

    #1052277

    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

    #1052281

    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

    #1052299

    :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

    #1052314

    Very neat - sadly can't use RANK() in 2000 db but otherwise nice!

  • September 11, 2009 at 5:01 pm

    #1052615

    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

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • September 11, 2009 at 9:19 pm

    #1052641

    Trust a respected DB newsletter not to constrain itself...

  • September 11, 2009 at 9:23 pm

    #1052642

    Trust a respected DB newsletter not to constrain itself...

  • September 14, 2009 at 5:14 am

    #1053090

    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

    #1053109

    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

    #1053328

    Heh... no loops, please... not even in 2k. 😉

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 15 posts - 1 through 15 (of 16 total)

You must be logged in to reply to this topic. Login to reply