What is Dynamic management view?

 No one likes problem .DMV helps to understand the problem before it occurs.Really it helps

to quick discover the slowest SQL queries on SQL servers.It helps to get details of missing

indexes that could significantly improve the performance of queries. All these things and more

are easily possible, typically in a matter of seconds, using DMVs.

  DMVs are views on internal SQL Server metadata, which can be used to significantly improve

the performance of SQL queries, often by an order of magnitude.Fixing any problem is knowing

what the underlying problem is. DMVs can give precisely this information. DMVs will pinpoint

where many of problems are, often before they become painfully apparent.DMV are existing from

SQL Server 2005.After executing sql queries on a SQL Server database, SQL Server automatically

records information about the activity that is taking place, internally into structures in memory, this information can be accessed via DMVs. So DMVs are basically SQL views on some pretty important internal memory structures.DMV information includes metrics that relate to indexes, query execution,  service broker, replication, query notification, objects,input/output (I/O), full-text search, databases, database mirroring, change data capture(CDC),the operating system, common language runtime (CLR), transactions, security, extended events, resource governor and much more. 

     When any query execute  on sql server following information captured 

 1.The query’s cached plan 

 2.What indexes were used

 3.What indexes the query would like to use but are missing

 4.What resources the query waiting upon

 5.How much IO occurred (both physical and logical)

 6.How much time was spent actually executing the query

 7.How much time was spent waiting on other resources.

    In addition to DMVs, there are several related functions that work in conjunction with DMVs, called Dynamic Management Functions (DMFs). In many ways DMFs are similar to standard SQL

functions, being called repeatedly with a DMV supplied parameter.DMVs and DMFs have been an integral part of SQL Server since version 2005 onwards. In SQL Server 2005 there are 89 DMVs (and DMFs), and in SQL Server 2008 there are 136 DMVs.  It is possible to discover the range of these DMVs by examining their names, by using the following query:

SELECT name, type_desc FROM sys.system_objects WHERE name LIKE 'dm_%' ORDER BY name

    DMVs can solve  problem including  Diagnosing , Performance Tuning, and Monitoring.

It is possible to query the DMVs to diagnose many common problems including slowest queries, the commonest causes of waiting/blocking, unused indexes, files having the most I/O, and lowest re-use of cached plans.

Data Mismatch on WHERE Clause by functions : will cause performance issue?

Yes.It will cause performance issue.
   Any function such as collate, convert, substring etc. applied on a column might make impossible for SQL Server to use any index on this column. This because SQL Server query optimizer considers the column after the function as a new column, because the column values are not stored in the index pages as returned by the function. For these reasons, it is not possible to use indexes on these columns.

This is of course is not only for WHERE clause columns. Join, order, group by or having clause may have same problem. However functions in the select list does not affect index selection.

Some common problematic functions are :

COLLATE
CONVERT
SUBSTRING
LEFT
LTRIM
RTRIM
User defined functions.

Data Mismatch on WHERE Clause : will cause performance issue?

Yes.Mismatch in data type on where clause will cause performance problem.
                   Any datatype mismatch on WHERE clause might cause serious performance problems.Predicates on both sides of comparisons (for example on WHERE clause) always must match datatypes. It means that if the left side of predicate is integer than the right side needs to be integer. If the datatypes are different then SQL Server tries to make and implicit conversion to match the datatypes of both sides like below example.

If an implicit conversion is not possible SQL Server returns an error like below.

When an implicit conversion is possible, SQL Server automatically converts the data from one data type to another. Selection of the conversion direction depends on data loss possibility. SQL server choose the side which is to avoid data lost. For example, when a smallint is compared to an int, the smallint is implicitly converted to int before the comparison proceeds.

how to write case statement if column exist then return same column value other wise return null?

--Creating work table test11

if exists(select * from sys.tables where name='test11')
drop table  test11
go

 CREATE TABLE test11
  (
     ID INT
  )

go
--Creating function

IF EXISTS (
    SELECT * FROM sysobjects WHERE id = object_id(N'fnColumnname')
    AND xtype IN (N'FN', N'IF', N'TF')
)
    DROP FUNCTION fnColumnname
GO
CREATE FUNCTION fnColumnname(@Table_Name  NVARCHAR(100),
                          @Column_Name NVARCHAR(100))
returns VARCHAR(200)
AS
  BEGIN
      IF EXISTS (SELECT*
                 FROM   sys.columns
                 WHERE  Object_name(object_id) = @Table_Name
                        AND name = @Column_Name)
        BEGIN
            RETURN
              (SELECT @Table_Name + '.' + @Column_Name)
        END

      RETURN 'NULL'
  END
 go

  --Creating table for columns

 IF OBJECT_ID('tempdb..#Column_exists') IS NOT NULL
  DROP TABLE #Column_exists
go
SELECT name
INTO   #Column_exists
FROM   sys.columns
WHERE  Object_name(object_id) = 'test11'
go

 INSERT INTO test11
VALUES      (1)
go

Declare @lclsql varchar(max)
SET @lclsql='select  case  when (select count(*) from #Column_exists where name=''ID'' )=0 then ''Null''  else '
       + dbo.fnColumnname('test11', 'ID')
       + ' end  ID ,case  when (select count(*) from #Column_exists where name=''NAME'' )=0 then ''Null''  else '
       + dbo.fnColumnname('test11', 'NAME')
       + ' end  NAME,case  when (select count(*) from #Column_exists where name=''SAL'' )=0 then ''Null''  else '
       + dbo.fnColumnname('test11', 'SAL')
       + ' end  SAL,case  when (select count(*) from #Column_exists where name=''DEPTNO'' )=0 then ''Null''  else '
       + dbo.fnColumnname('test11', 'DEPTNO')
       + ' end  DEPTNO' + ' from test11;'

 exec(@lclsql)
--Result:
-- Now we have only one field and so other field will be NULL  
     
  go

 --Adding Name column
 alter table test11
 add  NAME varchar(100)

go

 IF OBJECT_ID('tempdb..#Column_exists') IS NOT NULL
  DROP TABLE #Column_exists

go

SELECT name
INTO   #Column_exists
FROM   sys.columns
WHERE  Object_name(object_id) = 'test11'
go

 INSERT INTO test11
VALUES      (1,'Shrikant')

go

Declare @lclsql varchar(max)
SET @lclsql='select  case  when (select count(*) from #Column_exists where name=''ID'' )=0 then ''Null''  else '
       + dbo.fnColumnname('test11', 'ID')
       + ' end  ID ,case  when (select count(*) from #Column_exists where name=''NAME'' )=0 then ''Null''  else '
       + dbo.fnColumnname('test11', 'NAME')
       + ' end  NAME,case  when (select count(*) from #Column_exists where name=''SAL'' )=0 then ''Null''  else '
       + dbo.fnColumnname('test11', 'SAL')
       + ' end  SAL,case  when (select count(*) from #Column_exists where name=''DEPTNO'' )=0 then ''Null''  else '
       + dbo.fnColumnname('test11', 'DEPTNO')
       + ' end  DEPTNO' + ' from test11;'


exec( @lclsql)

go