Jeff's SQL Server Blog

Random Thoughts & Cartesian Products with Microsoft SQL Server
posts - 157, comments - 2686, trackbacks - 64

My Links

Advertisement

News

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.


Subscribe





Archives

Post Categories

Programming

Sports

SQL

June 2007 Blog Posts

Cross Join Updates!

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.  Has it really been that long!?  Some of the comments are trackbacks, but overall, it's pretty amazing to think about.  Only about 30% of them are people calling me a moron, which is an excellent ratio and well above what I expected.  In all seriousness, though, I love getting feedback, and it is a lot of fun to debate techniques and ideas, so please be sure to leave...

posted @ Tuesday, June 26, 2007 11:43 AM | Feedback (0) | Filed Under [ Miscellaneous ]

Passing an Array or Table Parameter to a Stored Procedure

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 for which you’d like to calculate a particular bonus. SQL Server already has everything you need to do this, and you don't need CSV or XML strings. read more...

posted @ Tuesday, June 26, 2007 9:36 AM | Feedback (29) | Filed Under [ T-SQL Techniques Database Design ]

SQL Data Modeling: Entities versus Attributes

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? Or should it be a foreign key reference to an AreaCodes table? read more...

posted @ Tuesday, June 19, 2007 11:47 AM | Feedback (3) | Filed Under [ Techniques Database Design DateTime Data ]

What I learned about SQL from using Access

Microsoft Access / JET SQL doesn't support cursors; the only way to process rows one by one is using VBA and opening a Recordset. This means that you're stuck using set-based "queries". So ... is this a good thing or a bad thing? A very, very good thing -- in fact, it's what makes Access a great tool for learning SQL! read more...

posted @ Wednesday, June 13, 2007 1:12 PM | Feedback (13) | Filed Under [ Miscellaneous Techniques MS Access ]

Using GROUP BY to avoid self-joins

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

posted @ Tuesday, June 12, 2007 11:35 AM | Feedback (5) | Filed Under [ T-SQL Techniques Efficiency Joins/Relations GROUP BY ]

SQL Server 2005: CROSS APPLY

SQL Server User-Defined Functions (UDFs) can return either a single value or virtual tables. However, sometimes we might like for a User-Defined Function to simply return more than 1 piece of information, but an entire table is more than what we need.

posted @ Tuesday, June 12, 2007 8:52 AM | Feedback (3) | Filed Under [ Techniques SQL Server 2005 Links ]

Slightly more dynamic ORDER BY in SQL Server 2005

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).  Overall, it is a very clever use of RANK() and definitely worth a look. Thanks for the great idea, John!

posted @ Tuesday, June 05, 2007 10:24 AM | Feedback (2) | Filed Under [ Techniques Links ]

Don't Let Output Dictate your Database Design

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

posted @ Tuesday, June 05, 2007 8:55 AM | Feedback (8) | Filed Under [ Database Design ]

Powered by:
Powered By Subtext Powered By ASP.NET