How to execute SQL files using T-SQL?

Sometimes ago I read an article on how to execute the sql file using powershell by Martin Bell. After reading it the question came into my mind is “Why not just use T-SQL? Would that be simpler? “. Indeed, powershell gives you a wide approach of doing the database activities, the same can be achieved using T-SQL as well. The below code snippet gives a overview of “How to execute sql using T-SQL”

DECLARE @fPath VARCHAR(4000) = 'C:\SQL\Script1.sql'
DECLARE @outPath VARCHAR(4000) = 'C:\SQL\Script_exec_log.txt'
DECLARE @serverPath VARCHAR(20) = 'localhost'
DECLARE @sqlString VARCHAR(4000)
DECLARE @dbList VARCHAR(1000), @dbName VARCHAR(100), @rowCount INT
SELECT @dbList=COALESCE(@dbList + ',', '')+ name FROM sys.databases WHERE name like '%Test'
SET @rowCount = @@rowcount WHILE 1= 1
BEGIN
SET @dbName = CASE WHEN (@rowCount = 1 OR @dbList != @dbName) THEN @dbList ELSE SUBSTRING(@dbList, 1, CHARINDEX(',', @dbList)-1) END
SET @sqlString = 'SQLCMD -E -S'+@serverPath+' -d'+@dbName+' -i'+@fPath +' -o'+ @outPath
PRINT @sqlString
EXEC master..xp_cmdshell @sqlString
IF @dbList= @dbName BREAK
SET @dbList = SUBSTRING(@dbList, CHARINDEX(',', @dbList)+1, LEN(@dbList))
END

Do’s and Don’ts in T-SQL

“What are Do’s and Don’ts while writing the T-SQL?” I heard this many time from developers or new learners or even some of the interviewers. You may get a lot many articles on Google if you search for T-SQL Best practices. In this article I have summarized Do’s & Don’t for your quick reference. I hope this will help you next time you write the T-SQL.

Do’s

  • Finalize or decide upon the database naming convention across the environment.
  • SELECT <required fields>. This will result in less I/O and network traffic and thus better performance.
  • Frequently consider advance SQL or views or table variables. But excessive use of table variable can cause memory overhead.
  • Writing explanatory comments in Code. This helps others to understand the code plainly. Writing or maintain lengthy comments won’t impact the code performance
  • Opt for set based approach instead of procedural approach. In exceptional cases, CURSOR can be avoided using WHILE loops.
  • If your database contains heavy text based search opt for FULL text search instead of LIKE operator.
  • Using SET NOCOUNT hint at the beginning of the code this improves the performance by reducing the network traffic.
  • Avoid Dynamic SQL. Dynamic SQL likely to be slower than the static SQL, as it generates the execution plan every time at runtime.
  • Using Error handling in stored procedures. @@ERROR or TRY CATCH.
  • Use the Column list in INSERT statement.
  • Using Transaction Handling (BEGIN TRANSACTION, COMMIT TRANSACTION, ROLLBACK TRANSACTION) if transaction involves more than one data manipulation (INSERT/UPDATE/DELETE) operation.
  • Use single (not double) blank lines to separate logical pieces of T-SQL code, and do so liberally

Don’ts

  • Use SELECT *.
  • Use temporary tables in processing data. It creates a more disk IO which could hit the performance.
  • Use the server side cursor
  • Use Sub queries in JOIN. In Sub-query Inner query is executed for each and every row in Outer query. This can be avoided using JOINS.
  • Use prefix for object “sp_”. The prefix “sp_” is reserved for System procedures.
  • Use the Index Name in Query.
  • Use wildcard character at the beginning of the word while searching using LIKE keyword.i causes index scan instead of index seek
  • Use locking Hints in SQL statement as SQL server automatically handles the locking in a way to minimize the level of locking.

For more Do’s and Don’t please navigate through the MSDN.