December 25, 2015 at 2:16 pm
The one I came across a couple days ago...
WHERE LEN(t.SSN) = 9
as opposed to
WHERE t.SSN LIKE [0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]
December 25, 2015 at 2:22 pm
GilaMonster (12/25/2015)
I see these sooo many times: https://www.simple-talk.com/content/article.aspx?article=2280, plus scalar UDFs, multi-statement table-valued UDFs, nested views, non-SARGable predicates
Gail - That's a HUGE one where I work! Our code is littered with control flow blocks.
I think that pins down my list...
UDFs... iTVF vs scalar & mTVF
SARGable perdicates
and Control Flow
December 25, 2015 at 2:35 pm
Jason A. Long (12/25/2015)
TheSQLGuru (12/25/2015)
How about I do a remote presentation of my Common TSQL Mistakes session? I have presented it somewhere around 100 times now for companies, User Groups, SQL Saturdays and other conferences. It is a AMAZING collection of magic bullets and bad-data causers, with lots of perf tuning hints thrown in along the way. Drop me a PM if you are interested. Depending on where you are I could even pop in for an onsite presentation of that and other sessions if your boss is interested. I love to travel and pick up Delta Qualification Miles! 🙂I'll talk to the boss man and see if he's willing to part with some of the training budget. If he's interested, I'l touch base and find out what you'd charge.
If you want remote I will do that for free. I just LOVE giving that session!! I once gave it to the dev staff at a major bank and started with over 300 people on a webex. 2.5 hours in (on a 75 minute schedule) there were still over 200 people online firing questions at me. 🙂
If you want to pay me or bring me up that would of course be awesome too! 😎
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
December 25, 2015 at 3:40 pm
Jason A. Long (12/25/2015)
The one I came across a couple days ago...
WHERE LEN(t.SSN) = 9
as opposed to
WHERE t.SSN LIKE [0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]
The LEN check doesn't do it alone. It could allow any characters where the LIKE code checks for length and digits only. BUT, for me, there's a much MUCH bigger implied problem there... SSN's in clear text.
--Jeff Moden
Change is inevitable... Change for the better is not.
December 25, 2015 at 7:14 pm
TheSQLGuru (12/25/2015)
Jason A. Long (12/25/2015)
TheSQLGuru (12/25/2015)
How about I do a remote presentation of my Common TSQL Mistakes session? I have presented it somewhere around 100 times now for companies, User Groups, SQL Saturdays and other conferences. It is a AMAZING collection of magic bullets and bad-data causers, with lots of perf tuning hints thrown in along the way. Drop me a PM if you are interested. Depending on where you are I could even pop in for an onsite presentation of that and other sessions if your boss is interested. I love to travel and pick up Delta Qualification Miles! 🙂I'll talk to the boss man and see if he's willing to part with some of the training budget. If he's interested, I'l touch base and find out what you'd charge.
If you want remote I will do that for free. I just LOVE giving that session!! I once gave it to the dev staff at a major bank and started with over 300 people on a webex. 2.5 hours in (on a 75 minute schedule) there were still over 200 people online firing questions at me. 🙂
If you want to pay me or bring me up that would of course be awesome too! 😎
That's a remarkably generous offer ant it certainly makes for easier sell... That said, I'd want to make sure you're compensated for your time and expertise. I'll what I can find out next week. I'll keep you posted.
December 25, 2015 at 7:17 pm
Jeff Moden (12/25/2015)
Jason A. Long (12/25/2015)
The one I came across a couple days ago...
WHERE LEN(t.SSN) = 9
as opposed to
WHERE t.SSN LIKE [0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]
The LEN check doesn't do it alone. It could allow any characters where the LIKE code checks for length and digits only. BUT, for me, there's a much MUCH bigger implied problem there... SSN's in clear text.
There are a lot of things that I love about the company I work for... But... There are still a things that make me repeat the alcoholics... prayer
December 25, 2015 at 8:44 pm
Implicit conversion due to data type precedence, forcing a scan (AKA Make sure data types match). Avoidable uses of temp tables, when a CTE or a derived table is equivalent and would instead expose all the stats (in addition to avoiding a round trip to tempdb). Failure to SET XACT_ABORT ON, leading to client loosing track of its transaction nesting level.
December 26, 2015 at 1:59 am
100% agreeing with Gail on this one.
😎
Piling on a bit
Most wanted (preferably dead) sql code objects and anti patterns:
1. Multi statement multi function table value functions
These functions tend to fetch latest/earliest dates or highest/lowest values etc., the combination of these two performance killing patterns are some of the worst performing code blunders possible.
2. Multi statement table value function
As 99.999% of these can be written as inline table value functions there is no justification for they're existence. The added execution overhead, IO and tempdb pressure can easily kill the healthiest of servers.
3. Multi function code modules
Typically found in EAV type schemas these modules, based on the parameters passed, search on different columns and or bring back different columns.
Other serious offenders:
4. Scalar functions.
5. Blanket DTA and Missing Index Suggestions application.
6. Nested Loop procedure execution.
7. Local variables as query predicates.
8. Multi use columns and other EAV type patterns.
9. Dynamic schemas.
10. GUID as clustered index.
December 26, 2015 at 9:39 am
Jason A. Long (12/25/2015)
Jeff Moden (12/25/2015)
Jason A. Long (12/25/2015)
The one I came across a couple days ago...
WHERE LEN(t.SSN) = 9
as opposed to
WHERE t.SSN LIKE [0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]
The LEN check doesn't do it alone. It could allow any characters where the LIKE code checks for length and digits only. BUT, for me, there's a much MUCH bigger implied problem there... SSN's in clear text.
There are a lot of things that I love about the company I work for... But... There are still a things that make me repeat the alcoholics... prayer
In the recent past, I did some side work (performance improvements for code and maintenance of indexes) where I found that they used SSN as the PK and about 1/3rd of their tables contained clear text SSNs. The Dev Manager brought it up to the compliance officer. She said that the Social Security Administration has no definitive rules about whether or not SSNs could be in clear text (she's right but they do suggest protecting it) and that "the systems" were adequately protected (not at all from internal thieves). I suggested that if she was so confident that she should enter her PII and SSN into the system as a sign of good faith and confidence. Of course, I got crickets for that suggestion.
I don't use the word often but I don't understand how people can be so utterly stupid about such things (not you... the people that originally did it and still allow it). If it were a company that I worked for and I actually liked working there, I'd be on them like white on rice to fix the problem. This is one of the big reasons why I refuse to do work for most hospitals and medical companies. Unprotected us of SSNs along with substantial PII is flagrant and the absolutely refuse to do anything about it. "It would cost too much". Heh... yeah... just wait until you're breached or some pissed off employee sells your goodies and every person who had data in your system sues your pants off. How much will THAT cost not to mention that no one in their right mind will ever use your services again.
--Jeff Moden
Change is inevitable... Change for the better is not.
December 26, 2015 at 12:32 pm
Jason A. Long (12/25/2015)
Jeff Moden (12/25/2015)
Jason A. Long (12/25/2015)
The one I came across a couple days ago...
WHERE LEN(t.SSN) = 9
as opposed to
WHERE t.SSN LIKE [0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]
The LEN check doesn't do it alone. It could allow any characters where the LIKE code checks for length and digits only. BUT, for me, there's a much MUCH bigger implied problem there... SSN's in clear text.
There are a lot of things that I love about the company I work for... But... There are still a things that make me repeat the alcoholics... prayer
Remember - the first step is admitting that you have a problem. Once that's done, it can be addressed.
December 26, 2015 at 12:36 pm
Jeff Moden (12/26/2015)
In the recent past, I did some side work (performance improvements for code and maintenance of indexes) where I found that they used SSN as the PK and about 1/3rd of their tables contained clear text SSNs. The Dev Manager brought it up to the compliance officer. She said that the Social Security Administration has no definitive rules about whether or not SSNs could be in clear text (she's right but they do suggest protecting it) and that "the systems" were adequately protected (not at all from internal thieves). I suggested that if she was so confident that she should enter her PII and SSN into the system as a sign of good faith and confidence. Of course, I got crickets for that suggestion.I don't use the word often but I don't understand how people can be so utterly stupid about such things (not you... the people that originally did it and still allow it). If it were a company that I worked for and I actually liked working there, I'd be on them like white on rice to fix the problem. This is one of the big reasons why I refuse to do work for most hospitals and medical companies. Unprotected us of SSNs along with substantial PII is flagrant and the absolutely refuse to do anything about it. "It would cost too much". Heh... yeah... just wait until you're breached or some pissed off employee sells your goodies and every person who had data in your system sues your pants off. How much will THAT cost not to mention that no one in their right mind will ever use your services again.
In the medical profession, there's also the HIPPA requirements to consider. I thought that credit monitoring was uber-expensive. Considering how many rows we have in tables, $20 per person per year for 2 years doesn't sound like much, but it sure multiplies quickly. HIPPA makes it much more expensive.
The plain SSNs is a pretty common sense one. If they don't do that and they have sa enabled, the only thing that's left is hearing about themselves on the evening news. They'll have to work hard to remind themselves of the justification that fixing it was too expensive.
December 26, 2015 at 12:55 pm
I'm sure it's on the radar of the SOC officers and security compliance personnel... I don't claim to be overly knowledgeable regarding the specific laws, rules & regs, so I leave it to those who are paid to be knowledgeable and create the appropriate policies...
I do know, however, that a masking demographic data would have a fairly profound impact on our EDI processes, where we're trying to create matches between existing data and incoming EDI data.
December 27, 2015 at 10:54 am
I have spent a large portion of my 20 years consulting on SQL Server doing tuning work in some fashion or another. The single worst thing I see in aggregate is mismatched data types. This is because it is SOOO easy to do and SOOO bad (in several ways). Some frameworks do this BY DEFAULT!! And yet it is SOOO easy to NOT DO simply because the schema is KNOWN as you are writing the code.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
December 28, 2015 at 7:26 am
I have two presentations I do on best practices. Both slide decks are available on slideshare.net. That could act as a guide. Just give me some attribution if you use 'em.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
December 28, 2015 at 7:44 am
Grant Fritchey (12/28/2015)
I have two presentations I do on best practices. Both slide decks are available on slideshare.net. That could act as a guide. Just give me some attribution if you use 'em.
Very cool of you to make such an offer, Grant.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 15 posts - 16 through 30 (of 73 total)
You must be logged in to reply to this topic. Login to reply