Thursday, June 30, 2011

How will you check all the checkboxes in a Grid ?

Can be done using javascript or code-behind(server code)

1. in javascript, access the gridview and then iterate through all child elements of type "checkbox".
then set the check if not already set using
using jquery this is simpler :

2. in the code behind use the DataRowBind method to check all the check boxes, accessing cells of the row, then identifying cell type of checkbox and then call clickon each item
(to be verified)

jQuery Tutorials Link :

jQuery Tutorials For ASP.Net Developers

Beginning jQuery Development

Using jQuery in ASP.Net Application

Building Ajax Application using jQuery

Ajax Application using jQuery and JSON

Wednesday, June 29, 2011



How to convert text to integer in SQL?
If table column is VARCHAR and has all the numeric values in it, it can be retrieved as Integer using CAST or CONVERT function.
How to use CAST or CONVERT?
Will CAST or CONVERT thrown an error when column values converted from alpha-numeric characters to numeric?
Will CAST or CONVERT retrieve only numbers when column values converted from alpha-numeric characters to numeric?
How to parse/retrieve only numbers from column values contains alpha-numeric characters?
SQL SERVER – UDF – Function to Parse AlphaNumeric Characters from String
What are the online references for CAST and CONVERT?

Tuesday, June 28, 2011

Which one are better ? scalar , Table , multiline , inline UDFs

Inline UDFs offer a SET based operation where as multi-line  and scalar udfs offer row-based operation.

Bottom Line : Avoid CURSORs , WHILE LOOPs , multi-line  and scalar udfs

1 indicate least recommended and 3 indicates most recommended UDFs

1. Scalar UDF
1. Multilne UDF
2. Table UDF
3. Inline Table UDF
3. Inline UDF

Page Split DBCC SQL Code

This is from QOD40. Remember that even a ROLLBack causes a page split.

create table test ( c1 int , c2 varchar(1000))create

insert into test values (1, REPLICATE('a' , 900))insert into test values (2, REPLICATE('b' , 900))insert into test values (3, REPLICATE('c' , 900))insert into test values (4, REPLICATE('d' , 900))insert into test values (6, REPLICATE('f' , 900))insert into test values (7, REPLICATE('g' , 900))insert into test values (8, REPLICATE('h' , 900))insert into test values (9, REPLICATE('i' , 900))dbcc
tran into test values (5, REPLICATE('e' , 900))rollback tran
ind(0, 'test' , 1)
clustered index pk on test(c1)

SQL Server Indexes Fact Sheet

MS SQL Server

1. What is the maximum allowed size of index key in an index ( total size of columns used in the index definition) ?

900 bytes

2. How many indexes a table can have ?

 1 Clustered  and
999 non-clustered

3. How many columns can be combined in a composite index key ?

4. What is a heap ?
If there is no clustered index on a table, the table is called a heap.

5. How do I identify heaps and clustered indexed tables in my SQL Server database ?
The following query gives listing of clustered , non-clustered indexes and heaps :
order by b.type_desc , = b.object_id
select object_id , name , type_desc from sys.indexes ) B
select id , name from sysobjects where xtype = 'U' ) A , b.type_desc from

A column may have null values. But I want to ensure that all not null values are UNIQUE.

A UNIQUE constraint is not useful here because, it will allow a single NULL value in the column, no DUPLICATE NULLs.

Use  a filtered index here :
CREATE UNIQUE INDEX indexName ON tableName(columnNames) WHERE columnName IS NOT NULL

Difference between PRIMARY KEY and UNIQUE KEY

NULL value is not allowed
NULL is allowed, subject to the condition that only a single NULL is allowed.
Inserting more null throws “Cannot insert duplicate key” error.
Table can have only one PRIMARY KEY constraint defined
Table can have multiple UNIQUE KEY constraints
By default, if a clustered index is not defined already on the table, defining a PRIMARY KEY constraint results in a clustered index with same name as PRIMARY KEY
By default non-clustered index is create when a UNIQUE KEY constraint is added.
Purpose is to provide a unique identity to each row
Purpose is to avoid duplication

Saturday, June 25, 2011

Which can be rolled back - DELETE or TRUNCATE ?


If DELETE or TRUNCATE are enclosed within a TRANSACTION, then both can be rolled back.

DELETE can be recovered from log file always if full recovery mode is set for database. TRUNCATE may or may not be recovered always from log files.

because : In case of TRUNCATE, SQL Server deallocates the data files in the table and records deallocation of the data files in the log files.

SQL : What is going under the hood ? Very good articles to get info about current SQL activities


Identifying currently running SQL queries

es.session_id, es.host_name, es.login_name   
, er.status, DB_NAME(database_id) AS DatabaseName   
, SUBSTRING (qt.text,(er.statement_start_offset/2) + 1,  
  ((CASE WHEN er.statement_end_offset = -1  
  ELSE er.statement_end_offset 
  END - er.statement_start_offset)/2) + 1) AS [Individual Query]  
, qt.text AS [Parent Query]  
, es.program_name, er.start_time, qp.query_plan 
, er.wait_type, er.total_elapsed_time, er.cpu_time, er.logical_reads 
, er.blocking_session_id, er.open_transaction_count, er.last_wait_type 
, er.percent_complete 
FROM sys.dm_exec_requests AS er 
INNER JOIN sys.dm_exec_sessions AS es ON es.session_id = er.session_id 
CROSS APPLY sys.dm_exec_sql_text( er.sql_handle) AS qt 
CROSS APPLY sys.dm_exec_query_plan(er.plan_handle) qp 
WHERE es.is_user_process=1  
AND es.session_Id NOT IN (@@SPID) 
ORDER BY es.session_id

Blocking.session_id as BlockingSessionId 
, Sess.login_name AS BlockingUser  
, BlockingSQL.text AS BlockingSQL 
, Waits.wait_type WhyBlocked 
, Blocked.session_id AS BlockedSessionId 
, USER_NAME(Blocked.user_id) AS BlockedUser 
, BlockedSQL.text AS BlockedSQL 
, DB_NAME(Blocked.database_id) AS DatabaseName 
FROM sys.dm_exec_connections AS Blocking 
INNER JOIN sys.dm_exec_requests AS Blocked  
ON Blocking.session_id = Blocked.blocking_session_id 
INNER JOIN sys.dm_os_waiting_tasks AS Waits  
ON Blocked.session_id = Waits.session_id 
RIGHT OUTER JOIN sys.dm_exec_sessions Sess  
ON Blocking.session_id = sess.session_id   
CROSS APPLY sys.dm_exec_sql_text(Blocking.most_recent_sql_handle) AS BlockingSQL 
CROSS APPLY sys.dm_exec_sql_text(Blocked.sql_handle) AS BlockedSQL 
ORDER BY BlockingSessionId, BlockedSessionId

Criterias for Primary Key

Which key will you choose as a primary key ?
1. Primary key should not contain null values (should be declared as NOT NULL)
2. Should be unique in the domain ( current and future expected values)
3. Should not change over time

Can a foreign Key refer to same  column in a table : YES

As per :

"A table can have multiple Candidate Keys that are unique as single column or combined multiple columns to the table. They are all candidates for Primary Key. Candidate keys that follow all the three rules - 1) Not Null, 2) Unique Value in Table and 3) Static - are the best candidates for Primary Key. If there are multiple candidate keys that are satisfying the criteria for Primary Key, the decision should be made by experienced DBAs who should keep performance in mind."

Friday, June 24, 2011

Thursday, June 23, 2011

The difference between <%= and <%# in ASP.NET

•The <%= expressions are evaluated at render time
•The <%# expressions are evaluated at DataBind() time and are not evaluated at all if DataBind() is not called.
•<%# expressions can be used as properties in server-side controls. <%= expressions cannot.

Do you need to use ResolveUrl for href ?



If a web method in a wcf service is returning a DataTable, the DataTable must be given a name :

 Public Function ValidateUser(ByVal UserId As String, ByVal Password As String) As DataTable Implements ILoginService.ValidateUserDim retval As Boolean = FalseDim dtTemp As New DataTable("ReturnTable")Dim cmd As New SqlCommandcmd.CommandType =
cmd.CommandText =

cmd.Connection =

New SqlParameter() _New SqlParameter("UserId", UserId), _New SqlParameter("Password", Password) _New SqlConnection(connstr)Return dtTempEnd Function

Other wise you may get an error something like this :

An existing connection was forcibly closed by the remote host

Exception Details: System.Net.Sockets.SocketException: An existing connection was forcibly closed by the remote host

Dim dtTemp As New DataTable("ReturnTable")

OPEN QUESTION : Are static classes provided default static constructor by compiler ?

Unless the class is static, classes without constructors are given a public default constructor by the C# compiler in order to enable class instantiation.
Static classes cannot contain a constructor, although it is still possible to declare a static constructor to assign initial values or set up some static state.

The first statements almost makes it clear, although it is not mentioned clearly anywhere that default static constructors are NOT provided by compiler to static classes.

Another intersesting thing, what if both a derived and base class have static constructors ?
see this :

Static constructor of derived class is called before base class's static constructor
When base class and derived class both have static contructors, then first derived class static constructor is called and then base class static constructor is called. (In C#.NET 2.0)
This is exactly opoosite to the instance constructor hierarchy.

Method Overloading is done only by parameter types, not by return types



int Meth(int a, int b)return 0;//compile time error //Error 1 Type 'Class1' already defines a member called 'Meth' with the same parameter types //long Meth(int a, int b)//{// return 0;




int Meth(string b, int a)return 0;
int Meth(int a, string b)return 0;  

Wednesday, June 22, 2011


When all the columns from a table are selected, a PK clustered index is clearly the best option.

This has reference to this article

dont know what the following makes sense :

LIVE_DATA_301    nonclustered located on PRIMARY        Timestamp

count : 1062425

select * from LiveData301 select * from LiveData301 WITH (INDEX(LIVE_DATA_301))select

GPSString from LiveData301 WITH (INDEX(LIVE_DATA_301))

How to find list of indexes in a database ?

Found very good options at

The ones which I liked most are :

DECLARE @IndexInfo  TABLE (index_name         varchar(250)
                          ,index_description  varchar(250)
                          ,index_keys         varchar(250))
INSERT INTO @IndexInfo exec sp_msforeachtable 'sp_helpindex ''?'''
select * from @IndexInfo

This does not return table name, but if you are sure you can makeout table name from
index name then this is sufficient, short and sweet.

NOTICE this sp : sp_msforeachtable

2. Following one is also good, but uses a loop and less readable than above.
But it returns table name also.

DECLARE @IndexInfoTemp TABLE (index_name varchar(250)
,index_description varchar(250)
,index_keys varchar(250)
DECLARE @IndexInfo TABLE (table_name sysname
,index_name varchar(250)
,index_description varchar(250)
,index_keys varchar(250)
DECLARE @Tables Table (RowID int not null identity(1,1)
,TableName sysname
DECLARE @MaxRow int
DECLARE @CurrentRow int
DECLARE @CurrentTable sysname
FROM sys.indexes i
INNER JOIN sys.tables t ON i.object_id = t.object_id
SELECT @MaxRow=@@ROWCOUNT,@CurrentRow=1
WHILE @CurrentRow<=@MaxRow
SELECT @CurrentTable=TableName FROM @Tables WHERE RowID=@CurrentRow
INSERT INTO @IndexInfoTemp
exec sp_helpindex @CurrentTable
(table_name , index_name , index_description , index_keys)
@CurrentTable , index_name , index_description , index_keys
FROM @IndexInfoTemp
DELETE FROM @IndexInfoTemp
SET @CurrentRow=@CurrentRow+1
SELECT * from @IndexInfo

SQL Server - Which are the database recovery modes ?


Tuesday, June 21, 2011

How to clear SQL Server Cache ?

1. Normally, use following two when you do not want query compilation also to
come into picture. 
2. When you want query compilation also to come into picture,  free the proccache also :



3. The most comprehensive cleaning strategy would be :




From :

NOTE: Do not run the aforementioned DBCC commands on a production server
Removes ALL CLEAN BUFFERS from the buffer pool.
MSDN recommends to use CHECKPOINT before DROPCLEANBUFFERS.  AS you can see, DROPCLEANBUFFERS will drop only clean buffers, what about dirty buffers ?
clean buffers  - memory and disk data is matching
dirty buffers  - updated data present in memory, not written to disk
CHECKPOINT forces data of dirty pages to be written to disk, and makes it clean.
So next question : What is the difference between DROPCLEANBUFFERS and
It says "DBCC FREEPROCCACHE will invalidate all stored procedure plans that the optimizer has cached in memory and force SQL Server to compile new plans the next time those procedures are run."
FREEPROCCACHE clears cached execution plans of SPs.
Use DBCC FREEPROCCACHE to clear the plan cache carefully. Freeing the plan cache causes, for example, a stored procedure to be recompiled instead of reused from the cache.
Now another question : What are clean , dirty and cold buffer caches ?
CLEAN and DIRTY are well explained above. Not much clear about cold,
refer here for some explanation :
It says :
The cold buffer cache has a couple of variants within SQL Server.    Typically it refers to the buffer pool right after restart.   The data cache is not loaded (cold) and requires physical reads to populate the cache.   This is often termed the RAMPUP phase.     Internally it can also refer to the free list activity.   SQL Server understands the time of last access to buffers and when appropriate takes this into account when handling the free buffer lists.   Buffers on the free list that have some age to them may be termed cold because the age increases the chance that they accessible with a near memory transfer from a location such as CPU cache.

As per Pinal Dave

The following commands clean only the plan cache:

To clear the data buffer use the following command:
For more information visit to :

How to disable WCF security ?

In the host web.config, include a binding like following and assign it to the service :

Note the "CustomBinding" below, and note the  "None"s : security, proxyCredential, clientcredential. Also note that the binding is assigned using the bindingConfiguration attribute.

      <wsHttpBinding >
        <binding name="CustomBinding" >
          <security mode="None">
            <transport proxyCredentialType="None" clientCredentialType="None"></transport>
      <service name="BusinessServiceLayer.LoginService">
        <endpoint address="" binding="wsHttpBinding" contract="BusinessServiceLayer.ILoginService"  bindingConfiguration="CustomBinding" >
        <endpoint address="mex" binding="mexHttpBinding" contract="IMetadataExchange" />
            <add baseAddress="" />

How to Host a WCF Class Library Project ?

When we create a WCF class library project, it is not ready to be hosted on IIS directly.
It needs web.config and .svc file.
Notice that the WCF class library project has a app.config file, but no web.config or .svc file.

There are two steps :
  Create web.cofig
  Create .svc file

1. To create web.config, add a blank text file to the class library project. Rename it to web.config.
Add the following to it  and save it:
 <?xml version="1.0" encoding="UTF-8"?>

Then copy the entire <system.serviceModel>  tag from the existing app.config file of the class library peoject.

2. Next add a blank file to the wcf class library project and name it as a .svc file, say xyz.svc.
In the svc file add two statements :
  <%@ ServiceHost Service="BusinessServiceLayer.LoginService" %>
  <%@ Assembly Name="BusinessServiceLayer" %>

 Note that the service value in the ServiceHost directive  is the name appearing in the <service> tag in app.config/web.cofig

Assembly name is the name of the dll. By default this dll is created in bin\Debug of bin\Release folder of the
 class library project. This must be copied out of the \Debug or \Release folder into bin folder for it to
 be accessible to the IIS.

The last step is to set .svc as the default page in the IIS web site settings.

Thursday, June 16, 2011

How to Store output of SQL Server EXEC into a table

how to produce 0 without using any number in sql server ?

select $
select £
Declare @num int
Set @num = ''
Select @num

this is more useful. see what happens when a set an integer to null

Note that the following does NOT produce 0, it will produce NULL

@num int @num

Wednesday, June 15, 2011

How to share data between stored procedures ? Good Link

Discusses in detail various methods :

This table summarises the methods:

MethodInput/ OutputSQL Server versionsComment
Using OUTPUT ParametersOutputAllNot generally applicable, but sometimes overlooked.
Table-valued FunctionsOutputSQL 2000Probably the best method for output, but has some restrictions.
Inline FunctionsUse this when you want to reuse a single SELECT.
Multi-statement FunctionsWhen you need to encapsulate more complex logic.
Using a TableIn/OutAllMost general methods with no restrictions, but a little more complex to use.
Sharing a Temp TableMainly for single pair of caller/callee.
Process-keyed TableBest choice for many callers to same callee.
Global Temp TablesA variation of Process-Keyed.
INSERT-EXECOutputSQL 6.5Does not require rewrite. Has some gotchas.
Table Parameters and Table TypesIn/(Out)SQL 2008Could have been the final answer, but due to a restriction it is only mildly useful in this context.
Using the CLROutputSQL 2005Does not require a rewrite. Clunky, but is useful as a last resort when INSERT-EXEC does not work.
OPENQUERYOutputSQL 7Does not require rewrite. Tricky with many pitfalls.
Using XMLIn/OutSQL 2005A roundabout way that requires you to make a rewrite, but it has some advantages over the other methods.
Using Cursor VariablesOutputSQL 7Not recommendable.

Have a look at other articles also on the home page :

  • The curse and blessings of dynamic SQL. How you use dynamic SQL, when you should - and when you should not. (Added June 2001, Completely revised: April 2006. Most recent update: December 2008.) German translations of an old version available.

  • Arrays and Lists in SQL Server. Several methods on how to pass an array of values from a client to SQL Server, and performance data about the methods. There are three articles: one for SQL 2008 which is devoted entirely to table-valued parameters, one for SQL 2005 and later (for situations when you cannot use TVPs), and one for SQL 2000 and earlier. (Added March 2003. Version for SQL 2005 added March 2007, major revision and addition of article for SQL 2008: January 2010. Most recent update October 2010.)

  • Dynamic Search Conditions. How to write a stored procedure that permits users to select among many search conditions, using both dynamic and static SQL. There are two articles: one for SQL 2008, and one for SQL 2005 and earlier. (Added May 2003. Most recent update: November 2009. French and German translations of the SQL 2005 version available.)

  • How to share data between stored procedures. Different ways of passing sets of data between stored procedures. (Added July 2001, Most recent update: January 2010)

  • Giving Permissions through Stored Procedures. SQL 2005 adds two new methods – signing with certificates and impersonation with EXECUTE AS – that can manage cases where the classic method of ownership chaining fails. I explain the two new methods, as well as the old one, and warn you about the pitfalls. (Added February 2006)

  • Slow in the Application, Fast in SSMS? Ever experienced the situation that your query runs slow in your application, but when you try it in SQL Server Management Studio, the response is immediate? This article explains why this may happen, what parameter sniffing is and suggests methods to analyse and address your performance problem. (Added February 2011.)

  • Build Your Own Index. This is a teaser for.. Well, follow the link and read on! (Added November 2009.)

  • Error Handling in SQL 2005 and Later. This is an unfinished article, which consists only of a jumpstart section that shows how to use BEGIN-TRY. The full article will appear ...sometime. The article also gives you links to my old articles for error handling in SQL 2000. (Added July 2003. Most recent update: November 2009 )

  • Connecting to MS SQL Server from Unix. A brief summary of what's available. Since I don't work with Unix myself, it's not extremely well-informed, but people have kept asking me about this. (Added Jan 1999, Latest update: June 2008)

  • How find the difference between two consecutive rows in a table ?

    By using a self join , as below.
    Note that this query assumes the table has an autoincrement column like ID . If it is not, then possibly it will have to generated using ROW_NUMBER. Note that using autoincrement is better than using something like TOP.

    table test ( ID int, val int) insert test( ID , val) select 1, 10union all select 2, 12union all select 3, 9union all select 4, 15union all select 5, 8union all select



    ID val
    1 10
    2 12
    3 9
    4 15
    5 8
    6 20

    select a.ID , b.ID , a.val , b.val , a.val - b.val as diff from test a left join test b on

    ID ID val val diff
    2 1 12 10 2
    3 2 9 12 -3
    4 3 15 9 6
    5 4 8 15 -7
    6 5 20 8 12
    b.ID = a.ID -1
    * from test
    a.ID , b.ID , a.val , b.val , a.val - b.val as diff from test a left join test b on b.ID = a.ID -1
    6, 20