Today we are continuing with tips for high volume transaction processing in an OLTP database. Yesterday we talked about preferring INSERT transactions over UPDATE or DELETE transactions. Today I want to talk about preferring SETS over multiple calls with single records. Sets are not always practical, or may not meet the business requirements. When sets are a valid options, then […]

There are some specific things you can do to optimize your OLTP database performance allowing it to scale with the ability to handle a large load of concurrent transactions. Today I’m going to start with some basic tips that work with just about any database engine. One of the most effective optimization techniques I know of is to prefer INSERT […]

If you’re not required by law to comply with external standards, does that mean you aren’t responsible for the risks of non-compliance. Sure, you may not have to prove compliance through some sort of audit, as you would if you were under PCI regulations, or some other standard. But, does that mean you don’t have to have the same diligence […]

When considering data types and the desire to constrain the values to the minimum required, there is a tangent question you might consider. The question, “Is this column part of the primary key, or a foreign key?” Before we get into a heated battle regarding primary keys, and always having that key be a sequential integer, let me suggest that […]

Today I want to talk about some very specific philosophical questions when it comes to defining tables. The choices we make have implications in both the short and long term. Let me start with a simple premise. I have been taught that when you create a table, for each column in the table, it should be constrained to allow only […]

I was reading an editorial on LinkedIn on the topic of API documentation. https://www.linkedin.com/pulse/api-documentation-iot-quantum-leap-usability-paradigm-ghersi.The primary position of the author is that as software becomes more segregated, and connected through APIs such as RESTful services, the need for documentation becomes more important than ever. Furthermore, the documentation needs to not only express the API implementation, but also provide the consumer with […]

When it comes to a database engine and maintaining efficient performance you have four basic options. Don’t do anything and take what the engine does by default Host it with a provider that manages your installation Outsource a professional service to manage your database as an add on service Hire your own expertise A lot of people get reliable performance […]

Generally, I don’t like using SQL Server materialized views, sometimes called Indexed Views, because they are materialized by creating an index on the view definition. In order to create an index on the view you have to define it with phrase WITH SCHEMABINDING. What this does is map the view definition to the underlying tables in the query, even if […]

What’s keeping us from embracing the cloud for our data storage? From my experience the primary hurdle has been cost. Because the cloud is so flexible on how you can store things, calculating that cost is different from one implementation to the next. One thing that seems to be pretty consistent, regardless of implementation, is the cost of bandwidth to […]

When you think about database redundancy and SQL Server, there are a number of ways to implement the duplication of data, built directly into the engine. SQL Azure automatically creates three copies of any database you publish. It has a working copy, a witness, and witness of the witness. If the working copy goes offline, the witness is promoted, and […]