Everyone seems to want to "pivot" or "cross tab" data, but knowing how to do the opposite is equally important. In fact, I would argue that the skill of "unpivoting" data is more useful and more important and more relevant to a SQL programmer, since pivoting results in denormalized data, while unpivoting can transform non-normalized data into a normalized result set.
Read more →
Let's say you have a very large DropDownList with lots of values and text. We need to maintain ViewState in this DropDownList so that we can retrieve the selected value on a post back.
Read more →
Microsoft Access has a pretty handy boolean property that you can set for any "text" column in your tables:
AllowZeroLength True means that empty strings ('') are allowed in the column, False means that they are not.
Read more →
Let's say you are struggling on a programming project. Your code is growing exponentially and becoming more convoluted by the day, and it is clearly out of control. You're getting run-time errors, compile-time errors, wrong output, no output, endless loops, your machine is overheating, and perhaps you are starting to feel like you might be a little over your head.
Read more →
I previously wrote that the .NET 2.0 DataTableReader class is really handy, but unfortunately there is no DataViewReader class. Thus, the only way to use the IDataReader interface with a sorted/filtered DataView was to first use the ToRows() method of the view to create a brand new DataTable, and then call CreateDataReader() on that new table.
Read more →
I recently helped a friend out who only had access to SQL Server Management Studio Express, and he needed to copy a database locally from his PC to his remote web hosting company.
Read more →
Here's something I was not aware of in .NET 2.0+ that I recently discovered. There is a new DataTableReader class that implements IDataReader so that you can loop through an in-memory DataTable using the same interface that you would to loop through a SqlDataReader.
Read more →
I got an email recently regarding one of my early blog posts from the olden days: Steve Kass wrote about your post:"there is no guarantee that WHERE <filter 1> OR <filter 2> will be optimized so that the filters are evaluated in the order typed".
Read more →
Here's a good question in the feedback from my post about using the T-SQL 2005 features to return the Top N per Group of a result set: Sani writes:
What about Top n Percent per Group?
Read more →
Ah, this is not an anti-identity rant, don't worry! Though, in a round-a-bout sort of way, it is yet another argument against always blindly using them – but not in the way you might expect.
Read more →
I have a new article up at SQLTeam: Implementing Table Inheritance with Sql Server It discusses the situation where you have multiple entities that are distinct, yet they have many attributes or relations in common.
Read more →
Because I feel pretty strongly about this and the entire focus of my blog is writing clear, clean and efficient SQL, I thought I'd repeat my response from a SQLTeam forum question here.
Read more →
I have to wonder: is it a sign of good writing when people interpret your words in multiple ways, or is it a sign of bad writing? I really don't know – I think it can go either way, I suppose it depends on what your intent is.
Read more →
Raise your hand if you've ever done this:
At a programming forum that you regularly visit, you see a post asking for help The post describes a situation that you may not intimately familiar with, or that you know has been covered elsewhere many times, and it is clear a quick Google search will find a good answer.
Read more →
Here's a very common situation that is very easy to optimize and simplify, submitted via the mailbag:
Nate writes: Hey, I have a read a bunch of your stuff on your blog and you seem tobe right on the money.
Read more →
Let's say you have a database that contains Companies, Products and Stores. Products and Stores are unique to each company – i.e., they are not "shared" across companies.
Read more →
As with any programming language, it is important in SQL to keep your code short, clear and concise. Here are two quick tips that I find are very helpful in obtaining this goal.
Read more →
Let's take a look at another one of those stupid, arbitrary SQL Server error messages that Bill Gates clearly only created because Micro$oft is evil and incompetent and they want to annoy us (and probably kill baby squirrels, too):
Read more →
I recently set up a web application using SQL Server Express for a client, and it was the first time I had done any "production" level work using the Express edition.
Read more →
In SQL, the general rule of thumb is that the number of rows returned from a SELECT will be zero if your criteria did not match any data. However, there is an important exception to this rule: it does not apply when asking for aggregate calculations such as SUM(), MIN() or MAX(), without any grouping.
Read more →