Transact-sql

T-SQL Performance Tip: NOT IN vs NOT EXISTS

Avoid using NOT IN whilst comparing nullable columns. Use NOT EXISTS instead. When NOT IN is used in a query, SQL Server compares each result to null (even if no rows are returned). NOT EXISTS does not perform a comparison with nulls.

T-SQL Performance Tip: The * Operator

It is best to avoid using the * operator in your queries, use explicit column names instead. SQL Server scans through all column names and replaces the * operator with all the column names of the tables.

T-SQL Performance Tip: Owner or Schema Name

In order to improve your queries’ performance, you should always include the owner or schema name of an object by including its prefix before an object. Otherwise, the SQL Server engine tries to find it in all schemas until it is located.

T-SQL FORCEPLAN

The SQL Server query optimizer processes a JOIN?in the same order as the tables appear in the FROM?clause, when FORCEPLAN?is ON. SET FORCEPLAN ON | OFF

Explore a Few Math Functions in SQL Server

There are a number of math functions in T-SQL. Here are a few of them: SELECT SQRT(49) –Gives 7, Square RootSELECT PI() –Gives 3.14 …. PISELECT ROUND(15.30, 0) –Gives 15. Rounds a numberSELECT CEILING(49.32) –Gives 50. Finds the next highest Integer valueSELECT FLOOR(49.32) –Gives 49. Finds the next lowest Integer

Using NUMERIC_ROUNDABORT in T-SQL

NUMERIC_ROUNDABORT?specifies the level of error reporting that has been generated when an expression being rounded has caused loss of precision. SET NUMERIC_ROUNDABORT ON

Working with the IIF Statement in T-SQL

You can use the IIF statement to check quickly for conditions. For example: DECLARE @Spy varchar(20) = ’07’ SELECT IIF(@Spy = ‘007’, ‘Bond, James Bond’, ‘Ernst Stavro Blofeld’) If the Spy Variable is 007, then it will return “Bond, James Bond.” If it is anything else, it will return the

Disable Indexes with SQL

Sometimes you will need to disable an index, albeit temporarily. In order to disable an Index, make use of the following T-SQL Query: USE DatabaseNameGO ALTER INDEX NameOfIndex ON Table.Field DISABLE GO SELECT NAME AS [IndexName], TYPE_DESC AS [IndexType], CASE IS_DISABLED WHEN 0 THEN ‘Enabled’ ELSE ‘Disabled’ END AS [IndexInfo],

No more posts to show