Question: How do you create a DateTime in a specific format in SQL Server? Answer: You don't. You can't. The only way to translate a DateTime into a specific format is to convert it to a VARCHAR or other "string" data type.
Read more →
It's been a while, so let's open up the old mailbag! Jeremy writes:
Can you spare a few minutes to show me a possible way to restrict a column in a table using a check constraint to only allow characters in a given range of the ASCII table?
Read more →
Sometimes, when writing SELECTs, we come across situations in which we we need to write a join containing a condition of some sort, so that the join processed will vary for each row depending on the data.
Read more →
Lots of questions come up in the SQL Team forums about conversions between Access and T-SQL and some of the differences between the two SQL dialects. Here's a few handy things to help you out with converting your projects.
Read more →
I previously wrote about a few of the new features in SQL 2005 and how they can be used to solve some old "classic" SQL problems very easily, and I thought I'd briefly discuss a few more.
Read more →
The RANK and PARTITION features in 2005 are simply amazing. They make so many "classic" SQL problems very easy to solve. For example, consider the following table:
create table Batting(Player varchar(10), Year int, Team varchar(10), HomeRuns int, primary key(Player,Year))insert into Battingselect 'A',2001,'Red Sox',13 union allselect 'A',2002,'Red Sox',23 union allselect 'A',2003,'Red Sox',19 union allselect 'A',2004,'Red Sox',14 union allselect 'A',2005,'Red Sox',11 union allselect 'B',2001,'Yankees',42 union allselect 'B',2002,'Yankees',39 union allselect 'B',2003,'Yankees',42 union allselect 'B',2004,'Yankees',29 union allselect 'C',2002,'Yankees',2 union allselect 'C',2003,'Yankees',3 union allselect 'C',2004,'Red Sox',6 union allselect 'C',2005,'Red Sox',9 Suppose we would like to find out which year each player hit their most home runs, and which team they played for.
Read more →
I thought I'd take a few minutes to discuss a general situation we see quite often in the programming world, using a simple T-SQL example. Let's say we have created a stored procedure that returns Customers from a database which allows you to optionally specify a particular Region to filter by.
Read more →
I posted this one a long time ago and needed to use it today, so I thought I'd post it up here as well in case anyone finds it useful. This simply attempts to capitalize the first letter of each word for the string passed in.
Read more →
Part I: Standard Date and Time Functions I've posted some variations of these before, but here they all are in 1 easy package: The essential date and time functions that every SQL Server database should have to ensure that you can easily manipulate dates and times without the need for any formatting considerations at all.
Read more →
(3/25/2007 update: Fixed the incorrect parameter name the last two examples)
Parameters without Stored Procedures? Let's assume that for some reason you are not using Stored Procedures. While I can respect your choice in that regard, that doesn't mean that you cannot still use parameters when constructing your SQL statements at the client.
Read more →
A common difficulty beginning SQL programmers encounter is joining two or more transactional tables all in one SELECT statement. Missing data, duplicates, time-out errors, and other unexpected results often arise from trying to directly write JOINS between two transaction tables.
Read more →
One of the things that troubles me most about SQL Injection is that is seems it is still very misunderstood.
Is the following psuedo-code vulnerable to SQL Injection?
String SearchTerm = {some user input here, unvalidated and unscrubbed -- uh oh !
Read more →
There's been quite a few posts out there with SQL implementations of Suduko puzzle solvers:
This one is a T-SQL solver, but it really doesn't do any set-based operations and doesn't really make use of SQL that much.
Read more →
Most of these are really basic. Some are my take on established ideas and standards, and may be controversial. Agree or disagree? All feedback is welcome! (well ... mostly the "I agree, you're a genius" feedback is welcome .
Read more →
Here’s my advice of the day: Try your best to store all of the data relating to your database in the tables.
Sounds pretty obvious, I know, but you might be surprised just how much data in your application is not in your tables, where it belongs, but rather nestled away in your code.
Read more →
One aspect of the versatile SELECT statement that seems to confuse many people is the GROUP BY clause. It is very important to group your rows in the proper place. Always push GROUP BY aggregations as far into your nested SELECT statements as possible – if you have a situation in which you are grouping by long lists of columns that are not part of primary keys, you are probably have not structured your query correctly.
Read more →
Many SQL books and tutorials recommend that you “avoid cross joins” or “beware of Cartesian products” when writing your SELECT statements, which occur when you don't express joins between your tables.
Read more →
As promised in my last post, here is some performance testing to help you determine the performance benefits (if any) of performing your crosstabs at the presentation or code layer, as opposed to forcing SQL Server to do this.
Read more →
In my last post, I spoke briefly about how I felt that in general crosstabbing data is something that a presentation layer should do and not the database. Consider the result of a crosstab operation -- the columns returned will vary depending on the data.
Read more →
First off, before going any further make sure you have read the hall of fame SQLTeam article by Rob Volk on generating crosstab results using a flexible, dynamic stored procedure that has been viewed over 100,000 times!
Read more →