- Feature Articles
- CodeSOD
-
Error'd
- Most Recent Articles
- Stop Poking Me!
- Operation Erred Successfully
- A Dark Turn
- Nothing Doing
- Home By Another Way
- Coast Star
- Forsooth
- Epic
- Forums
-
Other Articles
- Random Article
- Other Series
- Alex's Soapbox
- Announcements
- Best of…
- Best of Email
- Best of the Sidebar
- Bring Your Own Code
- Coded Smorgasbord
- Mandatory Fun Day
- Off Topic
- Representative Line
- News Roundup
- Editor's Soapbox
- Software on the Rocks
- Souvenir Potpourri
- Sponsor Post
- Tales from the Interview
- The Daily WTF: Live
- Virtudyne
Admin
Bawww, people in other professions can't do my profession as well as I can.
Admin
Even with the fix, 2 users can end up with the same ID. Dear Dev: Listen to the wisdom of Marle.
Admin
SCOPE_IDENTITY?
Well, I may just be a developer, but after reading the documentation of SCOPE_IDENTITY I'd have used an "INSERT ... OUTPUT" statement to get the last inserted ID.
Or are triggers forbidden in their databases by The Rule Of Merle?
Admin
Just realised UserLoginName is actually an integer id column. <bleurgh>
Admin
silly DBAs. living in past, thinking that data alone is what's important.
Admin
I thought that was the TWTF myself.
Admin
Being incompetent has nothing to do with them being developers. By now I think it's part of the human condition.
Can't help but hear Steve Ballmer every time someone talks about developers! developers! developers!
Admin
Looks like a bit of a cnt to me.
Admin
Seems like the code is an attempt to return the next available ID, rather than the one just inserted. Good advice on making the column the identity and looking at SCOPE_IDENTITY, but that would return a different answer than the code presented (or not, depending on concurrent operations).
Admin
umm...no. SCOPE_IDENTITY is trigger-safe, as it returns the last identity value generated in the scope. triggers execute in a different scope.
Admin
+1 internets
Admin
Why not use an output on the insert?
Admin
I think the number of poorly trained people who drift into becoming sysadmins and DBAs generally outweighs the number of poorly trained people who drift into becoming developers.
The difference however, is that the well trained developers can usually do the job of a DBA, whereas well trained DBAs wouldn't know the first thing about development.
Admin
Well, yes, the results would be different- identity columns would work consistently, whereas this approach has a non-zero chance of running into a race condition and attempting to reuse the same identity.
Admin
I really wonder why
I also do wonder why people like Marle always seem to think of themselves as a "know-it-all".
Admin
Try as I might, the reason for the "while" loop in the stored procedure escapes me. Apart from the fact that it can result in an endless loop if UserLoginName is not unique, why not simply do a "set @tempid = (select max(UserLoginName) from user_table;) + 1"?
And why the advice "use SCOPE_IDENTITY to get the id for the inserted record"? If I understand the code correctly, it returns the id of the last inserted record + 1, so (presumably) the id of the next record to insert?
Or am I completely misunderstanding TransactSQL?
Admin
Admin
Admin
Who says a 2-ton boulder can't be aerodynamic?
Admin
dev's lazy which is a good thing. He's refactoring someone else's work as little as possible to get back to their job.
Why are all the smart people in these articles women and all the dumb people men?
Admin
But, as I said, the stored procedure returns the lowest not yet assigned id from UserLoginName, i.e. max(UserLoginName) + 1, and not the last inserted id.
SCOPE_IDENTITY can only be used after an insert and only if used in the same batch as that insert.
What if the devleoper approached adding a new entry like:
After all, the UserLoginName seems to be a normal integer column, so whoever created the table's schema was new to T-SQL or simply didn't care.
Admin
Um... Why leave the previous code still commented in?
Admin
Admin
Most DBAs couldn't code their way out of a wet paper bag. Most DBAs I've worked with are arrogant, stupid, and a fucking pain to deal with.
Admin
That's because it was written by a fucking DBA with the brain capacity of a toadstool.
Admin
TRWTF is people who use the word "performant".
Admin
From experienced <Role> to someone who has less experience in <Role>:
Use <Feature> which you probably did not know about.
This is not a WTF -- it is just the proper role of someone with more experience.
I do it all the time to my junior developers. As in: instead of writing this loop, use Linq. Or, instead of using reflection, use proper inheritance. Etc.
This could have easily been a story about a golf pro showing a kid how to swing a club properly.
Admin
Admin
DINGDINGDING
If it ain't broke, don't fix it.
If it is broke, do as little as possible to avoid breaking everything else.
Admin
Admin
Admin
Our project: using Oracle DB using extended Latin encoding. Our problem: no Unicode support, users complaining. Our solution: change encoding to UTF-8 DBA's position: UTF-8 is forbidden, you may only change to WE8DEC (which is even worse than extended Latin).
He also refuses to give performance advisory or anything if queries are not supplied using Oracle's (+) syntax. ANSI SQL is totally off limits, because "that's how we always did it".
Out stance? EFF YOU! We use ANSI SQL, and he's forced to do his frickin' job, whether he likes the syntax or not. And we got him to migrate us to UTF-8 too, against his will.
Feels good.
Admin
The best thing to do here is commit the developer's fix (since it's better than the existing code even though it doesn't fix all the problems) and file a new bug to replace the whole thing with the correct solution. Then that bug can be prioritized based on how bad the rest of the problems are and how much other work is competing for attention.
Admin
Admin
The only thing in the code posted that even suggests that is the declaration of @tempid as an int and @tempid being set to the max value of UserLoginName. The column itself could be another data type entirely, like a numeric, that the database engine provides an implicit conversion for.
Admin
Yep, developers can't make good DBAs, and DBAs can't make good developers.
And they both suck at each other's domain.
I mean, neither thought to use GUIDs, and avoid the calculations / identity scope issue altogether.
Admin
For example, UserLoginName "ArthurPhilipDent" would logically be followed by "ArthUrpHilIpdenu". Only a complete jerk, a real kneebiter, would f**k this up.
Admin
Yes, you are.
It takes an IQ of roughly 70 to understand that MAX+1 is bad. I've seen someone fired for using it.
Admin
The new change still includes one of the most basic anti-patterns than any dev who has even heard of a database should know about.
Admin
You showed that arrogant SOB who's boss!
Admin
When you're immortal, you live long enough to see everything.
Admin
as long as the table was not acutally clustered on a GUID, as this can result in severe degradation of performance caused by excessive page splitting.
Admin
Admin
You don't think they'd make a good UserLoginName? :)
Admin
Indeed. I wonder what kind of user would want to use a GUID as their login name...
Admin
My name is actually a GUID, you just can't tell because I spell it in Canadian.
Admin
GUIDs are nice when merging data between databases. Identity columns tend to cause key collisions.
Admin
User names aren't keys. Nor are US SS#s.
Admin
They can possibly mitigate a security hole arising from a guessable index. Mind, if you've got such a security hole, it means your code is broken, not that you're using the wrong index.
Or if you just don't want your user knowing he's one of the first 10 people to register for your application ... But then, this use case can be solved by seeding the index with ARBITRARILY_LARGE_INT.
Admin
Then use GUIDs as a temporary supplemental identifier for a temporary solution. There's no need to have a GUID column just in case you need to merge data. Use an identity column, and if you need to merge the data, use a GUID, merge it, then remove the GUID. Unless you're merging (or something else that may require a GUID), it's not the right type of column.