SQL View performance

rodgerkong 

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]


GO

And 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