...
- SQL engines often default to using the primary key as the “Clustered Index”, which means it physically stores the records in that order. If you’re assigning random values, then the server will be reworking the file regularly to get those records in order. Therefore, you must explicitly tell your database to use a different key as the clustered key. (By the way, I've read that choosing the best key for clustering is one of the best techniques for efficient queries, and supposedly the primary key isn't usually the best key, so this is something you should ponder regardless.)
- According to Bruce Johnson at Capesoft, the speed impact is small, but I’ve spoken with administrators working on large databases with many new transactions each day, and they were uneasy about using this approach.
- Long strings take up more space than compact integers, so your database will be bigger (and consequently slightly slower).
- When debugging, working with random strings isn’t as easy as sequential numbers.
- On SQL Server the primary clustered key is included for all other index entries, so if you do use a GUID with a clustered primary key (the default) then you're potentially using a lot more data storage.
Do I Really Have to Change?
...
In this first article I’ll list the key areas that you must address, followed by a series of articles with the specific steps using a variety of database engines (PostgreSQL, Microsoft SQL Server, SQLite…). You can read this initial overview, then jump to the corresponding article for your engine of choice. I doubt that I’ll cover all possible back ends, but hopefully I'll visit enough to help the majority of Clarion users.
...
- Creating your Table in SQL – When working with ISAM files, we you probably use the Clarion CREATE command to create the file on disk. You don’t want to do this with SQL, as it doesn’t communicate all of the attributes that you’ll want in your SQL table. Instead, you must do it with should use a CREATE TABLE script. You’ll usually need additional commands to start and/or finish the task. You could write these by hand, or make use of DMC or FM3 to do this. I’ll be providing sample scripts with the engine specific articles, so you can fully understand how things work together.
- Types of Auto Numbering – Depending on your backend, you may use of “sequences” or “identities” (or something by yet another name) to generate the unique IDs. Some engines support both, while others can do only one or the other. They may not be called that on all back ends.
- Sequence – Think of this as a counting service. You create a sequence resource, then define the table so it uses that resource for its auto-number. These tend to provide more flexibility and control, but are trickier to setup. They can also be used for those “meaningful numbers” that the users want to see, like invoice IDs. (If your backend doesn’t support sequences and you still want to use them, you can roll your own with a manual counter field and a few stored procedures.)
- Identity – This is essentially an auto-numbered field. It’s usually done without a separate “sequence” facility, and may not provide as much control and flexibility as you may desire. I’m a control freak, so I generally I prefer to use sequences when the backend supports them.
- Primary Field – In most cases your auto-numbered Primary key field will be defined as a LONG, but there are additional settings required (especially user options), which vary depending on the backend, whether you’re using FM3, etc.
- Primary Key – You still define your key as Unique and Primary, but you turn OFF the Auto-Number switch. As with the primary field, there are additional attributes and options that must be specified.
- File Options – The driver string needs additional elements to tell it how to retrieve the newly assigned auto-number. There are a few other driver string attributes that I’ll discuss as well.
...
I’ve found that auto-numbering is a far more complex topic that I initially expected. I’m trying to distill all of those complexities, options and decisions into a simple set of rules. If you feel that I’ve I missed anything crucial or I’ve misspoke, then please feel free to provide feedback. I’ll occasionally update this article as my understanding and viewpoint evolves.