Auxiliary Tables
Auxiliary tables (also referred as utility or helper tables) are a great tool for many needs in SQL. The good part is they are portable between systems, easy to create and normally do not take much storage. There are many benefits: faster SQL, simplified problem solutions, provide flexibility and options that are not easy to […]
Prevent Table Drop
Since there is no direct permission in SQL Server to drop a table, here are two techniques to implement that. The first one is based on creating a view on the table with option SCHEMABINDING. When the SCHEMABINDING option is used the table cannot be modified in a way that will affect the view definition, […]
Unpivoting Data
Unpivoting data is a very useful technique for normalizing denormalized tables. It refers to the process of transposing multiple columns across into rows in a single column. In other words, taking the data below: sales_year first_quarter second_quarter third_quarter fourth_quarter———- ————- ————– ————- ————–2006 211203.50 381594.95 439187.00 503155.802007 231205.10 […]
Indexes on Table Variables
It is a common misunderstanding when comparing temporary tables and table variables that one difference is that table variables cannot have indexes. While table variables are not directly materialized and you cannot execute CREATE INDEX on them, an index can be part of the definition of the table variable. Here is one example with two […]
Importing Excel Data into SQL Server
Recently I had to import a huge load of surveys gathered in Excel sheets to SQL Server for further processing and analysis. Don’t ask why they were not entered on the first place to a database via some interface… Importing an Excel sheet to SQL Server seems like a trivial task. Here is an example: […]
Use ABS to Find the Closest Match
The ABS function in SQL Server is a little bit overlooked and unutilized. It returns the absolute, positive value of the given numeric expression. One particular set of problems that ABS is very useful is when needing the find the closest match to a given value. That could be to find the closest ZIP code, […]