SQL Server Management Studio 2008 suggests missing indexes with actual execution plan

When you choose to Include the Actual Execution Plan in SSMS 2008 it suggest any missing indexes it thinks that are needed. Plus it also calculates the performance benefit from adding them.

And best of all this also works when you're connected to a SQL Server 2000 or 2005.

Updated Unused Index Query

To clean up and update unused index query. Query below returns a list of indexes ordered by ascending reads. If the instance has been up for a long time, it is probably safe to drop indexes with zero or close to zero reads. If the reads are low and the writes are high, this may help improve your transactions per second count. Otherwise, you are just cleaning up unused space. Use caution though. A missing index is worse than an unused index.

SELECT objectname=OBJECT_NAME(s.OBJECT_ID)
, indexname=i.name
, i.index_id
, reads=user_seeks + user_scans + user_lookups
, writes = user_updates
, p.rows
FROM sys.dm_db_index_usage_stats s JOIN sys.indexes i
ON i.index_id = s.index_id AND s.OBJECT_ID = i.OBJECT_ID
JOIN sys.partitions p ON p.index_id = s.index_id AND s.OBJECT_ID = p.OBJECT_ID
WHERE OBJECTPROPERTY(s.OBJECT_ID,'IsUserTable') = 1
AND s.database_id = DB_ID()
AND i.type_desc = 'nonclustered'
AND i.is_primary_key = 0
AND i.is_unique_constraint = 0
AND p.rows > 10000
ORDER BY reads, rows DESC

Dynamic SQL using like

Dynamic SQL was the best choice by far in terms of performance for dynamic search problems,
check article below:
http://www.sommarskog.se/dyn-search-2008.html

It takes a long time from me to write a correct sysntax with LIKE criteria

First I wrote Syntax:

DECLARE @SQL nvarchar(4000)
SET @SQL='SELECT [CategoryName]
FROM [StarterSite_productcatalog].[dbo].[Adventure Works Catalog_CatalogProducts]
where categoryname like ''%@param1%'''
DECLARE @param1 nvarchar(20)
DECLARE @paramlist nvarchar(20)
SET @param1='sl'
SET @paramlist = '@param1 varchar(20)'
EXEC sp_executesql @sql, @paramlist,@param1

I found correct syntax:

DECLARE @SQL nvarchar(4000)
SET @SQL='SELECT [CategoryName]
FROM [StarterSite_productcatalog].[dbo].[Adventure Works Catalog_CatalogProducts]
where categoryname like ''%''+@param1+''%'''
DECLARE @param1 nvarchar(20)
DECLARE @paramlist nvarchar(20)
SET @param1='sl'
SET @paramlist = '@param1 varchar(20)'
EXEC sp_executesql @sql, @paramlist,@param1

LINQ to SQL Serialization

Problem:

• LINQ to SQL classes do not support binary serialization. Although I can manually modify them to meet my needs, it is a very time-consuming job, and difficult to maintain if the table is changed in the future.
• LINQ to SQL classes cannot be serialized by XML serializer if there is a relationship between tables.

These articles give us work around:
http://www.west-wind.com/WebLog/posts/147218.aspx
http://www.codeproject.com/KB/linq/linqsqlserialization.aspx

LINQ and Dynamic Query Expressions and SQL Injection

When you want to create LINQ with dynamic expression you will use concatenate string like:
var query = db.Customers.Where("City = '"+country+"' and Orders.Count >="+ordersCount)
.OrderBy("CompanyName")
.Select("new(CompanyName as Name, Phone)");

To prevent SQL injection you must use parameters:
var query = db.Customers.Where("City = @0 and Orders.Count >= @1", country, ordersCount)
.OrderBy("CompanyName")
.Select("new(CompanyName as Name, Phone)");