In the last hour, I’ve been told
- Don’t use SYSDATE in inserts/updates because although it’s bog standard SQL (I’m not sure if it’s ANSI standard, but it’s pretty common), it’s not supported by all Microsoft databases (even though we only use Oracle in this project)
- Do use VARCHAR2 instead of VARCHAR, because although VARCHAR2 is only supported by Oracle, and in the current release it’s just a synonym for VARCHAR, some day it’s going to have different comparison semantics than VARCHAR and Oracle says to use it, so we have to use it.
I’ve also been told that these two tables, which I have to populate by hand using manually taking the rows and columns of a spreadsheet and writing “INSERT…VALUES(‘row’,’column’,’value’)” statements several hundred times, can’t use semantic primary keys because they want to use UUIDs. So instead of looking at the spreadsheet and seeing that in the row labelled “Insurance” and the column labelled “Security” that the value is “V” and converting that to “INSERT … VALUES(‘insurance’,’security’,’V’)”, I’ve got too look up the uuid for the Insurance row and the uuid for the Security column, and change that to “INSERT … VALUES(‘6BAC51EC-C636-4C31-9E95-367062AC23F7′,’C78BF79B-3178-4F07-ACD3-92DF2742C932′,’V’)”. And I’ve got to do that several hundred times. Yes, that seems *much* less error prone that using keys I can actually tell what they mean and easily tell if I’ve got the wrong one. Oh, and even better, the code that uses the information in this table will have to hit the database to look up these uuids so that they can find the value of the “insurance” versus “security” instead of just coding those values directly.
SYSDATE is an Oraclism, I think. CURRENT_TIMESTAMP is the SQL standard equivalent.
For the INSERT …. VALUES(), could you not perform the name->uuid
mapping as a nested select, as in ..
INSERT …. VALUES ((SELECT ID FROM NAMETABLE WHERE NAME=’foo’), …)
i.e., make the database do the work.
I’d export the IDs and relevant identifying values to an Excel spreadsheet, add the insertable values in additional columns, and build the SQL statement file from those columns.
This is the point at which I, as a professional, push back against some idiocy and for ANSI standard SQL.
as someone else said, CURRENT_TIMESTAMP is the ANSI SQL, VARCHAR is the ANSI data type, and databases are designed to be used by humans.
in most cases UUIDs create a lot of work for the programmer to ensure they do not have duplicate values in the database rather than relying on the database to maintain consistency. If others argue that comparing UUIDs is faster than comparing (say) a timestamp or something else then they haven’t been around the internals of databases for very long. (for example database engines are very good at optimising queries and caching data and there is a battle for performance between the database vendors where they have way more programmers trying to get good performance then most companies have programmers trying to write code). Next, benchmark… if using semantic primary keys yields good enough performance then you may have saved yourself days of tuning and debugging code.
Finally, just because you wrote the query as “where a.key=’insurance'” doesn’t mean that is what the database executed; the database probably has indexes and if this is a probably key fetches an index hash that it uses in the rest of the execution plan (i.e. using something like the uUID internally without you having to worry at all about it!). One has to free ones mind from how one as a programmer would write code to execute a query and ‘hope’ the database engine does the right thing. I find that many procedural programmers can’t grasp that they need to me more expressive with SQL and less explicit.