Miscellaneous
Here are those random thoughts I warned you about. Beware!
I apologize for not posting any new content in quite some time, but now I am back and will soon start posting on a (hopefully!) regular basis once again.
The reason for my hiatus was a pretty good one, though: I recently became a father with the birth of my son Benjamin on October 2, 2008! He is doing great and already knows that he should always do his formatting at the client and never within the database. He's a natural!
As if that wasn't enough, I also found out recently I have been named as a 2009 SQL Server MVP! Now...
OK, boys and girls, it's time for the mailbag! There's lots of stuff to cover, so let's get to it!
---
Greg E writes:
Hello Jeff,
I just found your blog and wanted to know if you could point me in the right direction or possibly toss me a solution.
I am looking at a badly formed telelphone number column in a MS SQL Server db. Entries contain '(555) 555-1212' or '555.555.1212, etc. Do you know how I would go about stripping out unwanted characters from the telephone number?
Thanks for the brain cycles.
Greg -- A simple UDF should do the trick for you. For example,...
Introduction
I have been writing my little blog here for some time now, and my favorite part of doing this is of course the feedback. It's always great to hear from the readers, to have mistakes corrected, to debate various topics and techniques, and to learn a lot about SQL and the various topics I discuss here.
At this point, I have received over 1,700 comments over the years, and while all of them are truly appreciated, I have noticed that unfortunately many of the, uh, less helpful comments do seem to consistently fall neatly into various categories.
Let's take a...
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...
Welcome!
The reason you were directed here is because you need assistance, and I am here to help. I am not, however, here to provide you with any answers! You see, it looks like the assistance you need is not finding an answer; it is rather that you need assistance finding a question.
As you know, there are all kinds of questions. Questions that test memory recall. Questions that test logic skills. Brain-teasers and mathematical questions and so on. But there is one requirement that all good questions must have in common before they can be answered:
A proper question MUST provide ALL...
Let's say you are struggling on a programming project. Your code is growing exponentially and becoming more convoluted by the day, and it is clearly out of control. You're getting run-time errors, compile-time errors, wrong output, no output, endless loops, your machine is overheating, and perhaps you are starting to feel like you might be a little over your head. Maybe this is the first time you are using a particular programming tool or language, or it's the first time you've worked with a particular database, or maybe it's your first complicated project overall in any technology and you're getting...
Ah, this is not an anti-identity rant, don't worry!
Though, in a round-a-bout sort of way, it is yet another argument against always blindly using them -- but not in the way you might expect.
There is a simple rule I'd like to propose, let me know what you think:
"If your client code, SQL code, or configuration files reference the primary key column of a table to determine any application logic, that primary key column should not be an identity."
Now, I am not saying that primary key column can't be a meaningless integer. I am just saying it should not be an...
Raise your hand if you've ever done this:
At a programming forum that you regularly visit, you see a post asking for help
The post describes a situation that you may not intimately familiar with, or that you know has been covered elsewhere many times, and it is clear a quick Google search will find a good answer.
So, you think of some good key words, perform a few quick searches, examine the results, and determine which of your findings is a good...
As David Letterman would say, wake the kids, call the neighbors, it's time for The Mailbag! Just some quickies today.
Christopher writes:
Greetings Jeff,
First and foremost, great job with all of the blogs. I have a questions
that I cannot seem to get a straight answer for. I am working with SQL
Server Reporting Services (SSRS) and have the need to create VB
functions to customize the reports generated. For example, a setter/getter to
display information that would not be readily available from the
query. SSRS allows this type of custom Visual Basic code to reside in the
report itself, but since most of my code is across...
Every now and then I see T-SQL code written like this:
select somecolumn as 'columnname'
from sometable
Notice that 'columnname', despite its appearance, is not a string literal or a string expression, it is the alias that we are assigning to the column somecolumn. Putting the name of an alias in single quotes in T-SQL is completely valid syntax, and it will work fine, but I feel that it is a really bad practice. It makes the distinction between string literals and object names very blurry, and it can lead to confusion when examining and maintaining your code.
For example, to me this is...
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...
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...
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...
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...
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. You clearly do not have time to write silly test applications!
read more...
A Google search for the phrase sql "case statement" returns 127,000 results. Meanwhile, if we do a search for the phrase sql "case expression" we get back only 43,900 results. What does this mean? Most people don't understand what SQL's CASE feature really is, how to use it, or how it works!
read more...
Studies have shown that the "F1" key is the least commonly pressed key on today's keyboards! Ok, well maybe not actual studies, but from my own experience, I am convinced that on many keyboards here around the world the F1 key still has that shiny "new key" look and smell because it's never been used.
read more
Let's go to the mailbag!
In response to this post, Chris writes:
read more...
I've been playing around with a handy tool for creating Word and Excel files called OfficeWriter that's pretty impressive.
read more...
In most industries, beginners work on simple, short projects, using only the most basic concepts that they have learned. The experts in those industries, meanwhile, work on things that are very large, complicated, difficult to manage, and require lots of time, energy and resources.
read more...
Just a quick post of some benchmarking code for our good friend Guayo at TheDailyWTF, in regards to this discussion.
I just wanted to take a minute to highlight this thread in the sqlteam forums.
Handy reminder: The PARSENAME() function can be useful for parsing small strings. It returns parts 1-4 (working right to left) of a string, with each part delimited by periods.
read more...
This also is my first ever blog. Very nice!
I am definitely looking forward to the content on this page, we've got some great people here and the posts should be informative and I'm sure quite entertaining as well!