Introduction So you have added a number of new tables to your database and you now need to create stored procedures to insert, update, and delete from them. Now, depending on how many tables you have added,this can be a very time consuming task but fear not, as I will show you an automated solution [...]

Dates in Microsoft SQL Server can include both the date and the time. Frequently it is desirable to extract portions of the date field and ignore the other portions. Often this is done for display purposes. For example the Transact SQL DatePart() function can be used to get just the day, year, month, etc. portions [...]

Bad things tend to happen to developments where the scripts for the database layer are left out of source control. Now that we have the means to do it properly, there are many reasons to make the database an equal partner in the development process. Troy discusses some of those reasons. Software developers have trouble in treating all [...]

SQL Storage Compress does just that, and encrypts as well, allowing the files associated with a live database to take much less file-space, and securing them from unauthorised access. Why would this be useful, and is it really a free lunch? Brad puts it to the test to find out. In the database world there [...]

As a dedicated ‘twitcher’, Michael’s eye is caught by a ‘tweet’ from Red Jungle. In this case, it was not an exotic species, but a user of SQL Source Control who was pleased with the way that it had saved them considerable time in the development process. Out of curiosity, Michael contacted them to find out [...]

While we all know that the Query Optimizer uses Indexes to produce better execution plans, we don’t all know exactly which indexes will give the best results. Benjamin Nevarez has done the research, and in this sample from his upcoming book, "Inside the SQL Server Query Optimizer", he helps us to understand how the Query [...]

The use of constraints is a valuable way of improving query performance as well as maintaining the integrity of the data, but this is, inevitably,  a trade-off: The data uses up more storage, and the modifications are slower and more difficult.  In SQL Programming, there are few ‘best-practices’ that are universally appropriate. Sometimes a query [...]

Alex Kuznetsov is credited with a clever technique for creating a history table for SQL that is designed to store contiguous time periods and check that these time periods really are contiguous, using nothing but constraints. This is now increasingly useful with the DATE data type in SQL Server.  The modification of data in this [...]

It is always better, and more efficient, to maintain referential integrity  by using constraints rather than triggers. Sometimes it is not at all obvious how to do this, and the history table, and other temporal data tables, presented problems for checking data that were difficult  to solve with constraints. Suddenly, Alex Kuznetsov came up with [...]