/ notes / sql

⇒ This is just a collection of notes, not a structured or comprehensive document. ⇐

sql misc

XACT_ABORT

From the book “Sql Server 2012 Internals”: Many bugs occur in application because developer don’t understand how the failure of a constraint affects a multiple statement transaction. The biggest misconception is that any error, such a constraint failure, automatically aborts and roll-backs the entire transaction.

Use XACT_ABORT ON to automatically abort transaction in case of any constraint error.

SET XACT_ABORT ON

Update statistics on all tables

DECLARE @currentTableName nvarchar(max) = ''

DROP TABLE IF EXISTS #tableListUpdateStats

SELECT '[' + table_schema + '].[' + table_name + ']' as TableNameFull INTO #tableListUpdateStats 
       FROM information_schema.tables WHERE TABLE_TYPE = 'BASE TABLE' ORDER BY table_schema, table_name

WHILE 1=1
BEGIN
	SET @currentTableName = (select top(1) TableNameFull from #tableListUpdateStats where TableNameFull > @currentTableName order by TableNameFull)
	IF @currentTableName IS NULL BREAK
	DECLARE @sqlCommand nvarchar(max)  = 'UPDATE STATISTICS ' + @currentTableName + ' '
	PRINT @sqlCommand
	EXEC sp_executesql  @sqlCommand	
END

DROP TABLE IF EXISTS #tableListUpdateStats

SQLCMD input parameters

Using the -v option, we can pass parameters to a SQL script invoked by SQLCMD. The parameters can then be used inside the sql script using the $(param) syntax.

sqlcmd -S (LocalDb)\MSSQLLocalDB -d MyDB -i myscript.sql -v varCD="%CD%"

PRINT 'CD: ' + '$(varCD)'
SET @csvCurrPath  = '$(varCD)' + '\..\..\Data\OUT\data.csv'

Show query plan cache

SELECT TOP 1000
	databases.name,
	dm_exec_sql_text.text AS TSQLText,
	dm_exec_query_stats.creation_time, 
	dm_exec_query_stats.execution_count,
	dm_exec_query_stats.total_worker_time,
	dm_exec_query_stats.total_elapsed_time, 
	dm_exec_query_stats.total_logical_reads, 
	dm_exec_query_stats.total_physical_reads, 
	dm_exec_query_plan.query_plan
FROM sys.dm_exec_query_stats 
CROSS APPLY sys.dm_exec_sql_text(dm_exec_query_stats.plan_handle)
CROSS APPLY sys.dm_exec_query_plan(dm_exec_query_stats.plan_handle)
INNER JOIN sys.databases ON dm_exec_sql_text.dbid = databases.database_id
WHERE name = 'Contoso 1M'
ORDER BY creation_time DESC

Useful articles

“SQL Server internals and architecture guides” : https://learn.microsoft.com/en-us/sql/relational-databases/sql-server-guides