SQL Server 2019

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE dbo.MyComments 
(
  Textid   int identity NOT NULL primary key clustered,
  Comments varchar(max)
);

INSERT dbo.MyComments(Comments) VALUES
('cost expense cost'),
('Cost revenue revenue cost');


insert into dbo.MyComments(Comments)
select definition
from sys.all_sql_modules
cross join (values(1),(2),(3),(4)) as v(n)
order by v.n
8350 rows affected

--set showplan_xml on
set statistics time on
select t.textid, w.value, w.cnt
into yy
from dbo.mycomments as t
cross apply
(
select  cast(s.value as varchar(1000)) as value, count(*) as cnt
from string_split(t.comments, ' ') as s
where s.value <> ''
group by cast(s.value as varchar(1000))
) as w
order by t.textid
SQL Server parse and compile time: 
   CPU time = 125 ms, elapsed time = 125 ms.

 SQL Server Execution Times:
   CPU time = 11266 ms,  elapsed time = 11421 ms.
1100432 rows affected

--/*
SELECT c.Textid, s.value, COUNT(*) as cnt
into zz
  FROM dbo.MyComments AS c
  CROSS APPLY STRING_SPLIT(c.Comments, ' ') AS s
 -- where s.value <> '' 
  GROUP BY c.Textid, s.value
  ORDER BY Textid;
  --*/
Msg -2 Level 11 State 0 Line 0
Execution Timeout Expired.  The timeout period elapsed prior to completion of the operation or the server is not responding.
Msg 3613 Level 0 State 0 Line 2
SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 0 ms.

select 1


(No column name)


1


SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 0 ms.