CPU Uese in SQL Server

CPU Uese in SQL Server :

SELECT total_worker_time/execution_count AS AvgCPU
, total_worker_time AS TotalCPU
, total_elapsed_time/execution_count AS AvgDuration
, total_elapsed_time AS TotalDuration
, (total_logical_reads+total_physical_reads)/execution_count AS AvgReads
, (total_logical_reads+total_physical_reads) AS TotalReads
, 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 txt
, query_plan
FROM sys.dm_exec_query_stats AS qs
cross apply sys.dm_exec_sql_text(qs.sql_handle) AS st
cross apply sys.dm_exec_query_plan (qs.plan_handle) AS qp
ORDER BY 1 DESC

select dbs.name, cacheobjtype, total_cpu_time, total_execution_count from
    (select top 10
        sum(qs.total_worker_time) as total_cpu_time,
        sum(qs.execution_count) as total_execution_count,
        count(*) as  number_of_statements,
        qs.plan_handle
    from
        sys.dm_exec_query_stats qs
    group by qs.plan_handle
    order by sum(qs.total_worker_time) desc
    ) a
inner join
(SELECT plan_handle, pvt.dbid, cacheobjtype
FROM (
    SELECT plan_handle, epa.attribute, epa.value, cacheobjtype
    FROM sys.dm_exec_cached_plans
        OUTER APPLY sys.dm_exec_plan_attributes(plan_handle) AS epa
     /* WHERE cacheobjtype = 'Compiled Plan' AND objtype = 'adhoc' */) AS ecpa
PIVOT (MAX(ecpa.value) FOR ecpa.attribute IN ("dbid", "sql_handle")) AS pvt
) b on a.plan_handle = b.plan_handle
inner join sys.databases dbs on dbid = dbs.database_id

Create and Delete INDEX in SQL Server

Drop index from table :

DROP INDEX table_name.index_name

DROP INDEX aspnet_Paths.aspnet_Paths_index

Create Index in table:

CREATE INDEX aspnet_Paths_index
ON aspnet_Paths (PathID)

Find out index in table in SQL Server

Find out index in table :

USE table name
GO
DECLARE @dbid INT
SELECT @dbid = DB_ID(DB_NAME())
SELECT OBJECTNAME = OBJECT_NAME(I.OBJECT_ID),
INDEXNAME = I.NAME,
I.INDEX_ID
FROM SYS.INDEXES I
JOIN SYS.OBJECTS O
ON I.OBJECT_ID = O.OBJECT_ID
WHERE OBJECTPROPERTY(O.OBJECT_ID,'IsUserTable') = 1
AND I.INDEX_ID NOT IN (
SELECT S.INDEX_ID
FROM SYS.DM_DB_INDEX_USAGE_STATS S
WHERE S.OBJECT_ID = I.OBJECT_ID
AND I.INDEX_ID = S.INDEX_ID
AND DATABASE_ID = @dbid)
ORDER BY OBJECTNAME,
I.INDEX_ID,
INDEXNAME ASC
GO

MS-SQL predefined stored procedures in sql server

1.EXEC sp_databases database name on server.

2.SELECT DB_NAME() AS DataBaseName  current database name.

3.EXEC sp_MSForEachDB 'Print ''?'''

4.EXEC sp_MSForEachDB 'SELECT ''?'' AS DatabaseName'

5.SELECT name,crdate FROM master..sysdatabases with created date

6.DECLARE @sqlString NVARCHAR(max)
DECLARE @union NVARCHAR(max)
SET @sqlString = ''
SET @union = ''
DECLARE @name nvarchar(50);

DECLARE crs CURSOR FOR
SELECT Name FROM sys.databases WHERE  state = 0
OPEN crs
FETCH NEXT FROM crs INTO @name

WHILE @@FETCH_STATUS = 0
BEGIN
   SET @sqlString = @sqlString + @union
   SET @sqlString = @sqlString + '
    SELECT
    TOP 1
  ''' + @name + ''' as DBName, modify_date
  FROM
   [' + @name + '].sys.tables'

 SET @union = ' UNION '

    FETCH NEXT FROM crs INTO @name
END

SET @sqlString = @sqlString + ' ORDER BY DBName ASC'
CLOSE crs;
DEALLOCATE crs;
EXEC(@sqlString)



7.SELECT name,crdate,category,cmptlevel,filename,mode,reserved,sid,status,version FROM master..sysdatabases

How many data types in C# (Bool type)

Bool type
Bool type in C# can be used to represent a boolean value. A bool type is binary in nature and takes only 2 values true or false.

// Example for boolean type

using System;

    class bool_type
    {
        public static void Main(string[] args)
        {
            bool switch_on = true;
            bool switch_off = false;

            Console.WriteLine("If switch is ON,it is: " + switch_on);
            Console.WriteLine("If switch is OFF,it is: " + switch_off);
         
        }
    }


 Output
  If switch is ON,it is:True
  If switch is OFF,it is:False
 

How many data types in C# (Decimal type )

Decimal type
The decimal type in C# (not available in C, C++ or Java) is a 128-bit data type. It is mainly used for financial and monetary calculations. It’s range is between 1.0 * 10-28 to 7.9 * 1028 .
// Example for decimal types

using System;

    class decimal_types
    {
        public static void Main(string[] args)
        {
            decimal d = 1233455.25m;
            Console.WriteLine("Decimal Value: " +d);


        }
 }


Output:

   Decimal Value:1233455.25

How many data types in C# (Floating point types)

Floating point types
C# supports 2 floating point types viz. float and double.

Type # Floating type Data Type Default Value Range

1 float  32-bit single-precision IEEE 754 format 0.0f  1.5 * 10-45 to 3.4 * 1038
2 double  64-bit double-precision IEEE 754 format  0.0d 5.0 * 10-324 to 1.7 * 10308
Table – Floating point types

// Example for floating point types

using System;

    class float_types
    {
        public static void Main(string[] args)
        {
            float f = 12345.12545f;
            double d = 123456789.123456789d;

            Console.WriteLine("Float value: " + f);
            Console.WriteLine("Double value: " + d);
     
        }
    }


 Output:

   Float value:12345.13
 
   Double value: 123456789.123457

How many data types in C# (Integral types)

DataTypes In C#
Value Based Datatypes:

Value based datatypes are those which store the data directly into the variables. Value based datatypes can further be classified as simple types, enum-type and struct-type. We follow a different type of classification which is based on the "data-type".

1.Integral types
2.Floating point types
3.Decimal type
4.Bool type
5.Integral types

C# supports 9 different integral types.

Type # Integral type Data Type Default Value Range

1 sbyte 8-bit signed integer 0 -128 to +127
2 byte 8-bit unsigned integer 0 0 to 255
3 short  16-bit signed integer  0  -32,768 to +32,767
4 ushort 16-bit unsigned integer 0 0 to 65535
5 int 32-bit signed integer  0 -2,147,483,648 to +2,147,483,647
6 uint 32-bit unsigned integer 0 0 and 4294967295
7 long 64-bit signed integer 0 -9,223,372,036,854,775,808 to+ 9,223,372,036,854,775,807
8 ulong 64-bit unsigned integer 0 0 to 18446744073709551615
9 char 16-bit unsigned integer  '\x0000' 0 to 65535

// Example that demonstrates integral datatypes in C#

using System;

    class integral_dtypes
    {
        public static void Main(string[] args)
        {
         sbyte sb=-125;
            byte b=125; // try assigning -125 to this variable
            short s = -32225;
            ushort us = 32225; // try assigning -32225 to this variable
            int i = -123456789;
            uint ui = 123456789; //try assigning -123456789
            long l = -123456789;
            ulong ul = 11112222333344445555;
            char c = 'a';

            Console.WriteLine("Signed byte: " + sb);
            Console.WriteLine("Unsigned byte: " + b);
            Console.WriteLine("Signed short: " + s);
            Console.WriteLine("Unsigned short: " + us);
            Console.WriteLine("Integer: " + i);
            Console.WriteLine("Unsigned integer : " + ui);
            Console.WriteLine("Long: " + l);
            Console.WriteLine("Unsigned Long: " + ul);
            Console.WriteLine("Character: " + c);

        }
    }

Output:

   Signed byte: -125
   Unsigned byte:125
   Signed short:-32225
   Unsigned short:32225
   Integer: -123456789
   Unsigned integer :123456789
   Long: -123456789
   Unsigned Long:11112222333344445555
   Character: a

How can use Ajax validation control on our form.

Select ValidatorCalloutExtender from Ajax control:

After adding  ValidatorCalloutExtender on form set property.

   <script language="javascript" type="text/javascript">

  </script>

 

   <asp:ScriptManager ID="ScriptManager1" runat="server"></asp:ScriptManager>

    <asp:ValidatorCalloutExtender ID="reqCurPassword" runat="server" TargetControlID="curr_password">

    </asp:ValidatorCalloutExtender>

    <asp:ValidatorCalloutExtender ID="reqNewPassword" runat="server" TargetControlID="new_password">

    </asp:ValidatorCalloutExtender>

    <asp:ValidatorCalloutExtender ID="reqConfPassword" runat="server" TargetControlID="conf_password">

    </asp:ValidatorCalloutExtender>

Add .net RequiredFieldValidator on page:

<tr id="trBranch" runat="server">

        <td style="text-align: left; width: 188px;" class="text" >

        New Password :</td>

        <td style="text-align: left; ">

<asp:TextBox ID="txt_newpassword" runat="server" TabIndex="3" CssClass="TextBox" TextMode="Password"></asp:TextBox>

<asp:RequiredFieldValidator ID="new_password" runat="server"

ErrorMessage="Please enter new password." ToolTip="Please enter new password." ControlToValidate="txt_newpassword">*</asp:RequiredFieldValidator>

        </td>

    </tr>

Out put of Ajax validation control: