I don't get why I can't use the primary key as index in my view.
Here's the main table
CREATE TABLE [dbo].[xFedDBLogMsg](
[ID] [int] IDENTITY(1,1) NOT NULL,
[msgType] [int] NOT NULL,
[date] [datetime] NOT NULL,
[delay] [time](7) NOT NULL,
[error] [bit] NOT NULL,
[processID] [int] NULL,
CONSTRAINT [PK_tFedDBLogMsg] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
Here's the view
CREATE VIEW [dbo].[tFedDBLogMsg]
AS
SELECT
L.ID
, L.msgType
, L.[date]
, M.MsgSent
, M.MsgReceived
, L.[delay]
, L.error
, L.processID
, NEWID() AS INTERNALID
FROM dbo.xFedDBLogMsg AS L
LEFT JOIN FedDBMsg.dbo.tFedDBLogMsg AS M ON (
M.ID = L.ID
)
And here the procedure that gives me a warning:
ALTER PROCEDURE spGetFedDBErrorsByID (
@pIDS AS dbo.typeNumberList READONLY
)
AS
BEGIN
SELECT
MSG.ID
, MSG.msgType
, MSG.date
, MSG.MsgSent
, MSG.MsgReceived
FROM (
SELECT
CAST(ID.n AS INT) AS ID
FROM @pIDS AS ID
) AS X
INNER JOIN MyGolf.dbo.tFedDBLogMsg AS MSG WITH (INDEX(PK_tFedDBLogMsg)) ON (
MSG.ID = X.ID
)
END
GO
Warning: Index hints supplied for view 'MyGolf.dbo.tFedDBLogMsg' will be ignored.
PS: There are many variations of passages of Lorem Ipsum available, but the majority have suffered alteration in some form, by injected humour, or randomised words which don't look even slightly believable. If you are going to use a passage of Lorem Ipsum, you need to be sure there isn't anything embarrassing hidden in the middle of text. All the Lorem Ipsum generators on the Internet tend to repeat predefined chunks as necessary, making this the first true generator on the Internet. It uses a dictionary of over 200 Latin words, combined with a handful of model sentence structures, to generate Lorem Ipsum which looks reasonable. The generated Lorem Ipsum is therefore always free from repetition, injected humour, or non-characteristic words etc.
In the part
WITH (INDEX(PK_tFedDBLogMsg))
is PK_tFedDBLogMsg
besides the name of the constraint, a clustered index on the view as well?
If so, be sure to use the NOEXPAND
option as well.