February 3, 2016 at 4:46 pm
I wondered if anyone has found help changing a primary key fill factor before modifying data type for a column in a large table.
http://dinesql.blogspot.com/2015/03/how-to-change-data-type-of-column-of.html
As it is,, the alter table alter column approach will take hours and even in simple recovery possibly fill up the log.
February 3, 2016 at 8:11 pm
I don't think you can change a data type on a column.
It may look like changing a data type from you visual tool (SSMS, I guess), but what is actually happening behind the scene is creating a tmp table with the new table structure, copying all the data from the existing table to the new one, dropping the old table, renaming the new table ti the original table name.
And it's all wrapped into a transaction.
You can do the same, but with a twist:
1. Create a new table with desired table design.
2. Start populating the new table with portions of data from the existing table.
Choose appropriate size of chunks, so you do not overwhelm your transaction log, and take TRN backups in between of INSERT statements.
It may take many hours or even days - does not matter, as the original table is still operational.
Make sure that any changes made to the data in the original table get copied to the new one.
3. When you reach the point when 99.9% of the data is copied - BEGIN TRANSACTION.
4. Copy the rest of the data to the new table. Check if there was an error.
5. Drop the existing table. Check for an error.
6. Rename the new table to the old name. Check of an error.
7. If anywhere in steps 4,5,6 the @@ERROR was not 0 - ROLLBACK.
8. If you have reached the end of the script with @@ERROR = 0 - COMMIT.
_____________
Code for TallyGenerator
February 4, 2016 at 6:19 am
I'll check out that approach. Thanks. I'll have to look at referential integrity constraints. If the application could handle keeping historical data in the existing column and "data going forward" in a new column ( existing table ) with the integer data type that might also be a possibility.
February 4, 2016 at 5:00 pm
The command to change an existing data type is simple enough to issue:
ALTER TABLE dbo.table_name ALTER COLUMN <new_data_type> NULL --or "NOT NULL" of course
--always explicitly specify NULL or NOT NULL, whichever you need for that column
But preparing properly to issue that command and have things run smoothly can indeed be tricky, although 300M rows really isn't all that many, unless the rows are extremely wide (many bytes long).
1) Verify that you don't already have way too many VLFs in the log file (should already be done :-D, but just in case).
2) Pre-allocate enough log space to handle the entire change by expanding the log size if/as needed.
3) Make sure IFI is on (again, presumably it already is) and make sure the current data file increment is rather large, say 100M or more.
4) Either ALTER the table column directly; OR, much more complex but with much less disruption to current activity, script out the existing table and use that to create a new table, but with the new data type already in place, with just the clus index, copy the existing data to the new table, stop activity on the old table, insert/update/delete any rows added/changed/deleted from the original table since the data was copied, rename the old table, rename the new table to the old table name, and add all other constraints, indexes, triggers as they were on the original table.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
February 5, 2016 at 5:25 am
Thanks Scott. The direct alter table alter column approach would mean adding 250GB or so to our current 250GB transaction log ( spread over 8 equally sized data files on separate Netapp luns ) since the table in question occupies 460GB in production. Not really a way to test this in QA so runtime could be a problem -- locking the table all day Sunday and hoping it finishes in time ( and knowing a rollback would take forever )
I'm trying to see if development can code to allow a new integer column which would either take all new records for this "Line_number" column or only take those with values over 32,767 that can't go in the existing column.
Another option might be to create the new column in the existing table, load it with as much as a year's worth of the most recent values, then rename/drop the original column and rename new column to original.
The Copy To New Table with all of the constraints and gotchas gives me headaches.
On a netapp I never got a straight answer as to whether instant file initialization was a plus -- any event, I haven't been able to convince our storage people to turn it on.
I dealt with VLFs a while ago but can check again.
February 5, 2016 at 6:52 am
Sergiy (2/3/2016)
I don't think you can change a data type on a column.It may look like changing a data type from you visual tool (SSMS, I guess), but what is actually happening behind the scene is creating a tmp table with the new table structure, copying all the data from the existing table to the new one, dropping the old table, renaming the new table ti the original table name.
And it's all wrapped into a transaction.
You can do the same, but with a twist:
1. Create a new table with desired table design.
2. Start populating the new table with portions of data from the existing table.
Choose appropriate size of chunks, so you do not overwhelm your transaction log, and take TRN backups in between of INSERT statements.
It may take many hours or even days - does not matter, as the original table is still operational.
Make sure that any changes made to the data in the original table get copied to the new one.
3. When you reach the point when 99.9% of the data is copied - BEGIN TRANSACTION.
4. Copy the rest of the data to the new table. Check if there was an error.
5. Drop the existing table. Check for an error.
6. Rename the new table to the old name. Check of an error.
7. If anywhere in steps 4,5,6 the @@ERROR was not 0 - ROLLBACK.
8. If you have reached the end of the script with @@ERROR = 0 - COMMIT.
This is not a good thinking. What if you have many schema bound objects with that table?
The advise from ScottPletcher is just fine. Changing from smallint to int should not be a serious problem, unless maybe the time required to finish it all.
Igor Micev,My blog: www.igormicev.com
February 5, 2016 at 7:16 am
A test on a smaller database in QA showed that the alter table alter column approach ran for over an hour before I rolled it back. We have thousands of customers ( banks ) and this change is for the single customer that sends us files with more rows than will fit into a small integer.
One option is just say no, do nothing. If anything I think creating a new column in the existing table, possibly populating it, and then renaming is the option I like. In the table description below, the column in question only shows in the initial Create Table statement ( Line_number )
Production
Table Name------Rows---------Pct DB---Table MB--------Table GB
FILE_RECORD---327,205,035----9------481,706---------470.4
QA
Table Name-----Rows---------Pct DB----Table MB-------------Table GB
FILE_RECORD---214,440,651---7------249,034---------------243.2
CREATE TABLE [dbo].[FILE_RECORD](
[FILE_RECORD_ID] [int] IDENTITY(1,1) NOT NULL,
[LOGICAL_RECORD_DESCRIPTOR_REFERENCE_ID] [varchar](50) NOT NULL,
[RAW_CONTENT] [varchar](3000) NOT NULL,
[CREATED_DATE_TIME] [datetime] NOT NULL,
[LINE_NUMBER] [smallint] NULL,
[MESSAGE_ID] [int] NULL,
[FILE_ID] [int] NULL,
[CONCRETE_TYPE] [varchar](150) NOT NULL,
[LAST_MODIFIER] [int] NOT NULL,
[LAST_MOD_DATE_TIME] [datetime] NOT NULL,
[TRANS_SEQ_NUM] [smallint] NOT NULL,
[MESSAGE_DIRECTION] [char](8) NOT NULL,
[MESSAGE_ERRORERROR_ID] [int] NULL,
[ACCOUNT_DATA_ID] [int] NULL,
[RECORD_CONTEXT] [varchar](10) NOT NULL,
[FILE_DESCRIPTOR_ID] [int] NULL,
[FILE_RECORD_STATUS] [varchar](50) NULL,
[CHECKSUM] [char](48) NULL,
CONSTRAINT [FILE_RECORD_PK] PRIMARY KEY CLUSTERED
(
[FILE_RECORD_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [CM_Data]
) ON [CM_Data]
END
GO
SET ANSI_PADDING OFF
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[ACCOUNT_DATA_FILE_RECORD_FK1]') AND parent_object_id = OBJECT_ID(N'[dbo].[FILE_RECORD]'))
ALTER TABLE [dbo].[FILE_RECORD] WITH CHECK ADD CONSTRAINT [ACCOUNT_DATA_FILE_RECORD_FK1] FOREIGN KEY([ACCOUNT_DATA_ID])
REFERENCES [dbo].[ACCOUNT_DATA] ([ACCOUNT_DATA_ID])
GO
IF EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[ACCOUNT_DATA_FILE_RECORD_FK1]') AND parent_object_id = OBJECT_ID(N'[dbo].[FILE_RECORD]'))
ALTER TABLE [dbo].[FILE_RECORD] CHECK CONSTRAINT [ACCOUNT_DATA_FILE_RECORD_FK1]
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[ERROR_FILE_RECORD_FK1]') AND parent_object_id = OBJECT_ID(N'[dbo].[FILE_RECORD]'))
ALTER TABLE [dbo].[FILE_RECORD] WITH CHECK ADD CONSTRAINT [ERROR_FILE_RECORD_FK1] FOREIGN KEY([MESSAGE_ERRORERROR_ID])
REFERENCES [dbo].[ERROR] ([ERROR_ID])
GO
IF EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[ERROR_FILE_RECORD_FK1]') AND parent_object_id = OBJECT_ID(N'[dbo].[FILE_RECORD]'))
ALTER TABLE [dbo].[FILE_RECORD] CHECK CONSTRAINT [ERROR_FILE_RECORD_FK1]
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FILE_DESCRIPTOR_FILE_RECORD_FK1]') AND parent_object_id = OBJECT_ID(N'[dbo].[FILE_RECORD]'))
ALTER TABLE [dbo].[FILE_RECORD] WITH CHECK ADD CONSTRAINT [FILE_DESCRIPTOR_FILE_RECORD_FK1] FOREIGN KEY([FILE_DESCRIPTOR_ID])
REFERENCES [dbo].[FILE_DESCRIPTOR] ([FILE_DESCRIPTOR_ID])
GO
IF EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FILE_DESCRIPTOR_FILE_RECORD_FK1]') AND parent_object_id = OBJECT_ID(N'[dbo].[FILE_RECORD]'))
ALTER TABLE [dbo].[FILE_RECORD] CHECK CONSTRAINT [FILE_DESCRIPTOR_FILE_RECORD_FK1]
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FILE_FILE_RECORD_FK1]') AND parent_object_id = OBJECT_ID(N'[dbo].[FILE_RECORD]'))
ALTER TABLE [dbo].[FILE_RECORD] WITH CHECK ADD CONSTRAINT [FILE_FILE_RECORD_FK1] FOREIGN KEY([FILE_ID])
REFERENCES [dbo].[FILE] ([FILE_ID])
GO
IF EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FILE_FILE_RECORD_FK1]') AND parent_object_id = OBJECT_ID(N'[dbo].[FILE_RECORD]'))
ALTER TABLE [dbo].[FILE_RECORD] CHECK CONSTRAINT [FILE_FILE_RECORD_FK1]
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[MESSAGE_FILE_RECORD_FK1]') AND parent_object_id = OBJECT_ID(N'[dbo].[FILE_RECORD]'))
ALTER TABLE [dbo].[FILE_RECORD] WITH CHECK ADD CONSTRAINT [MESSAGE_FILE_RECORD_FK1] FOREIGN KEY([MESSAGE_ID])
REFERENCES [dbo].[MESSAGE] ([MESSAGE_ID])
GO
IF EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[MESSAGE_FILE_RECORD_FK1]') AND parent_object_id = OBJECT_ID(N'[dbo].[FILE_RECORD]'))
ALTER TABLE [dbo].[FILE_RECORD] CHECK CONSTRAINT [MESSAGE_FILE_RECORD_FK1]
GO
IF NOT EXISTS (SELECT * FROM sys.check_constraints WHERE object_id = OBJECT_ID(N'[dbo].[FILE_RECORDMESSAGE_DIRECTION_Chk]') AND parent_object_id = OBJECT_ID(N'[dbo].[FILE_RECORD]'))
ALTER TABLE [dbo].[FILE_RECORD] WITH CHECK ADD CONSTRAINT [FILE_RECORDMESSAGE_DIRECTION_Chk] CHECK (([MESSAGE_DIRECTION] = 'OUTGOING' or [MESSAGE_DIRECTION] = 'INCOMING'))
GO
IF EXISTS (SELECT * FROM sys.check_constraints WHERE object_id = OBJECT_ID(N'[dbo].[FILE_RECORDMESSAGE_DIRECTION_Chk]') AND parent_object_id = OBJECT_ID(N'[dbo].[FILE_RECORD]'))
ALTER TABLE [dbo].[FILE_RECORD] CHECK CONSTRAINT [FILE_RECORDMESSAGE_DIRECTION_Chk]
GO
IF NOT EXISTS (SELECT * FROM sys.check_constraints WHERE object_id = OBJECT_ID(N'[dbo].[FILE_RECORDRECORD_CONTEXT_Chk]') AND parent_object_id = OBJECT_ID(N'[dbo].[FILE_RECORD]'))
ALTER TABLE [dbo].[FILE_RECORD] WITH CHECK ADD CONSTRAINT [FILE_RECORDRECORD_CONTEXT_Chk] CHECK (([RECORD_CONTEXT]='Data' OR [RECORD_CONTEXT]='Footer' OR [RECORD_CONTEXT]='Header'))
GO
IF EXISTS (SELECT * FROM sys.check_constraints WHERE object_id = OBJECT_ID(N'[dbo].[FILE_RECORDRECORD_CONTEXT_Chk]') AND parent_object_id = OBJECT_ID(N'[dbo].[FILE_RECORD]'))
ALTER TABLE [dbo].[FILE_RECORD] CHECK CONSTRAINT [FILE_RECORDRECORD_CONTEXT_Chk]
GO
February 5, 2016 at 7:23 am
It sounds like your concerns are to load data from old table into a new table while mimimizing transaction logging, downtime, and risk.
Consider this approach.
1. Using BCP, bulk copy your original table out to a natively formatted file. Becuase we want to conserve disk space, locate the file on a storage drive other than that already used by SQL Server for data or log files. For performance reasons, a locally attached drive would be ideal, if you have one available or can mount one in temporarily.
https://msdn.microsoft.com/en-us/library/ms191232(v=sql.110).aspx
2. Create new table with desired schema and indexes (but not foreign keys or triggers at this point).
3. BCP the file back into the new table. If the new table is initially empty prior to the bulk load, then it should be minimally logged operation. You can read up on MSDN about prerequisites for minimal logging, but this article gives a good summary along with a convenient chart.
http://www.sqlservercentral.com/articles/Administration/100856/
4. After successful import, rename the original table, rename the new table to the original name, and then create foreign keys and triggers (if any) on the new table.
5. Smoke test the application to confirm nothing is broken.
6. Drop the original table (which also should be minimally logged), and then you're all done.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
February 8, 2016 at 3:46 pm
Igor Micev (2/5/2016)
This is not a good thinking. What if you have many schema bound objects with that table?The advise from ScottPletcher is just fine. Changing from smallint to int should not be a serious problem, unless maybe the time required to finish it all.
You obviously don't know what you are talking about.
You never tried it on a big table, so for you it "should not be a serious problem".
Increasing capacity of a column in table means a split on every page occupied by the table.
Which means effectively doubling the space needed to accommodate the table.
With corresponding requirements to the log file.
With "maybe the time required to finish it all" - hours and hours for the system to be offline.
Just to find out that after 20 hours the system has run out of space and started reversing the transaction.
Which will take almost twice as long.
By the end of the week, when the system is back online, your stuff is already packed into a box and ready for you to carry it away.
Scott expects something to happen, that's why he included the item 4 into his plan, which repeats my plan in shorter terms.
As for dependencies - it's not that hard to find them and script dropping and recreating.
Actually, SSMS can do it all for you.
Open the table in Design view, alter the column type and do not save but hit the "Generate Change Script" button - here is the script you can start from.
But schema bound objects is not the only one associated problem you need to solve.
Any procedure, any view or function, any script in SSIS which mentions this column needs to be revised not to generate run time errors or performance issues due to implicit data type conversions caused by the new data type used for the column.
Changing schema of a live database should not be taken lightly.
_____________
Code for TallyGenerator
February 8, 2016 at 4:46 pm
Sergiy:
"By the end of the week, when the system is back online, your stuff is already packed into a box and ready for you to carry it away."
I fell out of my chair when I read that :w00t::w00t:
My favorite choices now, in order:
1) do nothing, tell the one customer no
2) add a column with proper data type, rename old column to something like "archive", rename new column to old name, check all dependencies, copy most recent months of data into new column from archive.
I haven't found any triggers, procedures, foreign keys or SSIS that use this column. There is an index which would have to be dropped and re-created.
Any other changes to .net C# code would be the developer's responsibility.
February 8, 2016 at 6:20 pm
Hi Indianrock,
I see you liked the horizons opening in front of you. :hehe:
How far numbers of rows for those odd files go over the limit of 32768 the ?
If you can say they'll stay under 64k then you may use simple workaround:
Positive numbers use only half of the data type capacity.
So, do this simple trick to use another half:
DECLARE @i INT, @cap INT
SET @cap = 32768
SET @i = 60000
SELECT CONVERT(SMALLINT, CONVERT(BINARY(2), @I) )
You'll end up storing negative numbers for row numbers over 32767 (the limit for smallint)
When you need to get the original row number from a stored negative value, do the reversing transformation:
DECLARE @Si smallint
SET @Si = -5536
SELECT CONVERT(INT, CONVERT(BINARY(4), @Si))
If you expect the number of rows to go over the limit of 64k you can still store smallint values.
If you force order of records during INSERT into the table by RowNumber from the source file then you can easily restore the lost Row_Number values by counting FILE_RECORD_ID within the same FILE_ID.
_____________
Code for TallyGenerator
February 8, 2016 at 6:25 pm
I must add - after applying those tricks any existing code relying on the current RowNumber becomes irrelevant and needs to be revised.
_____________
Code for TallyGenerator
February 9, 2016 at 3:36 am
Sergiy (2/8/2016)
Igor Micev (2/5/2016)
This is not a good thinking. What if you have many schema bound objects with that table?The advise from ScottPletcher is just fine. Changing from smallint to int should not be a serious problem, unless maybe the time required to finish it all.
You obviously don't know what you are talking about.
You never tried it on a big table, so for you it "should not be a serious problem".
Hm, not true. I've tried it on huge and small tables. It depends on the updates how frequently are done in the table.
Increasing capacity of a column in table means a split on every page occupied by the table.
Which means effectively doubling the space needed to accommodate the table.
With corresponding requirements to the log file.
With "maybe the time required to finish it all" - hours and hours for the system to be offline.
If it's a few-columns table, then for sure it won't take for hours, even not for minutes. If it's a 20+ columns table, then space won't be doubled by just increasing one column from smallint to int. And yes, the transaction log file will increase alot.
Just to find out that after 20 hours the system has run out of space and started reversing the transaction.
Which will take almost twice as long.
By the end of the week, when the system is back online, your stuff is already packed into a box and ready for you to carry it away.
Scott expects something to happen, that's why he included the item 4 into his plan, which repeats my plan in shorter terms.
It's not the point to do the operation in such long time.
What if your table get updates every millisecond? Such tables by nature should be small, otherwise your table will be huge. You usually have scheduled archive data process.
If the table is just big and with not frequent updates, then your approach is just fine.
As for dependencies - it's not that hard to find them and script dropping and recreating.
Actually, SSMS can do it all for you.
Open the table in Design view, alter the column type and do not save but hit the "Generate Change Script" button - here is the script you can start from.
But schema bound objects is not the only one associated problem you need to solve.
Any procedure, any view or function, any script in SSIS which mentions this column needs to be revised not to generate run time errors or performance issues due to implicit data type conversions caused by the new data type used for the column.
Changing schema of a live database should not be taken lightly.
This very depends on the density of the dependencies with the other objects in the database. There are databases with 50 tables and with 500 tables. It's different.
I'm aware of your approach, anyway.
How would you do your approach for table that is updated in the second? You may lose consistency of data, especially if the table points many foreign keys in other tables.
Finally, if the operation lasts for a time that is not acceptable, then this is also a good approach:
My favorite choices now, in order:
1) do nothing, tell the one customer no
2) add a column with proper data type, rename old column to something like "archive", rename new column to old name, check all dependencies, copy most recent months of data into new column from archive.
Igor Micev,My blog: www.igormicev.com
February 9, 2016 at 6:26 am
Thanks for all of the feedback.. I'll check with development to see how large they expect line numbers to get ( I guess the @cap variable was just for documentation purposes )
Example I ran in QA
-- insert into File_Record Line_Number
DECLARE @NEWVALUE INT, @cap INT
SET @cap = 32768 ----- small integer can't reach this value
SET @NEWVALUE = 60000
INSERT INTO dbo.FILE_RECORD_TEST
( LOGICAL_RECORD_DESCRIPTOR_REFERENCE_ID,LINE_NUMBER )
SELECT 'LOGICAL_RECORD_DESCRIPTOR_REFERENCE_ID_value',CONVERT(SMALLINT, CONVERT(BINARY(2), @NEWVALUE) )
SELECT * FROM dbo.FILE_RECORD_TEST --- you now have negative 5536 stored in Line_number representing 60,000
-- select from file_record Line_number
DECLARE @StoredLineNumber smallint
DECLARE @ActualLineNumber int
Select @StoredLineNumber = Line_number FROM dbo.FILE_RECORD_TEST WHERE FILE_RECORD_ID=1
SELECT @actualLineNumber= CONVERT(INT, CONVERT(BINARY(4), @StoredLineNumber))
SELECT @StoredLineNumber,@ActualLineNumber
@StoredLineNumber @ActualLineNumber
-553660000
February 9, 2016 at 6:34 am
Igor Micev (2/9/2016)
Increasing capacity of a column in table means a split on every page occupied by the table.
Which means effectively doubling the space needed to accommodate the table.
With corresponding requirements to the log file.
With "maybe the time required to finish it all" - hours and hours for the system to be offline.
If it's a few-columns table, then for sure it won't take for hours, even not for minutes.
It does not matter how many columns in the table.
Here is everything we need to know about the table:
Production
Table Name------Rows---------Pct DB---Table MB--------Table GB
FILE_RECORD---327,205,035---- 9------481,706---------470.4
Half a terabyte remains half a terabyte, either in 2 or in 52 columns.
If your server can rewrite 0.5 TB of data (actually much more, but let's go with the min amount) + record at least 1TB of Log within minutes - we all are so happy for you.
Not every one has such a power on his/her side.
If it's a 20+ columns table, then space won't be doubled by just increasing one column from smallint to int. And yes, the transaction log file will increase alot.
Adding 1 (one) byte to varchar values in every record will double the space occupied by data pages.
Because every data page will split.
If the pages were not padded before that, of course.
And because we know that the table is used for loading data from external files with no further data manipulation it's safe to assume that the data is recorded continuosly and it's on nearly 100% density.
It's not the point to do the operation in such long time.
What if your table get updates every millisecond? Such tables by nature should be small, otherwise your table will be huge. You usually have scheduled archive data process.
Read my plan once again.
It clearly says what to do with records added/updated/deleted while data migration is in progress.
And it's not the point of the discussion how big should be the table.
It's 0.5TB. We have to deal with it.
As for dependencies - it's not that hard to find them and script dropping and recreating.
Actually, SSMS can do it all for you.
Open the table in Design view, alter the column type and do not save but hit the "Generate Change Script" button - here is the script you can start from.
But schema bound objects is not the only one associated problem you need to solve.
Any procedure, any view or function, any script in SSIS which mentions this column needs to be revised not to generate run time errors or performance issues due to implicit data type conversions caused by the new data type used for the column.
Changing schema of a live database should not be taken lightly.
This very depends on the density of the dependencies with the other objects in the database. There are databases with 50 tables and with 500 tables. It's different.
Don't see how it's different.
Longer script to submit - that's all.
And I don't understand why do you worry about number of tables only.
What about 2000 procedures, a quoter of which referencing the table?
How would you do your approach for table that is updated in the second? You may lose consistency of data, especially if the table points many foreign keys in other tables.
I actually have it done. Several times.
And 2 of those times I had to change heavily updated tables on databases with no down time.
I used triggers + a background process which was syncronysing records queued by those triggers.
Yes, there were times when the background process was not coping with the load, and discrepancies were growing for some time.
But eventually it's got to the point when it was acceptable to lock the tables for a minute, finalise data migration, run deployment scripts and complete the task.
Finally, if the operation lasts for a time that is not acceptable, then this is also a good approach:
My favorite choices now, in order:
1) do nothing, tell the one customer no
if you have a luxury of such a choice
2) add a column with proper data type, rename old column to something like "archive", rename new column to old name, check all dependencies, copy most recent months of data into new column from archive.[/i]
"Add a column" - are you sure it won't add any data to every record of the table, causing splits on every data page?
"Rename new column to old name" - can you provide a script for such an operation?
You see, you're missing the obvious points.
Which means - you did not think it through.
Which means - you've never actually done it on a live table of a significant size.
I'm sayng that to show - I did not say:
"You obviously don't know what you are talking about.
You never tried it on a big table, so for you it "should not be a serious problem"
just to say it.
If you'd try to apply your suggestions once on a really big table - you'd change your view on the subject dramatically.
_____________
Code for TallyGenerator
Viewing 15 posts - 1 through 15 (of 45 total)
You must be logged in to reply to this topic. Login to reply