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 Friday, June 20, 2014 5:59 AM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 1:09 PM
Points: 4,231, Visits: 3,665
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.



Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions
Post #1584312
Posted Friday, June 20, 2014 7:56 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 10:17 PM
Points: 17,815, Visits: 15,744
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?


That sounds just like a vendor app that I supported.

Another vendor created a "Number" data type and had it defined as UniqueIdentifier. That one ticked me off a bit.




Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server, MVP


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Post #1584373
Posted Friday, June 20, 2014 7:59 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Today @ 3:24 PM
Points: 358, Visits: 2,513
SQLRNNR (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?


That sounds just like a vendor app that I supported.

Another vendor created a "Number" data type and had it defined as UniqueIdentifier. That one ticked me off a bit.


Yeah, thats a tough situation, especially if you use it as a "quantity" column in a sales app, it'll kill your company's repeat business.
Post #1584378
Posted Friday, June 20, 2014 8:06 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 2:23 AM
Points: 217, Visits: 869
Ed Wagner (6/20/2014)

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.


At least they didn't try to call it "numeric"
Post #1584381
Posted Friday, June 20, 2014 2:31 PM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Today @ 4:44 AM
Points: 304, Visits: 3,421
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.


I'm a DBA.
I'm not paid to solve problems. I'm paid to prevent them.
Post #1584503
Posted Friday, June 20, 2014 2:33 PM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Today @ 4:44 AM
Points: 304, Visits: 3,421
SQLRNNR (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?


That sounds just like a vendor app that I supported.

Another vendor created a "Number" data type and had it defined as UniqueIdentifier. That one ticked me off a bit.


AAARGGGHHHHHH - my EYES!!!!!


I'm a DBA.
I'm not paid to solve problems. I'm paid to prevent them.
Post #1584504
Posted Friday, June 20, 2014 2:38 PM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Today @ 4:44 AM
Points: 304, Visits: 3,421
crmitchell (6/20/2014)
Ed Wagner (6/20/2014)

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.


At least they didn't try to call it "numeric"


Traumatic, it's a traumatic datatype


I'm a DBA.
I'm not paid to solve problems. I'm paid to prevent them.
Post #1584505
Posted Monday, June 23, 2014 12:44 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 1:28 PM
Points: 13,263, Visits: 11,053
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




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 #1584916
Posted Monday, June 23, 2014 2:32 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 1:53 PM
Points: 35,366, Visits: 31,905
Koen Verbeeck (6/23/2014)
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


+ 10 Gazillion!

The other little trick that I'll do is make the staging table look exactly like the final table and allow the BCP format file to bring in "too wide" data as well as turning the error file options on to sequester bad rows without blowing up the whole batch in the file. Saves on a whole lot of validation code (although there will always be validations to do in the staging table).


--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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1584950
Posted Monday, June 23, 2014 5:11 AM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 1:09 PM
Points: 4,231, Visits: 3,665
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.



Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions
Post #1585053
« Prev Topic | Next Topic »

Add to briefcase «««2425262728»»

Permissions Expand / Collapse