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
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
EXEC master..xp_cmdshell @sqlString
IF @dbList= @dbName BREAK
SET @dbList = SUBSTRING(@dbList, CHARINDEX(',', @dbList)+1, LEN(@dbList))
“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.
- 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
- 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.