Because SQL Server does not have any support for temporal data out of the box, there are always problems with this kind of data. The most important problem is the performance. Traditional solutions have to deal with slow queries, which also make slow constraints implemented through triggers. (more…)
I am continuing with presenting different solutions for interval queries in SQL Server. For an introduction, please refer to the blog post Interval Queries in SQL Server Part 1. Note that you also need to read an excellent article by Itzik Ben-Gan wrote on interval queries in SQL Server (http://sqlmag.com/t-sql/sql-server-interval-queries) by using the Relational Interval Tree model. I am using the tables and data Itzik has prepared. In order to test the solutions, you can download the code from Itzik’s article by using the link in this paragraph.
My good old friend Itzik Ben-Gan wrote an excellent article on interval queries in SQL Server (http://sqlmag.com/t-sql/sql-server-interval-queries
) by using the Relational Interval Tree model. Based on the model developed by Kriegel, Pötke, and Seidl, and enhanced by Martin, Itzik fully developed a T-SQL solution. The solution is great, and makes interval queries efficient in all circumstances. However, the solution is quite complex. Itzik made a Microsoft Connect feature proposal (https://connect.microsoft.com/SQLServer/feedback/details/780746
) to add SQL Server Engine support for interval queries. I fully agree that this would be the best solution; now when the theory is known and implementation made possible, it is time that Microsoft puts this in the database engine. (more…)
Bad database design and inefficient queries lead to applications that are hard to maintain and upgrade and that don’t perform well. Why don’t you start with a good design? Join me during the DevWeek 2013
post-conference seminar that provides developers and administrators with essential knowledge needed for a good database logical and physical design for well-performing applications in a single day. Of course, the knowledge gained by attending this seminar helps improving design and performance of existing databases as well.
With mixed feelings I am announcing this: C. J. Date has a two-day seminar in Ljubljana (http://www.dbprof.com/
).Why are my feelings mixed? Well, IMHO C. J. Date is the most important contemporary theorist on the Relational Model. If there is a single person in the world I would like to listen, this is Date. So what’s the bad part? I cannot join the seminar! During the same days, there is NT Conference event, the most important MS event in Slovenia. Of course, I will be there, as I will have a seminar and couple of presentations. What a rotten luck! (more…)
From a conversation with other MVPs, I came to a conclusion that most of people are confused with term “lookup table”. Many different opinions arose, like whether to have a single or multiple lookup tables, and whether this is a dimension, if RDBMS uses scan instead of seek to retrieve data from this table, and similar. Therefore, it seems it is time to explain what exactly a lookup table is.
No doubt data modeling is a process where you can add a lot of creativity. No doubt there is not a single solution for a business problem with relational model. However, does this mean that data modeling is more art than science? How much creativity is right? (more…)