On top of that throw in that, the DDL and the DML have different rules. In DML, the {false, unknown} values are treated the same and reject a search condition....

Cluster the Attrib table on ( ClinicID, ItemID ) and any of the methods will likely run fast enough, although I like the CROSS APPLY approach myself.

Nothing's wrong with it, per se.  I thought checking LEN was a bit more efficient, but either will do.  Edit: SQL may actually convert { = '' } to checking...

1 Don't concatenate the strings, that's more overhead, just check the len of each:

LEN([address 1]) + LEN([address 2]) + LEN([address 3]) = 0 /*easier, to me*/

OR

(LEN([address 1]) = 0 AND...

We have to make many guesses here, since you didn't specify any details on these tables.

If you only need to lookup consolidation_ind if it is = 1, then create a...

As regards the original "1. Key column ...", it should at least be demoted to a secondary key (to make the clustering key unique).

This table almost certainly should be clustered...

Try this:

BEGIN TRY

BEGIN TRANSACTION [Tran1]

select top (1) @caid=ca.id from Cases ca WITH  (UPDLOCK)

where ca.applicationstatusentityID in (1,2,12,15)

Insert into CaseAssigned table the caseId selected above

Delete from the Cases table once a case...

I ignored the actual calc before, but Drew is quite right, of course, that needs corrected too:

SELECT TOP (100) PERCENT 
    DATEADD(SECOND, DATEDIFF(SECOND, base_date,...

I'd use RIGHT rather than PATINDEX, just because I think's it mildly clearer:

WHERE RIGHT(name, 2) LIKE '[0-9][0-9]'

You can also use a computed column for as_of_month, there's no need to physically store it again.

as_of_date date NOT NULL,

as_of_month AS CONVERT(varchar(6), as_of_date, 112),

That column is fully usable by all...

I'm guessing the ParentTaskCd is used to link the tasks together.  Naturally adjust the code as needed to get the specific results you need, but this is a general way...

Assuming you don't have dates before 1980, then:

SELECT TOP (100) PERCENT DATEADD(MINUTE, ROUND(DATEDIFF(SECOND, base_date, DateTime) / 10.0, 0) * 30, base_date) AS Date_Time, SUM(Burner1) AS Burn1
FROM dbo.tblOilBurner
CROSS...

Here's a sample function using a physical tally table (it's not worth the trouble to me to try to use an inline tally table within a scalar function).  I've put...

Don't know if it's officially deprecated, but it has lots of issues, so, yeah, probably better to stick to decimal.

As to as_of_month, you'd be better off converting that to go...

1. bigint would be better.
2. date would be much better.
3. don't need to store.  Use code on the display side to show only year and month from the as_of_date column.

...