Tag Archives: filestream

Efficiently serving binary content from SQL Server in ASP.NET MVC – local user group talk

This will be a local user group talk that I’ll be having, in Bucharest on Tuesday 13th of November.

Storing large binary objects (usually image files) in the RDBMS has been a blessing but for some is unconceivable. We will explore different ways to do this, from worse to best and we will take advantage of a new feature introduced by SQL Server 2012.

The location is TeamNet Int’l HQ – Sema Parc, Splaiul Independenţei nr. 319, clădirea RiverView, etaj 8
Except an ID there is nothing else that you need to bring in order to participate to the event.
Further geographical details.

The most popular way to get there is by taking the subway as there is a station right near the building.

The official announcement can be found on RONUA’s site.

See you there!

——–

Later edit : It’s been great! Not too many people but keen to learn new stuff. Here’s two pictures from the talk. Notice a new generation of programmers forming 🙂

New features for database developers in SQL Server 2012 : simpler paging, sequences and FileTables

TL;DR :

  • Paging got simpler and more efficient
  • Sequences have been introduced; better performance for auto-generated IDs and easier to have IDs unique across tables
  • FileTables have been introduced : building upon the FileStream feature now we can have non-transactional access to files stored in the DB as a windows share along with transactional access via T-SQL

Lengthier version :

SQL Server 2012, in my opinion does not come with earth-shaking changes but comes with performance improvements, feature improvements and a some new features.

First of all, Management Studio has the same engine as Visual Studio which means you get a nice WPF experience, better font rendering and CTRL-scroll quick zoom-in/zoom-out.

Let’s say you want to retrieve data from the database in a paged way (that means chunks of data of a requested size or smaller). Typically you would write this in SQL Server 2008 R2 or older :


DECLARE	@Offset		AS INT = 6
DECLARE @PageSize	AS INT = 5

SELECT	Id,
		Name
FROM
(
	SELECT	Id,
			Name,
			ROW_NUMBER()	OVER (ORDER BY Id)	AS	RowNumber
	FROM	Users
) UsersSelection

WHERE	UsersSelection.RowNumber >  @Offset
	AND	UsersSelection.RowNumber <= @Offset + @PageSize

In SQL Server 2012 the T-SQL syntax has been updated introducing keywords that facilitate a simpler and more efficient paging, keywords such as OFFSET, FETCH, NEXT ROWS and ONLY. A script that would retrieve the same data would be :


DECLARE	@Offset		AS INT = 6
DECLARE @PageSize	AS INT = 5

SELECT		Id,
			Name
FROM		Users
ORDER BY	Id
OFFSET		@Offset		ROWS
FETCH NEXT	@PageSize	ROWS ONLY

Observe the simpler, clearer syntax. Also, considering that the subselect has been eliminated (the subselect was required because the ROW_NUMBER column could not be addressed in the same select – for the WHERE clause), also the query cost was improved :

2008 :

2012 :

Read more »