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'

Useful articles

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