Yesterday I presented a case for not storing BLOB data in SQL Server. Dave Shared in the comments an good case against my generalized position against using blobs. His primary concern was the sprawl of files by storing each instance of BLOB data as a separate file. His product was a self-contained image management tool. By using SQL Server as […]

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 […]