How can we help you today? How can we help you today?

Filtered Indexes are not compared properly

I am using SQL Server 2022. I have my database changes in version control using a Scripts folder created by SQL Compare (15.4.17.28422). 

One of my tables has two filtered indexes.  When I view the .sql file for the table in my scripts folder, the WHERE clause is present. When I use SQL Compare to compare the folder to the database table, it detects a difference in the index. The left side of the compare (from the scripts folder) does not show the WHERE clause in the SQL Compare summary view but the right side (DB side) does. 

If I deploy it, it removes the filter from the index. 

Help!

Toni Feltman
0

Comments

5 comments

Sort by
  • Eddie Davis

    Hi Toni,

    Would it be possible to obtain a copy of the DDL for the table with the two filtered indexes?  Also copy of the scripts file for the table from your scripts folder?  


    Just to confirm, the scripts folder is the source and the deployment target is the database?  As the deployment process when you deploy will remove the where clause, as the deployment will modify the database in this scenario so it matches the source scripts folder.
     
    The scripts folder, was it created from a different database?  If yes, does the source database include the filtered indexes with the where clause?

     

    Many Thanks

    Eddie

    Eddie Davis
    0
  • Toni Feltman

    To simplify:
    1. Our scripts folder is the source. It does contain the filter on the indexes. 

    2. The SQL Server DB is the destination. The table in the DB contains the filter on the indexes too.

    3. When I run SQL Compare, it says that the source file does not contain the filter. 

    4. I thought maybe it was a visual thing and that SQL Compare didn't really think there was a difference, so I deployed. When I did, the filter was removed in the DB. 

    I will send you a .zip file with the file from our scripts folder AND the DDL from SSMS. Where can I send it. 

    Also, if you want to hop on a Teams call, I can show you. Thanks!

    Toni Feltman
    0
  • ti 84 calculator

    Thanks for the clarification! Yes, confirming that the scripts folder is the source and the database is the deployment target. If the filtered indexes in the database have WHERE clauses, but those are missing in the scripts folder, that would explain why they're being removed during deployment. Sharing the DDL and scripts will definitely help in pinpointing the issue.

    ti 84 calculator
    1
  • Toni Feltman

    Good Morning Eddie, I still don't think you understand. See #1 in my last reply. The WHERE clause on the Index IS in the script AND on the DB Table. 

    How can I send the files to you?

     

    Toni Feltman
    0
  • Eddie Davis

    Hi Toni,

    Thank you for your replies. 

    I perfectly understand your reply.  The comment made one day ago is not from me, but by another party.

    A Redgate Product Support ticket has been created for you, the ticket reference number is 336689.  Please check you mail inbox and / or your junk mail folder incase the message I sent gets trapped there.  The email will be from support@red-gate.com.

    Many Thanks

    Eddie

    Eddie Davis
    0

Add comment

Please sign in to leave a comment.