What is the clustering key on the table?

If you just want the total hours, you don't need to return all the rows at all, simply total the time differences. This is very easy if there are...

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.