N_Muller (8/23/2016)
SQL Server doesn't allow to create filtered indexes or indexes on computed columns in temp tables with ansi warnings turned off. Here's a simple example:
More specifically, SQL Server does not allow indexes in a few scenarios when ANSI_WARNINGS is OFF, though tempdb has nothing to do with it:
SET ANSI_WARNINGS OFF;
CREATE TABLE dbo.temp1 ( [id] INT );
CREATE INDEX ix_temp1_id ON dbo.temp1 ( [id] ) WHERE ( [id] = 0 );
and:
SET ANSI_WARNINGS OFF;
CREATE TABLE dbo.temp2 ([Col1] INT, [Col2] AS ([Col1] * 2));
CREATE INDEX [IX_temp2_Col2] ON dbo.temp2 ([Col2]);
Both get:
Msg 1934, Level 16, State 1, Line 4
CREATE INDEX failed because the following SET options have incorrect settings: 'ANSI_WARNINGS'. Verify that SET options are correct for use with indexed views and/or indexes on computed columns and/or filtered indexes and/or query notifications and/or XML data type methods and/or spatial index operations.
More info on MSDN at: CREATE INDEX
Take care, Solomon...
SQL# — https://SQLsharp.com/ ( SQLCLR library ofover 340 Functions and Procedures)
Sql Quantum Lift — https://SqlQuantumLift.com/ ( company )
Sql Quantum Leap — https://SqlQuantumLeap.com/ ( blog )
Info sites — Collations • Module Signing • SQLCLR