You did review the stats SQL Server provides on index usage and missing indexes before you made your changes, right?

Edit: What indexes you think SQL will need/use and what indexes...

I think that's over-stated. Most optimizers will short-circuit when they safely can. Presumably later versions of the optimizer...

I wouldn't want to put them in a separate schema unless you really had to. Aside from hideous renaming issues (*), you might end up with what seems like...

If you want to keep/commit previous activities in the loop, you likely need to explicitly BEGIN and COMMIT a tran for each loop, something like this:

WHILE ...

BEGIN

...

Try clustering the table by datetime (rather than identity, I'm guessing) and specify...

Yes, it can be.

For overall performance, what is really critical is determining the best column(s) for the...

Given that it's a single string being split, I don't think there's much to gain by changing that code.

Don't care about pk/fk specifically. What indexes currently exist on these tables? Could you add a computed column(s) and create...

Not generally. But CASE WHEN conditions are always evaluated serially (as far as returning a result is concerned), so in that...

Also, be sure the relevant non-DBAs properly enable IFI and other local/policy/Windows permissions that are needed for SQL accounts, proxy accounts, etc..

Yes, you can create a non-clustered index w/o a clustered index, but you (almost) never should.

The cluster key does not have to be defined as unique (SQL will internally make...

Honestly the DBA should have admin access to the server to have maximum effectiveness.

If you don't, and in particular if you don't have direct directory access/control, be extraordinarily careful before...

You can't have a

GO

within a stored procedure. The first GO ends the stored procedure definition.

Don't use a simple UNIQUE constraint. You have to define a primary key, and the only way it's really useful to your query is if the first column in...

I'd strongly urge you to use a character-based method rather than simply trying to CAST to numeric, which is almost certain to cause a non-numeric data exception.

SELECT valid_id, SUBSTRING(valid_no, PATINDEX('%[^0]%',...