Code smells versus transgressions

Question

Code smells versus transgressions

Viewing 15 posts - 16 through 30 (of 44 total)

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

ZZartin SSC-Dedicated Points: 30894 More actions · Answer 1

GPO (4/4/2016)
On the other hand if SQL is the system of record they do not and are a very handy tool to use.
We're talking about two different kinds of unique. If your application generates a transaction that creates an order which is given a unique incrementing integer (say 1636462) and a millisecond later the app sends the same thing through again in error (causing SQL Server to generate a new unique incrementing integer, say 1636463) then from a business perspective a duplicate order has been generated. The only sense in which they are unique, is that the incrementing integer has CAUSED the uniqueness. The table hasn't enforced uniqueness.

If an application is that badly coded there's little you can do on the database side to fix it. At that point how do you know that whatever process the application is using to generate a unique order isn't also generating two ID's for that order?

But now we're talking about using random keys as unique identifiers regardless of where they're from or what they are.

GPO SSCarpal Tunnel Points: 4574 More actions · Answer 2

If an application is that badly coded there's little you can do on the database side to fix it.

I would have thought that's one reason why you put constraints in the database; to save you from badly designed applications.:-D Here's some light reading:

http://sqlblog.com/blogs/aaron_bertrand/archive/2010/02/08/bad-habits-to-kick-putting-an-identity-column-on-every-table.aspx

https://www.simple-talk.com/sql/t-sql-programming/declarative-sql-using-unique-constraints-/

...One of the symptoms of an approaching nervous breakdown is the belief that ones work is terribly important.... Bertrand Russell

ZZartin SSC-Dedicated Points: 30894 More actions · Answer 3

GPO (4/4/2016)
If an application is that badly coded there's little you can do on the database side to fix it.
I would have thought that's one reason why you put constraints in the database; to save you from badly designed applications.:-D Here's some light reading:
http://sqlblog.com/blogs/aaron_bertrand/archive/2010/02/08/bad-habits-to-kick-putting-an-identity-column-on-every-table.aspx
https://www.simple-talk.com/sql/t-sql-programming/declarative-sql-using-unique-constraints-/

You gave an example where one user request would generate multiple requests erroneously at the application level, there is no level of database constraints that can protect against that. The entire ecosystem has to work together properly not just the database design, you can put as many locks on the door as you want but if any key can open them it doesn't matter.

And like I said there's nothing inherently wrong with an identity, as long as it's not arbitrarily used to force uniqueness.

GPO SSCarpal Tunnel Points: 4574 More actions · Answer 4

as long as it's not arbitrarily used to force uniqueness

Hahaha! That's awesome!

...One of the symptoms of an approaching nervous breakdown is the belief that ones work is terribly important.... Bertrand Russell

HighPlainsDBA SSCrazy Points: 2611 More actions · Answer 5

This might be more than what you're asking for but.. I once had a programming 'whiz' who was new to SQL hand me this complex looking T-SQL (that had something to do with reading barcodes as I recall) and ask me if I could 'optimize' it. I took a look see at it and the conversation went something like this: 'Where are the tables?'

'There are none.'

'Then why are you using SQL Server.'

'Cuz it's fast isn't it?'

'That depends.'

It was clever in an academic sense I guess. What would you call that? 'Code stink'? Wrong type of language.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 6

ccd3000 (4/5/2016)
'Code stink'? Wrong type of language.

"Cannot run out time. There is infinite time. You are finite. Zathras is finite. This is wrong tool!"

http://www.imdb.com/title/tt0517722/quotes?item=qt0422044

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Eric M Russell SSC Guru Points: 125519 More actions · Answer 7

So here comes the question: if you were do decide on SQL practices that were always wrong, what would you include?

The following practices are almost always wrong.

1. A table without a primary key.

2. VarChar column containing multi-valued string values.

3. VarChar or Char column containing integer or date/time values.

4. Multiple indexes covering an identical column set in the same order.

5. Joining multiple remote tables using 4 part naming convention.

6. Adding ORDER BY clause to the SELECT statement of a view.

"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

Yet Another DBA SSCarpal Tunnel Points: 4299 More actions · Answer 8

GilaMonster (4/5/2016)
ccd3000 (4/5/2016)
'Code stink'? Wrong type of language.
"Cannot run out time. There is infinite time. You are finite. Zathras is finite. This is wrong tool!"
http://www.imdb.com/title/tt0517722/quotes?item=qt0422044

Wow, that takes me back a few decades!!

Yet Another DBA SSCarpal Tunnel Points: 4299 More actions · Answer 9

Jeff Moden (4/4/2016)
GPO (4/2/2016)
:exclamationmark: Storing data in mutli-valued, delimited fields because... Jeez do I have to write another CREATE TABLE statement? Sorry, multivalues are not relational. That's what Pick databases are for.
It's a funny thing that so many people agree that's absolutely the wrong thing to do in a database but will then allow storage of XML, JSON, or some other non-relational atrocity to settle in because "every does it".

It depends doesn't it.

I worked on a database dealing with contracts for royalties where the owners address

was a varchar(1000). Puzzled me to start with until the BA explained the requirement.

How many lines do you specify?

Phil Factor SSC-Insane Points: 20244 More actions · Answer 10

@eric

Thanks Eric,

Some great ones there. I agree that those are all Table smells.

Best wishes,
Phil Factor

Jeff Moden SSC Guru Points: 1003853 More actions · Answer 11

Yet Another DBA (4/7/2016)
Jeff Moden (4/4/2016)
GPO (4/2/2016)
:exclamationmark: Storing data in mutli-valued, delimited fields because... Jeez do I have to write another CREATE TABLE statement? Sorry, multivalues are not relational. That's what Pick databases are for.
It's a funny thing that so many people agree that's absolutely the wrong thing to do in a database but will then allow storage of XML, JSON, or some other non-relational atrocity to settle in because "every does it".
[font="Arial Black"]It depends doesn't it. [/font]
I worked on a database dealing with contracts for royalties where the owners address
was a varchar(1000). Puzzled me to start with until the BA explained the requirement.
How many lines do you specify?

I'll probably get a lot of flack from someone on this but, for me, no... it does not depend. Storing XML in a table has all of the problems that storing any blob has and has the serious problem of not being normalized data.

--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)

Steve Jones - SSC Editor SSC Guru Points: 734418 More actions · Answer 12

I'll disagree with Jeff. Depending on how often and how data is addressed, I think it's fine to keep blob data around. I might normalize pieces of it, but there can be complexities that aren't worth addressing, like an address, which isn't often a query field.

ZZartin SSC-Dedicated Points: 30894 More actions · Answer 13

I don't see the problem so much as storing blob data regardless of what it is but when people want to try to parse it in SQL Server in bulk.....

Jeff Moden SSC Guru Points: 1003853 More actions · Answer 14

Steve Jones - SSC Editor (4/8/2016)
I'll disagree with Jeff. Depending on how often and how data is addressed, I think it's fine to keep blob data around. I might normalize pieces of it, but there can be complexities that aren't worth addressing, like an address, which isn't often a query field.

Even with it's problems, it's not "BLOB" data that I'm opposed to storing. It's XML data that I'm opposed to storing.