From the Expert's Desk

This section contains:

• Best practices 
• Tips and tricks
• FAQs

Best Practices

The following best practices can be considered while the query statement in SQL Server 2005.

• When you write stored procedures, and user-defined functions,  you need to decide whether to use traditional Transact-SQL or a programming language that is compatible with the .NET Framework, such as Visual Basic .NET or C#. Transact-SQL is best for situations in which the code primarily performs data access with little  no procedural logic. Programming languages that are compatible with the .NET Framework are best-suited for computationally-intensive functions and procedures that feature complex logic or for situations where you want to take advantage of the .NET Framework class library.
• To rotate the data of a table you can use the PIVOT and UNPIVOT operators, instead of using complex JOIN statements.
• SQL Server 2005 providers three new data types for storing long columns: VARCHAR(MAX), NVARCHAR(MAX) and VARBINAR(MAX). These new data types are easier to use that the older large object data types, such as TEXT, NTEXT, and IMAGE. When building a new application that needs to store a column that possibly might exceed 8000 bytes, you should consider using these new large value data types.
• The total characters displayed in the Query Editor Results Window are limited to 256 characters. If the result exceeds the limit 256 characters, it gets truncated. In the case, you can use the Option tab in Management Studio to set the maximum column size under the Result section.
• The result sets that return from your database should be kept as small as possible. This greatly improve the performance and makes the database much more scalable.

Tips and Tricks

The following tips and tricks will help you perform effective query by using SQL Server2005:

• When writing calculations such as "exprl * expr2". ensure that the expression sweeping the largest area/volume is on the left side. For instance, write "Sales * ExchangeRate" instead of  "ExchangeRate * Sales", and "Sales * 1.15" instead of"1.15 * Sales". Here Sales value will be larger than the exchange rate.
• Consider replacing simple  "Measurel + Measure2" calculations with computed columns in the SQL data source.
• Avoid using select * in your query design. Instead, ensure that you use the proper column names in the query. Using the proper column names decreases network traffic, puts less load on the database, and therfore, improves performance.