Obtaining information about the queries by average CPU time.

1. SQL Exection CUP Times

It’s very use full one for optimizing and fine turning the SQL, and also to compare the SQL

CREATE PROC mySqlTime as

SELECT total_worker_time/execution_count AS [Avg CPU Time],

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 statement_text

FROM sys.dm_exec_query_stats AS qs

CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st

ORDER BY total_worker_time/execution_count DESC;

exec mySqlTime

2. SQL Time with details (mySqlTimeDetails)

The text of SQL queries that are being executed in batches and provides statistical information about them.

CREATE PROC mySqlTimeDetails as

SELECT s2.dbid,

s1.sql_handle,

(SELECT TOP 1 SUBSTRING(s2.text,statement_start_offset / 2+1 ,

( (CASE WHEN statement_end_offset = -1

THEN (LEN(CONVERT(nvarchar(max),s2.text)) * 2)

ELSE statement_end_offset END) - statement_start_offset) / 2+1)) AS sql_statement,

execution_count,

plan_generation_num,

last_execution_time,

total_worker_time,

last_worker_time,

min_worker_time,

max_worker_time,

total_physical_reads,

last_physical_reads,

min_physical_reads,

max_physical_reads,

total_logical_writes,

last_logical_writes,

min_logical_writes,

max_logical_writes

FROM sys.dm_exec_query_stats AS s1

CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS s2

WHERE s2.objectid is null

ORDER BY s1.sql_handle, s1.statement_start_offset, s1.statement_end_offset;

--Run The SQL

exec mySqlTimeDetails

Ref :MSDN

Passing a own class back to the server

Class Examples

The first example will return an own class that has some public fields to be used on the client-side JavaScript.

public class MyClass

{

public string FirstName = "";

public string FamilyName = "";

public int Age = 0;

}

It is also working if you inherit from a class and add your own properties to the new class.

public class MyInheritedClass : MyClass

{

public double SizeInMeters = 0.0;

public Guid ID = Guid.Empty;

}

Passing a own class back to the server

Next we want to pass the MyClass object back to the server. The first call will get an MyClass object from the server like we have done above. Then we want to modify the .FirstName property on the client, submit the object to the server, modify the .FamilyName there and see the results.

function doTest3() {

// synchronous call to the server-side method to get an MyClass object

var p = AJAXDemo.Examples.Classes.Demo.GetMyClass().value;

p.FirstName = "CLIENT-SIDE CHANGE"; // change one property

AJAXDemo.Examples.Classes.Demo.PutMyClass(p, doTest3_callback);

p = null;

}

[AjaxMethod]

public MyClass PutMyClass(MyClass c)

{

c.FamilyName = "SERVER-SIDE CHANGE"; // change one property

return c;

}

Create converters for your classes

One new feature is the use of converters to serialize a .NET object or deserialize a JSON string. In this example I am using a custom IJavaScriptConverter. This converter will return a new class on the client-side JavaScript that may have more properties or methods that are not returned using the built-in custom object converter (which will only return public fields and properties).

function doTest4() {

var p = AJAXDemo.Examples.Classes.Demo.GetPerson().value; // synchronous call to the server-side method

// access the properties of the Person object here

alert(p.FirstName);

// Now, we want to save it, we call the save method of the instance

// and get a boolean if succeded.

var b = p.save();

}

Ref :http://www.ajaxpro.info/Examples/Classes/default.aspx

List the COLUMN_NAME in MS SQL Tables

SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.Columns where TABLE_NAME = 'mpidallergy'

For all Tables

====================================================

SELECT table_name=sysobjects.name,

column_name=syscolumns.name,

datatype=systypes.name,

length=syscolumns.length

FROM sysobjects

JOIN syscolumns ON sysobjects.id = syscolumns.id

JOIN systypes ON syscolumns.xtype=systypes.xtype

WHERE sysobjects.xtype='U'

ORDER BY sysobjects.name,syscolumns.colid