Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««2425262728

Just curious, what are your SQL pet peeves ? Expand / Collapse
Author
Message
Posted Monday, June 23, 2014 5:16 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 10:46 AM
Points: 13,622, Visits: 10,513
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! Hated it.


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.




How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1585055
Posted Monday, June 23, 2014 5:18 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 9:46 AM
Points: 7,224, Visits: 13,696
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)
[quote]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. 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! Hated it.


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
Exploring Recursive CTEs by Example Dwain Camps
Post #1585057
« Prev Topic | Next Topic »

Add to briefcase «««2425262728

Permissions Expand / Collapse