Transact -SQL statements can be grouped together in batches, they can persist in the database, they can repeatedly execute as stored procedures, and they can be made to automatically fire as triggers. It is essential that you understand the differences between these functions and that you understand that their actions are not mutually exclusive.
Transact-SQL stored procedures can be quite complex, and they can become a significant portion of your application's source code. Fortunately, Visual Studio provides debugging support for SQL Server stored procedures.
Programming effectively with Transact-SQL also requires that you understand transactional topics, such as when transactions will be committed and when they can be rolled back. Since you'll likely be working in a multiuser environment, it is vital that you make the appropriate concurrency and consistency choices to suit the isolation level for your environment. Understanding isolation levels is also important for working with BLOBs in SQL Server using the special operators READTEXT, WRITETEXT, and UPDATETEXT. And no matter what task you are performing, it is important to realize that you must plan for various environmental options that will affect the behavior and semantics of your Transact-SQL code.