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 item.click.
http://www.codedigest.com/Articles/ASPNET/132_GridView_with_CheckBox_%e2%80%93_Select_All_and_Highlight_Selected_Row.aspx
using jquery this is simpler :
http://www.codedigest.com/CodeDigest/62-Check-All-Checkboxes-in-GridView-using-JQuery.aspx

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 : http://www.codedigest.com/Tutorials/jQuery_Tutorials_For_ASPNET_Developers.aspx

jQuery Tutorials For ASP.Net Developers

Beginning jQuery Development

What is jQuery? How to use it in ASP.Net Pages?

How to enable jQuery intellisense in Visual Studio 2008?

How to use jQuery intellisense in an external JavaScript file?

Introduction to jQuery in ASP.Net and 10 Advantages to Choose jQuery

Using jQuery in ASP.Net Application

Master-Detail View with Selectable GridView and DetailsView using jQuery in ASP.Net

Handling or Attaching Events to Dynamic HTML using jQuery in ASP.Net

Creating Accordion Panel using jQuery in ASP.Net

Building Collapsible Panel Control using jQuery in ASP.Net Page

http://www.codedigest.com/Tutorials/jQuery_Tutorials_For_ASPNET_Developers.aspx 

Building Ajax Application using jQuery

Doing AJAX with jQuery in ASP.Net

Calling a WebService using jQuery in ASP.Net

Ajax Application using jQuery and JSON

Building Cascading DropDownList in ASP.Net Using jQuery and JSON

Creating a Simple AJAX Master-Details View Using jQuery, JSON and jTemplates in ASP.Net

Populating RadioButtonList Using jQuery, JSON in ASP.Net

Lazy Loading jQuery Collapsible Panel in ASP.Net Using JSON

UserName or Login Availability Check Using jQuery in ASP.Net

Rotate Text Link Advertisements Using JQuery in ASP.Net

Lazy Loading jquery Accordion Panel in ASP.Net

Creating a Simple Rating control using jQuery and AJAX in ASP.Net

UserName or Login Availability Check Using jQuery in ASP.Net

GridView Style Edit Update in Repeater Control Using jQuery and Ajax

GOOD JAVASCRIPT SITES

http://www.w3schools.com/js/js_ex_dom.asp

http://www.quackit.com/javascript/examples/

http://www.comptechdoc.org/independent/web/cgi/javamanual/javadocument.html

CAST CONVERT PINAL

http://blog.sqlauthority.com/2007/07/07/sql-server-convert-text-to-numbers-integer-cast-and-convert/

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?
SELECT CAST(YourVarcharCol AS INT) FROM Table
SELECT CONVERT(INT, YourVarcharCol) FROM Table

Will CAST or CONVERT thrown an error when column values converted from alpha-numeric characters to numeric?
YES.

Will CAST or CONVERT retrieve only numbers when column values converted from alpha-numeric characters to numeric?
NO.

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?
CAST and CONVERT

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

http://www.sqlservercentral.com/articles/T-SQL/73887/
http://jahaines.blogspot.com/2009/10/converting-scalar-user-defined-function.html

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
begin
insert 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 ?
16

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 :
select
(
join
(
on
order by b.type_desc ,a.name a.id = b.object_id select object_id , name , type_desc from sys.indexes ) B select id , name from sysobjects where xtype = 'U' ) A a.name , 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

http://msdn.microsoft.com/en-us/library/cc280372.aspx

Difference between PRIMARY KEY and UNIQUE KEY

PRIMARY KEY	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 Defined
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

A Good Link on "Generics, Reference Types, Value Types, and Interview Questions " http://geekswithblogs.net/michelotti/archive/2010/08/06/generics-reference-types-value-types-and-interview-questions.aspx

Another good resume tips : http://ofb.net/~niniane/resume_howto.html http://www.detrition.net/resume.html

http://ofb.net/~niniane/resume_howto.html


http://www.detrition.net/resume.html

Which can be rolled back - DELETE or TRUNCATE ?

Refer  http://blog.sqlauthority.com/2007/12/26/sql-server-truncate-cant-be-rolled-back-using-log-files-after-transaction-session-is-closed/



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

1. http://dotnetslackers.com/articles/sql/Identifying-currently-running-SQL-queries.aspx

Identifying currently running SQL queries

SELECT  

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  

  THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2  

  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

2. What's blocking my running SQL?  http://dotnetslackers.com/articles/sql/Whats-blocking-my-running-SQL.aspx

SELECT 
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 http://dotnetslackers.com/articles/sql/Difference-Between-Candidate-Keys-and-Primary-Key.aspx :

"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."

Value of type '1-dimensional array of ServiceReference1.CompositeType' cannot be converted to 'System.Collections.Generic.List(Of ServiceReference1.CompositeType) Why WCF returns array representation on wire by default

Value of type '1-dimensional array of ServiceReference1.CompositeType' cannot be converted to 'System.Collections.Generic.List(Of ServiceReference1.CompositeType)



Why WCF returns array representation on wire by default
http://creamcode.wordpress.com/2011/05/15/why-wcf-returns-array-representation-on-wire-by-default/

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

http://blogs.msdn.com/b/dancre/archive/2007/02/13/the-difference-between-lt-and-lt-in-asp-net.aspx


•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 ?

ALMOST NO.

WCF : IMPORTANT : BE CAREFUL WHEN RETURNING A DATATABLE : ALWAYS NAME THE TABLE

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 = CommandType.StoredProcedure"ValidateUser"cmd.Parameters.AddRange(
{

})
cmd.Connection =
cmd.Connection.Open()
dtTemp.Load(cmd.ExecuteReader())
cmd.Connection.Close()


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 ?

ALMOST "NO".

http://msdn.microsoft.com/en-us/library/ms173115(v=vs.80).aspx
Unless the class is static, classes without constructors are given a public default constructor by the C# compiler in order to enable class instantiation.

http://msdn.microsoft.com/en-us/library/79b3xss3(v=vs.80).aspx
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 : http://msdn.microsoft.com/en-us/library/k9x6w0hc(v=vs.80).aspx

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;


 //VALID
{

}


//VALID//NOTE : EVEN A RETURN TYPE OF LONG IS ALSO VALID
{

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

Links FOR SQL SERVER PERFORMANCE TUNING

1. CODEPROJECT

http://www.codeproject.com/KB/database/SQLServerTipsGuidelines.aspx

2. DOTNETHEAVEN
http://www.dotnetheaven.com/UploadFile/skrishnasamy/SQLPerformanceTunning03112005044423AM/SQLPerformanceTunning.aspx

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

This has reference to this article http://sqlserverpedia.com/wiki/Using_the_Right_Indexes_for_Optimal_Performance

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
http://stackoverflow.com/questions/765867/list-of-all-index-index-columns-in-sql-server-db

The ones which I liked most are :


1.
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
INSERT INTO @Tables
SELECT
DISTINCT t.name
FROM sys.indexes i
INNER JOIN sys.tables t ON i.object_id = t.object_id
WHERE i.Name IS NOT NULL
SELECT @MaxRow=@@ROWCOUNT,@CurrentRow=1
WHILE @CurrentRow<=@MaxRow
BEGIN
SELECT @CurrentTable=TableName FROM @Tables WHERE RowID=@CurrentRow
INSERT INTO @IndexInfoTemp
exec sp_helpindex @CurrentTable
INSERT INTO @IndexInfo
(table_name , index_name , index_description , index_keys)
SELECT
@CurrentTable , index_name , index_description , index_keys
FROM @IndexInfoTemp
DELETE FROM @IndexInfoTemp
SET @CurrentRow=@CurrentRow+1
END --WHILE
SELECT * from @IndexInfo

SQL Server - Which are the database recovery modes ?

full
 simple
 bulk-logged

How to clear SQL Server Cache ?

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

CHECKPOINT
DBCC DROPCLEANBUFFERS

DBCC FREEPROCCACHE

3. The most comprehensive cleaning strategy would be :

DBCC FREESYSTEMCACHE(All)
DBCC FREESESSIONCACHE
DBCC FREEPROCCACHE
DBCC FLUSHPROCINDB( db_id )
CHECKPOINT
DBCC DROPCLEANBUFFERS

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



From : http://sqlserverpedia.com/wiki/SELECT_DISTINCT

DBCC DROPCLEANBUFFERS

DBCC FREEPROCCACHE

NOTE: Do not run the aforementioned DBCC commands on a production server

MSDN : DBCC DROPCLEANBUFFERS (Transact-SQL)

http://msdn.microsoft.com/en-us/library/ms187762.aspx

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.

REMEMBER that CHECKPOINT only works on CURRENT DATABASE.

So next question : What is the difference between DROPCLEANBUFFERS and

FREEPROCCACHE ?

See http://mssqlonline.blogspot.com/2007/03/stored-procedure-clean-cache-and-clean.html for answer.

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.

MSDN says http://msdn.microsoft.com/en-us/library/ms174283.aspx

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 :

http://blogs.msdn.com/b/psssql/archive/2009/03/17/sql-server-what-is-a-cold-dirty-or-clean-buffer.aspx

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 http://blog.sqlauthority.com/2008/07/22/sql-server-clear-sql-server-memory-caches/

The following commands clean only the plan cache:
DBCC FREESYSTEMCACHE(All | pool_name)
DBCC FREESESSIONCACHE
DBCC FREEPROCCACHE
DBCC FLUSHPROCINDB( db_id )
To clear the data buffer use the following command:
DBCC DROPCLEANBUFFERS
For more information visit to : http://blog.sqlauthority.com/2007/03/23/sql-server-stored-procedure-clean-cache-and-clean-buffer/

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.

  <system.serviceModel>
    <bindings>
      <wsHttpBinding >
        <binding name="CustomBinding" >
          <security mode="None">
            <transport proxyCredentialType="None" clientCredentialType="None"></transport>
          </security>
        </binding>
      </wsHttpBinding>
    </bindings>
    <services>
    
      <service name="BusinessServiceLayer.LoginService">
        <endpoint address="" binding="wsHttpBinding" contract="BusinessServiceLayer.ILoginService"  bindingConfiguration="CustomBinding" >
        </endpoint>
        <endpoint address="mex" binding="mexHttpBinding" contract="IMetadataExchange" />
        <host>
          <baseAddresses>
            <add baseAddress="http://RelayTrack_Services.retser.com/BusinessServiceLayer/LoginService/" />
          </baseAddresses>
        </host>
      </service>
    </services>

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"?>
  <configuration>
  </configuration>

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.

How to Store output of SQL Server EXEC into a table

http://it.toolbox.com/wiki/index.php/Store_output_of_SQL_Server_EXEC_into_a_table

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

1.
select $
2.
select £
3.
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

Declare
Select @num int @num

How to share data between stored procedures ? Good Link

http://www.sommarskog.se/share_data.html



Discusses in detail various methods :

This table summarises the methods:

Method	Input/ Output	SQL Server versions	Comment
Using OUTPUT Parameters	Output	All	Not generally applicable, but sometimes overlooked.
Table-valued Functions	Output	SQL 2000	Probably the best method for output, but has some restrictions.
Inline Functions			Use this when you want to reuse a single SELECT.
Multi-statement Functions			When you need to encapsulate more complex logic.
Using a Table	In/Out	All	Most general methods with no restrictions, but a little more complex to use.
Sharing a Temp Table			Mainly for single pair of caller/callee.
Process-keyed Table			Best choice for many callers to same callee.
Global Temp Tables			A variation of Process-Keyed.
INSERT-EXEC	Output	SQL 6.5	Does not require rewrite. Has some gotchas.
Table Parameters and Table Types	In/(Out)	SQL 2008	Could have been the final answer, but due to a restriction it is only mildly useful in this context.
Using the CLR	Output	SQL 2005	Does not require a rewrite. Clunky, but is useful as a last resort when INSERT-EXEC does not work.
OPENQUERY	Output	SQL 7	Does not require rewrite. Tricky with many pitfalls.
Using XML	In/Out	SQL 2005	A roundabout way that requires you to make a rewrite, but it has some advantages over the other methods.
Using Cursor Variables	Output	SQL 7	Not recommendable.

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

http://www.sommarskog.se/index.html

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.



create 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

 select



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
1 NULL 10 NULL NULL
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