在索引视图中,可否使用自定义函数?
WZZ 2006-11-08 11:41:07
CREATE TABLE SUPPLY1 (
supplyID INT PRIMARY KEY,
supplier CHAR(50)
)
CREATE TABLE SUPPLY2 (
supplyID INT PRIMARY KEY,
supplier CHAR(50)
)
INSERT SUPPLY1 VALUES ('1', 'CaliforniaCorp')
INSERT SUPPLY1 VALUES ('5', 'BraziliaLtd')
INSERT SUPPLY2 VALUES ('1', 'FarEast')
INSERT SUPPLY2 VALUES ('5', 'NZ')
-----------------------
CREATE FUNCTION dbo.scalar(@supplyID float)
RETURNS CHAR(50)
WITH SCHEMABINDING
AS
BEGIN
DECLARE @ AS CHAR(50)
SELECT @ = supplier FROM dbo.SUPPLY2 WHERE supplyID = @supplyID
RETURN @
END
GO
-- SELECT dbo.scalar(supplyID), * FROM SUPPLY4
-----------------------
/* 使用自定义函数 */
CREATE VIEW all_supplier_view WITH SCHEMABINDING
AS
SELECT dbo.scalar(supplyID) AS SID, supplyID, supplier
FROM dbo.SUPPLY1
-- SELECT * FROM all_supplier_view
------------------------
CREATE UNIQUE CLUSTERED INDEX /* 执行此句时出错 */
idx_all_supplier_view ON all_supplier_view(supplyID)