To write a good and correct query it is very important to understand the logical processing of a query. It is sad that very few SQL programming manuals start with that. Here is a look at the insides of logical query processing. First, to start with a note: the logical processing of a query does […]
Loading images and any binary files (like Adobe PDF documents) to a database is not always the best option but sometimes needed. In SQL Server 2005 this process is simplified a lot with the BULK option of OPENROWSET. Here is an example of inserting image data into a VARBINARY(MAX) column (the same applies to loading […]
Very often there is the need to archive data on daily basis. Just had that question today and here is trimmed down solution based on a recent project. The task is to keep history data on daily basis of changed data only (that is only rows that have changed for the last day). The scenario […]
SQL Server 2005 added a great new feature: Common Table Expressions (CTE). And even better than that – recursive CTEs. That provides a new powerful tool to solve many SQL problems. One of the areas where recursive CTEs shine is the hierarchical data management. Here is another side of the recursive CTEs – utilizing them […]
One of the most common tasks when working with data is to select data for a specific date range or a date. There are two issues that arise: calculating the date range and trimming the time portion in order to select the full days. Below are a few techniques to show how this can be […]
Very often there is a need to pivot (cross-tab) normalized data for some reporting purposes. While this is best done with reporting tools (Excel is one example with powerful pivoting capabilities), sometimes it needs to be done on the database side. The discussion here is limited to static pivoting (that is when the values to […]
Every once in a while there is a need to do something like this: SELECT person_id, person_nameFROM MyUsersWHERE person_id IN (@search_list); And @search_list contains some form of a delimited list. However, this is not a supported syntax and will fail. Here is one solution to this problem: — Create the test tableCREATE TABLE MyUsers ( person_id […]
The long awaited SQL Server 2005 SP2 is released! This is important for a few reasons, not the least of which that Vista requires SP2 to run SQL Server. It is an all inclusive service pack, which means it includes everything from service pack 1. The installation requires 1.9 GB of disk space available to […]
While using SQL Server Compact Edition for mobile applications makes perfect sense, using it for regular desktop applications may not be the best choice. Here is a summary of pros and cons of using the Compact Edition with desktop applications. Pros: – Very small footprint (only 7-8 DLLs needed, no services installed, runs as in-process […]
Tree structures are often represented in nested set model or adjacency list model. In the nested set model each node has a left and right, where the root will always have a 1 in its left column and twice the number of nodes in its right column. On the other side the adjacency list model […]