-

For SQL Server 2008: Better to use One Table with Partitioning. Also you may check the new FILESTREAM storage feature.

For SQL Server 2005: Better to use 3 tables. As using 3 partitions will hurt the performance because SQL Server 2005 only assigns one thread per partition when querying the data. nstead of distributing all the available threads among the partitions.

-

LLBLGen Pro doesn't support FILESTREAM btw, for the same reason it also doesn't support Oracle's BFILE (which is the same kind of type): the data can't be read in 1 go by a datareader in generic code.

Otis wrote:

LLBLGen Pro doesn't support FILESTREAM btw, for the same reason it also doesn't support Oracle's BFILE (which is the same kind of type): the data can't be read in 1 go by a datareader in generic code.

I would like to use one solution to manage my database interaction. In exempting the support of FileStream within LLBLGen, one is going to have to move to some messy hybrid of LLBLGen plus standard MS database interaction.

I know that it can't be read in on 1 go, so what is the optimal solution for managing this?

Would Store Procedure support within LLBLGen be a solution?

I need to presist data and images to my database within a transactional context as well.

Why not use the 'Image' type on the field? You can store 2GB in such a field. It's stored separately in the DB's filesystem anyway, so it's similar to a filestream.

-

Chombatta wrote:

Otis wrote:

Why not use the 'Image' type on the field? You can store 2GB in such a field. It's stored separately in the DB's filesystem anyway, so it's similar to a filestream.

I'd like to bring this to your attention:

"ntext, text, and image data types will be removed in a future version of Microsoft SQL Server. Avoid using these data types in new development work, and plan to modify applications that currently use them. Use nvarchar(max), varchar(max), and varbinary(max) instead." http://msdn.microsoft.com/en-us/library/ms187993.aspx

Hence, I guess one would want to use varbinary(max) instead of image.

Heh Yes, varbinary(MAX) is also a good candidate. I don't think they'll remove the types any time soon though, but to be safe, varbinary(MAX) will do the trick.

Otis wrote:

LLBLGen Pro doesn't support FILESTREAM btw, for the same reason it also doesn't support Oracle's BFILE (which is the same kind of type): the data can't be read in 1 go by a datareader in generic code.

Bummer I wanted to use it in a sql server 2008 Express database. It has a total limit of 4gb but that does not include FileStreams. Is there no way of supporting it somehow?

I wonder if the MS Entity Framework supports it?

Thanks,

Fishy

Fishy wrote:

Otis wrote:

LLBLGen Pro doesn't support FILESTREAM btw, for the same reason it also doesn't support Oracle's BFILE (which is the same kind of type): the data can't be read in 1 go by a datareader in generic code.

Bummer I wanted to use it in a sql server 2008 Express database. It has a total limit of 4gb but that does not include FileStreams. Is there no way of supporting it somehow?

I wonder if the MS Entity Framework supports it?

Thanks,

Fishy

Why not storage in FileSystem and Save Location to the same?.