Hey, don't speak like that! I want my channel to grow, too :)

​@@zoran-horvat I really hope you become as successful as you want to be on youtube! I was just thinking that the demographic for advanced programming discussions is not large. That's why we see channels who explain the basics receive way more views than channels who showcase advanced knowledge. If channel views were proportional to the knowledge of the creator I'm sure you'd be in the top 10 KZfaqrs already. Anyway, I was just trying to express my gratitude for your videos and everything you teach us. Agan. Thank you!

@@zoran-horvat You have my sub mister... and epic name

That is a very useful tip, thank you!

If I had a wishlist for SQLServer, right at the very top would be some sort of mechanism that optionally deferred validating referential integrity until commit.

@@billy65bob That is an interesting thought. Do you know of some other database that can do that already?

@@zoran-horvat I would not be surprised to learn that OracleDB does. But some googling suggests that both OracleDB and PostreSQL supports 3 levels of it on all constraints. Those levels being Never (e.g. UNIQUE), optionally during Statements (e.g. bulk insert with FOREIGN KEY to self), and Until Commit. With the level being set when the constraint is defined.

You have good points here, thanks! Especially the one on reading the data sequentially.

​@@zoran-horvat sql azure forces you to add a clustered index on your table(s). that said, it doesn't have to be the primary key :)

@@mannetjie3704 I am not sure that SQL Azure really forces you to use the clustered index. I'd rather say it takes it as a default, which is the same as in SQL Server. Are you sure about that? And yes, you can build a clustered index on any column you wish, even the nullable and non-unique columns are susceptible to clustered indexes. The rule that makes sense is that one table can only have one clustered index.

It depends on the use case. Seeking a clustered index saves the extra key lookup in the main table, but the table itself being the index also means that typically fewer rows fit in one page, and as a result, a clustered index seek causes more IO than a seek in a "lean" index. The best case is when you have a "lean" index with few rows that is still covering. That might not be possible for every use case.

@@cdoubleplusgood That is true, but this video is not about removing a clustered index. It is about a bug that may happen when there is a clustered index. To fix the bug, you must either guarantee the sequence or drop the clustered index. That is the message. Looking from the other end, then if the clustered index is required (e.g. for the performance benefits it offers in some scenarios), then we must be very careful when generating ID values.

That is the point, the DBA job must be done well for a database to operate well.

Glad to hear that.

Yes, the use of a UUID standard that guarantees sequence is preferable, but be careful if the system is truly distributed. The monotonicity could fall off node clocks are out of sync. On the other hand, a clustered index forgives small discrepancies, i.e. the performance loss will usually be immeasurable if it only has to move a few rows at worst. That is another hidden gem of clusters indexes.

If it only were "just"...

That is when you find out... What were your timings? How long would an insert take when you noticed the problem?

In many videos I cover different aspects of domain modeling, though only a few videos mention that intent in the title.

That would be an optimization and, as such, it is valid in certain scenarios. I have intentionally tried to avoid getting into the details of measuring and optimizing read and write performance because that would make it an hour long video at least, but it seems that the question of performance is inescapable...

There are many channels about SQL Server for example where you can find excellent videos on those topics. I am not sure I can explain indexes and performance even remotely well as in some videos I watched in the past.

@@zoran-horvat I have a question is this problem which you mentioned, only happen when you let the database create GUIDs as well as creating your own? How does this apply if you always create your own GUIDs, and when doing so you you have them as non clustered?

I have used similar tricks in the early days, but then found out that I favor simpler configurations... Though, the idea you mentioned will work.

Then you lose the greatest benefits of using GUIDs - the ability to generate the value before persistence and the ability to persist entities in the storage that does not support generating IDs.

@@zoran-horvat Such a setup doesn't preclude the benefits of application generated guids. Applications with such a setup are usually ignorant of the incrementing Ids when they're not resolving foreign keys in their queries. It does come at the cost of requiring 2 indexes at minimum; a clustered one for the incrementing ID, and a unique nonclustered one for the GUID; which does have some performance implications.

@@billy65bob Yes, a better formulation in my reply would be "you don't take these benefits for free".

The possibility of a collision in GIIDs is truly negligent, so you shouldn't count it in. There are issues that happen far more frequently in distributed databases and we still have the means to handle them.

Yeah I feel like I didn't really word what I was trying to say very well.

Yes, that could be a value topic for another video. I thought that covering other aspects in this video would hurt its purpose.

There is no difference between custom and generated migration code. Each migration is immutable and EF will not write into it again. As I pointed out, this is not the problem caused by EF. I have been using other migration tools before and they had the same problem, that you must add custom migration code to make a certain change. On the other hand, the whole issue only happened once I made a bug in the earlier configuration.

​@@zoran-horvatI understand that migrations are immutable once created, but how do you squash migrations that you cannot recreate using ef core? by squashing I mean creating a big migration from hundreds of smaller ones (which will get the custom migrations lost if not careful)

@@lettuceturnipthebeets790I’m trying to understand your question. What Zoran did was generate a migration, and then add to it. You can add whatever you want to the migration, even down to custom SQL statements. But that is the complete definition of your migration. If you later want to create a migration script, say an idempotent script which you will generate and apply within a CI pipeline, all you’re doing is combining the migrations you have defined. The migrations aren’t generated again. You’re just combining, or squashing if I am understanding your question correctly, into a single SQL migration script.

It's the other way around: database-generated IDs are normally safe, because the tools would set up the database correctly. It is the application-generated IDs that may sometimes cause trouble if not aligned with how the database is created.

From what I know, Postgres also supports clustered indexes.

Postgres has something similar, but it is not applied by default. By default, the PK is a "normal" index. Similar for Oracle. They have something called an "index organized table". But that is also not used by default.

It is a tradeoff, but not with those asymptotic complexities. The insert of out-of-sequence IDs into a table with the clustered index takes O(n) time and read takes O(log n). The insert and read from a table with a common key is O(log n) in both cases. So it is actually O(log n) vs. O(n) tradeoff, which becomes tragic on any table with more than, say, 100k rows and absolutely unsustainable on a 1M rows table. The point is that there is no arithmetic joggling that would make the relocation of half a table in every insert even remotely performant as anything else a man could imagine, lest the table is of a very small size, hence not important in any analysis.

@@zoran-horvat > The insert of out-of-sequence IDs into a table with the clustered index takes O(n) time Ok. Fair enough finding where to insert data is O(log n), but actually moving everything and inserting is O(n). For tables where I expect a lot of rows, and where I'm not going use id:guid as most used where condition I don't think it's a problem. It would be fun to see what are actually read and write speeds for 1k, 10k, 100+k rows table with clustered and not-clustered id:guid.

It is a safety valve in case you suddenly figure something out after deploying to production. I remember events of that kind in my career. Very rare indeed, but when it happened it was always driven by panic reactions at the customer, so they would request immediate rollback of the deployment and only then an investigation.

I have one such course in the queue about OOD and domain modeling, but no dates when I might actually start working on it.

There is a class that apparently does that (learn.microsoft.com/en-us/dotnet/api/microsoft.entityframeworkcore.valuegeneration.sequentialguidvaluegenerator?view=efcore-8.0). I must admit I have never used it. To make it worse, I have never even read is documentation carefully. The critical question to answer is whether the sequence remains ascending if you move the application to a new server? Though there exists the standard definition of GUID that puts a timestamp at the front, thus making any sequence of GUIDs ascending, there is a nasty corner case when the two servers are out of sync with time. The problem is the same as when using a timestamp to version entities where it may sometimes lead to lost updates! For all these reasons, I normally let the database generate IDs when a clustered index is a requirement (in systems with heavy inserts, e.g.), and drop the sequential IDs request otherwise.

Thank you so much @@zoran-horvat

When a clustered index is required, then the number of options is limited. That is the case in systems with heavy inserts. The way EF Core handles that is to initialize the table field with NEWSEQUENTIALID(), the same way as we would do when writing the CREATE TABLE script by hand.

It depends on what you need. Someone pointed out how fast they are if we tend to read the rules in chronological order, which is the central request in some applications. The clustered index will then make such reads substantially faster than the one with rows scattered all over the data pages. On the other hand, most of the business applications do not have such a request. We usually read the routes as per request, and requests are randomly driven by end users. That makes clustered indexes less important, even to a degree that maintaining them becomes a liability with no benefit in return.

@@zoran-horvat Yes, I found that clustered indexes worked faster with time-ordered data; happened to be quantities of mushrooms collected at distributed farms per picker over a week and aggregates per farm per cost-center.

First of all, my advice is NOT that you should not use a clustered index, as your comment seems to imply. The video is about a very specific situation when an application generates GUIDs using an algorithm that does not guarantee the sequence and there is a clustered index. One must go. Regarding your analysis, the actual performance depends on read and write patterns. Neither the clustered nor non-clustered index is better in advance without knowing the statistics. Your analysis does not generalize and, hence, does not lead to any conclusion.

@@zoran-horvat what pattern are you talking about when the sql server just sticks the data wherever it can find available space on the disc?

@@krm1t This what you said is too wrong to answer to.

@zoran-horvat sry it seems KZfaq decided to not show all of your message. A refresh added the first part which I hadn't read. That is true. You don't say it's a general advise. That's a misunderstanding on my part.

The percentage doesn't matter. It is the fact that an operation that takes near-constant time would be substituted with an operation that takes time proportional to a half of the table's size on average. One simply does not do the latter.

@@zoran-horvat I take your point that its is self evidently the correct approach, however I have applications with this issue and I need to prioritize my time. If I go back and spend the dev time on the migrations will I see a 5% or a 40% perf increase. Thanks for the amazing videos, big fan 😁

@@MrValhallans The point is that when this issue manifests, it makes every row insert a pain so obvious that measuring it makes no difference, especially knowing that every next day will only make it slower. How do you prioritize work on a system where inserting a row takes a second?

An application cannot generate an autoincrement number. It must rely on the database to communicate the ID back to the application. Not to mention that autoincrement anything is almost invariably out of the question in distributed systems. Now list ten situations that come to your mind where waiting for the autoincrement number is an issue, and you will have ten use cases for GUIDs. It shouldn't be hard.

​@@zoran-horvat Adding related data to many tables at once requires knowledge about PKs and FKs, but I always relied on EF or SQL's SCOPE_IDENTITY then and never set them by hand. Ok, it's just easier having keys before and after the action.

The problem was with IDs generated out of order when there was a clustered index. The database generates GUIDs in the ascending order when instructed, and so the problem would not occur then. However, that is not to say that database-generated IDs or clustered index perform better. The overall performance is also the result of read and write patterns exhibited by the application.

That is one possible design. But only one out of several valid ones. We should always keep the requirements in mind, both functional and nonfunctional, when making design decisions. For example, a clustered index can cause write congestion in some scenarios and work like a charm in others. A table with a common scattered index can cause read delays in other scenarios. There is no one size that fits all.

Why not just have an auto incremented long as Primary Key (generated by the db) and don't even have the Guid?

@@MrWTFNETWORK Because it might be that the application is generating ID and database is only storing it. That is frequently the case in distributed systems. In such a situation, autoincrement numbers are not an option.

@@zoran-horvat you made me think of a scenario where I store invoices and invoice line itens, 1 to Many relationship and we were using guid for primary Key of both tables. In reality to acess line items I see that the line items table could instead have a composite primary Key as invoice Id and line item id (incrementing integer generated in memory instead of guid as there can only be max 100000 items), what do you think about this approach? We only need to get 2000 items in a paged way until we allow clients to get all line items via API. We would use a continuation token being the id of the last item and always sort by the id

There is the new video demonstrating that possibility: kzfaq.info/get/bejne/g7CTlNuamMizf2w.html The caveat is that ULID will not solve the problem if the table already contains many rows. ULIDs are sorted left-to-right, where GUIDs used by the SQL Server have a different structure and sorting. Therefore, the sequence of ULIDs would not extend the sequence of IDs already in the table, once again causing the relocation of great numbers of existing rows upon every insert.

@@zoran-horvat thank you for you answer, I've already started watching your latest video, I'm glad that answer is there

If I got your description right, that is precisely the scenario where we choose to let the application generate IDs. The application then has the control of the times when it creates objects and times when it does then to the database - those two don't have to happen simultaneously. It is also useful when sending objects between systems, as well the systems will associate the same object with the same ID.

Thanks for the reply@@zoran-horvat

DDL statements in SQL Server exexute in a transaction, with a few exceptions like CREATE/DROP/ALTER DATABASE and the same for FULLTEXT INDEX. You can also mix DDL and DML statements in the same transaction. That is not limited to SQL Server. Other databases also support transactional DDL, most notably Oracle.

@@zoran-horvat didn't know. Thanks

That's not true for SQL Server is it?

@@eveneveneven SQL Server supports DDL in transactions.

Actually, I believe it is better to generate GUIDs in the application because that is their greatest strength: global uniqueness. It lets it generate them in the frontend as well. It lets us choose whether to store entities in aggregate storage in one node or bounded context and in a relational database in another. When you put everything on the table, leaving the database to generate values on insert begins to look less attractive.

Please correct me if I wrong here, but for Publishers table with this approach we don't have any indexes now because primary key PublisherId is not the clustered index now. Will our search and filter operations in this table now be slower than with a clustered index?

@@user-we6wp1ky7f There must always be an index on the key, or otherwise the database would perform a full table scan on every access to a row. The default in SQL Server (and only in SQL Server) is that it keeps the table sorted by the ascending je, a.k.a. the clustered index. But if you opt for a "regular" unique index, as I did in the video, then there will be a separate index built and updated on every insert. So, the answer is that both Books and Publishers tables will have unique indexes on their keys at the end of the video.

@@zoran-horvat Thank you for your explanations, I became smarter after watching your video and reading your answers!

It depends on what you think that I said. I see that many viewers didn't see the central message of the video, which is probably my fault in passing that message. So, the message is: IF the application has been generating IDs for some time AND there was a clustered index THEN... we begin. The video doesn't question any of the decisions because those are driven by entirely different forces. It only questions the combination of decisions.

@@zoran-horvat That does help to clarify the starting situation, and is still interesting as I definitely think others have been in this situation, although I do think it would've been nice to see some details on the improvement that the change made

That is true, with a caveat. What used to be encapsulated in the database, both creating and consuming IDs, now becomes the problem of someone else. It is entirely possible that a future update, such as accepting IDs from the node or service which was not entitled with that before, could break the rule causing a surge in performance. I would advise to either keep the dependency in an algorithm in one place (database), or to not have it at all.

@@zoran-horvat True, either the database needs to have an implementation of UUID7 that it uses to generate the IDs or (when the UUID7 must come from an external source) all pathways that result in an insert into that table must go through the same abstract layer that generates a UUID7 on the caller's behalf. Most systems have a "repository" layer that would suffice but once you have more than one system with access to insert into that database, each one becomes an entrypoint for bad (non-UUID7) IDs. Creating a shared library for interacting with tables of that database would be my next path forward to greatly reduce errors.

That is true. The problem occurs when GUIDs are generated in the application and only transferred to EF. In that case, the algorithm will be whatever the application used and, unless a specific sequential algorithm is configured, that will be an out-of-sequence algorithm, as it was in my demo.

@@zoran-horvat "whatever application"? eehh, i don't know. Let's say you have existing dataset from other system (SYSEXT) that you would like to integrate into your system (SYSPROD). This dataset has entities with attributes (RDBMS sense): SYSEXTItemID, SYSEXTItemTitle. You want to store these entities in your DB as well. In that case you may say this is application generated IDs. I can get such scenario. You see how I view PK - they are specific to your DAL of your DB (SYSPRODDB). IMHO DAL responsibility is to save, operate and retrieve data consistently from your data store. The implications of such approach: 1. Only DAL controls how data is saved and retrieved. 2. It must control indexes, constraints and relations as needed inside DAL. In your scenario you would may as well have EF CORE generated sequential IDs in following way: - define table like SYSPRODID, SYSEXTItemID, SYSEXTItemTitle - add index on SYSEXTItemID, if needed, but keeping in mind your goals of adding records quickly, probably you want to avoid that In that case you would remove design dependency between PK and "IDs generated by application". Even if the application changes its behavior, DAL implementation stay stable. If I would encounter such an issue, then I would keep my DAL fully responsible for consistency and performance and would remove coupling between DB design and application as it would lead to other issues. Team may misuse and abuse such approaches. Also this does not help if Item is deleted or SQL is restarted due to maintenance on-prem or in the cloud. Fragmentation still possible and index maintenance should help more than anything else.

Every nonclustered index does, and databases are still live and kicking.

Watch the video with more attention. You're welcome.

​@@zoran-horvatsorry, I've rephrased it, now it's both more precise and shorter :) don't get me wrong, many are interested in a full video, but most don't have 15min+ just to figure out what the whole thing about and and worth spending time, unfortunately it's rare that youtubers provide sufficient description, or even tray to bait with incomplete titles

@@kocot.It is not possible to state the problem precisely in 50-characters title, nor in the description for that matter. My channel consists of advanced-level videos and trust me, quite a few programmers watch them very carefully. I expect them to because I don't know how to pour an advanced piece of knowledge into a head that lacks patience. Look at me - I have been learning programming for 35 years now. Regarding the problem addressed in this video, the assumption is that the change cannot fit the existing table with data in it. That is a regular case in practice. Either the IDs are coming from another system that just didn't care to make them sortable, or the table already contains a lot of data, like a million rows, produced with a sortable UUID generated by a different algorithm, so the new algorithm does not extend the existing sequence. If either is the case, we must make cuts. One is to give up the clustered index, and that decision is tied to a few other considerations, such as write and read statistics. The other option is to recreate the table and generate new IDs using the new algorithm. Both decisions are hard to make, and this video does not suggest which one is the right one. Actually, chances are that it will be the DBA to decide. The upcoming video, which is in the publishing queue right now, will demonstrate both possibilities on two tables in the same database.

The structure of UUIDs is defined by the corresponding standards. If you need to analyze the values, then you take the parts as defined in the standard. There is no freedom to decide.

@@zoran-horvat yes but the sql server does sort the bytes/characters in order of left to right, and normal v1 uuids like you showed do not sort chronological this way. V6 are better in this regard.

It is not the question whether it is better or not, but which node in the distributed system is in charge to generate or, let's say, know the value of the ID. If it is an applicative node, a service, or anything of that sort, then keeping the storage in charge of generating IDs requires an additional trip back to the application before it can continue doing what it planned. It is also hard to rely on the database in polyglot systems where some of the storage nodes might not even support generating IDs.

That would resolve the problem, but with a caveat: Every node that generates the IDs would have to know the condition and, on top of that, use the same algorithm. I fear more from configuration errors than the conceptual ones, especially as the resulting big is a nonfunctional one, therefore hard to reproduce and catch. On a related note, I plan to make a video on strongly-typed IDs with EF Core. The strong ID type would be the ideal place to lock the GUID generation algorithm, though it remains that the C# module containing that decision would have to be in sync with any other source of IDs.

Yeah sure, but you forgot the problem statement. You just messed up the database in exactly the same way as I did in the demo. You're welcome.

@@zoran-horvat Explain how? I've been using Type 1 UUIDs for years. Never had a problem. Maybe this is SQL Server specific?

@@KangoV That is your homework, not mine. BTW, I did explain it in the video. Two times.

"Sequential" is the wrong word, I suppose. "Ordered" covers it better.

@@Dalendrion Yes, ordered is a better term for that. There are several standards for GUIDs, at least one of them guarantees order on top of universal uniqueness (that is the principal trait of that standard).

If you are going to query by GUID then you got nothing... That is the reason why we use external ID as the key (in those scenarios). Less code and better database performance, all in one package.

It is a good idea. The only issue with types that are sorted by time is when used for versioning - once the system truly becomes distributed, small discrepancies among timers will cause incorrect sorting of versions and lost updates. However, the concept alone is very useful.

Normal uuids are not sorted by timestamp but there are special variants which are

@@zoran-horvatwhy does gaps in timers cause data loss?

@@berndeckenfels When a time sequence is used to version changes in a distributed system, then we risk the case when two updates are reversed because the node that effectively generated a later update could have its timer late. In that case, the earlier update could overwrite it. That is one of the failure modes for distributed databases.

@@zoran-horvat ah ok, yeah that’s not really related to uuid but yes that’s a problem if you don’t use vector clocks or similar

It is tricky to vote this in terms of benefits. It is rather a reaction to other forces when designing a distributed system. So, in practice, you will need to cope with a situation where applications already have the need to generate IDs and letting a database do it for them is a sub par solution.

This is not the problem, nor is it with SQL Server specifically. Many other databases support clustered indexes - Oracle and Postgres do, for instance. The video is showing one typical mistake that can happen. A few commenters confirmed that happened to them in the past, and it also happened to me. You would typically notice the problem when an insert into a table becomes unreasonably long, such as over second or longer. I have heard horror stories of an insert taking half a minute before someone noticed that the things are not right. Regarding the naming, you are right that GUID is the ID. It is a good point one should keep in mind. However awkwardly, I was reading it in C# - Guid Id, i.e. the Guid type and the Id property.

@@zoran-horvat Sequential indexes shouldn't cause that problem if the database is implemented correctly. I can only assume that they're using the GUID directly as a key instead of hashing it. This kind of thing is why increasingly I write my own. I keep seeing these kinds of logical fails in "professional" software.

No, that's not why you use Postgres. One must be stupid to make such a random technical decision.

Did you just explain we should never use heap tables before asking what queries we plan to execute? You know better than that, I hope. For example, that "absolutely kills reading performance" depends on what we query, right?

@@zoran-horvat You ask me to provide context when you yourself provided none. In fact, that's actually the main objection I make to your video: the lack of context. Sure, Heaps have a place, sure non-clustered PKs have a place. But giving a 'general advice' to drop the Clustered when client-generated Guids are used as PKs without any warnings or caveats can put inexperienced developers in a much worse place than a fragmented clustered index would. I'm absolutely sure you know better too. 😉

@@SantiagoBurbano-su9oe I think I gave quite a sufficient amount of context when I said that the unsorted IDs are coming in from an external source. That condition is a given at the beginning of the demo. The rest is a consequence.

Did you add tags or links into them? KZfaq regularly deleted those.

@@zoran-horvat I wrote N3wId from M@ss Tr@ansit

You can use a specialized algorithm that guarantees sequence, but that opens a different set of issues. All components that you might allow assigning IDs must guarantee to use the same algorithm, for example. Any later change is not possible, and so on. All these limitations stem from the principal problem that we have separated the source of IDs from the storage that depends on them. None of the issues appear if we lift the artificial constraint of the storage, that is the point.

@@zoran-horvatThis is great information. Thank you!

Yes, sure, but that doesn't change the fact that every mainstream RDBMS supports transactional DDL. Actually, missing such a fundamental feature in 2024 either makes it outdated or a niche technology with an accent on something else (which is fine).

My video is not on the topic you are mentioning. I would appreciate it if you could watch my video as being what it is, rather than as not being on the topic of some other video. My video is about a very particular situation when a programmer unintentionally leaves the clustered index on the key column, and then intentionally uses an out-of-sequence source of IDs. The situation naturally occurs when using GUIDs. Contrary to what you have imposed, the video you have commented on is not about index performance. After reading your comment two times, I have an impression that you have missed the theme of my video entirely, particularly the point that out-of-sequence IDs cause relocation of large number of table rows on every insert - an effect that has no relationship to fragmentation, specificity, uniqueness, or any other property of indexes.

@@zoran-horvat And that video takes a deep dive into clustered indexes. There is no one best practice. It depends how you use your data. Heavy inserts are slower on an ordered clustered index because you are creating a hot spot to the last page. From the author of the video from another source: "First, I ask that you realize my video isn't just about GUIDs. I use Random GUIDs to emphasize many myths and points about all kinds of indexes. Shifting gears back to your question, when you're working on an almost pure OLTP platform and as I say in the video, logical fragmentation isn't going to matter at all. You're going to be looking up one row at a time and that means a trip in the B-Tree from the root thru the intermediate levels of the B-Tree, to the page in the leaf level where your row is. If the index is (for example), 4 levels deep, then you'll do 4 8k page reads for every look IF the index can be used. If not, then a scan of the leaf level is going to occur and I probably don't need to tell you that can be millions of pages. Also, Random GUIDs any better or worse than IDENTITY columns when it comes to finding things UNLESS you're using those things as previously identified keys. They're both surrogate keys and mostly tell you nothing about the data on the row. In both cases, they work great for OLTP IF you already know which key to lookup. For everything else, you're going to need a different column or two to find what you're looking for. The key thing to remember when it comes to "Clustered Indexes" is that "It Depends" and there is no panacea. If your system IS most OLTP then, like I said, Random GUIDs and IDENTITY columns are great even as a Clustered Index. If, however, you're doing "range" lookups by (for example), dates, then the better choice would be to put the Clustered Index on the date column as the first key and the unique surrogate column as the second key column so that you SQL doesn't have to do "Row Lookups" to get the information not carried in a non-clustered index with a date key. Even that isn't a universal recommendation because, just like an IDENTTY column, have a Clustered Index on the data will likely create an Insert "Hot Spot" at the logical last page of the Clustered Index. Even worse and as I mention in the video, it's even worse if you do such inserts (which all go in to 100% page fullness and then they create another page) and those are following by any UPDATE that makes the row larger (which I refer to as "ExpAnsive Updates") before you can rebuild your index at a lower fill factor, you're going to get massive page splitting and the resulting fragmentation. Of course, Random GUIDs don't have either of those issues because all INSERTs are fairly well equally distributed across multiple pages (usually 1 page per row if the table has at least 1 page per row being inserted). EVERYTHING is a tradeoff when it comes to the Clustered Index. If someone starts a recommendation about Clustered Indexes with "It a "Best Practice" that all Clustered Indexes should be...", just walk away because they completely wrong unless they end that with "assigned with great caution because there is no panacea". I also used to believe in the old advice that "every table should have a clustered index". Again, I'll now say "It Depends", especially since that came out with ALTER TABLE REBUILD in 2008. Yep... I know about that nasty old RID being added to non-clustered indexes instead of a nice narrow INTEGER or BIGINT but still "It Depends". So, for OLTP, "It Depends". For Analytics, "It Depends" on something else. Even within Analytics, each table may have (likely WILL have) different requirements to maximize performance and minimize resource usage. I'll also end this with logical fragmentation may or may not make a difference for reads (you need to test), will usually make a difference for writes because even supposed "good" page splits are 4 to 43 times more resource intensive as proven by Paul Randal, and page density should almost always be a primary concern. It's very bad to have a low page density in many cases and it's very bad to have a high page density in others. Again, I know it sounds like a cop out on my part but... "It Depends" and "Must Look Eye" are the only truths when it comes to indexes and the lack there of. Ah... there's one more truth... no matter what you decide on, it will be a compromise... especially for Clustered Indexes and Heaps :D"

@@jonijarvinen7833 My video does not take any dive on clustered indexes. My video is not about clustered indexes. OK?

Actually, EF did nothing here. It was just passing values between the application and the database with no modifications.

No, never.