Get Missing Index In database

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

Update On inner Join

    select  T.id,T1.IsFinalStatus_FirstLevel,T.IsFinalStatus_SecondLevel,T1.IsFinalStatus_SecondLevel As Change
    from IGRS_Report..tbl_Complaints_CompiledStatus T inner join IGRS_Report..tbl_Complaints_CompiledStatus T1

    On T.ComplaintCode=T1.ComplaintCode and  T1.IsFinalStatus_SecondLevel='ATRS' and T1.IsFinalStatus_FirstLevel <> 'ATRS'
    and T1.ComplaintCode=10159160008930
     and T.id >=T1.id

----------------------Update----------------------------------
     Update
    T set
    T.IsFinalStatus_FirstLevel=T1.IsFinalStatus_SecondLevel
    from IGRS_Report..tbl_Complaints_CompiledStatus T inner join IGRS_Report..tbl_Complaints_CompiledStatus T1

    On T.ComplaintCode=T1.ComplaintCode and  T1.IsFinalStatus_SecondLevel='ATRS' and T1.IsFinalStatus_FirstLevel <> 'ATRS'
    and T1.ComplaintCode=10159160008930
     and T.id >=T1.id

Display Number of Online Users Asp.net- Using Global.asax file

Global .asax file-

1. <%@ Application Language="C#" %>  
2.   
3. <script runat="server">  
4.   
5.     void Application_Start(object sender, EventArgs e)   
6.     {  
7.         // Code that runs on application startup  
8.         Application["TotalOnlineUsers"] = 0;  
9.     }  
10.       
11.     void Application_End(object sender, EventArgs e)   
12.     {  
13.         //  Code that runs on application shutdown  
14.   
15.     }  
16.           
17.     void Application_Error(object sender, EventArgs e)   
18.     {   
19.         // Code that runs when an unhandled error occurs  
20.   
21.     }  
22.   
23.     void Session_Start(object sender, EventArgs e)   
24.     {  
25.         // Code that runs when a new session is started  
26.         Application.Lock();  
27.         Application["TotalOnlineUsers"] = (int)Application["TotalOnlineUsers"] + 1;  
28.         Application.UnLock();  
29.     }  
30.   
31.     void Session_End(object sender, EventArgs e)   
32.     {  
33.         // Code that runs when a session ends.   
34.         // Note: The Session_End event is raised only when the sessionstate mode  
35.         // is set to InProc in the Web.config file. If session mode is set to StateServer   
36.         // or SQLServer, the event is not raised.  
37.         Application.Lock();  
38.         Application["TotalOnlineUsers"] = (int)Application["TotalOnlineUsers"] - 1;  
39.         Application.UnLock();  
40.     }  
41.          
42. </script>

Web config-

1. <system.web>  
2. <sessionState mode="InProc" cookieless="false" timeout="20"></sessionState>  
3. </system.web> 

Default.aspx -

1. <form id="form1" runat="server">  
2.     <div>  
3.     <p>No. of Online Users:<asp:Label ID="Label1" runat="server" Text="Label" ForeColor="#CC0000"></asp:Label></p>  
4.     </div>  
5. </form>  

Default.aspx.cs -

1. protected void Page_Load(object sender, EventArgs e)  
2. {  
3.         Label1.Text = Application["TotalOnlineUsers"].ToString();  
4. }

Instant and Dynamic Cross-Tab Reports using PIVOT in SQL Server 2005

It is a usual practice that we have data in a SQL Server table but without using any frontend application tool or programming language we find quite difficult to produce the data in our desired format. Most of the times the format is a 2 dimensional matrix sort and the report generation comes out to be quite cumbersome. I would not waste enough time in elaborating but would come straight to the point. I would explain with an example and give you all a Stored Procedure which would accept the input table and field on which the dynamic header should appear and immediately generate the output table.
Here we go,
 1. Create a table as follows,
     Create table DistrictData(DistName nvarchar(100) not null,MonYear varchar(6) not null,Expense float)
 2. Insert data into the table,
insert into DistrictData values('Agra','012011',2300.00)
insert into DistrictData values('Agra','022011',2600.00)
insert into DistrictData values('Agra','032011',1100.00)
insert into DistrictData values('Agra','042011',5500.00)
insert into DistrictData values('Agra','052011',1600.00)
insert into DistrictData values('Agra','062011',3400.00)
insert into DistrictData values('Aligarh','012011',1800.00)
insert into DistrictData values('Aligarh','022011',1100.00)
insert into DistrictData values('Aligarh','032011',1700.00)
insert into DistrictData values('Aligarh','042011',2400.00)
insert into DistrictData values('Aligarh','052011',2900.00)
insert into DistrictData values('Aligarh','062011',2700.00)

3. The data in the table can be seen by giving
(select * from DistrictData),

DistName	MonYear	Expense
Aligarh	012011	1800
Aligarh	022011	1100
Aligarh	032011	1700
Aligarh	042011	2400
Aligarh	052011	2900
Aligarh	062011	2700
Agra	012011	2300
Agra	022011	2600
Agra	032011	1100
Agra	042011	5500
Agra	052011	1600
Agra	062011	3400

4. Now a report is required in the format where the header gets generated on MonthYear dynamically and the values for the Expenses are shown under the  MonthYear and in front of the District. There is a often a requirement of a report like,

DistName	012011	022011	032011	042011	052011	062011
Agra	2300	2600	1100	5500	1600	3400
Aligarh	1800	1100	1700	2400	2900	2700

As and when a new MonthYear is added a new column automatically gets generated and similarly when a new district is added a new row for the report gets created.

5. The raw data (3) can be converted into the report form as in (4) simply by running a stored procedure given in (6) by creating the stored procedure just by running (6) and after successful creation then executing it as follows, 

                  exec PivotDynamicReport 'DistrictData','MonYear','Expense'

               [Sytax: exec PivotDynamicReport 'Table Name','Header Field Name','Data Field Name' ]

              
6. The above stored procedure 'PivotDynamicReport ' can be created as,

set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

go

CREATE Procedure [dbo].[PivotDynamicReport]

@SourceTable            nvarchar(50),

@ColumnHeadField  nvarchar(50),

@DataField              varchar(50)

As

BEGIN

Declare     @sql  nvarchar(1000)

declare @Temp table (NewField nvarchar(50))

set @sql = 'Select distinct ' + @ColumnHeadField + ' from ' + @SourceTable

Insert into @Temp(NewField) EXEC sp_executesql @sql

DECLARE @PivotColumnHeaders VARCHAR(100)

DECLARE @PivotTableSQL NVARCHAR(MAX)

SELECT @PivotColumnHeaders = COALESCE(@PivotColumnHeaders + ',[' + cast(NewField as varchar) + ']','[' + cast(NewField as varchar)+ ']')FROM @Temp

SET @PivotTableSQL = N'select * from ' + @SourceTable + ' pivot (Sum (' + @DataField + ') for ' + @ColumnHeadField + ' in ('+ @PivotColumnHeaders +')) as TotalField '

EXECUTE(@PivotTableSQL)

END