locked
Why does adding a new column cause indexes to be dropped?

    คำถาม

  • I am using VS 2010 and TFS to add a new nullable ModifiedByUserID column to the AdventureWorks (2005) Person.Address table. When I build and deploy the solution, the build script drops and recreates a multitude of objects that do not need to be dropped. When I run a comparison between the project and the target database using Schema Compare, however, the update script does not do this.

    Are there options that allow me to have more control over the build script? The script it's generating now is unusable to us for large tables.

    Here is the only command that I can see needs to be executed:

    • ALTER TABLE [Person].[Address] ADD [ModifiedByUserID] INT NULL

    Here are the objects that are dropped and re-created by the build script:

    • INDEX [IX_Address_AddressLine1_AddressLine2_City_StateProvinceID_PostalCode]
    • INDEX [AK_Address_rowguid] INDEX [IX_Address_StateProvinceID]
    • CONSTRAINT [DF_Address_ModifiedDate]
    • CONSTRAINT [DF_Address_rowguid]
    • CONSTRAINT [FK_VendorAddress_Address_AddressID]
    • CONSTRAINT [FK_SalesOrderHeader_Address_BillToAddressID]
    • CONSTRAINT [FK_SalesOrderHeader_Address_ShipToAddressID]
    • CONSTRAINT [FK_CustomerAddress_Address_AddressID]
    • CONSTRAINT [FK_EmployeeAddress_Address_AddressID]
    • CONSTRAINT [FK_Address_StateProvince_StateProvinceID]
    • CONSTRAINT [PK_Address_AddressID]

    Thanks,
    Aaron

    14 ตุลาคม 2553 19:29

คำตอบ

  • To update everyone, Gert and I went back and forth on this a few more times via email. I even emailed him the project I was using. He was unable to reproduce the problem I was seeing. I decided to see how my actual source code would handle the addition of a column, and oddly enough it does not unnecessarily drop and re-create the indexes, etc. I would still like to know what's causing this in AdventureWorks, but for right now since it's not occurring in our real source code I'm going to hold off on further research.

    Thanks for your help Gert.

    Aaron

     

    18 ตุลาคม 2553 19:17

ตอบทั้งหมด

  • Just tried this by adding the column, this was the result

    CREATE TABLE [Person].[Address] (
      [AddressID]    INT        IDENTITY (1, 1) NOT FOR REPLICATION NOT NULL,
      [AddressLine1]  NVARCHAR (60)   NOT NULL,
      [AddressLine2]  NVARCHAR (60)   NULL,
      [City]      NVARCHAR (30)   NOT NULL,
      [StateProvinceID] INT        NOT NULL,
      [PostalCode]   NVARCHAR (15)   NOT NULL,
      [SpatialLocation] [sys].[geography] NULL,
      [rowguid]     UNIQUEIDENTIFIER ROWGUIDCOL NOT NULL,
      [ModifiedDate]  DATETIME     NOT NULL,
    	[ModifiedByUserID] INT 				NULL 
    );
    
    PRINT N'Altering [Person].[Address]...';
    
    
    GO
    ALTER TABLE [Person].[Address]
      ADD [ModifiedByUserID] INT NULL;
    
    
    GO
    PRINT N'Refreshing [HumanResources].[vEmployee]...';
    
    
    GO
    EXECUTE sp_refreshview N'HumanResources.vEmployee';
    
    
    GO
    PRINT N'Refreshing [Sales].[vIndividualCustomer]...';
    
    
    GO
    EXECUTE sp_refreshview N'Sales.vIndividualCustomer';
    
    
    GO
    PRINT N'Refreshing [Sales].[vSalesPerson]...';
    
    
    GO
    EXECUTE sp_refreshview N'Sales.vSalesPerson';
    
    
    GO
    PRINT N'Refreshing [Sales].[vStoreWithAddresses]...';
    
    
    GO
    EXECUTE sp_refreshview N'Sales.vStoreWithAddresses';
    
    
    GO
    PRINT N'Refreshing [Purchasing].[vVendorWithAddresses]...';
    
    
    GO
    EXECUTE sp_refreshview N'Purchasing.vVendorWithAddresses';
    
    
    GO
    

    So this did not manifest the problem you are seeing. When you add the column in the middle, which is what I suspect happened in your case you will get this:

    CREATE TABLE [Person].[Address] (
      [AddressID]    INT        IDENTITY (1, 1) NOT FOR REPLICATION NOT NULL,
      [AddressLine1]  NVARCHAR (60)   NOT NULL,
      [AddressLine2]  NVARCHAR (60)   NULL,
      [City]      NVARCHAR (30)   NOT NULL,
      [StateProvinceID] INT        NOT NULL,
      [PostalCode]   NVARCHAR (15)   NOT NULL,
      [SpatialLocation] [sys].[geography] NULL,
      [rowguid]     UNIQUEIDENTIFIER ROWGUIDCOL NOT NULL,
    	[ModifiedByUserID] INT 				NULL, 
      [ModifiedDate]  DATETIME     NOT NULL
    );
    
    PRINT N'Dropping DF_Address_rowguid...';
    
    
    GO
    ALTER TABLE [Person].[Address] DROP CONSTRAINT [DF_Address_rowguid];
    
    
    GO
    PRINT N'Dropping DF_Address_ModifiedDate...';
    
    
    GO
    ALTER TABLE [Person].[Address] DROP CONSTRAINT [DF_Address_ModifiedDate];
    
    
    GO
    PRINT N'Dropping FK_Address_StateProvince_StateProvinceID...';
    
    
    GO
    ALTER TABLE [Person].[Address] DROP CONSTRAINT [FK_Address_StateProvince_StateProvinceID];
    
    
    GO
    PRINT N'Dropping FK_BusinessEntityAddress_Address_AddressID...';
    
    
    GO
    ALTER TABLE [Person].[BusinessEntityAddress] DROP CONSTRAINT [FK_BusinessEntityAddress_Address_AddressID];
    
    
    GO
    PRINT N'Dropping FK_SalesOrderHeader_Address_BillToAddressID...';
    
    
    GO
    ALTER TABLE [Sales].[SalesOrderHeader] DROP CONSTRAINT [FK_SalesOrderHeader_Address_BillToAddressID];
    
    
    GO
    PRINT N'Dropping FK_SalesOrderHeader_Address_ShipToAddressID...';
    
    
    GO
    ALTER TABLE [Sales].[SalesOrderHeader] DROP CONSTRAINT [FK_SalesOrderHeader_Address_ShipToAddressID];
    
    
    GO
    PRINT N'Starting rebuilding table [Person].[Address]...';
    
    
    GO
    SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
    
    SET XACT_ABORT ON;
    
    BEGIN TRANSACTION;
    
    CREATE TABLE [Person].[tmp_ms_xx_Address] (
      [AddressID]    INT        IDENTITY (1, 1) NOT FOR REPLICATION NOT NULL,
      [AddressLine1]   NVARCHAR (60)   NOT NULL,
      [AddressLine2]   NVARCHAR (60)   NULL,
      [City]       NVARCHAR (30)   NOT NULL,
      [StateProvinceID] INT        NOT NULL,
      [PostalCode]    NVARCHAR (15)   NOT NULL,
      [SpatialLocation] [sys].[geography] NULL,
      [rowguid]     UNIQUEIDENTIFIER CONSTRAINT [DF_Address_rowguid] DEFAULT (newid()) ROWGUIDCOL NOT NULL,
      [ModifiedByUserID] INT        NULL,
      [ModifiedDate]   DATETIME     CONSTRAINT [DF_Address_ModifiedDate] DEFAULT (getdate()) NOT NULL
    );
    
    ALTER TABLE [Person].[tmp_ms_xx_Address]
      ADD CONSTRAINT [tmp_ms_xx_clusteredindex_PK_Address_AddressID] PRIMARY KEY CLUSTERED ([AddressID] ASC) WITH (ALLOW_PAGE_LOCKS = ON, ALLOW_ROW_LOCKS = ON, PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF, STATISTICS_NORECOMPUTE = OFF);
    
    IF EXISTS (SELECT TOP 1 1
          FROM  [Person].[Address])
      BEGIN
        SET IDENTITY_INSERT [Person].[tmp_ms_xx_Address] ON;
        INSERT INTO [Person].[tmp_ms_xx_Address] ([AddressID], [AddressLine1], [AddressLine2], [City], [StateProvinceID], [PostalCode], [SpatialLocation], [rowguid], [ModifiedDate])
        SELECT  [AddressID],
             [AddressLine1],
             [AddressLine2],
             [City],
             [StateProvinceID],
             [PostalCode],
             [SpatialLocation],
             [rowguid],
             [ModifiedDate]
        FROM   [Person].[Address]
        ORDER BY [AddressID] ASC;
        SET IDENTITY_INSERT [Person].[tmp_ms_xx_Address] OFF;
      END
    
    DROP TABLE [Person].[Address];
    
    EXECUTE sp_rename N'[Person].[tmp_ms_xx_Address]', N'Address';
    
    EXECUTE sp_rename N'[Person].[tmp_ms_xx_clusteredindex_PK_Address_AddressID]', N'PK_Address_AddressID', N'OBJECT';
    
    COMMIT TRANSACTION;
    
    SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
    
    
    GO
    PRINT N'Creating [Person].[Address].[AK_Address_rowguid]...';
    
    
    GO
    CREATE UNIQUE NONCLUSTERED INDEX [AK_Address_rowguid]
      ON [Person].[Address]([rowguid] ASC) WITH (ALLOW_PAGE_LOCKS = ON, ALLOW_ROW_LOCKS = ON, PAD_INDEX = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, STATISTICS_NORECOMPUTE = OFF, ONLINE = OFF, MAXDOP = 0)
      ON [PRIMARY];
    
    
    GO
    PRINT N'Creating [Person].[Address].[IX_Address_AddressLine1_AddressLine2_City_StateProvinceID_PostalCode]...';
    
    
    GO
    CREATE UNIQUE NONCLUSTERED INDEX [IX_Address_AddressLine1_AddressLine2_City_StateProvinceID_PostalCode]
      ON [Person].[Address]([AddressLine1] ASC, [AddressLine2] ASC, [City] ASC, [StateProvinceID] ASC, [PostalCode] ASC) WITH (ALLOW_PAGE_LOCKS = ON, ALLOW_ROW_LOCKS = ON, PAD_INDEX = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, STATISTICS_NORECOMPUTE = OFF, ONLINE = OFF, MAXDOP = 0)
      ON [PRIMARY];
    
    
    GO
    PRINT N'Creating [Person].[Address].[IX_Address_StateProvinceID]...';
    
    
    GO
    CREATE NONCLUSTERED INDEX [IX_Address_StateProvinceID]
      ON [Person].[Address]([StateProvinceID] ASC) WITH (ALLOW_PAGE_LOCKS = ON, ALLOW_ROW_LOCKS = ON, PAD_INDEX = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, STATISTICS_NORECOMPUTE = OFF, ONLINE = OFF, MAXDOP = 0)
      ON [PRIMARY];
    
    
    GO
    PRINT N'Creating FK_Address_StateProvince_StateProvinceID...';
    
    
    GO
    ALTER TABLE [Person].[Address] WITH NOCHECK
      ADD CONSTRAINT [FK_Address_StateProvince_StateProvinceID] FOREIGN KEY ([StateProvinceID]) REFERENCES [Person].[StateProvince] ([StateProvinceID]) ON DELETE NO ACTION ON UPDATE NO ACTION;
    
    
    GO
    PRINT N'Creating FK_BusinessEntityAddress_Address_AddressID...';
    
    
    GO
    ALTER TABLE [Person].[BusinessEntityAddress] WITH NOCHECK
      ADD CONSTRAINT [FK_BusinessEntityAddress_Address_AddressID] FOREIGN KEY ([AddressID]) REFERENCES [Person].[Address] ([AddressID]) ON DELETE NO ACTION ON UPDATE NO ACTION;
    
    
    GO
    PRINT N'Creating FK_SalesOrderHeader_Address_BillToAddressID...';
    
    
    GO
    ALTER TABLE [Sales].[SalesOrderHeader] WITH NOCHECK
      ADD CONSTRAINT [FK_SalesOrderHeader_Address_BillToAddressID] FOREIGN KEY ([BillToAddressID]) REFERENCES [Person].[Address] ([AddressID]) ON DELETE NO ACTION ON UPDATE NO ACTION;
    
    
    GO
    PRINT N'Creating FK_SalesOrderHeader_Address_ShipToAddressID...';
    
    
    GO
    ALTER TABLE [Sales].[SalesOrderHeader] WITH NOCHECK
      ADD CONSTRAINT [FK_SalesOrderHeader_Address_ShipToAddressID] FOREIGN KEY ([ShipToAddressID]) REFERENCES [Person].[Address] ([AddressID]) ON DELETE NO ACTION ON UPDATE NO ACTION;
    
    
    GO
    PRINT N'Refreshing [HumanResources].[vEmployee]...';
    
    
    GO
    EXECUTE sp_refreshview N'HumanResources.vEmployee';
    
    
    GO
    PRINT N'Refreshing [Sales].[vIndividualCustomer]...';
    
    
    GO
    EXECUTE sp_refreshview N'Sales.vIndividualCustomer';
    
    
    GO
    PRINT N'Refreshing [Sales].[vSalesPerson]...';
    
    
    GO
    EXECUTE sp_refreshview N'Sales.vSalesPerson';
    
    
    GO
    PRINT N'Refreshing [Sales].[vStoreWithAddresses]...';
    
    
    GO
    EXECUTE sp_refreshview N'Sales.vStoreWithAddresses';
    
    
    GO
    PRINT N'Refreshing [Purchasing].[vVendorWithAddresses]...';
    
    
    GO
    EXECUTE sp_refreshview N'Purchasing.vVendorWithAddresses';
    
    
    GO
    PRINT N'Creating [Person].[Address].[MS_Description]...';
    
    
    GO
    EXECUTE sp_addextendedproperty @name = N'MS_Description', @value = N'Street address information for customers, employees, and vendors.', @level0type = N'SCHEMA', @level0name = N'Person', @level1type = N'TABLE', @level1name = N'Address';
    
    
    GO
    PRINT N'Creating [Person].[Address].[AddressID].[MS_Description]...';
    
    
    GO
    EXECUTE sp_addextendedproperty @name = N'MS_Description', @value = N'Primary key for Address records.', @level0type = N'SCHEMA', @level0name = N'Person', @level1type = N'TABLE', @level1name = N'Address', @level2type = N'COLUMN', @level2name = N'AddressID';
    
    
    GO
    PRINT N'Creating [Person].[Address].[AddressLine1].[MS_Description]...';
    
    
    GO
    EXECUTE sp_addextendedproperty @name = N'MS_Description', @value = N'First street address line.', @level0type = N'SCHEMA', @level0name = N'Person', @level1type = N'TABLE', @level1name = N'Address', @level2type = N'COLUMN', @level2name = N'AddressLine1';
    
    
    GO
    PRINT N'Creating [Person].[Address].[AddressLine2].[MS_Description]...';
    
    
    GO
    EXECUTE sp_addextendedproperty @name = N'MS_Description', @value = N'Second street address line.', @level0type = N'SCHEMA', @level0name = N'Person', @level1type = N'TABLE', @level1name = N'Address', @level2type = N'COLUMN', @level2name = N'AddressLine2';
    
    
    GO
    PRINT N'Creating [Person].[Address].[City].[MS_Description]...';
    
    
    GO
    EXECUTE sp_addextendedproperty @name = N'MS_Description', @value = N'Name of the city.', @level0type = N'SCHEMA', @level0name = N'Person', @level1type = N'TABLE', @level1name = N'Address', @level2type = N'COLUMN', @level2name = N'City';
    
    
    GO
    PRINT N'Creating [Person].[Address].[StateProvinceID].[MS_Description]...';
    
    
    GO
    EXECUTE sp_addextendedproperty @name = N'MS_Description', @value = N'Unique identification number for the state or province. Foreign key to StateProvince table.', @level0type = N'SCHEMA', @level0name = N'Person', @level1type = N'TABLE', @level1name = N'Address', @level2type = N'COLUMN', @level2name = N'StateProvinceID';
    
    
    GO
    PRINT N'Creating [Person].[Address].[PostalCode].[MS_Description]...';
    
    
    GO
    EXECUTE sp_addextendedproperty @name = N'MS_Description', @value = N'Postal code for the street address.', @level0type = N'SCHEMA', @level0name = N'Person', @level1type = N'TABLE', @level1name = N'Address', @level2type = N'COLUMN', @level2name = N'PostalCode';
    
    
    GO
    PRINT N'Creating [Person].[Address].[SpatialLocation].[MS_Description]...';
    
    
    GO
    EXECUTE sp_addextendedproperty @name = N'MS_Description', @value = N'Latitude and longitude of this address.', @level0type = N'SCHEMA', @level0name = N'Person', @level1type = N'TABLE', @level1name = N'Address', @level2type = N'COLUMN', @level2name = N'SpatialLocation';
    
    
    GO
    PRINT N'Creating [Person].[Address].[rowguid].[MS_Description]...';
    
    
    GO
    EXECUTE sp_addextendedproperty @name = N'MS_Description', @value = N'ROWGUIDCOL number uniquely identifying the record. Used to support a merge replication sample.', @level0type = N'SCHEMA', @level0name = N'Person', @level1type = N'TABLE', @level1name = N'Address', @level2type = N'COLUMN', @level2name = N'rowguid';
    
    
    GO
    PRINT N'Creating [Person].[Address].[ModifiedDate].[MS_Description]...';
    
    
    GO
    EXECUTE sp_addextendedproperty @name = N'MS_Description', @value = N'Date and time the record was last updated.', @level0type = N'SCHEMA', @level0name = N'Person', @level1type = N'TABLE', @level1name = N'Address', @level2type = N'COLUMN', @level2name = N'ModifiedDate';
    
    
    GO
    PRINT N'Creating [Person].[Address].[AK_Address_rowguid].[MS_Description]...';
    
    
    GO
    EXECUTE sp_addextendedproperty @name = N'MS_Description', @value = N'Unique nonclustered index. Used to support replication samples.', @level0type = N'SCHEMA', @level0name = N'Person', @level1type = N'TABLE', @level1name = N'Address', @level2type = N'INDEX', @level2name = N'AK_Address_rowguid';
    
    
    GO
    PRINT N'Creating [Person].[Address].[IX_Address_AddressLine1_AddressLine2_City_StateProvinceID_PostalCode].[MS_Description]...';
    
    
    GO
    EXECUTE sp_addextendedproperty @name = N'MS_Description', @value = N'Nonclustered index.', @level0type = N'SCHEMA', @level0name = N'Person', @level1type = N'TABLE', @level1name = N'Address', @level2type = N'INDEX', @level2name = N'IX_Address_AddressLine1_AddressLine2_City_StateProvinceID_PostalCode';
    
    
    GO
    PRINT N'Creating [Person].[Address].[IX_Address_StateProvinceID].[MS_Description]...';
    
    
    GO
    EXECUTE sp_addextendedproperty @name = N'MS_Description', @value = N'Nonclustered index.', @level0type = N'SCHEMA', @level0name = N'Person', @level1type = N'TABLE', @level1name = N'Address', @level2type = N'INDEX', @level2name = N'IX_Address_StateProvinceID';
    
    
    GO
    PRINT N'Creating [Person].[DF_Address_ModifiedDate].[MS_Description]...';
    
    
    GO
    EXECUTE sp_addextendedproperty @name = N'MS_Description', @value = N'Default constraint value of GETDATE()', @level0type = N'SCHEMA', @level0name = N'Person', @level1type = N'TABLE', @level1name = N'Address', @level2type = N'CONSTRAINT', @level2name = N'DF_Address_ModifiedDate';
    
    
    GO
    PRINT N'Creating [Person].[DF_Address_rowguid].[MS_Description]...';
    
    
    GO
    EXECUTE sp_addextendedproperty @name = N'MS_Description', @value = N'Default constraint value of NEWID()', @level0type = N'SCHEMA', @level0name = N'Person', @level1type = N'TABLE', @level1name = N'Address', @level2type = N'CONSTRAINT', @level2name = N'DF_Address_rowguid';
    
    
    GO
    PRINT N'Creating [Person].[FK_Address_StateProvince_StateProvinceID].[MS_Description]...';
    
    
    GO
    EXECUTE sp_addextendedproperty @name = N'MS_Description', @value = N'Foreign key constraint referencing StateProvince.StateProvinceID.', @level0type = N'SCHEMA', @level0name = N'Person', @level1type = N'TABLE', @level1name = N'Address', @level2type = N'CONSTRAINT', @level2name = N'FK_Address_StateProvince_StateProvinceID';
    
    
    GO
    PRINT N'Creating [Person].[PK_Address_AddressID].[MS_Description]...';
    
    
    GO
    EXECUTE sp_addextendedproperty @name = N'MS_Description', @value = N'Primary key (clustered) constraint', @level0type = N'SCHEMA', @level0name = N'Person', @level1type = N'TABLE', @level1name = N'Address', @level2type = N'CONSTRAINT', @level2name = N'PK_Address_AddressID';
    
    
    GO
    PRINT N'Creating [Person].[FK_BusinessEntityAddress_Address_AddressID].[MS_Description]...';
    
    
    GO
    EXECUTE sp_addextendedproperty @name = N'MS_Description', @value = N'Foreign key constraint referencing Address.AddressID.', @level0type = N'SCHEMA', @level0name = N'Person', @level1type = N'TABLE', @level1name = N'BusinessEntityAddress', @level2type = N'CONSTRAINT', @level2name = N'FK_BusinessEntityAddress_Address_AddressID';
    
    
    GO
    PRINT N'Creating [Sales].[FK_SalesOrderHeader_Address_BillToAddressID].[MS_Description]...';
    
    
    GO
    EXECUTE sp_addextendedproperty @name = N'MS_Description', @value = N'Foreign key constraint referencing Address.AddressID.', @level0type = N'SCHEMA', @level0name = N'Sales', @level1type = N'TABLE', @level1name = N'SalesOrderHeader', @level2type = N'CONSTRAINT', @level2name = N'FK_SalesOrderHeader_Address_BillToAddressID';
    
    
    GO
    PRINT N'Creating [Sales].[FK_SalesOrderHeader_Address_ShipToAddressID].[MS_Description]...';
    
    
    GO
    EXECUTE sp_addextendedproperty @name = N'MS_Description', @value = N'Foreign key constraint referencing Address.AddressID.', @level0type = N'SCHEMA', @level0name = N'Sales', @level1type = N'TABLE', @level1name = N'SalesOrderHeader', @level2type = N'CONSTRAINT', @level2name = N'FK_SalesOrderHeader_Address_ShipToAddressID';
    
    
    GO
    

    Which is expected since SQL can only add to the end.

     


    GertD @ www.DBProj.com
    15 ตุลาคม 2553 19:32
  • I started from scratch and tried again with the same results. Here are the steps I followed:

    1. Deleted the project from TFS.
    2. Deleted the AdventureWorks database from SQL and restored from backup.
    3. Re-created the project using the SQL Server 2008 Wizard.
    4. Added the project to TFS.
    5. Made the change to the Person.Address table and checked this in to TFS.
    6. Queued the build.

    Below is what is in the Person.Address file. As you can see the column was added at the end and is nullale. Once again the build script dropped and re-created everything. Maybe there's an option I can set in the Database.sqldeployment file? The contents of that file is below as well.

    Person.Address.table.sql

    CREATE

     

     

     

     

     

    TABLE [Person].[Address] (
    [AddressID] INT IDENTITY (1, 1) NOT FOR REPLICATION NOT NULL,
    [AddressLine1] NVARCHAR (60) NOT NULL,
    [AddressLine2] NVARCHAR (60) NULL,
    [City] NVARCHAR (30) NOT NULL,
    [StateProvinceID] INT NOT NULL,
    [PostalCode] NVARCHAR (15) NOT NULL,
    [rowguid] UNIQUEIDENTIFIER ROWGUIDCOL NOT NULL,
    [ModifiedDate] DATETIME NOT NULL,
    [ModifiedByUserID] INT NULL
    );

     

     

     

     

     

     

    Database.sqldeployment

    <?xml version="1.0" encoding="utf-8"?>
    <DeploymentConfigurationSettings xmlns="urn:Microsoft.VisualStudio.Data.Schema.Package.DeploymentConfigurationSettings">
      <Version>1.0</Version>
      <Properties>
        <AlwaysCreateNewDatabase>False</AlwaysCreateNewDatabase>
        <BlockIncrementalDeploymentIfDataLoss>True</BlockIncrementalDeploymentIfDataLoss>
        <DeployDatabaseProperties>False</DeployDatabaseProperties>
        <DeploymentCollationPreference>UseTargetModelCollation</DeploymentCollationPreference>
        <DoNotUseAlterAssemblyStatementsToUpdateCLRTypes>False</DoNotUseAlterAssemblyStatementsToUpdateCLRTypes>
        <GenerateDropsIfNotInProject>False</GenerateDropsIfNotInProject>
        <IgnorePermissions>True</IgnorePermissions>
        <IgnoreRoleMembership>True</IgnoreRoleMembership>
        <IncludeTransactionalScripts>True</IncludeTransactionalScripts>
        <PerformDatabaseBackup>False</PerformDatabaseBackup>
        <SingleUserMode>False</SingleUserMode>
      </Properties>
    </DeploymentConfigurationSettings>

    15 ตุลาคม 2553 20:25
  • Tried with your deployment settings, no luck reproducing it. The behavior matches when you would have a schemabound view or function referencing the table you are altering. In the version of AW that I have this is not the case.

    Are there rebuilds of VIEWs or FUNCTIONs in the deployment script that get created which are SCHEMABOUND by any change?


    GertD @ www.DBProj.com
    16 ตุลาคม 2553 3:05
  • Gert,

    I wish I knew what the issue was because this is a big sticking point for us. We are using Visual Studio 2010 Ultimate. I've copied below all the version numbers from Help -> About. Below that I've copied step by step what we do to create a database project and implement builds. The database is on SQL Server 2008 R2 Enterprise Edition (10.50.1600).

    Any insight you can provide would be very much appreciated.

    Aaron

    Help -> About

    Microsoft Visual Studio 2010
    Version 10.0.30319.1 RTMRel
    Microsoft .NET Framework
    Version 4.0.30319 RTMRel

    Installed Version: Ultimate

    Microsoft Office Developer Tools   01019-532-2002102-70296
    Microsoft Office Developer Tools

    Microsoft Visual Basic 2010   01019-532-2002102-70296
    Microsoft Visual Basic 2010

    Microsoft Visual C# 2010   01019-532-2002102-70296
    Microsoft Visual C# 2010

    Microsoft Visual C++ 2010   01019-532-2002102-70296
    Microsoft Visual C++ 2010

    Microsoft Visual F# 2010   01019-532-2002102-70296
    Microsoft Visual F# 2010

    Microsoft Visual Studio 2010 Architecture and Modeling Tools   01019-532-2002102-70296
    Microsoft Visual Studio 2010 Architecture and Modeling Tools
       
    UML® and Unified Modeling Language™ are trademarks or registered trademarks of the Object Management Group, Inc. in the United States and other countries.

    Microsoft Visual Studio 2010 Code Analysis Spell Checker   01019-532-2002102-70296
    Microsoft Visual Studio 2010 Code Analysis Spell Checker

    Portions of International CorrectSpell™ spelling correction system © 1993 by Lernout & Hauspie Speech Products N.V. All rights reserved.

    The American Heritage® Dictionary of the English Language, Third Edition Copyright © 1992 Houghton Mifflin Company. Electronic version licensed from Lernout & Hauspie Speech Products N.V. All rights reserved.

    Microsoft Visual Studio 2010 Team Explorer   01019-532-2002102-70296
    Microsoft Visual Studio 2010 Team Explorer

    Microsoft Visual Web Developer 2010   01019-532-2002102-70296
    Microsoft Visual Web Developer 2010

    CcVsiWanService   1.0
    ClearCase Visual Studio Integration WAN Service Package

    Crystal Reports Templates for Microsoft Visual Studio 2010  
    Crystal Reports Templates for Microsoft Visual Studio 2010

    Microsoft Team Foundation Server 2010 Power Tools   3.1.30831.0
    Power Tools that extend the Team Foundation Server integration with Visual Studio.

    Microsoft Visual Studio 2010 SharePoint Developer Tools   10.0.30319
    Microsoft Visual Studio 2010 SharePoint Developer Tools

    Microsoft Visual Studio Process Editor   1.0
    Process Editor for Microsoft Visual Studio Team Foundation Server

     

    Creating a database project in TFS

    Initial import:
    1. File -> New -> Project
     a) SQL Server 2005 Wizard
     b) Name = database name without environment prefix
     c) Location = C:\Data\Dev\Databases\01-Dev
     d) Solution name = database name without environment prefix
     e) Create directory for solution
     a) Add to source control
    2. New Project Wizard:
     a) Project Properties
      i) Database project
      ii) Organize by type of object
      iii)Include schema name in file name
     b) Set Database Options - leave defaults
     c) Import Database Schema
      i) Import existing schema
      ii) Specify connection
      iii)Do not import extended properties
      iv) Maximum files per directory = <Do not distribute files over directories>
      iii)Leave remaining defaults
     d) Configure Build and Deploy - leave defaults
     

    Remove users, roles, and object security from project:
    1. Exclude Properties\Database.sqlpermissions
    2. Exclude Schema Objects\<Database>.rolememberships.sql
    3. Exclude Schema Objects\Security\Users\*
    4. Exclude Schema Objects\Security\Roles\Application Roles\*
    5. Exclude Schema Objects\Security\Roles\Database Roles\*
    5. Exclude Schema Objects\Security\Schema all schemas do not have associated objects in sys.objects

     

    Fix synonyms:
    1. Create sqlcmd variable files in <Project>\Properties and add synonym variables accordingly:
     a) Integration.sqlcmdvars
     b) QAS.sqlcmdvars
    2. Modify project synonyms to use variables.

    Set database deployment properties (Database.sqldeployment):
    1. Deployment comparison collation = Use the collation of the server
    1. Uncheck "Deploy database properties"
    2. Uncheck "Generate DROP statements"
    3. Advanced Options:
     a) IgnorePermissions
     b) IgnoreRoleMembership
     c) IncludeTransactionalScripts
    4. Ignore 04151 build warnings "Warning: Unresolved reference to object":
     a) Highlight the project in Solution Explorer
     b) In main menu select:
      Project -> <Project_Name> Properties -> Build
     c) In Suppress Warnings type "04151" without quotes


    Add post deployment scripts:
    1. Under <Project>\Scripts\Post-Deployment copy the following scripts from the network:
     a) ObjectPermissions.sql
     b) Script.PostDeployment.sql
     c) SeedData.sql

     
    ***** Run a local build by pressing F6 or selection Build -> Build Solution


    Create and configure build:
    1. If this is a new project, check it into TFS before continuing.
    2. Clone build for Integration and QAS from another project.
    3. Change build definition parameters:
     a) General
      i) Name
     b) Trigger
      i) Integration = Continuous Integration
      ii) QAS = Manual
     c) Workspace
      i) Source Control Folder
     d) Build Defaults - no changes required
     e) Process -> Required
      i) Items to Build
      ii) Projects to Build
     f) Process -> Misc
      i) DBCommandVariablesName
      ii) ManifestFileName
      iii)TargetDBConnection
      iv) TargetDBName
     g) Retention policy - no changes required


    ***** Queue an Integration build

     

     

    16 ตุลาคม 2553 19:16
  • I will try to retrace your steps. Two questions:

    1. You use the 2005 Wizard, but you have a 2008 R2 SQL Server? This seems odd to me can you please confirm this is not a typo or are you importing from a SQL Server 2005 instance?
    2. Can you please elaborate on what "Fix synonyms" means, what are you exactly doing their? This feels it could be the root cause.

    Probably it is easiest if you ZIP up your project, since it is AdventurwWorks based there should be no private stuff in there and send it to me at gertd at dbproj dot com.

    Thanks,


    GertD @ www.DBProj.com
    16 ตุลาคม 2553 19:26
  • To update everyone, Gert and I went back and forth on this a few more times via email. I even emailed him the project I was using. He was unable to reproduce the problem I was seeing. I decided to see how my actual source code would handle the addition of a column, and oddly enough it does not unnecessarily drop and re-create the indexes, etc. I would still like to know what's causing this in AdventureWorks, but for right now since it's not occurring in our real source code I'm going to hold off on further research.

    Thanks for your help Gert.

    Aaron

     

    18 ตุลาคม 2553 19:17
  • I'm seeing the same issue with AdventureWorks db. Did u find a fix for it?
    8 มีนาคม 2555 20:31