Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

UPDATE: I included some updated information in this question, because there was no way of progressing here given the previous constraints. Scroll to the bold-faced paragraphs to see my updates; I included partition definition and got rid of some of the constraints previously described.

I am sorry, I initially posted this question on stackoverflow, but then it came to my mind that this is by far better suited for this site ;)

I am trying to improve performance for some specific queries in a logging database that was setup with AppFabric. The database layout is the standard layout after a default installation. The problem is, I cannot/must not change the table structure itself in that database and it is horrible :) (IMHO) The Table I want to query contains about 21 million rows and has no primary key. For simplicity I am not showing the whole database structure here, but only the one table that is being queried in my tests:

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[ASWcfEventsTable](
    [Id] [bigint] NOT NULL,
    [EventTypeId] [int] NOT NULL,
    [EventSourceId] [int] NOT NULL,
    [ProcessId] [int] NOT NULL,
    [TraceLevelId] [tinyint] NOT NULL,
    [E2EActivityId] [nvarchar](36) NULL,
    [TimeCreated] [datetime2](7) NOT NULL,
    [Data1UniqueIdentifier] [nvarchar](36) NULL,
    [Data1Str] [nvarchar](450) NULL,
    [Data2Str] [nvarchar](450) NULL,
    [Data3Str] [nvarchar](450) NULL,
    [Data4Str] [nvarchar](450) NULL,
    [Data1MaxStr] [nvarchar](max) NULL,
    [Data1Int] [int] NULL,
    [Data2Int] [int] NULL,
    [Data3Int] [int] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

Having these indexes:

CREATE CLUSTERED INDEX [CI_ASWcfEventsTable_TimeCreated] ON [dbo].[ASWcfEventsTable]
(
    [TimeCreated] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

CREATE NONCLUSTERED INDEX [NCI_ASWcfEventsTable_E2EActivityId] ON [dbo].[ASWcfEventsTable]
(
    [E2EActivityId] ASC
)
INCLUDE (   [Id]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

GO

CREATE NONCLUSTERED INDEX [NCI_ASWcfEventsTable_EventSourceId] ON [dbo].[ASWcfEventsTable]
(
    [EventSourceId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

GO

CREATE NONCLUSTERED INDEX [NCI_ASWcfEventsTable_EventTypeId_TimeCreated] ON [dbo].[ASWcfEventsTable]
(
    [EventTypeId] ASC,
    [TimeCreated] ASC
)
INCLUDE (   [EventSourceId],
    [Data1Int]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

GO

CREATE NONCLUSTERED INDEX [NCI_ASWcfEventsTable_Id] ON [dbo].[ASWcfEventsTable]
(
    [Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

GO

There are 5 queries that I want to improve performance for, each one introduces a new column that is being filtered by. Please do not get confused by the non-english execution plans, the german word "Kosten" just means "cost".

1st query The first query just filteres by a specific string in the Data1MaxStr column:

SELECT TOP (100) 
        [Extent1].[Id] AS [Id], 
        [Extent1].[EventTypeId] AS [EventTypeId], 
        [Extent1].[EventSourceId] AS [EventSourceId], 
        [Extent1].[ProcessId] AS [ProcessId], 
        [Extent1].[TraceLevelId] AS [TraceLevelId], 
        [Extent1].[E2EActivityId] AS [E2EActivityId], 
        [Extent1].[TimeCreated] AS [TimeCreated], 
        [Extent1].[Data1UniqueIdentifier] AS [Data1UniqueIdentifier], 
        [Extent1].[Data1Str] AS [Data1Str], 
        [Extent1].[Data2Str] AS [Data2Str], 
        [Extent1].[Data3Str] AS [Data3Str], 
        [Extent1].[Data4Str] AS [Data4Str], 
        [Extent1].[Data1MaxStr] AS [Data1MaxStr], 
        [Extent1].[Data1Int] AS [Data1Int], 
        [Extent1].[Data2Int] AS [Data2Int], 
        [Extent1].[Data3Int] AS [Data3Int]
    FROM (SELECT 
                  [ASWcfEventsTable].[Id] AS [Id], 
                  [ASWcfEventsTable].[EventTypeId] AS [EventTypeId], 
                  [ASWcfEventsTable].[EventSourceId] AS [EventSourceId], 
                  [ASWcfEventsTable].[ProcessId] AS [ProcessId], 
                  [ASWcfEventsTable].[TraceLevelId] AS [TraceLevelId], 
                  [ASWcfEventsTable].[E2EActivityId] AS [E2EActivityId], 
                  [ASWcfEventsTable].[TimeCreated] AS [TimeCreated], 
                  [ASWcfEventsTable].[Data1UniqueIdentifier] AS [Data1UniqueIdentifier], 
                  [ASWcfEventsTable].[Data1Str] AS [Data1Str], 
                  [ASWcfEventsTable].[Data2Str] AS [Data2Str], 
                  [ASWcfEventsTable].[Data3Str] AS [Data3Str], 
                  [ASWcfEventsTable].[Data4Str] AS [Data4Str], 
                  [ASWcfEventsTable].[Data1MaxStr] AS [Data1MaxStr], 
                  [ASWcfEventsTable].[Data1Int] AS [Data1Int], 
                  [ASWcfEventsTable].[Data2Int] AS [Data2Int], 
                  [ASWcfEventsTable].[Data3Int] AS [Data3Int]
              FROM [dbo].[ASWcfEventsTable] AS [ASWcfEventsTable]) AS [Extent1]
    WHERE N'some string' <> [Extent1].[Data1MaxStr]
    ORDER BY [Extent1].[TimeCreated] DESC

execution plan for query 1

2nd query Next query filteres also by TraceLevelId looking like this:

SELECT TOP (100) 
        [Extent1].[Id] AS [Id], 
        [Extent1].[EventTypeId] AS [EventTypeId], 
        [Extent1].[EventSourceId] AS [EventSourceId], 
        [Extent1].[ProcessId] AS [ProcessId], 
        [Extent1].[TraceLevelId] AS [TraceLevelId], 
        [Extent1].[E2EActivityId] AS [E2EActivityId], 
        [Extent1].[TimeCreated] AS [TimeCreated], 
        [Extent1].[Data1UniqueIdentifier] AS [Data1UniqueIdentifier], 
        [Extent1].[Data1Str] AS [Data1Str], 
        [Extent1].[Data2Str] AS [Data2Str], 
        [Extent1].[Data3Str] AS [Data3Str], 
        [Extent1].[Data4Str] AS [Data4Str], 
        [Extent1].[Data1MaxStr] AS [Data1MaxStr], 
        [Extent1].[Data1Int] AS [Data1Int], 
        [Extent1].[Data2Int] AS [Data2Int], 
        [Extent1].[Data3Int] AS [Data3Int]
    FROM (SELECT 
                  [ASWcfEventsTable].[Id] AS [Id], 
                  [ASWcfEventsTable].[EventTypeId] AS [EventTypeId], 
                  [ASWcfEventsTable].[EventSourceId] AS [EventSourceId], 
                  [ASWcfEventsTable].[ProcessId] AS [ProcessId], 
                  [ASWcfEventsTable].[TraceLevelId] AS [TraceLevelId], 
                  [ASWcfEventsTable].[E2EActivityId] AS [E2EActivityId], 
                  [ASWcfEventsTable].[TimeCreated] AS [TimeCreated], 
                  [ASWcfEventsTable].[Data1UniqueIdentifier] AS [Data1UniqueIdentifier], 
                  [ASWcfEventsTable].[Data1Str] AS [Data1Str], 
                  [ASWcfEventsTable].[Data2Str] AS [Data2Str], 
                  [ASWcfEventsTable].[Data3Str] AS [Data3Str], 
                  [ASWcfEventsTable].[Data4Str] AS [Data4Str], 
                  [ASWcfEventsTable].[Data1MaxStr] AS [Data1MaxStr], 
                  [ASWcfEventsTable].[Data1Int] AS [Data1Int], 
                  [ASWcfEventsTable].[Data2Int] AS [Data2Int], 
                  [ASWcfEventsTable].[Data3Int] AS [Data3Int]
            FROM [dbo].[ASWcfEventsTable] AS [ASWcfEventsTable]) AS [Extent1]
    WHERE (N'some string' <> [Extent1].[Data1MaxStr]) AND (4 =  CAST( [Extent1].[TraceLevelId] AS int))
    ORDER BY [Extent1].[TimeCreated] DESC

execution plan for query 2

3rd query The third query adds the EventSourceId to the where-condition:

SELECT TOP (100) 
        [Extent1].[Id] AS [Id], 
        [Extent1].[EventTypeId] AS [EventTypeId], 
        [Extent1].[EventSourceId] AS [EventSourceId], 
        [Extent1].[ProcessId] AS [ProcessId], 
        [Extent1].[TraceLevelId] AS [TraceLevelId], 
        [Extent1].[E2EActivityId] AS [E2EActivityId], 
        [Extent1].[TimeCreated] AS [TimeCreated], 
        [Extent1].[Data1UniqueIdentifier] AS [Data1UniqueIdentifier], 
        [Extent1].[Data1Str] AS [Data1Str], 
        [Extent1].[Data2Str] AS [Data2Str], 
        [Extent1].[Data3Str] AS [Data3Str], 
        [Extent1].[Data4Str] AS [Data4Str], 
        [Extent1].[Data1MaxStr] AS [Data1MaxStr], 
        [Extent1].[Data1Int] AS [Data1Int], 
        [Extent1].[Data2Int] AS [Data2Int], 
        [Extent1].[Data3Int] AS [Data3Int]
    FROM (SELECT 
                  [ASWcfEventsTable].[Id] AS [Id], 
                  [ASWcfEventsTable].[EventTypeId] AS [EventTypeId], 
                  [ASWcfEventsTable].[EventSourceId] AS [EventSourceId], 
                  [ASWcfEventsTable].[ProcessId] AS [ProcessId], 
                  [ASWcfEventsTable].[TraceLevelId] AS [TraceLevelId], 
                  [ASWcfEventsTable].[E2EActivityId] AS [E2EActivityId], 
                  [ASWcfEventsTable].[TimeCreated] AS [TimeCreated], 
                  [ASWcfEventsTable].[Data1UniqueIdentifier] AS [Data1UniqueIdentifier], 
                  [ASWcfEventsTable].[Data1Str] AS [Data1Str], 
                  [ASWcfEventsTable].[Data2Str] AS [Data2Str], 
                  [ASWcfEventsTable].[Data3Str] AS [Data3Str], 
                  [ASWcfEventsTable].[Data4Str] AS [Data4Str], 
                  [ASWcfEventsTable].[Data1MaxStr] AS [Data1MaxStr], 
                  [ASWcfEventsTable].[Data1Int] AS [Data1Int], 
                  [ASWcfEventsTable].[Data2Int] AS [Data2Int], 
                  [ASWcfEventsTable].[Data3Int] AS [Data3Int]
            FROM [dbo].[ASWcfEventsTable] AS [ASWcfEventsTable]) AS [Extent1]
    WHERE (N'some string' <> [Extent1].[Data1MaxStr]) AND (4 =  CAST( [Extent1].[TraceLevelId] AS int)) AND ([Extent1].[EventSourceId] IN (68,69))
    ORDER BY [Extent1].[TimeCreated] DESC

execution plan for query 3

4th query Skipping the query that also filteres by a minimum date this next query filteres by an interval of dates:

DECLARE @p__linq__0 DATETIME = DATEADD(DAY, -100, GETUTCDATE())
DECLARE @p__linq__1 DATETIME = DATEADD(DAY, -50, GETUTCDATE())
SELECT TOP (100) 
        [Project1].[Id] AS [Id], 
        [Project1].[EventTypeId] AS [EventTypeId], 
        [Project1].[EventSourceId] AS [EventSourceId], 
        [Project1].[ProcessId] AS [ProcessId], 
        [Project1].[TraceLevelId] AS [TraceLevelId], 
        [Project1].[E2EActivityId] AS [E2EActivityId], 
        [Project1].[TimeCreated] AS [TimeCreated], 
        [Project1].[Data1UniqueIdentifier] AS [Data1UniqueIdentifier], 
        [Project1].[Data1Str] AS [Data1Str], 
        [Project1].[Data2Str] AS [Data2Str], 
        [Project1].[Data3Str] AS [Data3Str], 
        [Project1].[Data4Str] AS [Data4Str], 
        [Project1].[Data1MaxStr] AS [Data1MaxStr], 
        [Project1].[Data1Int] AS [Data1Int], 
        [Project1].[Data2Int] AS [Data2Int], 
        [Project1].[Data3Int] AS [Data3Int]
    FROM ( SELECT 
                [Extent1].[Id] AS [Id], 
                [Extent1].[EventTypeId] AS [EventTypeId], 
                [Extent1].[EventSourceId] AS [EventSourceId], 
                [Extent1].[ProcessId] AS [ProcessId], 
                [Extent1].[TraceLevelId] AS [TraceLevelId], 
                [Extent1].[E2EActivityId] AS [E2EActivityId], 
                [Extent1].[TimeCreated] AS [TimeCreated], 
                [Extent1].[Data1UniqueIdentifier] AS [Data1UniqueIdentifier], 
                [Extent1].[Data1Str] AS [Data1Str], 
                [Extent1].[Data2Str] AS [Data2Str], 
                [Extent1].[Data3Str] AS [Data3Str], 
                [Extent1].[Data4Str] AS [Data4Str], 
                [Extent1].[Data1MaxStr] AS [Data1MaxStr], 
                [Extent1].[Data1Int] AS [Data1Int], 
                [Extent1].[Data2Int] AS [Data2Int], 
                [Extent1].[Data3Int] AS [Data3Int]
            FROM (SELECT 
                  [ASWcfEventsTable].[Id] AS [Id], 
                  [ASWcfEventsTable].[EventTypeId] AS [EventTypeId], 
                  [ASWcfEventsTable].[EventSourceId] AS [EventSourceId], 
                  [ASWcfEventsTable].[ProcessId] AS [ProcessId], 
                  [ASWcfEventsTable].[TraceLevelId] AS [TraceLevelId], 
                  [ASWcfEventsTable].[E2EActivityId] AS [E2EActivityId], 
                  [ASWcfEventsTable].[TimeCreated] AS [TimeCreated], 
                  [ASWcfEventsTable].[Data1UniqueIdentifier] AS [Data1UniqueIdentifier], 
                  [ASWcfEventsTable].[Data1Str] AS [Data1Str], 
                  [ASWcfEventsTable].[Data2Str] AS [Data2Str], 
                  [ASWcfEventsTable].[Data3Str] AS [Data3Str], 
                  [ASWcfEventsTable].[Data4Str] AS [Data4Str], 
                  [ASWcfEventsTable].[Data1MaxStr] AS [Data1MaxStr], 
                  [ASWcfEventsTable].[Data1Int] AS [Data1Int], 
                  [ASWcfEventsTable].[Data2Int] AS [Data2Int], 
                  [ASWcfEventsTable].[Data3Int] AS [Data3Int]
                FROM [dbo].[ASWcfEventsTable] AS [ASWcfEventsTable]) AS [Extent1]
            WHERE (N'some string' <> [Extent1].[Data1MaxStr]) AND ([Extent1].[TimeCreated] >= @p__linq__0) AND ([Extent1].[TimeCreated] <= @p__linq__1) AND (4 =  CAST( [Extent1].[TraceLevelId] AS int)) AND (68 = [Extent1].[EventSourceId])
    )  AS [Project1]
    ORDER BY [Project1].[TimeCreated] DESC

execution plan for query 4

5th query Last, but not least this query then also filteres by a custom message (like the first query but including all the other search parameters and this string is not the same constant as seen above)

DECLARE @p__linq__0 NVARCHAR = 'another string'
DECLARE @p__linq__1 DATETIME = DATEADD(DAY, -100, GETUTCDATE())
DECLARE @p__linq__2 DATETIME = DATEADD(DAY, -50, GETUTCDATE())
SELECT TOP (100) 
        [Project1].[Id] AS [Id], 
        [Project1].[EventTypeId] AS [EventTypeId], 
        [Project1].[EventSourceId] AS [EventSourceId], 
        [Project1].[ProcessId] AS [ProcessId], 
        [Project1].[TraceLevelId] AS [TraceLevelId], 
        [Project1].[E2EActivityId] AS [E2EActivityId], 
        [Project1].[TimeCreated] AS [TimeCreated], 
        [Project1].[Data1UniqueIdentifier] AS [Data1UniqueIdentifier], 
        [Project1].[Data1Str] AS [Data1Str], 
        [Project1].[Data2Str] AS [Data2Str], 
        [Project1].[Data3Str] AS [Data3Str], 
        [Project1].[Data4Str] AS [Data4Str], 
        [Project1].[Data1MaxStr] AS [Data1MaxStr], 
        [Project1].[Data1Int] AS [Data1Int], 
        [Project1].[Data2Int] AS [Data2Int], 
        [Project1].[Data3Int] AS [Data3Int]
    FROM ( SELECT 
                [Extent1].[Id] AS [Id], 
                [Extent1].[EventTypeId] AS [EventTypeId], 
                [Extent1].[EventSourceId] AS [EventSourceId], 
                [Extent1].[ProcessId] AS [ProcessId], 
                [Extent1].[TraceLevelId] AS [TraceLevelId], 
                [Extent1].[E2EActivityId] AS [E2EActivityId], 
                [Extent1].[TimeCreated] AS [TimeCreated], 
                [Extent1].[Data1UniqueIdentifier] AS [Data1UniqueIdentifier], 
                [Extent1].[Data1Str] AS [Data1Str], 
                [Extent1].[Data2Str] AS [Data2Str], 
                [Extent1].[Data3Str] AS [Data3Str], 
                [Extent1].[Data4Str] AS [Data4Str], 
                [Extent1].[Data1MaxStr] AS [Data1MaxStr], 
                [Extent1].[Data1Int] AS [Data1Int], 
                [Extent1].[Data2Int] AS [Data2Int], 
                [Extent1].[Data3Int] AS [Data3Int]
            FROM (SELECT 
                      [ASWcfEventsTable].[Id] AS [Id], 
                      [ASWcfEventsTable].[EventTypeId] AS [EventTypeId], 
                      [ASWcfEventsTable].[EventSourceId] AS [EventSourceId], 
                      [ASWcfEventsTable].[ProcessId] AS [ProcessId], 
                      [ASWcfEventsTable].[TraceLevelId] AS [TraceLevelId], 
                      [ASWcfEventsTable].[E2EActivityId] AS [E2EActivityId], 
                      [ASWcfEventsTable].[TimeCreated] AS [TimeCreated], 
                      [ASWcfEventsTable].[Data1UniqueIdentifier] AS [Data1UniqueIdentifier], 
                      [ASWcfEventsTable].[Data1Str] AS [Data1Str], 
                      [ASWcfEventsTable].[Data2Str] AS [Data2Str], 
                      [ASWcfEventsTable].[Data3Str] AS [Data3Str], 
                      [ASWcfEventsTable].[Data4Str] AS [Data4Str], 
                      [ASWcfEventsTable].[Data1MaxStr] AS [Data1MaxStr], 
                      [ASWcfEventsTable].[Data1Int] AS [Data1Int], 
                      [ASWcfEventsTable].[Data2Int] AS [Data2Int], 
                      [ASWcfEventsTable].[Data3Int] AS [Data3Int]
                FROM [dbo].[ASWcfEventsTable] AS [ASWcfEventsTable]) AS [Extent1]
            WHERE ([Extent1].[Data1MaxStr] LIKE @p__linq__0 ESCAPE N'~') AND ([Extent1].[TimeCreated] >= @p__linq__1) AND ([Extent1].[TimeCreated] <= @p__linq__2) AND (4 =  CAST( [Extent1].[TraceLevelId] AS int)) AND (68 = [Extent1].[EventSourceId])
        )  AS [Project1]
    ORDER BY [Project1].[TimeCreated] DESC

execution plan for query 5

I want to try to improve performance for these queries without changing the queries themselves (must not change them nor must I change the Linq-statement that generates it) or the underlying table structure; I can however create views upon that table and create indexes on those views.

UPDATE: Changes for possible performance improvements should be as small as possible, but as we figured out in the comments, there will be no improvement by just creating views and indexes upon them; so suggestions towards indexing the table itself (but still not changing the queries itself) are now appreciated, too.

Is there a possibility to do that this way?

Here's my starting thoughts(unfortunately obsolete by now, because, as we figured out, the edition we are running sql server on will not apply automatic view matching): The table (~21 million rows in total) is always queried to compare the Data1MaxStr against some string, either for unequality to a very specific string that is always the same if the user did not enter something specific (this sorts out some noisy results ~60.7% of all rows share this specific string) or for equality to a specific log message the user is searching for (~27.4% of the table's rows don't contain a value in that column meaning they contain NULL here)

I think it could be an enhancement if I could reduce the number of rows, that need to be queried by adding some constellation of view and index on it to => never query rows that have a NULL-value in that column (these are of no interest to us, ever) => reduce the rows that are being queried by the fact that the string that is being compared against is the same in many cases

One of my first tries was to create this view:

CREATE VIEW [dbo].ASEventsIDTimeCreated
WITH SCHEMABINDING
AS
  SELECT
        [ASWcfEventsTable].[Id], 
      [ASWcfEventsTable].[EventTypeId], 
      [ASWcfEventsTable].[EventSourceId], 
      [ASWcfEventsTable].[ProcessId], 
      [ASWcfEventsTable].[TraceLevelId], 
      [ASWcfEventsTable].[E2EActivityId], 
      [ASWcfEventsTable].[TimeCreated], 
      [ASWcfEventsTable].[Data1UniqueIdentifier], 
      [ASWcfEventsTable].[Data1Str], 
      [ASWcfEventsTable].[Data2Str], 
      [ASWcfEventsTable].[Data3Str], 
      [ASWcfEventsTable].[Data4Str], 
      [ASWcfEventsTable].[Data1MaxStr], 
      [ASWcfEventsTable].[Data1Int], 
      [ASWcfEventsTable].[Data2Int], 
      [ASWcfEventsTable].[Data3Int]
  FROM [dbo].[ASWcfEventsTable]
  WHERE [dbo].[ASWcfEventsTable].[Data1MaxStr] <> N'some string'
GO

CREATE UNIQUE CLUSTERED INDEX CI_ASEventsIDTimeCreated ON [dbo].ASEventsIDTimeCreated (Id, TimeCreated DESC)
GO

CREATE NONCLUSTERED INDEX NI_ASEventsIDTimeCreated ON [dbo].ASEventsIDTimeCreated (Id, TimeCreated DESC) INCLUDE(Data1MaxStr)
GO

But unfortunately this had no effect on the resulting execution plan. Do I even have a chance here to enforce using a different index (on a view I define) when there is no primary key on the table? Any help is appreciated.

share|improve this question
3  
What edition of SQL Server are you using? Automatic indexed view matching only occurs in Enterprise. The lack of any unique index on the table would stymie any attempt at creating an indexed view though except if the indexed view was to enforce a new unique constraint not currently enforced. If you are allowed to create indexed views on the table you might as well be allowed to create indexes on the table directly IMO. There is no practical difference. At one extreme the indexed view could just be a copy of the table itself with proper indexes. Obv less efficient than creating idxs directly. –  Martin Smith Sep 4 '13 at 9:09
    
"sql-server-2008-r2" - indexed view matching does work in general, I tested that on another table in the same database –  DrCopyPaste Sep 4 '13 at 9:16
2  
@MartinSmith just read that part of the question. Sounds like heart surgery with bear hands. –  ypercube Sep 4 '13 at 10:16
1  
@ypercube yes it feels like that too ;) –  DrCopyPaste Sep 4 '13 at 10:18
1  
Isn't it easier to push for allowing adding indexes and simple changes in the Linq code than upgrading to Enterprise? –  ypercube Sep 4 '13 at 11:40
show 12 more comments

closed as off-topic by Max Vernon, bluefeet, RolandoMySQLDBA, Mark Storey-Smith, Jon Seigel Oct 4 '13 at 3:05

This question appears to be off-topic. The users who voted to close gave this specific reason:

  • "Too localized - this could be because your code has a typo, basic error, or is not relevant to most of our audience. Consider revising your question so that it appeals to a broader audience. As it stands, the question is unlikely to help other users (regarding typo questions, see this meta question for background)." – Max Vernon, bluefeet, RolandoMySQLDBA, Mark Storey-Smith, Jon Seigel
If this question can be reworded to fit the rules in the help center, please edit the question.