Jeff's SQL Server Blog

Random Thoughts & Cartesian Products with Microsoft SQL Server
posts - 155, comments - 2679, trackbacks - 64

My Links



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.



Post Categories




October 2007 Blog Posts

Date Only and Time Only data types in SQL Server 2005 (without the CLR)

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

posted @ Wednesday, October 31, 2007 9:13 AM | Feedback (16) | Filed Under [ Techniques SQL Server 2005 DateTime Data ]

Taking a look at CROSS APPLY

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

posted @ Thursday, October 18, 2007 5:01 PM | Feedback (10) | Filed Under [ T-SQL Techniques SQL Server 2005 Joins/Relations ]

The Mailbag: Referencing Assemblies in Reporting Services; some SQL help

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

posted @ Thursday, October 18, 2007 12:19 PM | Feedback (0) | Filed Under [ Miscellaneous Report Writing GROUP BY Reporting Services ]

Some HTML / ASP.NET Thoughts ...

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

posted @ Wednesday, October 17, 2007 11:13 AM | Feedback (2) | Filed Under [ ASP.NET ]

Working with Time Spans and Durations in SQL Server

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

posted @ Monday, October 15, 2007 9:28 AM | Feedback (0) | Filed Under [ T-SQL Links DateTime Data ]

By The Way ... DISTINCT is not a function ...

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

posted @ Friday, October 12, 2007 10:45 AM | Feedback (3) | Filed Under [ T-SQL ]

Be Careful When Mixing INNER and OUTER Joins

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

posted @ Thursday, October 11, 2007 2:39 PM | Feedback (27) | Filed Under [ T-SQL Techniques Joins/Relations ]

Creating CSV strings in SQL: Should Concatenation and Formatting Be Done at the Database Layer?

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

posted @ Tuesday, October 09, 2007 3:02 PM | Feedback (19) | Filed Under [ T-SQL .NET (C# / VB) Efficiency Report Writing ASP.NET Joins/Relations ]

Powered by:
Powered By Subtext Powered By ASP.NET