Just curious, what are your SQL pet peeves ?

Question

Post reply

Just curious, what are your SQL pet peeves ?

Viewing 2 posts - 271 through 271 (of 271 total)

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

Koen Verbeeck SSC Guru Points: 259015 More actions · Answer 1

Ed Wagner (6/23/2014)

Koen Verbeeck (6/23/2014)

Ed Wagner (6/19/2014)

Or worse - they store integer values in an nvarchar(8)! Seen it! :w00t: Hated it. :angry:

When importing data from an untyped source - a flat file for example - I usually import everything as strings into the staging table. Because you don't know what garbage might be in those columns.

After validation however, the data is converted to it's correct data type in the next stage.

Some people forget that last step 🙂

You're absolutely right - the staging tables are the exception, with nvarchar columns that are nice and wide, but not the production tables. I've seen it in production plenty of times and haven't liked any of them.

There are some string columns in the production data warehouse that contain integers, but usually those are IDs. Meaning, they are integer for the moment, but they can be changed to alphanumeric at any time. And more importantly, they are not to be used in calculations.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 2

andrew gothard (6/20/2014)

Ed Wagner (6/20/2014)

andrew gothard (6/20/2014)

Ed Wagner (6/19/2014)

andrew gothard (6/19/2014)

below86 (6/18/2014)

andrew gothard (6/18/2014)

below86 (6/16/2014)

andrew gothard (6/15/2014)

Koen Verbeeck (6/14/2014)

Badly phrased, sorry. In a column that should be a date, it's plain stupid (if you do that in your Order Taken timestamp column in your SOP system, you're an imbecile and your keyboard should be confiscated) - in a DateDim, ok-ish, but personally I'd prefer an identity. If it looks like a date, someone's going to do something stupid with it at some point.

That's what I 'm afraid someone outside of IS is going to look at that 'date ID' field and say, 'Well that's the date.' and try and do "something stupid with it".

Outside IS?

Yes, we have certain departments that have a few people that know just enough SQL to write some queries, most of the ones I've seen would be perfect examples for this post, on what not to do.:w00t: We currently don't have enough programmers(developers) to fill all the request from everyone, so some areas have found people in their departments that can run queries to answer some questions. The amount of manual work some of these people go thru is amazing. Spending days to put together reports that if we had the time we could automate and have it built in minutes. But what can you do, only so many hours in a day. 🙂

Not quite what I meant. I've seen plenty in IT departments who would work against an int value that looks like a date and do batshit crazy stuff with it. Probably starting with converting it to an nvarchar(8).

Or worse - they store integer values in an nvarchar(8)! Seen it! :w00t: Hated it. :angry:

Hows about a system where the tables all have user defined datatypes for the columns, including one called Number - which is defined as NVARCHAR(20). I $*** you not.

I mean, what kind of colossal pervert comes out with something like that?

Yeah, that's pretty bad. It takes an awfully twisted mind to come up with something like that. Granted, we're all probably a little bit twisted, but that's just sick and wrong. Honestly, I was a little surprised that you could create a type named "number" so I just had to try it. It worked, so I immediately dropped it.

I hope you had a shower afterwards.

Come to think of it, I'm going to try and seed that as "The Colossal Pervert Anti-Pattern", it might short circuit some discussions "Nah mate - you can't do that - Colossal Pervert Anti-Pattern there - Google it ... " in loud carrying voice.

Colossal Rabid Anti-Pattern provides a more apt acronym. Here's another: Works by Accident, No Knowledge Of Entity Relationships.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden