SQL Server and MS BI With a Geek Touch

Query to track all the indexes with fragmentation in a database.

SELECT 

OBJECT_NAME(object_id) AS TablelName

,(SELECT name FROM sys.indexes WHERE object_id = a.object_id and index_id = a.index_id) IndexName

,avg_fragmentation_in_percent

FROM sys.dm_db_index_physical_stats(DB_ID(‘<<DatabaseName>>’), NULL, NULL, NULL, NULL) a

WHERE avg_fragmentation_in_percent > 40

AND index_type_desc IN(‘CLUSTERED INDEX’, ‘NONCLUSTERED INDEX’)

ORDER BY avg_fragmentation_in_percent DESC

The <<DatabaseName>> needs to be filled with the database of your choice.

SQL server usually selects the best query plan required to execute a T-SQL query. However, there might be cases where the plans selected are not the best. In such situations, you can use the query hints to suggest the best possible plan/method of executing a query. The Query hints can be applied on all operators in a query. These hints apply to SELECT, DELETE, INSERT, UPDATE and MERGE. You can use the Query hints in the OPTION clause after the query.

Few commonly used Query hints with examples are as below.

Read more…

 By using “Merge”, you can perform insert, delete and update on a table in a single SQL statement. This helps in synchronizing the tables as well.

However while using merge there are several points that you need to keep in mind. Few

• In a “Merge” statement, a “When Matched” clause with a search condition cannot appear after a ‘When Matched’ clause with no search condition.
• A “Merge” statement must be terminated by a semi-colon (;).
• An action of type ‘DELETE’ is not allowed in the ‘WHEN NOT MATCHED’ clause of a MERGE statement.
• At least one of the three MATCHED clauses must be specified, but they can be specified in any order. A variable cannot be updated more than once in the same MATCHED clause.
• Any insert, update, or delete actions specified on the target table by the MERGE statement are limited by any constraints defined on it, including any cascading referential integrity constraints.

Few examples of merge are as given below.

Read more…