Reputation: 5319
I'm trying to create an index on a view and it keeps failing, I'm pretty sure its b/c I'm using an alias for the column. Not sure how or if I can do it this way. Below is a simplified scenario.
CREATE VIEW v_contracts WITH SCHEMABINDING
AS
SELECT
t1.contractid as 'Contract.ContractID'
t2.name as 'Customer.Name'
FROM contract t1
JOIN customer t2
ON t1.contractid = t2.contractid
GO
CREATE UNIQUE CLUSTERED INDEX v_contracts_idx ON v_contracts(t1.contractid)
GO
---------------------------
Incorrect syntax near '.'.
CREATE UNIQUE CLUSTERED INDEX v_contracts_idx ON v_contracts(contractid)
GO
---------------------------
Column name 'contractid' does not exist in the target table or view.
CREATE UNIQUE CLUSTERED INDEX v_contracts_idx ON v_contracts(Contract.ContractID)
GO
---------------------------
Incorrect syntax near '.'.
Anyone know how to create an indexed view using aliased columns please let me know.
Upvotes: 1
Views: 471
Reputation: 755531
How about a comma between the two columns???
SELECT
t1.contractid as 'Contract.ContractID' -- <=== comma missing here
t2.name as 'Customer.Name'
And I probably wouldn't really use "Contract.ContractID" as my alias..... the dotted notation has special meaning in SQL Server (database.schema.object) - so I would avoid anything that could cause trouble there.....
CREATE VIEW v_contracts WITH SCHEMABINDING
AS
SELECT
t1.contractid as 'ContractID' , -- comma here at the end!!
t2.name as 'CustomerName'
FROM contract t1
JOIN customer t2 ON t1.contractid = t2.contractid
GO
CREATE UNIQUE CLUSTERED INDEX v_contracts_idx ON v_contracts(ContractID)
GO
Upvotes: 1
Reputation: 39986
Why are you aliasing the tables if you are simply going to re-alias the columns back to the original?? Just do
CREATE VIEW v_contracts WITH SCHEMABINDING
AS
SELECT
Contract.ContractID,
Customer.Name
FROM contract
JOIN customer
ON contract.contractid = customer.contractid
GO
And yes, you were missing a comma.
Upvotes: 0
Reputation: 135181
try using brackets around the column name because the name is not a valid column name
CREATE UNIQUE CLUSTERED INDEX v_contracts_idx
ON v_contracts([Contract.ContractID])
GO
Also indexed views require 5 or so SET options to be on, more info here: http://msdn.microsoft.com/en-us/library/ms191432.aspx
Upvotes: 2