Today I want to return to the topic of FileStream and other BLOB (Binary Large Object) data types storage in SQL Server with the exception of XML. Let me start with a near dogmatic statement that BLOB data doesn’t belong in a relational database; especially when there is a lot of it. Let me provide a few reasons why. Using […]

Recently I received an inquiry regarding the use of SQL Server FileStreams. The database was designed on one machine, and restoring to a new machine made it difficult to get the file streams to work again. If you follow this link to Microsoft they provide an overview of what a filestream data type is and how it is implemented. http://technet.microsoft.com/en-us/library/bb933993%28v=sql.105%29.aspx […]

User Defined Table Types have been in SQL Server since 2008. They are mostly used to pass sets of data into a stored procedure. You create the user defined table type first in a database. Then you can populate the type from your client application or another SQL Server object. That table, defined as your own table type, may then […]

Have you found an opportunity to use Sequences in SQL Server Yet? Sequences can be very powerful when implementing a type/subtype database pattern. Using a type/Subtype pattern you may have a number of tables that have similar characteristics. For example, you could have a number of different tables representing a person. You could have people tables like Employee, Sales Representative, […]

We have been talking about creating an index on a view in order to enhance query performance. Today I wanted to share another technique that can be just as helpful. Did you know you can create an index on a table or view having a where clause? The resulting index only contains records meeting the criteria of the where clause. […]

Materialized views are enabled by creating an index on a view definition. You have to define the view with Schema Binding enabled in order to create an index. Moreover, if you have a materialized view you cannon change the schema of a table without first disabling the view, because the view depends on the schema of the table(s) on which […]

SQL Server has different kinds of non-materialized table objects. A materialized object is a table or indexed view which has physical representation stored on the disk. All other set techniques rely on materialized or static data in order to generate a set of data. CTE (Common Table Expressions) are useful in that they may contain multiple different expressions resulting in […]

How are you query plan evaluation skills? Reviewing a query plans is often the easiest way to find out why a specific query is not performing well. You can use the standard reports in SQL Server to identify long running queries, or queries that use a lot of resources. Once identifying those queries you can attempt to execute them and […]

My family loves to play with words. We do a lot of puns, rhymes, poems, and other fun stuff. This year we are doing Hinky Pinkies in a thanksgiving contest. “What is a Hinky Pinky?”, you may ask. A Hinky Pinky consists of two rhyming words with two syllables. If a word only has one syllable it is called a […]

Damian has been taking advantage of the Parallel capabilities using a number of different technologies. Some enable parallel computing on a massive scale. Damian writes: Check out links below. Very informative. http://blog.sqlauthority.com/2011/02/06/sql-server-cxpacket-parallelism-usual-solution-wait-type-day-6-of-28/ http://blog.sqlauthority.com/2010/03/15/sql-server-maxdop-settings-to-limit-query-to-run-on-specific-cpu/ We have also being using the new parallel options within .net 4.5 in our applications to speed up web request calls, loop processing (massive performance gains) and […]