I have an application where the instruction says that it doesn't work with clustered index with more than one field. Couldn't resist testing and it is true. The app gets stuck in some endless lopp if i add a clustered index with 2 fields.
I thought the index was something internal to the database that doesn't affect the application. How do you write sql that doesn't work in the presense of a clustered index?
The app is using sql server 2005
Was it the clustered index or the primary key that you made composite? These are sometimes treated as synonomous in SQL Server, even though one is a Domain relationship (the primary key) and the other (the clustered index) is an implementation detail.
Some ORMs don't work well with composite primary keys.
Does the database have a regularly scheduled maintenance plan enabled? Have you manually run DBCC CHECKDB?
This probably indicates that the application uses metadata about the indexes on a table to create code dynamically - for instance an audit trigger generation script I have requires a single-column primary key. But that's a different from a clustered index, of course.
It's probably not worth investigating further since it's a documented limit in whatever app this is, but if you need to reverse-engineer this, you can use the profiler to see what's going on in this loop.