我为之工作的公司使用第三方软件,而且我们没有任何应用程序代码的公开,所以我不能更改很多db对象。
我试图在一个不是sarg的视图中做一些工作,视图中有一个合并来将空值替换为0,并且在应用程序上调用该视图,在同一列上有一个筛选器。
SQL Server版本:2005
场景如下:
IF OBJECT_ID('test01' , 'U') IS NOT NULL
DROP TABLE test01;
CREATE TABLE test01 ( ID INT IDENTITY(1,1) PRIMARY KEY
, Parent NVARCHAR(100)
);
GO
IF OBJECT_ID('test02' , 'U') IS NOT NULL
DROP TABLE test02;
CREATE TABLE test02 ( ID INT IDENTITY(1,1) PRIMARY KEY
, ParentID INT
, UserName NVARCHAR(100)
);
GO
ALTER TABLE test02 ADD CONSTRAINT FK_test02_ParentID FOREIGN KEY (ParentID) REFERENCES test02(ID);
INSERT INTO test01
SELECT 'DGN010101'
UNION ALL SELECT 'DGN020202'
UNION ALL SELECT 'DGN030303'
UNION ALL SELECT 'DGN040404'
GO
INSERT INTO test02 (ParentID, UserName)
SELECT 1, 'DGN010101'
UNION ALL SELECT 2, 'DGN020202'
UNION ALL SELECT NULL, 'DGN030303'
GO
第一次测试-SARG问题
--drop view vw_test
CREATE VIEW vw_test
AS
SELECT tmp1.ID as ParentID
, tmp1.Parent
, COALESCE(tmp2.ID, 0) as UserID
, tmp2.UserName
FROM test01 tmp1
LEFT JOIN test02 tmp2
on tmp1.ID = tmp2.ID
------------------------
-- An index scan is performed
SELECT ParentID
, Parent
, UserID
, UserName
FROM vw_test
WHERE UserID = 1
第二次测试-无SARG问题
--drop view vw_test
CREATE VIEW vw_test
AS
SELECT tmp1.ID as ParentID
, tmp1.Parent
, tmp2.ID as UserID
, tmp2.UserName
FROM test01 tmp1
LEFT JOIN test02 tmp2
on tmp1.ID = tmp2.ID
------------------------
-- Index seek is performed
SELECT ParentID
, Parent
, UserID
, UserName
FROM vw_test
WHERE UserID = 1
要记住的东西:
我仍然需要用0替换空值。
我无法改变如何调用视图。
我很感激你们。
最佳答案
如果test02表的id列(userid)不允许为空,请尝试以下版本:
CREATE VIEW vw_test2
AS
SELECT tmp1.ID as ParentID
, tmp1.Parent
, tmp2.ID as UserID
, tmp2.UserName
FROM test01 tmp1
JOIN test02 tmp2
on tmp1.ID = tmp2.ID
UNION ALL
SELECT tmp1.ID as ParentID
, tmp1.Parent
, 0 as UserID
, tmp2.UserName
FROM test01 tmp1
LEFT JOIN test02 tmp2
on tmp1.ID = tmp2.ID
WHERE tmp2.ID IS NULL;
GO