Strategies for Checking Isnull on Varbinary Fields

Strategies for checking ISNULL on varbinary fields?

I think it's slow because the varbinary column is not (and can't be) indexed. Therefore, your approach to use a computed (and indexed) column is valid.

However, I would use ISNULL(DATALENGTH(Content), -1) instead, so that you can distinguish between length 0 and NULL. Or just use DATALENGTH(Content). I mean, Microsoft SQL Server is not Oracle where an empty string is the same as NULL.

Efficiency of checking for null varbinary(max) column?

It uses the NULL bitmap. So no.

Check varbinary type not is null and download it

One of the major rules of using any kind of database is never return data you don't need. With this in mind you should exclude rows with no image using the query rather than checking after the fact.

So:

"select file from sentmail where msg_id='"+id of a particular row+"' and file is not null"

Rather than:

if (dr["file"] != DBNull.Value)
{
    picturebox.Visible = true;
}

Giving us:

private void ViewSentMailDet_Load(object sender, EventArgs e)
{
    picturebox.Visible = false;
    string con_string = @"Data Source=(local);Initial Catalog=fyp;Integrated Security=true";
    SqlConnection con = new SqlConnection(con_string);
    string qry = "select file from sentmail where msg_id='"+id of a particular row+"' and file is not null";
    SqlDataAdapter ad = new SqlDataAdapter(qry, con);
    DataTable dt = new DataTable();
    ad.Fill(dt);
    foreach (DataRow dr in dt.Rows)
    {
        using (var ms = new MemoryStream((byte[])dr["file"])) 
            picturebox.Image = Image.FromStream(ms);
        picturebox.Visible = true;
    }
}

Where clause with varbinary doesn't work

Don't think or assume that it couldn't possibly be blocking, just because a different query returns immediately. With a different where clause, and a different plan, and possibly different locking escalation, you could certainly have cases where one query is blocked and another isn't, even against the same table.

The query is obviously being blocked, if your definition of "stuck" is what I think it is. Now you just need to determine by who.

In one query window, do this:

SELECT @@SPID;

Make note of that number. Now in that same query window, run your query that gets "stuck" (in other words, don't select a spid in one window and expect it to have anything to do with your query that is already running in another window).

Then, in a different query window, do this:

SELECT blocking_session_id, status, command, wait_type, last_wait_type 
  FROM sys.dm_exec_requests 
  WHERE session_id = <spid from above>;

Here is a visualization that I suspect might help (note that my "stuck" query is different from yours):

If you get a non-zero number in the first column, then in that different query window, do this:

DBCC INPUTBUFFER(<that blocking session id>);

If you aren't blocked, I'd be curious to know what the other columns show.

---

As an aside, changing the WHERE clause to use slightly different predicates to identify the same rows isn't going to magically eliminate blocking. Also, there is no real benefit to doing this:

SELECT Size
FROM
(
    SELECT ISNULL(DATALENGTH(MyField), 0) AS Size FROM MyTable
) AS A
WHERE A.Size > 0

When you can just do this:

SELECT ISNULL(DATALENGTH(MyField), 0) AS Size 
  FROM dbo.MyTable -- always use schema prefix
  WHERE DATALENGTH(MyField) > 0; -- always use semi-colon

SQL Server is smart enough to not calculate the DATALENGTH twice, if that is your concern.

Check whether image column is null

CASE <expression> WHEN <value> THEN uses equality/equivalence comparison, but you need an IS NULL check because NULL is not a comparable quantity and — as the error indicates — images can't be "compared".

Fortunately, there is another construct — CASE WHEN <test> THEN — that brings the equality out into the user-provided parameters, allowing you to omit it:

SELECT  OutgoindDoc = CASE
                        WHEN ReceivedData IS NULL THEN
                           'null'
                        ELSE
                           CONVERT(xml,(CONVERT(varbinary(max),ReceivedData))) 
                      END
FROM    ib_IncomingData

Best way to check if a Data Table has a null value in it

Try comparing the value of the column to the DBNull.Value value to filter and manage null values in whatever way you see fit.

foreach(DataRow row in table.Rows)
{
    object value = row["ColumnName"];
    if (value == DBNull.Value)
        // do something
    else
        // do something else
}

More information about the DBNull class

---

If you want to check if a null value exists in the table you can use this method:

public static bool HasNull(this DataTable table)
{
    foreach (DataColumn column in table.Columns)
    {
        if (table.Rows.OfType<DataRow>().Any(r => r.IsNull(column)))
            return true;
    }

    return false;
}

which will let you write this:

table.HasNull();

Unexpected behavior of binary conversions (COALESCE vs. ISNULL)

This is not a bug. They're documented to handle data type precedence differently. COALESCE determines the data type of the output based on examining all of the arguments, while ISNULL has a more simplistic approach of inspecting only the first argument. (Both still need to contain values which are all compatible, meaning they are all possible to convert to the determined output type.)

From the COALESCE topic:

Returns the data type of expression with the highest data type precedence.

The ISNULL topic does not make this distinction in the same way, but implicitly states that the first expression determines the type:

replacement_value must be of a type that is implicitly convertible to the type of check_expression.

I have a similar example (and describe several other differences between COALESCE and ISNULL) here. Basically:

DECLARE @int int, @datetime datetime;
SELECT COALESCE(@int, CURRENT_TIMESTAMP);

-- works because datetime has a higher precedence than the chosen output type, int

2020-08-20 09:39:41.763

GO
DECLARE @int int, @datetime datetime;
SELECT ISNULL(@int, CURRENT_TIMESTAMP);

-- fails because int, the first (and chosen) output type, has a lower precedence than datetime
Msg 257, Level 16, State 3
Implicit conversion from data type datetime to int is not allowed. Use the CONVERT function to run this query.

---

Related Topics