SQL Server 中基于函数的索引

2021-12-13 00:00:00 indexing sql expression sql-server-2008 sql-server

我试图了解 SQL Server 中是否存在类似于 Oracle 或 PostgreSQL 中的基于函数的索引

I'm trying to understand if function based indexes similar to the one's in Oracle or PostgreSQL exist in SQL Server

在 PostgreSQL 中，我可以使用以下语法创建基于函数的索引:

In PostgreSQL, I can create a function based index using by using the following syntax:

CREATE INDEX sample ON "TestDB" (("expression1" || ' ' || "expression2"));

我发现了一篇文章，其中我发现了一个名为index onSQL Server 中的计算列.这是一个基于函数的索引，就像在 Oracle/PostgreSQL 中一样吗?谁能给我提供一个示例查询来创建/查看这样的索引?

I found a article where I found something called "index on computed columns" in SQL Server. Is this a function based index just like in Oracle/PostgreSQL? Can anybody provide me a sample query to create/view such an index?

推荐答案

我根据 Damien 的评论进行了进一步研究，并找到了一个非常接近匹配 Oracle/PostgreSQL 基于函数的索引的答案.

I researched a bit further based on Damien's comment and found an answer that comes very close to matching Oracle's/PostgreSQL's function based indexes.

我有一个名为 PARCELS 的表，我在其中使用如下所示的更改语句创建了一个新列 COMPUTEDPARCELS:

I have a table named PARCELS where I created a new column COMPUTEDPARCELS by using the alter statement as given below:

ALTER TABLE [PARCELS] ADD COMPUTEDPARCELS AS CONVERT(CHAR(8), [MAPNO], 112);

然后在计算列上创建索引:

And then create an index on the computed column:

CREATE INDEX function_index ON [PARCELS](COMPUTEDPARCELS);

当然，这个例子非常简单，但表现得就像一个基于函数的索引.

Of course the example is pretty simple but behaves just like a function based index.

相关文章