November 30, 2016 at 7:56 am
Steve Jones - SSC Editor (11/29/2016)
ZZartin (11/29/2016)
What about cases where you don't necessarily want to sync up the tables exactly, there's advantages to having a simple make tables match kind of command but unlike stored procedures or views I often do want to manipulate individual columns without having to potentially affect the entire table.
What cases do you mean? How would a CREATE OR ALTER where you specify the definition cause you to be unable to manipulate a single column? You could use multiple CREATE OR ALTER statements if needed. At the end of the day, this means my code and my table evolution looks like every other type of source code over time.
But tables aren't source code they're part of the infrastructure of the system. And I haven't had the luxury of being able to do a complete environment to environment migration in a long long time, far more often I have multiple projects interacting with the same tables that all have different roll out dates so being able to manipulate individual columns is important. Trying to manage that with complete table replaces would be a nightmare compared to just altering columns as needed.
November 30, 2016 at 8:18 am
Jeff Moden (11/29/2016)
Treating a table like code makes no sense.
Why? I ask not as a challenge, but to understand your reasoning. I have generally leaned towards treating tables as code, just like stored procedures, functions, and indeed all schema objects. A table is defined by code, checked in to source control as code, deployed by SSDT as code. Yes it has sub objects (columns, triggers, indices etc) that can be treated somewhat independently, but that doesn't take away from the nature of the table as a code-defined object. I should note that we are not talking about the data inside the table, but the structure and definition of the table itself.
Respectfully, having to issue 140 lines of code to change one column in a 140-column table, as opposed to issuing one line of code, is hardly a reason to treat it as not-code.
Hakim Ali
www.sqlzen.com
November 30, 2016 at 8:26 am
Ed Wagner (11/30/2016)
KenpoDBA (11/29/2016)
If we could get something like the following we could also avoid most of the dynamic-sql out there:select * from @DBName.dbo.T1;
update @DBName.dbo.T1;
select * from @DBName.sys.database_files;
etc.
They force us to use dynamic-sql for ridiculous reasons when a simple replacement like that would not only take most of that away, but would also make the code so much more readable.
I have to agree with you 100% on that one. While CREATE OR ALTER is nice, your suggestion would make a far more tangible difference. Further, I'd extend it to:
SELECT @ColumnList FROM @DBName.dbo.@TableName;
I have no idea how this would be implemented in the plan cache, unless they treated each expanded version of the query independently rather than the version as written. If not, it could give a whole new meaning to parameter sniffing. It would certainly be an interesting trip under the hood.
One more extension of that thought to make it completely useful...
SELECT @ColumnList FROM @DBName.@SchemaName.@TableName;
I can just see the CONNECT item for such a thing now, though. "WORKAROUND: Use sp_MSForEachTable." and "Marked as functions as designed", which is totally ridiculous.
--Jeff Moden
Change is inevitable... Change for the better is not.
November 30, 2016 at 9:10 am
Jeff Moden (11/29/2016)
[It's a lot more difficult than just altering a table to alter a single column. The reason why tables are handled differently than store procedures, functions, and views is that tables have easily identifiable "sub-entities" (attributes) known as columns. There isn't an easy way to identify the parts of a programmable object that have been changed.
Treating code like code is one thing. Treating a table like code makes no sense.
That's not true. That's only in the current framework. We could easily have a structure like a "-Whatif" in PoSh that lets you know what changes will be made if you need to know. We also have lots of diff tools that programmers use to solve this problem. It's only the programmers that don't bother using current practices that struggle here.
It's (barely) like a carpenter that doesn't actually use known methods to ensure both sides of a structure are cut to the same angle. They cut one, then guess or re-measure the second, not using the known size to cut the second.
However, if you decide this is the code you want, why shouldn't a table just be coded as the current structure you want to appear at the end?
November 30, 2016 at 9:18 am
ZZartin (11/30/2016)
But tables aren't source code they're part of the infrastructure of the system....
Trying to manage that with complete table replaces would be a nightmare compared to just altering columns as needed.
Ah, perhaps that's where we differ. Tables are code. And they're structures. We're supposed to worry about logical modeling of tables (code) and not physical (infrastructure). There's not a great separation in SQL Server at times, as we can get caught up with physical layouts due to various options (partitioning, indexes, etc).
However, Microsoft wants to ease that burden. At least, that's what they constantly try to do for the developer. In that case, shouldn't we treat tables like code? Could we have ways to code this that allow us to specify the end result (CREATE OR ALTER) at development time.
At deployment time, the actual implementation could be controlled by settings at the instance/db/table level that determine how the change might minimally affect the system.
Again, the way things happen today in SQL Server 2012-SQL Server 2016 isn't the way they need to happen in the future. MS controls the platform and code, so they could implement something that would have less impact on the application.
November 30, 2016 at 11:13 am
Jeff Moden (11/30/2016)
Ed Wagner (11/30/2016)
KenpoDBA (11/29/2016)
If we could get something like the following we could also avoid most of the dynamic-sql out there:select * from @DBName.dbo.T1;
update @DBName.dbo.T1;
select * from @DBName.sys.database_files;
etc.
They force us to use dynamic-sql for ridiculous reasons when a simple replacement like that would not only take most of that away, but would also make the code so much more readable.
I have to agree with you 100% on that one. While CREATE OR ALTER is nice, your suggestion would make a far more tangible difference. Further, I'd extend it to:
SELECT @ColumnList FROM @DBName.dbo.@TableName;
I have no idea how this would be implemented in the plan cache, unless they treated each expanded version of the query independently rather than the version as written. If not, it could give a whole new meaning to parameter sniffing. It would certainly be an interesting trip under the hood.
One more extension of that thought to make it completely useful...
SELECT @ColumnList FROM @DBName.@SchemaName.@TableName;
I can just see the CONNECT item for such a thing now, though. "WORKAROUND: Use sp_MSForEachTable." and "Marked as functions as designed", which is totally ridiculous.
Your principle is definitely sound, but I don't think they'd recommend that procedure by name because then it would be mentioned, which means it would be documented, which means they'd have to support it. Naturally, they would have more Connect items open to fix the darn thing.
November 30, 2016 at 4:27 pm
hakim.ali (11/30/2016)
Jeff Moden (11/29/2016)
Treating a table like code makes no sense.Why? I ask not as a challenge, but to understand your reasoning. I have generally leaned towards treating tables as code, just like stored procedures, functions, and indeed all schema objects. A table is defined by code, checked in to source control as code, deployed by SSDT as code. Yes it has sub objects (columns, triggers, indices etc) that can be treated somewhat independently, but that doesn't take away from the nature of the table as a code-defined object. I should note that we are not talking about the data inside the table, but the structure and definition of the table itself.
Respectfully, having to issue 140 lines of code to change one column in a 140-column table, as opposed to issuing one line of code, is hardly a reason to treat it as not-code.
Try to execute a table and see why. You might be able to execute what's in a table but you cannot execute a table because... it's not code and shouldn't be treated like code.
--Jeff Moden
Change is inevitable... Change for the better is not.
November 30, 2016 at 4:53 pm
I would argue that
ALTER TABLE MyTable Add SalesID int
is code. It gets compiled and executed against a physical structure on disk.
November 30, 2016 at 10:49 pm
The code defines only the metadata.
Apart from metadata there is user data in any table.
What happens to it when you execute "create or alter"?
What if the code does not contain a column which has been added recently?
Let's say it: updating static tables on fly is a terrible idea, and it should never be allowed.
In a multi-user environment altering a table by 2 or more users at the same time - yes, it's not something which should be placed in a stored procedure code.
So, we can only talk about deployment scripts here.
And deployment scripts for altering table schemas are generated either by dedicated tools (like DB Diff), or by SSMS (Script Action to ...), or manually be developers.
And ALTER TABLE would be the tiniest part of those scripts.
Accommodating the existing data to the new table design, managing data type compatibility, conversions, collations, nullability, other constraints, etc. - that what altering table is about.
Will "Create or Alter" take care of it all?
Obviously not. It was not made for doing a proper job.
_____________
Code for TallyGenerator
December 1, 2016 at 8:40 am
The way I think of it is the following:
- a stored procedure is a container for code that executes on request
- a trigger is a container for code that executes on event
- a view is a container for a query
- a table is a container for data
Yes we use code to create, alter, or maintain all those different types of container objects, but that doesn't mean that what those objects contain is also code.
January 18, 2017 at 12:31 pm
Steve Jones - SSC Editor - Tuesday, November 29, 2016 4:44 PMJeff Moden (11/29/2016)
Heh... yeeaaahhh... that's what I want to do for a 140 column table. 😉If you're treating code like code, then you'd always have the definition of the table. How is this hard to manage?
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
January 19, 2017 at 2:21 am
Nelson Petersen - Wednesday, January 18, 2017 12:31 PMSteve,
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
Code as data? What does that mean to you? How does that differ to treating code as code?
Gaz
-- Stop your grinnin' and drop your linen...they're everywhere!!!
January 19, 2017 at 6:11 am
Gary Varga - Thursday, January 19, 2017 2:21 AMNelson Petersen - Wednesday, January 18, 2017 12:31 PMSteve,
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
Code as data? What does that mean to you? How does that differ to treating code as code?
Next it will be data as code! 🙂 Think "Codd and Date" who started us down this track might have something to say! :cool
January 19, 2017 at 8:49 am
Nelson Petersen - Wednesday, January 18, 2017 12:31 PMSteve,
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?
January 19, 2017 at 8:55 am
Steve Jones - SSC Editor - Thursday, January 19, 2017 8:49 AMNelson Petersen - Wednesday, January 18, 2017 12:31 PMSteve,
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.
Viewing 15 posts - 31 through 45 (of 48 total)
You must be logged in to reply to this topic. Login to reply