Moving away from rational databases as a data storage technique may have its own set of problems to solve. For example, what do you do when you have two collections of data with a common attribute (like a foreign key in a relational database) on which you join the two sets, resulting in a new set of data with attributes […]

Today I am sharing an approach from Maurice which uses views and triggers on views to present a consistent interface to consumers. This technique works differently from the one we presented yesterday using views, table value functions and stored procedures to maintain a static interface. Instead, Maurice exposes the views directly to consumers. Updates and inserts are propagated to one […]

Today Aaron shares his implementation for applying the open/closed principle to SQL Server data and code. As stated previously it is more difficult to introduce a static set of parameters in SQL because TSQL does not support interfaces or inheritance. The primary point of interfaces is that you can implement them in many different ways and still keep the contract […]

I have used SQL Server integration Services (SSIS) since it was first released in SQL Server 2005. I have used a lot of ETL tools over the years, and in my opinion, SSIS is equal to the best of them. It is amazing some of the features included such as fuzzy logic. And the number of data connections it is […]

Have you ever tried applying the Open/Closed principle to SQL Server stored procedures or functions? Since SQL Server is not an object oriented language it can be difficult to achieve, and may not be worth the effort. The point of the Open/Closed principle is to assure that existing code continues to work as you extend your code for newer behavior. […]

If you have looked into sharding there are a number of techniques you can use. You can shard data of the same type across multiple servers. You can replicate the same data duplicating it across multiple servers. You can shard data of different types across different servers. You can do any combination of the previous techniques. How you choose to […]

Sharding data is a good way to increase performance for persistence and containing the cost of your software, depending on the engine you select. Sharding data is accomplished by using an algorithm to separate data, placing it on different storage instances for the purposes of balance, redundancy, increased capacity or any combination of the above. You can shard data using […]

SQL Server introduced the XML data type in the 2005 release resulting in a lot of power when dealing with both XML data and native SQL. The engine even has the ability to validate XML against a schema, shard the data so that it can be indexed, and included XPath and XQuery syntax for searching XML data quickly and efficiently. […]

What Do You Think of Hybrid Environments? I have been working with several different people setting up hybrid environments. With pieces on-premise, and pieces in the cloud, it gets pretty interesting very quickly. There are a number of things I’ve learned, and one ruling that has just been issued certainly weighs heavily on configuration thoughts. First, something that is becoming […]

Before SQL Server 2008 there was no native method to pass sets to a stored procedure or function. With SQL Server 2008 they introduced the User Defined Table Type. You could define a type just like a table, and pass in data using a variable of that type allowing sets of data to be passed to stored procedures like never […]