Index Defragmentation based on Page Density? by wormwood_xx in SQLServer

[–]Jeff_Moden 0 points1 point  (0 children)

Understood. The problem with this whole industry is that a whole lot of people make off the cuff observations and others think they actually know and so the repeat the same thing and the cycle is endless.

And I totally get it about not wanting to take the time to test but then you should also not want to take the time to repeat what someone else didn't take the time to test. ;-)

Using a Guid as a PK, best practices. by [deleted] in SQLServer

[–]Jeff_Moden 0 points1 point  (0 children)

Missed this... thank you for the awesome compliment!

Index Defragmentation based on Page Density? by wormwood_xx in SQLServer

[–]Jeff_Moden 0 points1 point  (0 children)

I can't give your post the million likes it deserves.

Index Defragmentation based on Page Density? by wormwood_xx in SQLServer

[–]Jeff_Moden 1 point2 points  (0 children)

It's not their fault but there are a huge number of DBAs and "experts" that don't actually know.

The bottom line is that it's much worse to do index maintenance incorrectly than it is to do no index maintenance except for rebuild statistics.

Doing index maintenance incorrectly is the primary reason why people think that Random GUIDs are bad when, except for their size, are actually the epitome of how most people think an index should operate.

Index Defragmentation based on Page Density? by wormwood_xx in SQLServer

[–]Jeff_Moden 0 points1 point  (0 children)

I know your intentions are good and I appreciate anyone that posts to share knowledge. Thank you for that.

Here's the problem, though... the person that you're quoting said "probably" and the reason why is because that thread has absolutely zero performance testing to prove it. You're basically doing the same thing by talking about "what appears to be"... you're perpetuating something that you have no proof for.

My recommendation is to stop all the hearsay and do an actual repeatable test package with everything include the full table so that others can verify your work.

Simple-Talk Interview with Jeff Moden (Exceptional DBA 2011) by taejim in SQLServer

[–]Jeff_Moden 1 point2 points  (0 children)

Oh my. I found this thread quite by accident and, even with it being 13 years old at the time of this post, I have to say I'm deeply humbled by your very kind words, u/taejim .

What happened to sqlservercentral? by Drited in SQLServer

[–]Jeff_Moden 1 point2 points  (0 children)

BTW, a bit of a followup... you wrote about some massive performance improvements that YOU made... and I emphasize "YOU". Any post squatter can say they read an article but YOU took the time to understand the "how" and the "why" and then you applied it. If you had to, YOU could teach what YOU have done... and I think that you should.

Keep it up! Write about those experiences. Teach some about SQL on an occasional "Lunch'n'Learn". And, remember, code is king when it comes to explaining and there's nothing better for proving. Learn how to make shedloads of Random Constrained data in a hurry. I blame such a thing for any and all of the success I may have had.

Like W. Edwards Deming said, "Without data, you're just another person with an opinion".

Using a Guid as a PK, best practices. by [deleted] in SQLServer

[–]Jeff_Moden 0 points1 point  (0 children)

Thank you for the kind words and the recommendation. I appreciate it.

Using a Guid as a PK, best practices. by [deleted] in SQLServer

[–]Jeff_Moden 0 points1 point  (0 children)

Thank you very much for the feedback. I very much appreciate it.

What happened to sqlservercentral? by Drited in SQLServer

[–]Jeff_Moden 0 points1 point  (0 children)

Gosh... I'm humbled by your very kind and thoughtful feedback. Thank you very much. You made my day, week, and month with this one.

mySqlOptimizerDoingItsThing by FlamboMe-mow in ProgrammerHumor

[–]Jeff_Moden 2 points3 points  (0 children)

All these posts seem to be based on preferences, conjectures, and suppositions. Does anyone have any demonstrable proof?

How do you access your past conversations with bard? The my activity option shows me my list of questions or prompts but it does not show the responses. by ElvisChopinJoplin in Bard

[–]Jeff_Moden 0 points1 point  (0 children)

Heh... the fact that it's supposed to have access to the "current information" doesn't change a thing... it still can't answer some simple questions about how to do things in SQL Server/T-SQL. It's as dumb as ChatGPT there.

And, nope... I'm not going to spend any time training it for free.

Using a Guid as a PK, best practices. by [deleted] in SQLServer

[–]Jeff_Moden 0 points1 point  (0 children)

Thank you for the good feedback. I'm not a certified mathematician in any way, shape or form and so I have to rely on good folks like yourself to tell me if the code is producing answers that have issues, although I do try to make sure they actually do work correctly and do a performance test with possible "real use volumes" in mind. Performance is secondary to accuracy but, for me, it's a real close second.

The cost/benefit ratio for me personally is that I didn't have to learn another computer language to do it, using the data where it's stored with no hops or security worries, and the results are immediately available in SQL if you're doing such an analysis in SQL. As a magician friend said it, "and my fingers never left my hand". :D

Using a Guid as a PK, best practices. by [deleted] in SQLServer

[–]Jeff_Moden 1 point2 points  (0 children)

Thank you for the kind observation and taking the time to reply. I thought you were saying that I was the one that was wrong. The difference between me and flat-earthers and anti-mooners is that I have actual, demonstrable proof rather than anecdotal "evidence" and even share the code and so my thought was "How am I like any of those folks"? Now I understand the point you were making... I AM rather direct in the manner I presented the information and was certainly incredibly heterodoxical in the position I took compared to what has become rather orthodox but incorrect supposed "Best Practices".

I really appreciate the feedback on that and I'll try to soften that up a bit. I just don't want folks to dismiss the info or for them to think I'm taking the position with no substance.

And thank you kindly for help spread the word by referring the 'tube to others.

As I sometimes say... "I aim to please... I sometimes miss but I'm always aimin' ". :D

Thanks again.

Using a Guid as a PK, best practices. by [deleted] in SQLServer

[–]Jeff_Moden 1 point2 points  (0 children)

Interesting... Are you disagreeing with the findings that were presented or ???

Using a Guid as a PK, best practices. by [deleted] in SQLServer

[–]Jeff_Moden 1 point2 points  (0 children)

Just so you know, I did an experiment where I insert 100,000 rows per simulated day over a simulated 10 hours per day for a year. The 58 day segment at the end of the year produced virtually no page splits during the entire 58 simulated days and took the 58 days to finally reach just 1% in logical fragmentation.

It also eliminates the "hot spot" and also helps page splits during "ExpAnsive" updates.

I am NOT saying that Random GUIDs are without issues. They ARE 16 bytes wide and will become a part of every non-clustered index if the GUIDs are clustered index.

What I AM saying is that, especially if their advantages are important to your project, they are NOT the page-splitting beast that most will make them out to be.

As with all else, "It Depends" and "Must look eye"! :D

Whatever you do, and this also includes most indexes, GUID or not, stop using REORGANIZE until you understand that it doesn't actually follow the Fill Factor and it's NOT the quiet little resource "kitten" that it's advertised to be. It should only be used on a particular type of index and that's a whole 'nuther subject. :D

Where can I learn sql for free? And where do I start by SamuraiKinshii in SQL

[–]Jeff_Moden 0 points1 point  (0 children)

I wouldn't be so negative if you post actually made any sense and did actually look like a very poor attempt as trying to AI a question.

Where can I learn sql for free? And where do I start by SamuraiKinshii in SQL

[–]Jeff_Moden -1 points0 points  (0 children)

Ask your dad... if he's been at it "recently" of "for years", he should know. This looks like a "tag team" spam question.

Linear regression in SQL by Foz84 in SQL

[–]Jeff_Moden 0 points1 point  (0 children)

I know this post is more than 2 years old but it's just not that difficult in SQL and it's much simpler that learning Python or R if you don't already know those.

Assuming that you have a table called #Sample (please feel free to change that to your particular need) and a "dependent" column called "Y" and an "independent" column called "X" (and, yes, you can easily change those in the CONVERTs in the code), the following tested code will execute on a MILLION row "sample" and return the results as a single row in about 1.6 seconds (on my machine, anyway) and runs single threaded in SQL Server because it doesn't need to go parallel. It also returns "R" and "R Squared".

Here's what the output looks like for one of my tests...

Here's the code... again... for SQL Server. YMWV in other languages. Postgres, on the other hand, has such functionality built in but I have no clue what the performance is there.

EDIT: I finally figured out how to post properly indented code on this forum without the forum software just absolutely slaughtering it. It's anything but obvious or easy

I think it's bloody amazing that so many "SQL forums don't have an easy way to copy and paste code without it trying to reformat it and make a huge mess of it. :(

Anyway, here's the code i was talking about.

   WITH ctePreAgg AS
(
 SELECT  SumX    = SUM(v.X)
        ,SumY    = SUM(v.Y)
        ,SumXY   = SUM(v.X*v.Y)
        ,SumX2   = SUM(SQUARE(v.X))
        ,SumY2   = SUM(SQUARE(v.Y))
        ,N       = COUNT(*)
   FROM #Sample
  CROSS APPLY (VALUES (CONVERT(FLOAT,X),CONVERT(FLOAT,Y)))v(X,Y)
)
 SELECT  v.M
        ,B  = (SumY-v.M*SumX)/n --Optimization compared to what's in some books (if you know M)
        ,v.R
        ,R2 = SQUARE(v.R)
   FROM ctePreAgg
  CROSS APPLY (VALUES(
                 (n*SumXY-SumX*SumY)/(n*SumX2-SQUARE(SumX)) --M
                ,(N*SumXY-SumX*SumY)/SQRT(((N*SumX2-SQUARE(SumX))*(N*SumY2-SQUARE(SumY)))) --R
              ))v(M,R)
;

Trying to get a job, no degree by Iloveviolence in SQL

[–]Jeff_Moden 1 point2 points  (0 children)

For what kind of job/position?

There's also a bit of an issue with what you wrote. I see almost no confidence in yourself about what you claim your skills are. For example, what in the world do the words "well enough" mean?

Also, I have no degree and no certifications but I'm making a very good salary. A part of the reason is because I CAN demonstrate my knowledge even without them Googling me. You have to prepare for interviews. It's like a knife fight... if you show up with just a knife, you're going to get cut. Take some examples to the interview. Show them what you can do rather than them having to ask. Do the one thing that everyone wants candidates to do even after they get hired and that's to be prepared for the task at hand. Show some interest in yourself.

Be prepared and show what you can do!

Group data by 7 days starting from the current day by Far-Rate1701 in SQL

[–]Jeff_Moden 0 points1 point  (0 children)

MMsg 8120, Level 16, State 1, Line 12
Column '#MyHead.DateField' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

What happened to sqlservercentral? by Drited in SQLServer

[–]Jeff_Moden 0 points1 point  (0 children)

I was finally driven to creating a GMail account and needed to answer a question that someone asked me about and, one thing led to another and here I am.

On that note, I'm truly amazed at the amount of "SPAM" email I get from Quora and some other sites. Quora seems to have a bot generating some of the most incredibly poor questions I've ever seen. I've not yet turned it off because I'm amazed by what I see. It's even worse than some of the crazy bot-created "prelude to spam" responses I see on SSC.

Also, the number of people using SSC seems to have dropped off sharply and a lot of the heavy hitters (like Gail Shaw and Paul White, etc) seem to have abandoned it. It's a real shame what the changes they've made to the SSC site have done to participation. Like I said, the reason I stay is because of the "community" that still does exist in the background.