One to many SQL Server books

Question

One to many SQL Server books

Kathi Kellenberger

SSChasing Mays

Points: 601
More actions
December 11, 2021 at 12:00 am

#3962437

Comments posted to this topic are about the item One to many SQL Server books

Viewing 6 posts - 1 through 5 (of 5 total)

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

Jeff Moden SSC Guru Points: 1004323 More actions · Answer 1

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

https://docs.microsoft.com/en-us/sql/t-sql/statements/alter-index-transact-sql?view=sql-server-ver15#a-reorganize-demo

... where you'll find the following code example...

-- Create a database   
CREATE DATABASE [ columnstore ];  
GO  
  
-- Create a rowstore staging table  
CREATE TABLE [ staging ] (  
     AccountKey              int NOT NULL,  
     AccountDescription      nvarchar (50),  
     AccountType             nvarchar(50),  
     AccountCodeAlternateKey     int  
     )  
  
-- 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  
BEGIN TRAN  
    WHILE (@loop < 300000)   
      BEGIN  
        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;  
    END  
COMMIT  
  
-- Create a table for the clustered columnstore index  
  
CREATE TABLE cci_target (  
     AccountKey              int NOT NULL,  
     AccountDescription      nvarchar (50),  
     AccountType             nvarchar(50),  
     AccountCodeAlternateKey int  
     )  
  
-- 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.

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

Eric M Russell SSC Guru Points: 125561 More actions · Answer 2

Jeff Moden wrote:

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)...
https://docs.microsoft.com/en-us/sql/t-sql/statements/alter-index-transact-sql?view=sql-server-ver15#a-reorganize-demo
... where you'll find the following code example...

At the top of most MSDN articles is a button labeled 'Edit' where community members can contribute. Perhaps that's the crux of the problem you're referring to. So, MSDN [documentation] has become less like the expertly written reference books we knew from the past and more like Wikipedia.

"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

Chandler Miller Grasshopper Points: 20 More actions · Answer 3

The pace of technological change and the exponential increase in the number of SQL Server, Azure, related data analytics tools, and the explosion of other technologies ensure that the shortage of computer occupation vacancies will continue forever. Nobody can study all the time, which is required if IT professionals want to stay current. Of course, it helps if one is a total nerd, will never want a life outside of work, and plans on never having a family.

My daughter is a medical doctor now, but I missed years of spending time with her when she was little. She would come in my office and ask me to play with her. Because I was always working 24/7, I had to say daddy is working now. I cannot imagine any computer science graduate today wanting to have a career like this. The technologies in demand change from year to year so, it is almost impossible to know or prepare for what is the next big thing. The half life on what you learn in college is short lived.

In August 2021, the number of open roles was at 1.2 million. The latest news reports the number in all occupations is much higher. Companies often want database systems and other IT professionals onsite amid the pandemic, which has no real end in sight. Infections now are exceedingly high again as people let their guard down and try to have a normal life.

No position is worth dying over, even if it pays over $100,000 per year or more. I have spent my entire career learning and applying new technologies. Working all the time gets old. Couple this with all the third-party recruiters and this creates a recipe for IT career failure.

May I recommend if you are a new IT graduate, deploy your skills outside of information technology. A career in business operations is far more stable. Of course, companies are so desperate for IT talent they may do a bait and switch and stick you in an IT role.

Jeff Moden SSC Guru Points: 1004323 More actions · Answer 4

Eric M Russell wrote:

Jeff Moden wrote:
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)...
https://docs.microsoft.com/en-us/sql/t-sql/statements/alter-index-transact-sql?view=sql-server-ver15#a-reorganize-demo
... where you'll find the following code example...
At the top of most MSDN articles is a button labeled 'Edit' where community members can contribute. Perhaps that's the crux of the problem you're referring to. So, MSDN [documentation] has become less like the expertly written reference books we knew from the past and more like Wikipedia.

The bottom line though is that such "public" edits are supposed to be reviewed. Apparently that didn't happen. Worse yet, the original article went out with the mistakes I pointed out and no one, not even the original MS reviewers, caught the problems. Remember that the original article was created by a fellow at MS and so it was wrong from the git (pun intended).

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

Jo Pattyn SSC-Dedicated Points: 32344 More actions · Answer 5

I disagree with Chandler on avoiding an ICT position but heartily agree with "No position is worth dying over". Live your life instead of being lived.

Remote work is way more adopted because of Covid. Externals stay remote when possible.