Thanks Jo.

I'm residential (local SAN - on premise) no cloud.

So the current recommendation is to not even do rebuilds?

ended up writing the results to a table in a different DB via a job and granted access to that table. works fine.

Thanks Perry/Fredrico.

Ahh great catch Jeffery! Much thanks!!

ahh persisted...hmm its not used that much so maybe overkill for this.

I like the two left joins - trying that now.

Thanks Phil - appreciate the help!

Kinda yeah - feel my pain

I know there is a translate function that can sometimes be used in place of 'replace' but I'm on v120. So no easy way out...

• This reply was modified 9 months, 3 weeks ago by  krypto69.
• This reply was modified 9 months, 3 weeks ago by  krypto69.

Yeah I tried the try cast - didn't work.

I just removed the formatting - fine for now.

Thanks guys!

this works - but I loose the formatting

SELECT
DISTINCT
    provTaxID
   ,provNPI
   ,provName
   ,provShortName
   ,'https://npiregistry.cms.hhs.gov/registry/provider-view/' + provNPI BillProvURL_NPPES
...

I think I just may be stuck with "it is what it is" - I know its a ton of records. Really just wanted to make sure I tried everything...

• This reply was modified 10 months, 3 weeks ago by  krypto69.

Sure, logical assumption and my fault for not specifying

It returned in same time..

Thanks Jonathon.

Thanks Brian - yeah that table gets massive inserts and updates so I guess indexed view is a bad option.

Tried using exists - same run time:

select...

Trying to index the view

--Set the options to support indexed views.
SET NUMERIC_ROUNDABORT OFF;
SET ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, ARITHABORT,
   QUOTED_IDENTIFIER, ANSI_NULLS ON;

--Create view with SCHEMABINDING.
IF OBJECT_ID ('dbo.FastEligibilityView',...

Jeff

the query is the slow part of a larger process.its not being posted to a screen - query is part of universe of claims process that needs to look at...

• This reply was modified 10 months, 3 weeks ago by  krypto69.
• This reply was modified 10 months, 3 weeks ago by  krypto69.

Thanks!

Thanks Ant-Green. Kinda figured that.

I have an A,B servers. Sys admins want to move the B server to the new location on one weekend and server A the following weekend.

Assuming...