March 23, 2015 at 12:03 am
Comments posted to this topic are about the item SQL Cop and SQLServerCentral
March 23, 2015 at 1:37 am
Many Thanks Steve.
March 23, 2015 at 7:13 am
Steve,
Thanks for the post. As the author of SQLCop, it's very informative for me to know which tests you find interesting and which ones you don't.
When running SLQCop, it's important to realize that not all issues detected by SQLCop require fixing. Many of the items detected have valid reasons for being there. I am currently working on an upgrade to SQLCop that allows you to easily ignore certain items.
George Mastros
Orbit Software, Inc.
March 23, 2015 at 7:17 am
I would prefer to see most of the same tests in Dev databases as in production, because that would help educate developers (most of whom are not trained to be DBAs) from creating bad database design. Plus, I prefer to see dev & test databases to be on pretty much the same server infrastructure and database design as production so they can spot performance problems in their code before it gets to production. Many times for new tables or other DDL changes to be ported to production, there may or may not be a code review and most DBAs don't have time to go through DDL they have been handed as being "tested" and correct and rewrite DDL code (although they should).
To me, this is part of a good development environment -- that includes code reviews, and preferably peer code reviews so that everyone learns "best practices". Plus, peer code reviews are the perfect place to teach and reinforce coding standards. (There's one important rule for leading peer code reviews: Everyone should be told to avoid using the words "you" and "your" in their comments; just "refer to the code". This avoids hurting egos and triggering ego defense mechanisms. The goal is "egoless programming" and what Ed Yourdon calls a "world class software development organization". (Pardon my referring to SQL coding as "programming". Hope I didn't hurt anyone's ego.)
March 23, 2015 at 3:17 pm
What is meant by an undocumented procedure? Is it an undocumented MS procedure?
If it can include user defined procedures, then what constitutes a documented procedure?
March 23, 2015 at 4:16 pm
An undocumented procedure is a procedure that is distributed along with the database engine, but is undocumented. Since it is undocumented, there is no guarantee that Microsoft will continue to support the procedure in the future. It's also possible that Microsoft could change the parameters or somehow make the procedure unusable in the way that it had previously been used.
For documented procedures, Microsoft may deprecate them, but they will do it slowly over many subsequent versions. For the most part, Microsoft guarantees their documented procedures but not their undocumented procedures.
George Mastros
Orbit Software, Inc.
March 24, 2015 at 12:00 pm
gmmastros (3/23/2015)
An undocumented procedure is a procedure that is distributed along with the database engine, but is undocumented. Since it is undocumented, there is no guarantee that Microsoft will continue to support the procedure in the future. It's also possible that Microsoft could change the parameters or somehow make the procedure unusable in the way that it had previously been used.For documented procedures, Microsoft may deprecate them, but they will do it slowly over many subsequent versions. For the most part, Microsoft guarantees their documented procedures but not their undocumented procedures.
Thanks for the response. I was guessing you meant undocumented Microsoft procedures, but I wasn't sure.
March 24, 2015 at 12:58 pm
Here are a couple of things to consider.
When SQL2005 introduced VarChar(max) I encouraged Devs to adopt the new data type immediately and most did. Eventually, we decided to script the column data type change from Text to Varchar(max). We applied the change and about 100 or so columns were changed in a few minutes.
Unfortunately, it turns out that BCP looked at Varchar(max) and Text differently. A couple of weeks later we found that literally dozens of routines had to be reworked to make BCP treat the new data correctly.
I disagree with the prohibition against Float and note that if you decide you must get rid of Float you will also have to handle Real. IMHO, Float/Real are powerful tools that a competent computer scientist must understand and know how/when to use. Like GUIDs, Cursors, and triggers; Float is not inherently bad, but it is often misused, abused, and misunderstood. In fact, IMHO both of the URLs you provide demonstrate that lack of understanding. For example "Decimal/Numeric is Fixed-Precision data type, which means that all the values in the data type reane can be represented exactly with precision and scale. "(sic). This is simply not true. Every number base we commonly use has an infinite number of "irrational" numbers (e.g. in Base 10 - PI, e, 1/3, ...) that cannot be exactly represented regardless of the number of digits one chooses. And these values crop up constantly in our calculations of Area, Volume, Average, Standard Deviation, Amortization, etc.
Check out "What Every Computer Scientist Should Know About Floating-Point Arithmetic "
Don't throw a well designed, well implemented, powerful tool out of your box just because you don't understand how to use it. Take the time to learn its features and own it.
March 27, 2015 at 9:28 am
Ray Herring (3/24/2015)
I disagree with the prohibition against Float and note that if you decide you must get rid of Float you will also have to handle Real.
These aren't rules. They're tests for certain items. I think a general test against these types makes sense for some people, not for others. If it doesn't fit, remove the test. That's what I've done.
March 27, 2015 at 5:01 pm
Ray Herring (3/24/2015)
Here are a couple of things to consider.When SQL2005 introduced VarChar(max) I encouraged Devs to adopt the new data type immediately and most did. Eventually, we decided to script the column data type change from Text to Varchar(max). We applied the change and about 100 or so columns were changed in a few minutes.
Unfortunately, it turns out that BCP looked at Varchar(max) and Text differently. A couple of weeks later we found that literally dozens of routines had to be reworked to make BCP treat the new data correctly.
I disagree with the prohibition against Float and note that if you decide you must get rid of Float you will also have to handle Real. IMHO, Float/Real are powerful tools that a competent computer scientist must understand and know how/when to use. Like GUIDs, Cursors, and triggers; Float is not inherently bad, but it is often misused, abused, and misunderstood. In fact, IMHO both of the URLs you provide demonstrate that lack of understanding. For example "Decimal/Numeric is Fixed-Precision data type, which means that all the values in the data type reane can be represented exactly with precision and scale. "(sic). This is simply not true. Every number base we commonly use has an infinite number of "irrational" numbers (e.g. in Base 10 - PI, e, 1/3, ...) that cannot be exactly represented regardless of the number of digits one chooses. And these values crop up constantly in our calculations of Area, Volume, Average, Standard Deviation, Amortization, etc.
Check out "What Every Computer Scientist Should Know About Floating-Point Arithmetic "
Don't throw a well designed, well implemented, powerful tool out of your box just because you don't understand how to use it. Take the time to learn its features and own it.
I agree totally.
Another question we should be looking at is when will SQL Server provide support for the decimal 64 and decimal 128 (and indeed the binary 128) formats of the current floating point standard (IEEE 754-2008, ISO/IEC/IEEE 60559:2011)? And when will windows run on and fully exploit hardware that supports the current standard? All the usual complaints (that have any basis in facts - most of them don't) about floating point are fully addressed by use of the decimal formats (since those formats can represent the same decimal fractions as can decimal/numeric type), and the 128 bit binary format adds quite a bit to the binary float capability.
Tom
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply