In the Stores table, use a function to compute a persisted column that is the store name with all non-alphabetic characters stripped. Do the comparisons on that column, but...

To make sure you don't get a really poor plan, force SQL to recompile the query:

SELECT p.ProjectID, p.ProjectName, p.ProjectDescription

FROM tbl_Projects p

WHERE

p.ProjectID = @ProjectNumber

OPTION(RECOMPILE)

What is the clustering key on the table?

Can you determine a range of clustering key values for the rows you want to DELETE?

Full-text indexing would likely help you the most here.

I don't think you actually need to go thru all those gyrations; instead, try this:

;WITH allstores(storeid, doc, article) AS(

SELECT 'ALL', '0010', '001' UNION ALL

...

Looks reasonable to me. I don't see a "better" way.

SELECT

ProjectID,

Project_Name AS [Project_Name|UpdateDescription],

CONVERT(varchar(10), Start_date, 120) AS Start_date,

ProjectDescription

FROM #tbl_projects

UNION ALL

SELECT

...

Since it's only three values, maybe this:

...

,isnull(cast(CASE ca1.COMM_max WHEN 1 THEN COMM1 WHEN 2 THEN COMM2 ELSE COMM3 END*QTY*1.8 as numeric(6,2)),0) as RATE

FROM dbo.yourtablename

CROSS APPLY (

SELECT...

MysteryJimbo (5/13/2014)

---

ScottPletcher (5/13/2014)

---

You have broad control of the frequency of cdc scan processing, and can even customize it if you want. I wouldn't ever want to risk the never-ending...

You have broad control of the frequency of cdc scan processing, and can even customize it if you want. I wouldn't ever want to risk the never-ending maintenance of...

You can't use a standard CHECK constraint, as all values must be available on a single row for that.

You could easily use an AFTER DELETE, INSERT[, UPDATE ] trigger if...

CDC should perform far better than hand-written triggers on all those tables.

Make sure you have sufficient pre-allocated log space to hold the changes until CDC can process them all.

It's testing to make sure that @Num contains only "0"-"9"s and/or "-"s.

Jeff Moden (5/12/2014)

---

ScottPletcher (5/9/2014)

---

I don't understand the rationale...

I don't understand the rationale for that statement....