A occasional question seen in the forums, which was just recently asked today, is:
"I know I can use SELECT * FROM tableto get all of the columns from a table, but is there a way to write SELECT * minus columnXFROM Tableto get all of the columns except for certain ones?
Read more →
Sorry for the delay, Denis; thinking of ways to improve our developer skills is a great idea, thank you for including me. Here are my thoughts on the next 6 months.
Read more →
I recently submitted my Data in tables versus data in code post from a ways back to reddit, and I was surprised that there ended up being quite a bit of discussion about it.
Read more →
Server: Msg 8120, Level 16, State 1, Line 1Column 'xyz' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Read more →
There's a great series of posts over at Scott Guthrie's Blog covering LINQ, a new feature in the upcoming version of Visual Studio ("Orcas"). Check it out; I have not had a chance to play around with it yet, but it certainly looks very interesting.
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 →
There's two interesting posts over at the MSDN blogs from Craig Freedman about the new PIVOT operator in SQL 2005. First, he gives a nice overview of the operator and how to use it, and then he follows it up with a performance analysis as well.
Read more →
Database design can be very complicated, and it truly is an art as opposed to a science; sometimes there are multiple correct ways to model the same data with pros and cons to each.
Read more →
Suppose you have the following tables:
create table Customers (CustomerID int identity primary key, CustomerName varchar(100) not null)create table AddressTypes (AddressType varchar(10) primary key)create table CustomerAddress (CustomerID int references Customers(CustomerID),AddressType varchar(10) references AddressTypes(AddressType),Street varchar(100),City varchar(100),State varchar(2),ZIP varchar(20),primary key (CustomerID, AddressType)) This is a simple schema for which a Customer can have multiple addresses, one per AddressType.
Read more →
A couple of quick blog updates for those that are interested: A Comment Milestone! Recently, I realized that I have received 1,000 comments since this little blog was started way back in September 2003.
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 →
I often talk about "database layer" versus "presentation layer", but even the within just the database layer it is important to understand that how the data is physically stored does not always have to correlate with how the database returns results.
Read more →
That's right boys and girls, it's what you've been waiting for all weekend: Another edition of the mailbag! Damian writes:
Hi I have a tricky SQL question that I have been trawling the net and workmates to find an answer.
Read more →
You are a very important, talented, enterprise-level programmer! You write and maintain millions of lines of code, compiling your applications takes several hours, and your databases contain hundreds of tables with millions of rows.
Read more →