User table


  • :belt_onion:

    I just came across the definition of the user table for the legacy application I'm currently working on:

    CREATE TABLE [dbo].[t_sec_users](
    	[user_id] [dbo].[d_id] IDENTITY(1,1) NOT NULL,
    	[user_name] [dbo].[d_user] NOT NULL,
    	[user_name_ucase] [dbo].[d_user] NOT NULL,
    	[last_modified] [datetime] NOT NULL CONSTRAINT [df1_sec_users]  DEFAULT (getdate()),
    	[modified_by] [dbo].[d_user] NOT NULL CONSTRAINT [df2_sec_users]  DEFAULT (suser_sname()),
    ) ON [PRIMARY]
    
    The values in columns user_name and user_name_ucase are identical (fortunately), except for the case sometime. 


  •  So if you want lower case, you can just select lcase (user_name_ucase) from [dbo].[t_sec_users] where user_id > 200. AWESOME!



  • you are right, the square brackets are too much visual clutter and confusing for people used to programming languages with arrays. i much prefer backticks for identifier quoting.

    lower case columns can be useful if you want to index the columns and don't know how to set case insensitive collation orders. if the index doesn't fit your lookup the db is forced to do a table scan. maybe they have a staged lookup, first searching case sensitive and then case insensitive, if nothing was found?


  • :belt_onion:

    @strcmp said:

    maybe they have a staged lookup, first searching case sensitive and then case insensitive, if nothing was found?
     

    The look-up is done in a stored procedure:

    -- lookup user_id and if user_id is null then return error
    SELECT @user_id = user_id
    FROM t_sec_users
    WHERE user_name_ucase = UPPER(@user_name)

    IF @user_id IS NULL
    BEGIN
    RAISERROR (100017, -1, -1, @proc_name, @user_name)
    RETURN (1)
    END

    The @user_name is a parameter, defined as type [d_user] which is the same type ( varchar(256) ) as both user_name columns in the table. The values in column user_name are mixed case. Probably saved the way they were entered by the application's administrator.



  • Would it be possible for people to actually explain what is wrong with SQL examples when posting?  I have the supreme joy and luck not to have to deal with that blather, so I have absolutely no idea what the issue might be, or why it might be considered a WTF. 


  • :belt_onion:

    @mrprogguy said:

    Would it be possible for people to actually explain what is wrong with SQL examples when posting? ...

     

    I did write: "The values in columns user_name and user_name_ucase are identical (fortunately), except for the case sometime".The application is storing the exact same data in two separate columns. One of the columns ("user_name_ucase") promises that the data is in upper case, but it would be easier to "SELECT UPPER(user_name) FROM t_sec_users"



  • @bjolling said:

    -- lookup user_id and if user_id is null then return error
    SELECT @user_id = user_id
    FROM t_sec_users
    WHERE user_name_ucase = UPPER(@user_name)

    The @user_name is a parameter, defined as type [d_user] which is the same type ( varchar(256) ) as both user_name columns in the table. The values in column user_name are mixed case. Probably saved the way they were entered by the application's administrator.

    Ok, so this is for indexing purposes, and for being able to output the user_name "John McSmith" instead of "JOHN MCSMITH". Usually databases are capable of doing this themselves via declaring collation order or with functional indexes, but this may be older code for a version of the database that didn't have this feature? Or the developer didn't know this 'magic' feature? Or wanted to make it explicit to get it readable and maintainable for his successors?

    @bjolling said:

    IF @user_id IS NULL
    BEGIN
    RAISERROR (100017, -1, -1, @proc_name, @user_name)
    RETURN (1)
    END

    The real WTF is not simply returning NULL and putting the policy into the application. How can you use this procedure to just see if a user exists? If you have to do it in SQL, then why the procedure?


  • :belt_onion:

    @strcmp said:

    Ok, so this _is_ for indexing purposes, and for being able to output the user_name "John McSmith" instead of "JOHN MCSMITH". Usually databases are capable of doing this themselves via declaring collation order or with functional indexes, but this may be older code for a version of the database that didn't have this feature? Or the developer didn't know this 'magic' feature? Or wanted to make it explicit to get it readable and maintainable for his successors?
     

    I think I saw a comment from 1998 in one of the stored procedure somewhere, so this could have been designed for SQL Server 6.5 or SQL Server 7.0 (?) I checked the indexes and there 3 of them. A unique clustered index on [user_id], a unique non-clustered index on [user_name] and another unique non-clustered index on [user_name_ucase].

    And I just found this object:

    EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Name of the user using only capitals. This column is automatically maintained and used by processes in order to get reliability and good performance on case-sensitive SQL Server instances.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N't_sec_users', @level2type=N'COLUMN',@level2name=N'user_name_ucase'


     Thanks for sharing your insights with me



  • I can't be 100% sure of this because I don't think I've ever used it in a production app, but that looks a lot like the autogenerated ASP.NET membership table.  As you seem to have noticed later on, it does this because authentication obviously has to be case-insensitive, and when doing this on a large scale, it's far cheaper to do the case conversion once on creation than it is to do every time on authentication. or even to perform a case-insensitive comparison every time.

    I'm not sure how it deals with localization issues, but it's typically not a big problem since it's going to be the same block of code doing the initial conversion and the per-login conversion.  As long as uniqueness is enforced on the ucase column, or the conversion code can guarantee no weird collisions, it's perfectly fine.  Standard denormalization-for-better-performance trick.


Log in to reply