July 2007 Blog Posts
I've written a two part article on using SQL GROUP BY clauses over at SQLTeam.com. read more...
A occasional question seen in the forums, which was just recently asked today, is:
"I know I can use
SELECT *
FROM table
to get all of the columns from a table, but is there a way to write
SELECT * minus columnX
FROM Table
to get all of the columns except for certain ones?"
Now, my goal isn't to debate whether not "SELECT *" is bad or good or should be used or not. The fact of the matter is, people use it all the time because it is quicker and shorter than typing out all of the column names. People are lazy, right? ...
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.
More design, less code. I'm going to try to focus a little more on user interfaces, graphics, layouts, and so on. I tend to write boring, plain vanilla corporate-style applications that frankly aren't much fun. Sure, they work great (usually!) and the layout is clean and easy to use (hopefully!), but my goodness -- it is boring. I plan on focusing a little more on the aesthetic design of...
What is data, and what is code? How do we define the difference, and decide what goes where? It is great to say "keep data out of your code", but what if that data is integral to the application itself? Isn't it therefore code, and not data? read more...
Column 'xyz' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Arggh!! There it is, yet again .. that annoying error message. Why is SQL so picky about this? What's the deal!? 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. The articles are very well done and explain the concept very clearly with lots of examples.
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. A good read, check it out.
I see it time and time again in forums -- "dates" that don't sort properly, "numbers" that don't add correctly, "boolean" data with 10 different values, and so on ... Since we are rarely provided any DDL to review, it often takes many posts going back and forth until we finally realize: "wait ... you aren't using a datetime data type to store these dates?!" read more...
You're INSERTing multiple rows from an un-normalized import table. Each row is assigned an identity primary key. You know you can use scope_identity() to retrieve one identity at a time, but how do you retrieve a whole set of them? Are cursors the only answer? As usual, it depends on having logical specifications and a good design. read more..