Jeff's SQL Server Blog

Random Thoughts & Cartesian Products with Microsoft SQL Server
posts - 155, comments - 2679, trackbacks - 64

My Links



Welcome to my weblog. My name is Jeff Smith, I am software developer in Boston, MA and I was recently named a 2009 SQL Server MVP. Check in frequently for tips, tricks, commentary and ideas on SQL Server and .NET programming.



Post Categories




July 2007 Blog Posts

More on GROUP BY; Examining SUM(Distinct)

I've written a two part article on using SQL GROUP BY clauses over at read more...

posted @ Tuesday, July 31, 2007 12:44 PM | Feedback (6) | Filed Under [ T-SQL Links GROUP BY ]

SELECT * FROM TABLE -- except for these columns

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? ...

posted @ Thursday, July 26, 2007 3:36 PM | Feedback (25) | Filed Under [ Miscellaneous T-SQL ]

Becoming a Better Developer, plus a SQL Crime Drama!

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...

posted @ Thursday, July 26, 2007 10:36 AM | Feedback (2) | Filed Under [ Miscellaneous ]

Distinguishing data from code

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...

posted @ Tuesday, July 24, 2007 9:02 AM | Feedback (18) | Filed Under [ Miscellaneous Database Design ]

But *WHY* Must That Column Be Contained in an Aggregate Function or the GROUP BY clause?

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...

posted @ Friday, July 20, 2007 10:33 AM | Feedback (35) | Filed Under [ Miscellaneous GROUP BY ]

Using LINQ with SQL (link)

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.

posted @ Friday, July 13, 2007 4:27 PM | Feedback (1) | Filed Under [ ASP.NET Links ]

"Nested WHERE-IN" Anti-Pattern Follow-up; More on Derived Tables (sub-queries)

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...

posted @ Friday, July 13, 2007 9:33 AM | Feedback (3) | Filed Under [ T-SQL Techniques Joins/Relations ]

The "Nested WHERE-IN" SQL Anti-Pattern

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...

posted @ Thursday, July 12, 2007 12:50 PM | Feedback (19) | Filed Under [ T-SQL Techniques Joins/Relations ]

SQL 2005 PIVOT Operator (link)

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.

posted @ Thursday, July 12, 2007 9:24 AM | Feedback (2) | Filed Under [ CrossTabs / Pivoting Data SQL Server 2005 Links ]

Data Types -- The Easiest Part of Database Design

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...

posted @ Tuesday, July 03, 2007 10:13 AM | Feedback (9) | Filed Under [ Miscellaneous Database Design DateTime Data ]

Retrieving Identity Values When Inserting Multiple Rows

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..

posted @ Monday, July 02, 2007 12:31 PM | Feedback (9) | Filed Under [ T-SQL Database Design Imports/Exports ]

Powered by:
Powered By Subtext Powered By ASP.NET