Skip to main content
9 votes
Accepted

Are unique filtered indexes considered an antipattern for enforcing constraints?

They are not quite considered an anti-pattern, but they are to some extent a denormalization. Under very strict 6th Normal Form rules, you should really take out that bit column and instead make a ...
Charlieface's user avatar
  • 13.7k
8 votes
Accepted

Best Way to Determine if a Table has a Long Running Transaction

The best way to test if a lock can be acquired is to try to acquire it. Use LOCK_TIMEOUT to give up after waiting a specified time, or immediately if a conflicting lock is encountered, as you prefer. ...
Paul White's user avatar
  • 87k
3 votes

create_date and modify_date for sys.objects in MSSQL shows same date

I assume you are actually talking about sys.objects view, and not sys.sysobjects. It seems like the modify_date is set to create_date until some other changes to table are made (which is pretty common ...
Yano_of_Queenscastle's user avatar
3 votes
Accepted

Trouble with slow stored procedure performance on 35M rows with CTE/Temp table

This is a classic Kitchen Sink Query. While you could add OPTION (RECOMPILE) as a quick fix, this comes at a cost of recompiling on every run. Instead, you should build a dynamic query. Don't forget ...
Charlieface's user avatar
  • 13.7k
3 votes

SQL Server Trace Flag 5013

My issue here is that this flag undocumented, so we are not able to verify exactly what it does. Depends on who told you to enable it. If Microsoft Support told you to do it, then it should be a ...
Sean Gallardy's user avatar
2 votes

What is the purpose of parent_plan_handle in sys.dm_exec_cached_plans?

parent_plan_handle is undocumented. There is no official purpose. It would not work for you here. I am attempting to prove whether the plan cache is caching statements individually within multiple ...
Paul White's user avatar
  • 87k
2 votes
Accepted

Understanding Recovery Forks in SQL Server: Meaning and Significance

From the information I have gathered, I understand those GUID are kind of the DNA of the database and no matter how far on the kin line the database is, it shares info to identify itself and the first ...
Ronaldo's user avatar
  • 5,636
2 votes

Best Way to Determine if a Table has a Long Running Transaction

You could in theory check sys.dm_tran_locks to see if there is an existing lock, and otherwise wait and loop again. DECLARE @startLoop datetime2 = SYSUTCDATETIME(); WHILE 1=1 BEGIN IF NOT EXISTS (...
Charlieface's user avatar
  • 13.7k
2 votes

Are unique filtered indexes considered an antipattern for enforcing constraints?

A more realistic use than a table of fools, would be a certain style of table with versioned rows, where the current row is marked. There can, of course, be only one current row, and there needs to be ...
Steve's user avatar
  • 549
1 vote

Are unique filtered indexes considered an antipattern for enforcing constraints?

The example is a bit odd. You have FoolID which I would expect to be the primary key of the table. Making it kind of a filtered primary key doesn't seem right. Using a filtered index to ensure that ...
David Wiseman's user avatar
1 vote

Tsql help - doing a large update from CSV file to a table in ssms

You can use https://sqlgenerator.io to convert an excel/csv/json file into a CREATE TABLE and INSERT INTO TABLE SQL statements with the corresponding data. It's a free tool I created as I have to do ...
Ryan Waldorf's user avatar
1 vote

Running into MSSQLSERVER_701 "insufficient memory in the resource pool to run the SQL queries" when Azure SQL backup runs

The issue does appear to be too many backup jobs running on the same server at the same time. There are multiple SQL instances on the same server, and their backup schedules were for the same time. ...
j. kiel's user avatar
  • 21
1 vote

Does SQL Server application database and tempdb collation mismatch affect performance

I believe the more likely potential problem you could run into would be a collation mismatch error. That would occur if the ERP software executes a query that does something like a JOIN or ...
Solomon Rutzky's user avatar
1 vote
Accepted

.DTSX package is there a way to wrap a workflow into a transaction

You can read about SSIS transactions here. Configure a package to use a single transaction In this option, the package itself initiates a single transaction. You configure the package to initiate ...
David Browne - Microsoft's user avatar
1 vote
Accepted

TRUNCATE WITH PARTITION Constantly Fails on 1 Out of 620 Tables

As Dan Guzman mentioned, a truncate requires exclusive access to the entire table. It appears this is the case even when truncating a specific partition, as opposed to just requiring exclusive access ...
Ross Bush's user avatar
  • 567

Only top scored, non community-wiki answers of a minimum length are eligible