TRUNCATE TABLE WITH PARTITIONS empties one partition and logs far less than DELETE. See the syntax, the index rule and the limits in SQL Server.
SQL Server – Single Scan for 3 Operations – COUNT(*) COUNT(1) SUM(1)
Today we will take the same conversation to the next level by discussing Single Scan for 3 Operations – COUNT(*) COUNT(1) SUM(1).
Latch Waits at the Top: Reading sys.dm_os_latch_stats
Sample SQL Server latch waits by class, then connect ACCESS_METHODS_DATASET_PARENT, FGCB_ADD_REMOVE, and LOG_MANAGER to the next check.
SQL SERVER – SUM(1) vs COUNT(*) – Performance Observation
My Performance Observation found similar counting plans for a nonempty table. SUM(1) and COUNT(*) still differ on empty input.
Index Used for COUNT(*): SQL Server Scans the Narrowest One
The index used for COUNT(*) is the narrowest one that holds every row. See the pages, the scans and the reads that prove it in SQL Server.
SQL SERVER – Index Scans are Not Always Bad
Are Index Scans Bad? The answer No. Index Scans are Not Always Bad. This was in-depth discussed with my client during the Health Check.
Table Variables and Parallelism: Reads Yes, Inserts No
Table variables and parallelism: reads can run in parallel, inserts cannot. See the plan reason, the 1-row estimate at old levels, and when to use a temp table.