index – Indexed columns in SQL Server do not appear to work for basic queries according to execution plan

Disclaimer: I’m not a DBA. I have picked up a few things from this board in the past that I’m building from.

I have a table of google analytics session start times. I have an index on each column. I want to filter for all sessions that were started between two dates. Screenshot below shows the query, and the index.

Query text and index properties

The query runs quickly but I do not believe it’s using the index based on the Execution plan which both says that there’s a missing index and shows a table scan rather than an index scan:

execution

Why?

Is it because of something about the way I’m searching through the datetime? If instead of looking between dates, I set it equal to a date, the execution plan shows it using the index:

Using index

But it’s not just this table or datetime. Here’s a different table with an index on a varchar column:

metadata index

And a simple query on this one also tells me I’m missing the index:

missing md index

I’m stumped.

homepage – Page archives indexed with static home page

I am using a page as the home page (not the standard blogroll).

So now I have all my page archive URL’s going to home page.

For example:
The following page is indexed in google search console:

https://askanydifference.com/page/194/?a%2525252525%25252%252C52525252525252C252Cmp

Even someone has done some negative things to the website by indexing following type of URL’s in search console:

https://askanydifference.com/page/176/?k=real homemade hidden camera&related

When I open that URL, it shows my home page.
Ideally, since the above URL is no longer valid, it should return a 404 error but WordPress seems to redirect it to the home page.

How can I handle the URL’s with /page/….
I am not using any SEO plugins (just doing any customizations using PHP code in headers.php or functions.php in a child theme)

Why does the archive.org of most YouTube videos fail with “Sorry the Wayback Machine does not have this video () archived (or not indexed yet)”?

I have seen several videos which show as archived, and video metadata does show up, but the video itself doesn’t with a magic error hacked into the rendered page:

Sorry the Wayback Machine does not have this video (<id>) archived (or not indexed yet).

So does this message indicate:

I did however find some videos that still work, e.g.: https://web.archive.org/web/2oe_/http://wayback-fakeurl.archive.org/yt/d81mEbL8Yw8 but it’s just too unreliable, I can’t predict which ones will work and which ones won’t.

So for now I’ll have to stick to https://github.com/ytdl-org/youtube-dl + reupload when I find a hot video I want to archive.

Related posts:

search – 0% of the site has been indexed. There are 400 items left to index

You need to run cron. Because building the search engine can take time, Drupal does it on cron runs (the scheduling system).

First, to make sure everything is OK, run cron manually from /admin/config/system/cron to ensure that the search items get indexed.

Then set up cron to run automatically following the instructions in the documentation.

sql server – Updating an indexed view with NOEXPAND

I will preface this by saying that this is a cross post from an unanswered question on Stack Overflow.
I am not doing this simply to get more views on the question, I would like the DBA community to share their view on whether this might even be a bug in SQL Server. I don’t think the SO community has the expertise to decide that, so I’m reposting here.


Suppose I have a table T, and I have an indexed view V on it:

CREATE TABLE dbo.T (id int PRIMARY KEY, b bit NOT NULL, txt varchar(20));
GO
CREATE VIEW dbo.V
WITH SCHEMABINDING AS
  SELECT T.Id, T.txt
  FROM dbo.T AS T
  WHERE T.b = 1;
GO
CREATE UNIQUE CLUSTERED INDEX idx_V ON dbo.V (Id);

In this trivial example it’s basically just a filtered index, but it could also have joins and such like.

I would now like to select some rows in T where b = 1, the filtered view here is very useful, and I’m on Standard so have to use NOEXPAND (or it’s too complex for view matching):

SELECT Id, txt
FROM V WITH (NOEXPAND);

This works nicely.

Now I want to update these rows to some value. The view qualifies as updatable so I can do this:

UPDATE V
SET txt = 'Foo';

This does not use the indexed view to find the rows to update, even though it needs them to actually update the view. What I would like it to do is use the view like a normal table index, and identify the rows to update from it, pass them to the Clustered Index Update on T, followed by an Update on the view. So I try this:

UPDATE V WITH (NOEXPAND)
SET txt = 'Foo';

This fails with “Hint ‘noexpand’ on object ‘V’ is invalid.”.

I know I can get round it with a query such as this:

UPDATE T
SET txt = 'Foo'
FROM T
JOIN V WITH (NOEXPAND) ON V.Id = T.Id;

But this means an extra Seek. Not only that, it adds a Filter on the subsequent indexed view update to check the rows match the view (a joined view would require the joins to be evaluated) when clearly they must match the view.

Is there any way of getting this to work in the way I would like?

I believe there is an optimization called rowset-sharing which may make a difference here, does anyone know about this?


Update:

It does not help to put the view in a FROM clause or even in a derived table. As soon as it looks to the parser that it’s being used for an update, it fails.

There is no indication that NOEXPAND should not work either in the Table Hints, Indexed View, or Updatable Views documentation. The docs for the UPDATE statement specifically mention that certain table hints are not allowed, but only NOLOCK and READUNCOMMITTED are excluded.


So, maybe this is even a bug in SQL Server, and I should file it on Azure Feedback. What do people think?

sql server – Indexed view not used in actual execution plan?

I’ve got a modest use case for an indexed view, which collapses out a column and sums all entries from a large table:

CREATE VIEW dbo.Losses_CombinedPortfolio WITH SCHEMABINDING
AS
    SELECT (Category), (Year), SUM(ISNULL(Loss,0)) AS (Loss), COUNT_BIG(*) as (Count)
    FROM dbo.Sub_Portfolio_Losses
    GROUP BY (Category), (Year)
GO
CREATE UNIQUE CLUSTERED INDEX Idx ON dbo.Losses_CombinedPortfolio((Category), (Year));

My original goals were more ambitious, but indexed views are so restrive… I’d be happy enough just to get this to work.

Sadly, when I try to do a basic query on this indexed view (SELECT TOP (100) * FROM Losses_CombinedPortfolio ORDER BY Loss DESC), the query is just as slow, and the actual execution plan shows that it’s always going back to the source table and computing the aggregations from scratch each time:

actual execution plan

I can only assume that this is because my computed “Loss” column isn’t getting materialized – but that would surprise me, since the clustered index creation succeeds.


Note that my primary use-case for this view is to sort by Loss descending, but I couldn’t manage to explicitly create an index that includes it:

CREATE UNIQUE CLUSTERED INDEX Idx ON dbo.Losses_CombinedPortfolio(Category, Loss DESC, (Year));

I get the error:

Cannot create index or statistics 'Idx' on view 'dbo.Losses_CombinedPortfolio' because key column 'Loss' is imprecise, computed and not persisted. Consider removing reference to column in view index or statistics key or changing column to be precise. If column is computed in base table consider marking it PERSISTED there.

I tried resolving the “imprecise” by casting the summed loss to types other than float (even tried truncating it to bigint) but it seems this error is stemming from the underlying type used to compute the sum.

I’m confused – I’ve seen other questions claim that they were able to successfully perform aggregations such as sum in their indexed views, so I’m not sure why this wouldn’t work.