MSSQL Productivity Tips – Part 1

This post going to be short and straight to the point 🙂 There are small compilation of 3 tips which can have positive impact on quality of your work.

  1. When doing UPDATE it is maybe usefull sometimes to see the results of updated value. It can be done by SQL OUTPUT command.

Example :

UPDATE SOME_TABLE
SET KValue=@NewValue
OUTPUT DELETED.KValue,inserted.KValue
WHERE tID=@Guid AND tVal = @PVal

Result will be old value and new value

sql_output1

2. Sometimes it’s make sense to write set of queries as a transaction. For example you want to upgrade DB or part of it. So it may useful to wrap it up by following code:

Example:

SET XACT_ABORT ON
BEGIN TRAN
DECLARE @IsToCommit AS BIT = 1

…  _WRITE_YOUR_SET_OF_QUERIES_ …

END
IF @IsToCommit = 0 BEGIN
PRINT ‘Changes are rolling back .  @IsToCommit param needs to be changed to ”1” in order to commit.’
ROLLBACK TRAN
END ELSE BEGIN
PRINT ‘Changes have been committed.’
COMMIT TRAN
END

3. There situations in which we want to see if there additional usages of some specific column name. For that we can use INFORMATION_SCHEMA.COLUMNS :

 –Check if any of the object( Table,View) has column name=’Address’

SELECT * FROM INFORMATION_SCHEMA.COLUMNS

WHERE COLUMN_NAME=’ADDRESS’

SQL_Search_Result

If you have any ideas, suggestions or you would love to share you own tips, write it down in a comments.

Leave a Reply

Your email address will not be published. Required fields are marked *