SQL Snippets - PHIntelligence/CodingStandards GitHub Wiki

Find Duplicates

SELECT   Col1
        ,Col2
        ,Col3
        ,COUNT(*)
FROM     dbo.TableName
GROUP BY Col1
        ,Col2
        ,Col3
HAVING   COUNT(*) > 1

Bulk Load from delimited text file

 BULK INSERT dbo.data
         FROM '\\ServerName\Folder\data.txt'
         WITH
         (
          FIRSTROW           = 1
         ,FIELDTERMINATOR    = '|'
         ,ROWTERMINATOR      = '0x0a'
         ,KEEPNULLS
         )
GO

Get a row count for tables without the wait!

SELECT		 SCM.name
		,TAB.name
		,STAT.row_count
FROM		sys.tables			TAB
INNER JOIN	sys.dm_db_partition_stats	STAT ON TAB.object_id = STAT.object_id
INNER JOIN	sys.schemas			SCM  ON SCM.schema_id = TAB.schema_id
WHERE		TAB.type_desc	= 'USER_TABLE'
AND		TAB.name	= 'MyTableName'

Shrink the tempDB database

USE  tempdb
GO

DBCC FREEPROCCACHE
DBCC SHRINKDATABASE (tempdb, 1) -- 1 is the target percentage

-- check the filesize

SELECT	 FilePath = physical_name
	,FileSizeKB = (size * 8)
FROM sys.database_files