-- remove carriage return, line feed, and tab from a field in tsql
REPLACE(REPLACE(REPLACE(MyField, CHAR(10), ''), CHAR(13), ''), CHAR(9), '')
Great SQL Help. First place I go to to find help with Complex Unions or functions.�Tech on the Net
Q. how do i tune my sql queries?
A. Performance Tips for Queries
Carefully monitor the indexes on the table in your queries to make sure your queries are using them.
Try not to use WHERE clauses that don’t use SARG logic. For example, OR, <<>>, !=, !<<, !>>, IS NULL, NOT, NOT EXISTS, NOT IN, NOT LIKE, and LIKE clauses cannot use the SARG logic, which slows down the Query Optimizer drastically. You may find that some of your queries that use these clauses are not using indexes.
Avoid using cursors whenever possible. Before moving forward with the cursor, seeif you can do the same operation with a normal query. Consider using a temp table instead of the cursor, since there is less overhead involved.
Avoid using UNION statements, unless you’re removing duplicate rows. Instead, use a UNION ALL statement, which is much faster and doesn’t look for duplicate rows.
Always list your column names when performing a select statement. If a column is added in the schema, it could harm your application if not properly handled, since you’re pulling down a larger resultset than the application is expecting. Your network time could be much reduced if you only return data that you need.
Consider breaking large tables into smaller views. A good view could take a subset of the records based on a date or location.
If you don’t have a requirement to remove duplicates or order the data, avoid using an ORDER BY or DISTINCT statement. If there is no clustered index on the column to satisfy the query, a temporary workspace must be created to fulfill the query, which can take quite a long time for large tables.
Use the EXISTS clause rather than the IN clause. The EXISTS clause is slightly faster.
Use the TOP statement if you need a limited amount of records.
Non-logged SQL Statements
The following tips could help speed up delete and insert processes in your applications:
The TRUNCATE TABLE <> command is much faster than the DELETE command, since the former command is non-logged. Non-logged statements only write minimal data into the transaction log. Because it is non-logged, there is an element of danger since it is not as easily recovered if you make a mistake. This command can only be run on tables that don’t have foreign keys. The TRUNCATE TABLE command does not support any type of WHERE clause.
Use commands that minimize logging, such as BULK INSERT, TRUNCATE TABLE, and SELECT…INTO. For text, ntext, and image fields, use WRITETEXT and UPDATETEXT commands, which lower the amount of logging.
Add table locks on inserts where you can. For example, if you’re performing a data load at 2 A.M. and you’re not worried about anyone reading from the table, you can easily use a table lock. You can do this with the TABLOCK hint when you issue a BULK INSERT command.