How does a SQL Server database project: publication determines when to recreate a table?

Question

I have a SQL Server database project in which I made a few changes to the schema where I changed the column data types from NUMERIC (18,0) to INT . We are trying to normalize the data type used for primary keys, currently 50/50.

When I create a publication script, some of the tables are recreated in the script:

 CREATE TABLE [dbo].[tmp_XYZ] INSERT TABLE [dbo].[tmp_XYZ] SELECT ... FROM [dbo].[XYZ] DROP TABLE [dbo].[XYZ] sp_rename N'[dbo].[tmp_XYZ]', N'XYZ'; 

but other tables are just updated with ALTER statements

ALTER TABLE [dbo].[ABC] ALTER COLUMN [AbcID] INT NULL;

Is there any rule that dictates when a table will be recreated, and when it will just change in place?

Answer 1

Perhaps the best way is to right-click on the name of your object and select the script as ... Then you have options for creating or changing If you could not find Alter, you can go to the design, right-click and select Generate Change script ... to find the alter operator.

Answer 2

This is a simple problem. This is the same problem as changing the table in the table designer. I think you changed the column inside your spreadsheet design, which should drop and recreate the table to allow the column order in the same position.

Here is a brief example. Take this table design as indicated:

 CREATE TABLE dbo.Test( id int identity(1,1), firstname nvarchar(100), name nvarchar(100), street nvarchar(100) ) 

This will create the columns in the specified order. You can see this order here:

 SELECT name, column_id FROM sys.columns WHERE object_id = OBJECT_ID(N'dbo.Test') 

You will see something like this:

 column_name column_id id 1 firstname 2 name 3 street 4 

If you change the name column through the constructor or in your case in the data project, this will force SQL Server to get this order upright. In this case, you will try to change the name column to lastname . This will ensure that SQL Management Studio and other similar programs run to keep column_id upright. This can only be done if the table is completely recreated with the correct column order. SQL Server creates a temporary stub for the table, inserts everything into it, discards the old table, and renames the temporary table to the old original name. As in your code above.

After that you will see something like this:

 column_name column_id id 1 firstname 2 lastname 3 street 4 

If you just rename the last column or do it manually, everything will be fine. Manually will be much more efficient since there is no need to move ALL data to a new table. The manual way would be the following:

 -- Create the new column ALTER TABLE dbo.Test ADD lastname nvarchar(100) GO -- Populate the new column using the old one UPDATE dbo.Test SET lastname = name GO -- Drop the old column afterwards ALTER TABLE dbo.Test DROP COLUMN name 

This will result in the following result:

 column_name column_id id 1 firstname 2 street 4 lastname 5 

The latter will be much more effective, as already mentioned.

I hope this answers your question, even if the answer comes recently.