Post reply

Just curious, what are your SQL pet peeves ?

  • June 19, 2014 at 8:28 am

    #1723281

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

    I'm a DBA.
    I'm not paid to solve problems. I'm paid to prevent them.

  • June 19, 2014 at 9:10 am

    #1723304

    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:

    Tally Tables - Performance Personified
    String Splitting with True Performance
    Best practices on how to ask questions

  • June 19, 2014 at 4:32 pm

    #1723439

    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:

    Heh... I'm dealing with little piece of design heaven even as we speak...

    --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)
    Intro to Tally Tables and Functions

  • June 20, 2014 at 5:22 am

    #1723539

    Jeff Moden (6/19/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:

    Heh... I'm dealing with little piece of design heaven even as we speak...

    Yeah, me too. Well, I'm helping others deal with it, anyway. It's this kind of basic design stuff that drives me truly nuts.

    Tally Tables - Performance Personified
    String Splitting with True Performance
    Best practices on how to ask questions

  • June 20, 2014 at 5:28 am

    #1723540

    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?

    I'm a DBA.
    I'm not paid to solve problems. I'm paid to prevent them.

  • June 20, 2014 at 5:59 am

    #1723551

    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.

    Tally Tables - Performance Personified
    String Splitting with True Performance
    Best practices on how to ask questions

  • June 20, 2014 at 7:56 am

    #1723604

    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?

    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[/url]
    Learn Extended Events

  • June 20, 2014 at 7:59 am

    #1723609

    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)

    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?

    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.

  • June 20, 2014 at 8:06 am

    #1723612

    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"

  • June 20, 2014 at 2:31 pm

    #1723711

    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.

    I'm a DBA.
    I'm not paid to solve problems. I'm paid to prevent them.

  • June 20, 2014 at 2:33 pm

    #1723712

    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)

    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?

    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.

  • June 20, 2014 at 2:38 pm

    #1723713

    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.

  • June 23, 2014 at 12:44 am

    #1723898

    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 🙂

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

  • June 23, 2014 at 2:32 am

    #1723925

    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.
    "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)
    Intro to Tally Tables and Functions

  • June 23, 2014 at 5:11 am

    #1724009

    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.

    Tally Tables - Performance Personified
    String Splitting with True Performance
    Best practices on how to ask questions

Viewing 15 posts - 256 through 270 (of 271 total)

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