SQL server query for Concatenate column fail

You can query for combine columns using ‘+’ operator.

for example:
SELECT Col1 + Col2 AS CombineColumn

But this query will sometimes fail, how is that?

This query will fail if either of the concatenating column contains ‘Null’ value.

The isNull() Function can be used to check whether the column is Null.

Example With isNull() Function.
Select isnull(CAST(Customer.Contactid as nvarchar(10)),”) +’-‘+ isnull(Customer.Firstname,”) +’-‘+ isnull(Customer.LastName,”)
as [Full Name] from Customer

The first parameter is the column and the second parameter is what the method return if ‘Null’ was encountered.


Paging query in Sql Server 2005 or above 用Sql Server2005做分頁查詢

MS Sql server do not have none standard features like SQL_CALC_FOUND_ROWS and LIMIT in MySql.
That makes paging by SQL a little difficult. Luckly start from Sql Server 2005, MS provide a kind of work-around.

微軟的Sql Server並沒有像MySql一樣有提供非標準的SQL_CALC_FOUND_ROWS和LIMIT去做分頁動作.
這樣令用SQL做分頁困難了一點,還好Sql server從2005版開始提供了一個解決方法.

We can use the ROW_NUMBER() method to add an incremental column with some ordering and use the row number we’ve just created to achieve paging.
Note that this doesn’t mean the data you retrieve is lesser, NO, we are doing this to save memory and time for the web server by making paging happen in SQL server.


SELECT ROW_NUMBER() OVER (ORDER BY SortColumn ASC) AS RowNumber, ColumnName
FROM TableName
WHERE RowNumber > 20 AND RowNumber < 50
ORDER BY RowNumber

The above example shows how to get one column ColumnName from table with TableName, order by SortColumn where row number between 20 and 50.
We use the nested query to generate a temporary view, this view will contain a new column call RowNumber. We will use it to do paging in the next example.

public static DataTable QueryDatabaseForDataTable(string queryStr, int pageNumber, int RowsPerPage, string OrderByColumn)
SqlConnection l_sqlConn = GetSqlConnection();
//modify query string
string l_rowNumberColumn = string.Format(@"SELECT ROW_NUMBER() OVER (ORDER BY {0:s} ASC) AS RowNumber, ", OrderByColumn);
queryStr = queryStr.Replace("SELECT ", l_rowNumberColumn);
string l_wrapQueryStr = string.Format("SELECT * FROM ({0:s}) AS TEMPVIEW WHERE RowNumber Between @LowerBoundIndex AND @UpperBoundIndex ORDER BY RowNumber", queryStr);
SqlCommand l_sqlCmd = new SqlCommand(l_wrapQueryStr, l_sqlConn);
l_sqlCmd.Parameters.Add("LowerBoundIndex", SqlDbType.Int).Value = pageNumber * RowsPerPage;
l_sqlCmd.Parameters.Add("UpperBoundIndex", SqlDbType.Int).Value = (pageNumber + 1) * RowsPerPage;
SqlDataAdapter l_sqlDA = new SqlDataAdapter(l_sqlCmd);
DataTable l_dt = new DataTable();
return l_dt;

In this example, it is a method to do general paging retrieve, it can be even more general, we will leave this to the readers.
We pass the query string, page number, number of rows in one page and the ordering column as parameters. The method will return the data in that page for us.

This might comes in handy someday when you have a large distrubuted system.