First, if you're getting corruption issues in TempDB, I'd start by making sure I've got DBCC CHECKDB reporting clean results across my user databases. I'd also restore those backups on another server and run DBCC CHECKDB there just to make sure the problems are only confined to TempDB.
Then I'd remove any filter drivers on the servers - antivirus, disk defragmenting, storage mirroring, etc - and try to run multiple load-intensive queries against TempDB to see if I can reproduce the issue. Do simple select-intos, dumping Cartesian joins of your user databases into TempDB.
Finally, if I can get an outage window, I'd try SQLIO - not SQLIOSIM - hammering the TempDB drives. SQLIOSIM is interesting if you want to reproduce a vague similarity of a benchmark, but SQLIO is better if you just want to repeatedly punch your storage in the junk. Plus, it's really good about bubbling failures up quickly.
You also asked:
how much more likely is drive corruption than, say, I/O path driver
issues (storport, HBA driver or firmware, filter drivers).
In my experience, the most common corruption issues are connectivity problems, followed by storage processor/controller firmware bugs, followed by filter drivers (I see less only because they were so unreliable that most folks have uninstalled them), finally followed by drive corruption. After all, in modern storage, the RAID controllers are pretty good about catching problems with a single failing drive.
Yes, you can do this with a COMPUTED
column, It's a workaround really as the column has to be PERSISTED
to be used for the foreign key constraint so it consumes storage space:
CREATE TABLE dbo.OtherTable (
ID INT PRIMARY KEY IDENTITY(1,1),
Blah VARCHAR(100),
FieldName AS CAST('This' AS VARCHAR(100)) PERSISTED NOT NULL
) ;
ALTER TABLE dbo.OtherTable WITH CHECK
ADD CONSTRAINT [FK__OtherTable__Blah]
FOREIGN KEY (FieldName, Blah)
REFERENCES Lookup (FieldName, Value) ;
Tested at SQL-Fiddle.
The table can then be used as if the FieldName
does not exist. You could even define a view that does not include this column and make your applications use that view: SQL-Fiddle-2
The syntax you hoped for, although looking pretty, has not been implemented in any DBMS I know of:
ALTER TABLE dbo.OtherTable WITH CHECK
ADD CONSTRAINT [FK__OtherTable__Blah]
FOREIGN KEY('This', Blah)
REFERENCES Lookup (FieldName, Value) ;
Note however that the workaround with the computed columns would be 100% equivalent to this syntax, if only SQL-Server removed in the future the restriction of using only persisted columns. You could add a Connect item with your request.
For the question whether there is any other DBMS that allows this, no, there isn't. Check also a related question:
Are there DBMS that allow a Foreign Key that References a View (and not only base tables)?
If you really don't want to use this method (because of the additional storage requirements, alternative paths would be to enforce the constraint via procedures or triggers (Note that you would have to write procedures or triggers for both tables involved.)
Best Answer
A very basic example would be to get the
AVG
andSTDEV
of the range of numbers and then exclude any that were more than 1 Standard Deviation from that average.You then take the average of the new range.
This is quite a basic bit of code (don't forget the
CAST
to aDECIMAL
) which you can expand upon to make it more suitable to your needs.