Top 10 T-SQL tips for improved SQL Server development

Learn to script SQL Server backups, why T-SQL in SSIS has pros and cons and workarounds for T-SQL code causing SQL Server performance problems.

Having difficulty with your T-SQL programming extensions or simply want to make improvements within your SQL Server development? We've compiled a Top 10 list of the most popular tips your peers are reading. Find out how to script SQL Server backups, why T-SQL in SSIS has pros and cons and what to do about poorly written T-SQL code that's causing performance problems.

1. Restore basics: How to restore using T-SQL commands

SQL Server Enterprise Manager may be a quick and easy way to run backups and restores, but T-SQL commands offer much greater flexibility.

Find out how to script your SQL Server backups or restores.

2. Stored procedure: Execute T-SQL code from a file

Need to execute T-SQL code in a file from within other T-SQL code? Existing routines used to execute T-SQL code from a file, without using osql and xp_cmdshell, have many limitations. The sp_ExecuteSQLFromFile stored procedure addresses the shortcomings and adds new functionality.

3. T-SQL performance problems and solutions

Don't assume all SQL Server performance problems are database-related. Poorly written T-SQL code could be to blame. Learn about common problems and workarounds in this clinic.

4. How to debug T- SQL stored procedures

These easy-to-use graphical debugging tools in Visual Studio 2005 will help simplify the process of unit testing your T-SQL code.

5. Pitfalls of using T-SQL cursors

Cursors have a bad reputation: They're considered poor performers, resource hogs and a favorite tool of every inexperienced DBA. But they do have a place. Contributor Hilary Cotter explains how cursors work and their pros and cons.

6. T-SQL in SSIS: The power and the weaknesses

Using T-SQL vs. the Data Flow task in SQL Server Integration Services (SSIS) is a decision dependent on a number of factors. Serdar Yegulalp shares some things to consider, including data origin, operation complexity, stored procedure purpose and whether the data is likely to change in SSIS.

7. Developing with SQL Server 2005 Express

With SQL Server 2005 Express Edition, Microsoft offers a feature-rich tool set with a familiar interface free of charge. Edgewood Solutions' Jeremy Kadlec provides an overview of this SQL Server version that includes Management Studio Express.

8. Writing T-SQL functions to SQL Server system schema

When dealing with many applications that need similar functionality, often the best thing to do is to write functions directly to your SQL Server system's schema. This will enable all databases within the server to execute the function as though it was a regular expansion of the T-SQL language. This how-to will take you through the process.

9. T-SQL and PL/SQL languages for database-independent applications

Common languages in T-SQL and PL/SQL exist to allow developers to write applications in database-independent environments. Common languages and interfaces such as ANSI and ODBC offer solutions to the database interoperability problem.

10. Getting an environment variable value using T-SQL

There are times when passing parameters does not work. For example: when dealing with DTS packages or when an external (non-SQL Server) platform activates a process inside a SQL Server environment. Here's how to solve the problem by setting an environment variable in the Windows operating system and then getting its value inside the SQL Server environment.

This was first published in June 2007

Dig deeper on SQL-Transact SQL (T-SQL)

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

-ADS BY GOOGLE

SearchBusinessAnalytics

SearchDataCenter

SearchDataManagement

SearchAWS

SearchOracle

SearchContentManagement

SearchWindowsServer

Close