I certainly agree that the overhead of a variable isn't needed.

But tThe RIGHT function is going to implicitly convert the value to varchar anyway, so I would stick...

ALTER TRIGGER [dbo].[xxatr_slauf_AufartChangeU]
ON [dbo].[XXASLAuf]
AFTER UPDATE
AS
SET NOCOUNT ON;
IF UPDATE(aufart)
BEGIN
  DECLARE @aufintnr VARCHAR(8000)
  SET @aufintnr = ''
  SELECT @aufintnr...

Best practice is to explicitly specify the table name/alias for every column in a query.  Otherwise, if one of the tables adds a column with the same name, the same...

Other issues:
1) if there's an error in a db file (such as would show up in a DBCC CHECKDB), it won't reattach
2) during a reattach, the file gets...


SELECT *
FROM #Date
WHERE ISDATE(DateString) = 0 OR
  DateString NOT LIKE '__[/.-]__[/.-]____' OR
  SUBSTRING(DateString, 3, 1) <> SUBSTRING(DateString, 6, 1)

It's no longer needed for moving files around on the same instance.  Instead you can update the master catalog with the file locations, take the db offline, move the files,...

You don't need an ID in this table at all (at least on what's known so far); in fact, as so often is the case, it corrupts what would be...

It may depend on specifically what you're trying to do.

For example, if you just want to execute a stored proc in the context of that db, you can...

Based on what's been show so far, I believe the best clustering key for this table would be:
( ProductCode, ValidFrom, ValidTo )
rather than just ValidFrom and ValidTo, assuming...

Yeah, your "DBAs" are dead wrong, and don't seem to understand indexing at even a basic level, which is a tough situation for you (and all the dbs they control!).

It seems as if you'd be scanning both tables anyway, since you're reading all the master and details (?).

Maybe just?:

FROM Details d
INNER JOIN Master m...