Indexes are important for any database. For canned packages the application designer makes the best indexes they can. The problem is that indexes on any system go through a lot of change as data is added, updated or deleted. Data changes cause indexes to become fragmented, and statistics about the indexes to become inaccurate. As your data is modified it […]

Even canned software solutions using a SQL Server database may benefit from tuning. SQL Server does a pretty good job of hiding these details from you with “out of the box” configurations performing database maintenance operations, sometimes without users even knowing they exist. Automatically creating or updating statistics helps keep your database tuned as your data grows. When a canned […]

There are four data types that will be deprecated in a future release of SQL Server as announced by Microsoft. They are the Timestamp, Text, NText and Image. The great news is that they have all been replaced with equivalent or more powerful data types so that the needed functionality has not been eliminated completely. http://msdn.microsoft.com/en-us/library/ms143729.aspx The downside is that […]

I was talking with a colleague recently who uses a canned application to assist in his daily work. The application uses SQL Server, granting him a them a lot of opportunity to extend their understanding and mining of the data. The problem they are experiencing is that the database is slow, and continues to get slower as it accumulates more […]

Today let’s complete the series reviewing the different join techniques found in SQL Server with the Loop Join. This techniques works well for smaller tables or smaller sets. The join is performed by walking through each record of the first table meeting the users critiera. For each row found it then seeks the joining records in the table to be […]

Merge Joins are another technique used by SQL Server to join tables with a large number of records. A merge join works effectively when the data from both tables is sorted in the same order. It takes the data from each table and works through both tables simultaneously, moving forward in either table when the value from the opposite table […]

SQL Server uses three different techniques when joining tables depending on the size of the table and the indexes available. When no good index exists on either table to implement an efficient join process, the query optimizer will often select a HASH Join. A hash join is performed by creating a hash value for the attributes used to join both […]

What do you do when you need to enforce referential integrity across databases on the same SQL Instance, or maybe on a different instance or server altogether? This kind of eventuality occurs often in data warehousing where sharding I used to increase performance, or sharded OLTP databases separated for performance, often seen in MySQL large scale implementations. Some implementations attempt […]

DRI (Declarative Referential Integrity) does a lot of busy work for you we used to have to do ourselves when it wasn’t available. At one time, referential integrity had to be manually coded through the use of triggers. You can still do this today if you have a need. In the trigger for a child table you would test for […]

Are you coding? You can ask me this just about any time and my answer will be, “Yes”. Even though I enjoy employing agile techniques for software development, it doesn’t mean that I don’t have a strategy to solve a problem. It doesn’t me there isn’t an architectural process, or no cogitation, about how to solve different issues. It just […]