There are extensions to the GROUP BY clause available in TSQL allowing you to do some really cool stuff. This capability is often forgotten, because we tend to think of doing these kinds of functions in a Data Warehouse, instead of in traditional SQL statements. Those extensions are CUBE, ROLLUP, and GROUPING SETS. There is a good explanation of these […]

I received a tweet today pointing me to a really cool EBook written by David Klee and James Green. Together they wrote “The Gorilla Guide to Modern Storage Strategies for SQL Server. The EBook is only 78 pages long. In that limited space they do an amazing job defining and demonstrating the different technologies and processes that bring together a […]

How many times have you had to make software, or architecture decisions, based on the currently acceptable configuration of your servers and networks? What about being limited to the personal experience of key decision makers or influencers? For some clarification, let me share some situations I have experienced in the last few years? You can’t use NoSQL (specifically Mongo) because […]

Recently I was talking with a colleague about long running processes in IIS. This is considered a bad practice for a number of reasons. IIS is made to serve many different requests concurrently, often of short duration. If you have long running processes, you can dramatically impact yours, and potentially other applications performance. For example, IIS will be configured sometimes […]

Recently I picked up a project where I am integrating data into an SQLite database that will be used for an embedded controller. I was provided the database by another software engineer. It already has the table design, and the software is configured to use the schema as is. All I have to do is populate the tables from my […]

Recently I experienced something most of us run into at one time or another. It’s related to the DRY (don’t repeat yourself) principle. I was looking into using a custom tool designed in house, and applying it to a new internal application. The first thing we talked about was how to connect the two applications together. We had already done […]

In SQL Server you can’t nest transactions. The transactional syntax allows for you to create more than one transaction, and it appears that more than one transaction is running. In fact, the transaction count returned by the system property @@TRANCOUNT will increase with each transaction. However, the first transaction completion command closes all open transactions. Here is an example. SELECT […]

Indigestible is not a word commonly used with computer software. Perhaps it is time to add another definition to this adjective of the English language. As a database professional you will quickly see the value. adjective (of food) difficult or impossible to digest too complex or awkward to read or understand easily. “a turgid and indigestible book” I suggesting we […]

One reason an SQL database is chosen for data persistence is the ability to have ACID transactions (If you are not familiar with the acronym ACID you can do a Google search for more details). Not all applications require ACID capabilities, nor do all data engines provide ACID capabilities. There is a lot of overhead required to assure a transaction […]

I received some interesting feedback on my editorial, “Write Polite SQL”. . There were a couple questions asking why you might even use the concept in the first place? The questions were reasonable based on the example I provided. It was too simple, and other techniques, such as a filtered index, were better solutions (as suggested by A.J. Jim).Today I […]