Cannot create a row of size which is greater than the allowable maximum row size of

Error message:

Cannot create a row of size 8066 which is greater than the allowable maximum row size of 8060.

The case:

I tried to alter a column from nvarchar(max) to nvarchar(1500).

I run:

UPDATE MyTable set Col1 = Left(Col1, 1500) where len(Col1) > 1500

ALTER TABLE [dbo].[MyTable] ALTER COLUMN [Col1] [nvarchar](1500) NULL

And I get the error message for the ALTER TABLE statement.

Solution:

ALTER TABLE [dbo].[MyTable] REBUILD -- before  the ALTER COLUMN statement.

http://stackoverflow.com/questions/7631546/entity-framework-row-size-greater-than-allowable-maximum-row-size-of-8060

Find column dependency

select OBJECT_NAME(k.parent_object_id) as parentTable,

c1.name as parentColumn, 

OBJECT_NAME(k.referenced_object_id) as referencedTable, 

c2.name as referencedColumn,

k.name

from  sys.foreign_keys k

join sys.foreign_key_columns f on f.parent_object_id = k.parent_object_id

join sys.columns c1 on c1.column_id = f.parent_column_id

and c1.object_id = k.parent_object_id

join sys.columns c2 on c2.column_id = f.referenced_column_id

and c2.object_id = k.referenced_object_id

where   c2.name = 'ColumnName'

and     OBJECT_NAME(k.referenced_object_id) = 'TableName'

select top n WITH TIES

"WITH TIES" is a case of TOP:

SELECT TOP N WITH TIES  ... 

FROM Table Name ORDER BY  ColumnA

WITH TIES Specifies that additional rows (more than N) be returned from the base result set with the same value of the last of the TOP N. 

Example:

Top 3 - returns exactly 3 records. Which one from the 4 records that can be chosen?  who knows...

Top 3 With Ties - returns with the third record all the appropriate records.

Notes:

• WITH TIES can return more than N records. It's important to remember it.
• WITH TIES is used only with ORDER BY clause. If we don't use the ORDER BY clause an error will be generated.

Missing Index Script

The Author is Pinal Dave:

http://blog.sqlauthority.com/2011/01/03/sql-server-2008-missing-index-script-download/

-- Original Author: Pinal Dave (C) 2011

SELECT TOP 25

dm_mid.database_id AS DatabaseID,

dm_migs.avg_user_impact*(dm_migs.user_seeks+dm_migs.user_scans) Avg_Estimated_Impact,

dm_migs.last_user_seek AS Last_User_Seek,

OBJECT_NAME(dm_mid.OBJECT_ID,dm_mid.database_id) AS [TableName],

'CREATE INDEX [IX_' + OBJECT_NAME(dm_mid.OBJECT_ID,dm_mid.database_id) + '_'

+ REPLACE(REPLACE(REPLACE(ISNULL(dm_mid.equality_columns,''),', ','_'),'[',''),']','') +

CASE

WHEN dm_mid.equality_columns IS NOT NULL AND dm_mid.inequality_columns IS NOT NULL THEN '_'

ELSE ''

END

+ REPLACE(REPLACE(REPLACE(ISNULL(dm_mid.inequality_columns,''),', ','_'),'[',''),']','')

+ ']'

+ ' ON ' + dm_mid.statement

+ ' (' + ISNULL (dm_mid.equality_columns,'')

+ CASE WHEN dm_mid.equality_columns IS NOT NULL AND dm_mid.inequality_columns IS NOT NULL THEN ',' ELSE

'' END

+ ISNULL (dm_mid.inequality_columns, '')

+ ')'

+ ISNULL (' INCLUDE (' + dm_mid.included_columns + ')', '') AS Create_Statement

FROM sys.dm_db_missing_index_groups dm_mig

INNER JOIN sys.dm_db_missing_index_group_stats dm_migs

ON dm_migs.group_handle = dm_mig.index_group_handle

INNER JOIN sys.dm_db_missing_index_details dm_mid

ON dm_mig.index_handle = dm_mid.index_handle

WHERE dm_mid.database_ID = DB_ID()

ORDER BY Avg_Estimated_Impact DESC

GO

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