A SQL sample for pager

The best way for pager of searching result is use pager in Database side, use stored procedure:

Here is a sample :

SELECT TOP pageSize *
From  [TestDB].[dbo].[USER_EVENTS_LOG]
Where [LogEventsUserID] not in
(select top pageSize * pageID [LogEventsUserID]
  FROM [TestDB].[dbo].[USER_EVENTS_LOG]
  order by CreateTime  desc)
  order by CreateTime desc

(the pageID start from 0 above, and also it is descent order)

SQL DateTime Operations

SQL DateTime Operations:

SELECT * FROM DATE_SAMPLE WHERE SAMPLE_DATE = ‘2003-04-09’

SELECT * FROM DATE_SAMPLE WHERE SAMPLE_DATE between ‘2003-04-09’
AND ‘2003-04-10’

SELECT * FROM DATE_SAMPLE
WHERE CONVERT(CHAR(10),SAMPLE_DATE,120) = ‘2003-04-09’
(about why use 120 above, please check MSDN here)

SELECT * FROM DATE_SAMPLE
WHERE SAMPLE_DATE >= ‘2003-04-09’
AND SAMPLE_DATE <‘2003-04-10’

SELECT * FROM DATE_SAMPLE
WHERE
DATEPART(YEAR, SAMPLE_DATE) = ‘2003’ AND
DATEPART(MONTH,SAMPLE_DATE) = ’04’ AND
DATEPART(DAY, SAMPLE_DATE) = ’09’

SELECT * FROM DATE_SAMPLE WHERE
CAST(FLOOR(CAST(SAMPLE_DATE AS FLOAT))AS DATETIME) =
‘2003-04-09’

Above samples from databasejournal, they have very detail explanation for those samples. Please read there.

C# Transaction for SQL Server

      SqlConnection db = new SqlConnection(“connstring”);
      SqlTransaction transaction;

      db.Open();
      transaction = db.BeginTransaction();
      try
      {
         new SqlCommand(“INSERT INTO TransactionDemo ” +
            “(Text) VALUES (‘Row1’);”, db, transaction)
            .ExecuteNonQuery();
         new SqlCommand(“INSERT INTO TransactionDemo ” +
            “(Text) VALUES (‘Row2’);”, db, transaction)
            .ExecuteNonQuery();

         transaction.Commit();
      }
      catch (SqlException sqlError)
      {
         transaction.Rollback();
      }
      db.Close();

SQL: select record # 25 – 45 ?

How to easily select a specified range records ? such as from #25 to #45 ?

samples:

record 1 – 20:
SELECT TOP 20 * from member where … order by ID

record 21 – 40:
SELECT TOP 20 * from member where … AND ID NOT IN (SELECT TOP 20 ID FROM member order by ID) order by ID

record 41 – 50:
SELECT TOP 10 * from member where … AND ID NOT IN (SELECT TOP 40 ID FROM member order by ID) order by ID

nchar vs nvarchar (Transact SQL)

Both of nchar and nvarchar are character data types that are either fixed-length, nchar, or variable-length, nvarchar, Unicode data and use the UNICODE UCS-2 character set.

nchar [ ( n ) ]

    Fixed-length Unicode character data of n characters. n must be a value from 1 through 4,000. The storage size is two times n bytes. The ISO synonyms for nchar are national char and national character.

nvarchar [ ( n | max ) ]

    Variable-length Unicode character data. ncan be a value from 1 through 4,000. max indicates that the maximum storage size is 2^31-1 bytes. The storage size, in bytes, is two times the number of characters entered + 2 bytes. The data entered can be 0 characters in length. The ISO synonyms for nvarchar are national char varying and national character varying.

From here MSDN