Links
Random links from the "Internet tubes" that you may enjoy -- free of charge!
Another great post SQL-related post over at TheDailyWTF regarding a, umm, "creative" use of "Stored Procedures":
http://thedailywtf.com/Articles/For-the-Ease-of-Maintenance.aspx
This is from March, so it's a little old, but I just stumbled upon it and found it a bit interesting. I never thought of programming this way before, but it does make sense:
Programming is like a dream
Most people know about cascading updates and deletes, but did you know there are two other foreign key constraint options you can use to maintain referential integrity?
Read all about them in my latest article over at SQLTeam.com.
These features, introduced with SQL Server 2005, haven't got a lot of publicity, but they can be very useful. I just used the SET NULL option recently for the first time (inspiring me to put together an article on it) and it works great.
Hello and welcome to the 98th edition of Log Buffer. My name is Jeff Smith and I will hosting this week's exciting episode. If, for some reason, you are not completely satisfied with this edition, simply write in and complain to Dave over at The Pythian Group and you will receive Log Buffer #99 absolutely free! Now that is a guarantee you can feel good about. OK, let's get to work.
I have only limited exposure to both PostgreSQL and MySQL, but I have often wondered why MySQL is so popular while...
I have a new article up at SQLTeam:
Implementing Table Inheritance with Sql Server
It discusses the situation where you have multiple entities that are distinct, yet they have many attributes or relations in common. There is an easy way to simplify your database design and your code if you use the concept of a "base table" for that common data, which is very similar to the concept of Inheritance in Object-Oriented programming.
I recently had to do this for a client that tracks Contributions to their foundation; there is a base set of data that all Contributions have, such as who donated,...
If you read this blog on a regular basis, you probably know that a frequently covered topic is dates and times and the use of the DateTime data type. Well, along those same lines, my latest article has just been published over at SQL Team: Working with Time Spans and Durations in SQL Server.
From the Article:
What is the best way to return the "duration" of an event in SQL, given the start and end datetime values? How can we add up these durations to return grand totals? What data types should be used to return this data to our clients?...
Speaking of dates and times, there's a nice post from Ravi. S. Maniam over at the msdn blogs regarding the new and exciting Enhancements in Date and Time Data Types for SQL Server 2008.
It will be very interesting to see how date and time usage changes once SQL Server 2008 becomes the most commonly used edition. Of course, since even SQL 2005 still doesn't seem to be as widely adopted as I would like, who knows when that will be!
I've written a two part article on using SQL GROUP BY clauses over at SQLTeam.com. 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.
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.
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.
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!
In my SQLBulkCopy article, I mentioned that you can quickly copy data from anything that implements IDataReader to a SQL Server table using SQLBulkCopy (new in .NET 2.0). In this SQLTeam forum post, Jesse Hersch (jezemine) tells us that SQLBulkCopy only actually uses 3 methods of the interface. This means that it is very easy to quickly create your own custom class which implements this interface to bulk copy pretty much any type of object collection or array to a SQL Server table.
Did you know that a new feature in SQL Server 2005 allows you to specify an OVER partition for aggregate functions in your SELECT statements?
read more...
Here's my response to a SQL Challenge, regarding how to find consecutive free time slots in a schedule. This can sometimes be tricky to solve in SQL, but using either of the two techniques shown here, it is actually pretty easy. (Updated to show 2 possible solutions)
read more...
My latest article has just been posted over at SQLTeam.com:
Use SqlBulkCopy to Quickly Load Data from your Client to SQL Server
.NET Framework 2.0 introduces a very handy new class in the System.Data.SqlClient namespace called SqlBulkCopy that makes it very easy and efficient to copy large amounts of data from your .NET applications to a SQL Server database. You can even use this class to write a short .NET application that can serve as a "middleman" to move data between database servers.
If you ever need to move large amounts of data to SQL Server from a .NET application, SQLBulkCopy...
Be sure to visit SQLTeam.com to check out my latest article, Custom Auto-Generated Sequences in SQL Server. It addresses a common question we see in the SQL Team forums.
I just discovered Joe Celko's weblog; it is really entertaining, at least the 5-6 posts I've read so far! It seems the "blog" is actually just a collection of questions and Celko's rather, umm, "honest" responses from various newsgroups and forums.
Today's article at WorseThanFailure.com is one that the SQL Server community might find enjoyable.
I may try to implement something similar in my next project .... or, maybe not!
Are there another other great designs like this that you've come across? Maybe even some that you designed yourself? Come on, you can admit it, let us know! I won't tell anyone. After all, no one ever reads the comments ...
see also:
Discussing VB and/or MS Access in a Programming Forum
Top 10 Things I Hate About SQL Server
SQLTeam Dating Advice !
...
Congratulations to our good friend Alex Papadimoulis over at the asp.net weblogs on his Microsoft MVP award. Well-earned!
Here's kind of a funny/scary thread over at the DailyWTF:
read more...