Forum Discussion
SQL View performance
Hello! if a base SQL table has both cluster and non clustered index, do I need to create again indexes in the SQL view to get better query performance.
It depends on the structure of your view. In general, if a view uses only one table and the main fields designed by the query condition have indexes in the base table, the view need not built indexes. It also depends on the context in which the view is used, such as whether has more conditions when using the view. Other situations require closer analysis. Therefore, it is a good idea to provide table structure, definition of views, and use cases to provide better support.
Thanks for your response!
Here is the base table structure
CREATE TABLE [dbo].[MyTable](
[Row_Id] [bigint] NOT NULL,
[Year_Code] [varchar](100) NOT NULL,
[Row_Values] [text] NULL,
[Date_Refreshed] [datetime] NOT NULL,
CONSTRAINT [PK_MyTable_Row_Id] PRIMARY KEY CLUSTERED
(
[Row_Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
ALTER TABLE [dbo].[MyTable] ADD CONSTRAINT [DF_MyTable_Date_Refreshed] DEFAULT (getdate()) FOR [Date_Refreshed]
GO
CREATE NONCLUSTERED INDEX [IX_MyTable_Code] ON [dbo].[MyTable]
(
Year_Code ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
GO
the SQL View structure is as below
CREATE VIEW [dbo].[vw_MyTable] AS
SELECT
JSON_VALUE(cast([Row_Values] as varchar(8000)), '$."id"') AS id,
JSON_VALUE(cast([Row_Values] as varchar(8000)), '$."year"') AS year_code,
JSON_VALUE(cast([Row_Values] as varchar(8000)), '$."emp_id"') AS emp_id,
JSON_VALUE(cast([Row_Values] as varchar(8000)), '$."emplocalid"') AS emplocalid,
JSON_VALUE(cast([Row_Values] as varchar(8000)), '$."firstname"') AS firstname,
JSON_VALUE(cast([Row_Values] as varchar(8000)), '$."lastname"') AS lastname,
JSON_VALUE(cast([Row_Values] as varchar(8000)), '$."middlename"') AS middlename,
JSON_VALUE(cast([Row_Values] as varchar(8000)), '$."dateofbirth"') AS dateofbirth,
JSON_VALUE(cast([Row_Values] as varchar(8000)), '$."race"') AS race,
JSON_VALUE(cast([Row_Values] as varchar(8000)), '$."gender"') AS gender
FROM [dbo].[MyTable]
GOAnd I would like to query the view as below and my question do I need to create any index on the view to get better query performance.
SELECT [id]
,[year_code]
,[emp_id]
,[emplocalid]
,[firstname]
,[lastname]
,[middlename]
,[dateofbirth]
,[race]
,[gender]
FROM [dbo].[vw_MyTable] where [year_code] ='yr2023'
Looking forward to hear from you