-- clean up any messes left over from before: if OBJECT_ID('AllTeams') is not null drop view AllTeams go
if OBJECT_ID('Teams') is not null drop table Teams go
-- sample table: create table Teams ( id int primary key, City varchar(20), TeamName varchar(20) )
Read more →
Nothing earth-shattering here, I was just helping out a colleague with this so I thought I'd post up the example I gave him. -- sample table:create table People(Person varchar(1) primary key,City varchar(10),Age int)go-- with some sample data: insert into People select 'A','Boston',23 union all -- odd #select 'B','Boston',43 union allselect 'C','Boston',29 union allselect 'D','Chicago',15 union all -- single #select 'E','NY',12 union all -- even #select 'F','NY',55 union allselect 'G','NY',57 union allselect 'H','NY',61go-- here's our query, showing median age per city: select city,AVG(age) as MedianAgefrom (select City, Person, Age, ROW_NUMBER() over (partition by City order by Age ASC) as AgeRank,COUNT(*) over (partition by City) as CityCountfromPeople) xwherex.
Read more →
Always remember that the column names returned in a result set do not have to be the same as what you eventually output at your presentation layer. For example, suppose you have stored procedure that accepts a @CurrentYear parameter and returns a sales variance between the current year and the previous year for each customer.
Read more →
A common mistake beginners make when working with SQL is trying to format their output at the database layer, as opposed to simply doing this at the presentation layer (i.e., client application, reporting tool, web page, etc).
Read more →
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 →
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 →
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 this post, I showed a simple way to create simple but useful Date and Time user-defined data types in SQL Server 2000. Here's how to do it in SQL Server 2005, without the need for CLR types or anything fancy.
Read more →
Applying a Sub-Query, Joining a Derived Table … I think the easiest way to think of CROSS APPLY is that it is like doing a CROSS JOIN with a correlated sub-query instead of a derived table.
Read more →
I had previously written about the danger of Criteria on Outer Joins, but recently another situation popped up that occasionally causes confusion with OUTER JOINS that I thought I might address.
Read more →
Introduction Previously, I wrote about grouping transactions by month. Another common area of difficulty or confusion for SQL beginners is how to efficiently retrieve data just for a single month.
Read more →
Ah … primary keys … such a topic! When discussing what columns to define as a primary key in your data models, two large points always tend to surface:
Surrogate Keys versus Natural Keys Normalization These can be very complicated and sometimes polarizing things to debate.
Read more →
A quick follow up to the "Nested WHERE-IN" anti-pattern post from yesterday … If you didn't get a chance, be sure to read the comments from that post as well, there are some great points in there so far.
Read more →
There's a fascinating technique that I see many beginners use when writing SQL statements, and I call it the "Nested WHERE-IN" anti-pattern. It is, unfortunately, a common SQL technique used to avoid JOINS at all costs.
Read more →
Introduction SQL is a set-based language, and often we wish to pass sets of data as a parameter to a stored procedure. For example, you might wish to pass a set of Customers to an invoice generating stored procedure, or a set of employees which for you’d like to calculate a particular bonus.
Read more →
Introduction <o:p>There’s a handy little rule of thumb I use when developing my database models.It comes up often in situations like this:
If we are storing phone numbers in the database, should AreaCode be a “free-form” char(3) column?
Read more →
Let's open up the mailbag! In today's exciting episode, Nemo writes:
Hi, I am doing a project that my boss "requires" to be done in Access, even though we have MS SQL server 2005!
Read more →
Sometimes, it appears that a necessary solution to common SQL problems is to join a table to itself. While self-joins do indeed have their place, and can be very powerful and useful, often times there is a much easier and more efficient way to get the results you need when querying a single table.
Read more →
If you are using SQL 2005 and find User Defined Functions helpful, be sure to read my new article over at SQLTeam: Returning Complex Data from User-Defined Functions with CROSS APPLY
Read more →
There's a very interesting way of handling complicated, multi-column dynamic sorts over at john-sheenan.com. I am not sure about the efficiency of this approach, but in general dynamic sorting tends not to be very efficient anyway (unless you use dynamically-created SQL statements).
Read more →