Interval Queries in SQL Server Part 5

This is the fifth part of the solutions for interval queries in SQL Server. I know I wrote in the first one that there would be four solutions; however, I tested another one, and I think it is worth mentioning. However, if you don’t want to lose your time, please stop reading now; this blog post is showing a very inefficient solution, and you should take it as a warning what you should never do. For an introduction, please refer to the blog post Interval Queries in SQL Server Part 1. You can find the second part of the solutions in the blog post Interval Queries in SQL Server Part 2, the third part in the blog post Interval Queries in SQL Server Part 3, and the fourth part in the blog post Interval Queries in SQL Server Part 4 (http://blogs.solidq.com/dsarka/Post.aspx?ID=152&title=Interval+Queries+in+SQL+Server+Part+4). (more…)

Interval Queries in SQL Server – Wrap Up

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…)

Interval Queries in SQL Server Part 4

This is the fourth part of the solutions for interval queries in SQL Server. For an introduction, please refer to the blog post Interval Queries in SQL Server Part 1. You can find the second part of the solutions in the blog post Interval Queries in SQL Server Part 2, and the third part in the blog post Interval Queries in SQL Server Part 3. 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.

(more…)

Interval Queries in SQL Server Part 3

This is the third part of the solutions for interval queries in SQL Server. For an introduction, please refer to the blog post Interval Queries in SQL Server Part 1. You can find the second part of the solutions in the blog post Interval Queries in SQL Server Part 2. 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.

(more…)

Interval Queries in SQL Server Part 2

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.

(more…)

Interval Queries in SQL Server Part 1

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…)

Data Modeling Essentials Seminar at DevWeek 2013

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.

(more…)

C.J. Date in Ljubljana

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…)

What is a lookup table? Lookup tables explained.

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.

(more…)

Data Modeling and Creativity

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…)