Verify FileStream Column. How fast is it? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Verify FileStream Column. How fast is it?

Author

Topic

shapper
Constraint Violating Yak Guru

450 Posts

Posted - 2011-07-04 : 21:08:37

Hello,

I have a table with a Filestream column.

I need to select N records but don't want to select the Filestream column.

However, I need to check if the column is null or if contains a file.

How heavy is it to check if that column is null in my query?

Thank You,

Miguel

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2011-07-05 : 03:18:27

It should be very fast. Have you tried?

SELECT TOP 10 Colname1, ColName2, RowHasFile = CASE WHEN FileStreamColumn IS NULL THEN 'No' ELSE 'Yes' END
FROM FilesTable

- Lumbago
My blog-> http://thefirstsql.com/2011/02/07/regular-expressions-advanced-string-matching-and-new-split-function-sql-server-2008-r2/

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2011-07-05 : 04:26:23

you could use a filtered index to make it even faster.

Filtered indexes were introduced in 2008. You can specify ranges so they are great at doing the where X IS / IS NOT NULL etc.

Something like:


CREATE NONCLUSTERED INDEX IX_FILT_FilesTable_NOTNULL
    ON dbo.FilesTable ([Filestream])
    WHERE [Filestream] IS NOT NULL ;
GO

Check em out in bol.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Durden81
Starting Member

1 Post

Posted - 2012-02-16 : 08:04:14

I have encountered a problem in my db (Sql server 2008 r2) that for a few hours all the "is null" conditions in the where clause that included a FILESTREAM column would lock the table.

This has been very strange and I can confirm it with 100% certainty.
To make sure this would never happen again (how am I supposed to know if this happens sometimes even if for a few minutes?!), I am adding a bit field everytime there is a table with a filestream that is nullable and I will make sure that it contains the information if the field is null or not.

I just wanted to warn people that this might happen and not to trust the filestream in a where clause.

The column was not indexed for "is null" like Transact Charlie suggested. If someone ever experience the same bug it would be interesting to try to add this index and see if it stops.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources