Thanks for taking the time to write this article, Kathi.
What's even worse than what you mention is that the quality of the "official" documentation has totally tanked as is frequently a worst example of how to do things. The same holds true when considering other sources written by "experts".
Here's just one of hundreds of examples that combines both of the problems I've noted above.
Here's the link to the example I'm talking about (it’s about how to use REORGANIZE for ColumnStore indexes)...
... where you'll find the following code example...
-- Create a database
CREATE DATABASE [ columnstore ];
-- Create a rowstore staging table
CREATE TABLE [ staging ] (
AccountKey int NOT NULL,
AccountDescription nvarchar (50),
-- Insert 10 million rows into the staging table.
DECLARE @loop int
DECLARE @AccountDescription varchar(50)
DECLARE @AccountKey int
DECLARE @AccountType varchar(50)
DECLARE @AccountCode int
SELECT @loop = 0
WHILE (@loop < 300000)
SELECT @AccountKey = CAST (RAND()*10000000 as int);
SELECT @AccountDescription = 'accountdesc ' + CONVERT(varchar(20), @AccountKey);
SELECT @AccountType = 'AccountType ' + CONVERT(varchar(20), @AccountKey);
SELECT @AccountCode = CAST (RAND()*10000000 as int);
INSERT INTO staging VALUES (@AccountKey, @AccountDescription, @AccountType, @AccountCode);
SELECT @loop = @loop + 1;
-- Create a table for the clustered columnstore index
CREATE TABLE cci_target (
AccountKey int NOT NULL,
AccountDescription nvarchar (50),
-- Convert the table to a clustered columnstore index named inxcci_cci_target;
CREATE CLUSTERED COLUMNSTORE INDEX idxcci_cci_target ON cci_target;
The incredibly obvious errors are as follows...
- The first step is to create a new database... it has both leading and trailing spaces in the name of the database.
- There is no USE statement after that, which means that the example will not be created in the new database. At best, it will be created in whatever your current data base is . At worst, that will be in your [master] database.
- A table is created. Same problem. It has both leading and trailing spaces in the name of the table.
- Later on, the table is used without brackets, which means the code will fail because of the leading and trailing spaces that are now missing.
- Look at the comment that states "-- Insert 10 million rows into the staging table." and then realize that the WHILE loop only inserts 300,000 rows
Some not so obvious errors and extremely poor practices are...
- The two part naming convention isn't used anywhere. That sets a really bad example for neophytes that might be reading the example.
- Each variable in the WHILE loop is set by a separate SELECT instead of all of them being set by a single SELECT, the former being a worst practice for performance.
- The INSERT doesn't have a column list. While that does work in this case, it's still a worst practice.
- This example was originally written my people at Microsoft (I've seen the original article and don't want to cite the person that wrote it). It's obvious that they don't have a clue as to build test randomize test data using RAND without using a WHILE loop. For people that know better, it 's a huge warning sign that the person writing the code is probably not an expert at writing anything and doesn't instill any confidence in the subject at hand.
- None of the examples have any output posted. For the author, that's actually a good thing. Again, I've seen the original article and it doesn't work as advertised unless you have the same number of core as the author and he cited that fact in his original article and left it out in the official article!!!.
And then there are the "expert" authors out there. To cite just one example there, one rather well known, high profile, "trusted" author wrote that it's "always" a best practice to shrink your log file before you do a full backup to make restores quicker if needed. The article has been unchanged in the 3 years it has been been posted even though a couple of people pointed out exactly why that's such a worst practice.
And then there are those blogs where there is no way to provide feedback or the "Comments are Closed".
In the last several years, the old saw about how "Half of all that is written is wrong and the other half is written is such a fashion that you can't tell" has become absolutely true. Even a lot of people that use test data to supposedly "prove" something have managed to do it wrong (especially in areas of performance) because they use extremely low cardinality data, which emphasizes the "written in such a fashion that you can't tell" part of that old saw.
I used to be pretty tough on the poor folks known as "Front End Coders" that need to write SQL. I'm seriously trying to soften in that area not only because it's becoming more difficult to find anything in the "official" documentation, but the documentation is frequently very poorly written. Then there's the fact that their environments seem to change about every 20 minutes because companies decided to release "something" and then go back and fix it over and over again. I was ticked off about it when I was a Front-Ender 20 years ago and it's gotten much worse in the last 2 decades. I don't know how they keep up and suspect that they simply can't.
And the really bad part of it all is that not all the changes that are coming out are worth the powder to blow them to hell. Heh... have you tried making font color changes in SSMS and then try to copy and paste to Word or Power Point lately? Yeah... good luck with that. 🙁 Even the special setup under the category of "Printer and Copy/Paste" doesn't do it all. What ever happened to WYSIWYG?
Keeping up is no longer an option. It's more like "Roll with the punches".
And, seriously MS... it took you 6 years to figure out and partially fix STRING_SPLIT() and still not return a NULL when you send it a NULL? Lordy. No documentation can fix that.
Don't get me started on the documentation for Index Maintenance and REORGANIZE. MS has perpetuated fragmentation with that bit of documentation for more than 2 decades. We even though Random GUIDs were a bad thing because of it.