Finally, Create or Alter

Question

Finally, Create or Alter

Viewing 4 posts - 46 through 48 (of 48 total)

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

Bill Talada SSChampion Points: 11954 More actions · Answer 1

ZZartin - Thursday, January 19, 2017 8:55 AM
Steve Jones - SSC Editor - Thursday, January 19, 2017 8:49 AM
Nelson Petersen - Wednesday, January 18, 2017 12:31 PM
Steve,
To expand this slightly, metadata, code and data could all considered to be data.
When you update data, do you update all values or only those that are changing?
I prefer to update only what is changing.
Nelson Petersen
Except that's not how we code. If we change a method, or a stored procedure, we show the entire procedure. We change something, and compile the entire thing. With a table, all the existing items are hidden. We aren't changing data here, we are changing the code that will manage our data.
If we want to say that code is data, then why do we submit all the data with the changes?
Yes but pretty much every programming language has the concept of individual components that comprise a whole system, even something as simple as batch files has the concept of calling individual batch files that can be modified individually without having to view, deploy or even necessarily understand the whole thing. That's how I see columns in tables.

I see database objects as true object oriented OBJECTS. A table is much more than a structure definition; it is an object. Defaults, validations, constraints, filtered indexes, and triggers are all part of a full "table" definition and all affect the outcome of DML statements. Validation changes may require rows to first be updated before being set to enabled. Even settings such as collation or ANSI can dramatically affect existing data.

In every aspect of life there seems to be STATE and PROCESS. The current STATE "A" goes through a PROCESS to get to STATE "B". Between STATE "A" and "B" everything is indeterminate. Afterwards, STATE "B" rules can be enforced. What I'm trying to say is that there is no true separation of code and data without a loss of meaning and information.

Nelson Petersen SSC Enthusiast Points: 195 More actions · Answer 2

Steve Jones - SSC Editor - Thursday, January 19, 2017 8:49 AM
Nelson Petersen - Wednesday, January 18, 2017 12:31 PM
Steve,
To expand this slightly, metadata, code and data could all considered to be data.
When you update data, do you update all values or only those that are changing?
I prefer to update only what is changing.
Nelson Petersen
Except that's not how we code. If we change a method, or a stored procedure, we show the entire procedure. We change something, and compile the entire thing. With a table, all the existing items are hidden. We aren't changing data here, we are changing the code that will manage our data.
If we want to say that code is data, then why do we submit all the data with the changes?

Code is stored in the database. A database stores data. Therefore, code is data.
Of course, it is very special data. I think code could be considered to be a very special type of string.
All the formatting (linefeeds and spacing) that is added to make it easy to read makes us think it is something else.

>>> update code set code_string='new_value'; go
The dbms does not allow us to update "line 3128 of code#965", which is why we appear to be submitting "all the data with the changes."
It forces us to update the whole string, not just one part.

I'm not completely convinced by this idea of code as data, but I wonder about it. It might not totally fit, but . . .

Steve Jones - SSC Editor SSC Guru Points: 736212 More actions · Answer 3

Code certainly is data, but we handle it differently.

I think people are just stuck on the idea an ALTER TABLE containing the changes because that's what we've had, historically, for decades. We've never seen anything else, and we see the trouble this causes.

I still think we should see ALTER TABLE with the entire statement, which would be even better with CREATE OR ALTER, since then a diff of two table statements from a VCS would easily show the progression of things. The vendors could certainly implement better handling of renames, column order swaps, etc. In fact, most of that shouldn't touch a db and be a simple metadata update.

Bill Talada SSChampion Points: 11954 More actions · Answer 4

Steve Jones - SSC Editor - Friday, January 20, 2017 7:27 AM
Code certainly is data, but we handle it differently.
I think people are just stuck on the idea an ALTER TABLE containing the changes because that's what we've had, historically, for decades. We've never seen anything else, and we see the trouble this causes.
I still think we should see ALTER TABLE with the entire statement, which would be even better with CREATE OR ALTER, since then a diff of two table statements from a VCS would easily show the progression of things. The vendors could certainly implement better handling of renames, column order swaps, etc. In fact, most of that shouldn't touch a db and be a simple metadata update.

Code is PROCESS and Data is STATE. All input must flow through a process to become persisted as data in a database. There is one instance of code while many data have flowed through that one instance of code to be persisted.

The difficulty in comprehending this is that we are dealing with a holarchy. Look at the many holarchical levels in your laptop: BIOS, Bootstrap, Basic OS, GUI, Apps like Sql Server, Database, Database Code, and Persisted Data. Notice how at each adjacent level, one is considered Code, and the next is considered Data. What was data is now considered code when you shift forward one level. This is relative from your point of view of where you are working. In the end, it is all just bits in bytes and all the meaning is an illusion in your head.

If you think of a chain of foreign keys such as Parent, Child, Grand Child, Great Grand Child and you ask which is the parent then you can see there are actually three parents here and three children relatively speaking.