Troubleshooting problems with views in SQL Server 2014
If you have problems with views in SQL Server 2014, review this troubleshooting checklist
to find potential solutions.
1. Install the latest SQL Server 2014 service pack.
Because many bugs with views in SQL Server 2014 were fixed in SQL Server service
packs, you should install the latest SQL Server service pack.
At the time this article was written the latest SQL Server 2014 service pack was
service pack 1. You can download the SQL Server 2014 service pack 1 at here:
https://www.microsoft.com/en-us/download/details.aspx?id=46694
2. When you run log shipping from many databases to one secondary server, the
deadlock may occur in sys.dm_database_encryption_keys dynamic management view.
This is SQL Server 2014 bug. To work around this problem, you can decrease the
number of primary databases that are merged on the secondary server.
3. After you change the computer name, the SQL Server login name is not updated
in the sys.syslogins catalog compatibility view.
This problem occurs when the SQL Server login uses Windows Authentication mode from
a Windows local user. To work around this problem, you can correct the login name
manually by using the ALTER LOGIN statement.
4. You can find out that the query does not use the indexed view in
SQL Server 2014 Enterprise Edition.
This problem occurs when you define an indexed view that involves an ISNULL function
on a nonnullable column. To work around this problem, you should rewrite the view
definition without using the ISNULL function on the nonnullable column, and then
re-create the index.
5. When you update a view in SQL Server 2014 an index corruption may occur.
To work around this problem, you can use DBCC CHECKDB command with the REPAIR_REBUILD
option to try to correct the corruption or restore the latest backup that does not
have the corruption.
6. The SQL Server 2014 session may be blocked unexpectedly when you execute
an ALTER VIEW statement on a view.
This problem occurs when two or more SQL Server sessions execute an ALTER VIEW
statement against the same view at the same time. To work around this problem,
avoid executing an ALTER VIEW statement against the same view at the same time.
To resolve this problem, install the latest SQL Server service pack.
7. When you run a distributed partitioned view query and enable the
“lazy schema validation” option, an access violation may occur.
This is SQL Server 2014 bug. To work around this problem, you can disable the
“lazy schema validation” option for all linked servers that are involved in the
distributed partitioned view query. To resolve this problem, install the latest
SQL Server service pack.
8. The MDS subscription view returns fewer records than expected in SQL Server 2014.
This is SQL Server 2014 bug. This bug was first fixed in Cumulative Update
package 1 for SQL Server 2014. You can download the Cumulative Update package 1
for SQL Server 2014 at here:
https://support.microsoft.com/en-us/kb/2931693
9. The error occurs when you try to create an indexed view on the partitioned
table that has a clustered columnstore index.
This is the error message text: “Internal Query Processor Error: The query processor
could not obtain access to a required interface.” This bug was first fixed in
Cumulative Update package 3 for SQL Server 2014. You can download the Cumulative
Update package 3 for SQL Server 2014 at here:
https://support.microsoft.com/en-us/kb/2984923
10. Some columns in sys.column_store_segments view show NULL value when the table
has non-dbo schema in SQL Server 2014.
In this case, the column has_nulls, base_id, magnitude, min_data_id, max_data_id,
and null_value returns NULL value unless the user has select permission. This bug
was first fixed in Cumulative Update package 3 for SQL Server 2014. You can
download the Cumulative Update package 3 for SQL Server 2014 at here:
https://support.microsoft.com/en-us/kb/2984923
11. When you run a query that contains CLR functions in the WHERE clause against
an indexed view, a fatal exception is generated and the session is disconnected.
This problem occurs when the CLR functions are deterministic. This bug was first
fixed in Cumulative Update package 4 for SQL Server 2014. You can download the
Cumulative Update package 4 for SQL Server 2014 at here:
https://support.microsoft.com/en-us/kb/2999197
12. Incorrect results are returned in an indexed view after an Insert or Delete
operation on the base table occurs in SQL Server 2014.
This problem occurs when an indexed view uses a scalar SUM clause. This bug was
first fixed in Cumulative Update package 8 for SQL Server 2014. You can download
the Cumulative Update package 8 for SQL Server 2014 at here:
https://support.microsoft.com/en-us/kb/3067836