Data Type Precedence

When an operator combines two expressions of different data types, the rules for data type precedence specify that the data type with the lower precedence is converted to the data type with the higher precedence.

This conversion can have a big influence regarding performance.

For the precedence order for data types see:

http://msdn.microsoft.com/en-us/library/ms190309.aspx

Errors while Indexed View creation

Here are fer errors (and solutions...) while Indexed View creation.

Error: 

Cannot schema bind view because name is invalid for schema binding. Names must be in two-part format and an object cannot reference itself

Solution:

Add schema to the objects names.

Error:

Cannot schema bind view 'dbo.MyView'. 'dbo.OtherView' is not schema bound.

Solution:

Use tables, not views. 

Another solution: Create the other views 'WITH SCHEMABINDING' also.

Error: 

Cannot create index on view . The view contains a self join on

Solution:

Sorry, no solution...... :-(

Indexed View creation

To create indexed view it's the simpler than we cam imaging...

Just add an index on the view:

CREATE UNIQUE CLUSTERED INDEX [IX_V_IndexName] ON [dbo].[MyView]

(

Col1 DESC, 

Col2 DESC

)

GO

SQL Sentry Plan Explorer

SQL Sentry Plan Explorer (sqlSentry) is a FREE tool that builds upon the graphical plan view in SQL Server Management Studio to make query plan analysis more efficient.

It is a Recommended tool to more comfortable view and for better understand of execution plans:

• You can see the relevant icons on the execution plan by sign a part of the query.
• You can see lists of top operations by costs and more, and move from an operation in the table to the plan.
• You have few views of the execution plan: diagram, XML, etc.
• And more...

http://www.sqlsentry.com/plan-explorer/sql-server-query-view.asp

Find out Page-splits

select Operation, AllocUnitName, COUNT(*) as OperationCount

from sys.fn_dblog(null,null)

where Operation = N'LOP_DELETE_SPLIT'

group by Operation, AllocUnitName

Performance statistics for cached query plans

SELECT

DB_NAME(st.[dbid]) AS DBName,

OBJECT_NAME(st.objectid, st.[dbid]) as ObjectName,

qs.plan_generation_num,

qs.execution_count,

( SUBSTRING(st.[text], (qs.statement_start_offset/2) + 1,

 ((CASE qs.statement_end_offset 

 WHEN -1 THEN DATALENGTH(st.[text])

 ELSE qs.statement_end_offset END 

 - qs.statement_start_offset)/2) + 1)

) AS statement_text

FROM sys.dm_exec_query_stats qs

CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS st

WHERE DB_NAME(st.[dbid]) = 'DataBaseName' 

AND OBJECT_NAME(st.objectid, st.[dbid]) = 'ObjectName'

plan_generation_num - number of plan generation for this Object

execution_count - number of executions of the current plan

if plan_generation_num is more than 1 and execution_count 1:

new plan is generated for each execution --> probably not a good situation.

if plan_generation_num is 1 and execution_count is big:

probably good situation.

Select Code Between Two Parenthesis

Press CTRL+SHIFT+] before the opening '(' to select code between two parenthesis.
Without the SHIFT (CTRL+] ) the cursor will jump to the closing parenthesis.