October 2007 Blog Posts
In this post, I showed a simple way to create simple but useful Date and Time user-defined data types in SQL Server 2000. Here's how to do it in SQL Server 2005, without the need for CLR types or anything fancy.
First, we must create two user defined data types:
create type Date from dateTime
create type Time from dateTime
So, internally (and externally to our clients), these types are really just DateTime. But, we will apply some rules to these types so that the Date data type will always be constrained to a time exactly at midnight, and the the Time data type...
Applying a Sub-Query, Joining a Derived Table ...
I think the easiest way to think of CROSS APPLY is that it is like doing a CROSS JOIN with a correlated sub-query instead of a derived table. Let's see if I can explain that .... A derived table is "self-contained", in that all tables and columns in the parent SELECT are not accessible (though variables and parameters can be referenced). For example, consider:
select A.*, b.X
from A
cross join (select B.X from B where B.Val=A.Val) b
That is not legal because A.Val is out of scope within the derived table; this is because the derived...
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...
On a particular real estate website, we have a "Property Summary" section that contains each property's name, the address, a phone number (if it exists), and a contact email address (if it exists).
The information is data bound to properties in the page's code-behind, and the ASP.NET code basically looks like this:
<div>
<%# Property Name%>
<%# AddressLine1 %>
<%# AddressLine2 %>
<%# AddressLine3 %>
<%# CityStateZIP %>
<%# Phone %>
<%# Email %>
</div>
Because lots of these elements are optional, all of these properties have all been written to work like this in the code-behind:
Protected Readonly Property AddressLine1 as String
Get
If...
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?...
Have you ever seen (or written) code like this:
select distinct(employeeID), salary
from salaryhist
That compiles and executes without returning any errors. I've seen that attempted many times over the years, and of course people think DISTINCT is "broken" and "not working" because they see multiple rows for each employeeID. "But I asked for only distinct employeeIDs!" they say.
Well, the DISTINCT has nothing to do with the EmployeeID column; it is not a function that accepts arguments! It is just a tag that you can put after the word SELECT to indicate that you want only distinct combinations of all columns in the...
Be careful when mixing OUTER and INNER JOINS together in a single SELECT statement. The end result doesn't always seem to "work", and it can be tricky to understand exactly why and how to fix it without incurring additional unintended side effects. Here's an example that demonstrates some of the issues. read more...
A question I see very often in the SQLTeam forums is how to return data in a summarized form by concatenating a list of values into a single CSV column. This can be done fairly easily in T-SQL, but as the formatting and concatenation requirements becomes more elaborate, be sure to ask yourself: Am I forcing presentation code into the database layer? read more...