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




February 2008 Blog Posts

Simple DataViewReader class that implements IDataReader for a DataView

I previously wrote that the .NET 2.0 DataTableReader class is really handy, but unfortunately there is no DataViewReader class.   Thus, the only way to use the IDataReader interface with a sorted/filtered DataView was to first use the ToRows() method of the view to create a brand new DataTable, and then call CreateDataReader() on that new table.   This is not the most efficient process when all you want to do is enumerate a DataView. So, here's a solution: a simple, efficient, mostly-complete DataViewReader class.   Just create a new instance of it passing in the source DataView and off you go.  This will...

posted @ Thursday, February 28, 2008 10:34 AM | Feedback (4) | Filed Under [ Code Library - C# / VB.NET ]

Exporting data to a remote server with SQL Express

I recently helped a friend out who only had access to SQL Server Management Studio Express, and he needed to copy a database locally from his PC to his remote web hosting company.  Normally, the process is a simple backup/restore, but his hosting company does not allow restoring databases.  Luckily, however, the company does allow direct access to his hosted database via client tools such as SSMS.  Unluckily, SSMS Express does not include any tools that allow you to export data to a remote server. So, here's what we did:  First, we scripted out the entire database, including all tables, indexes,...

posted @ Wednesday, February 27, 2008 3:04 PM | Feedback (10) | Filed Under [ Code Library - C# / VB.NET Imports/Exports ]

DataTable, DataView and CreateDataReader

Here's something I was not aware of in .NET 2.0+ that I recently discovered. There is a new DataTableReader class that implements IDataReader so that you can loop through an in-memory DataTable using the same interface that you would to loop through a SqlDataReader.  You can create this object by using the CreateDataReader() method of a DataTable.  This is great because now you can create a generic ReadFromDatabase() method for your classes that accepts an IDataReader parameter and you can create those objects from pretty much any data source, including DataTables, which wasn't as easy to do previously. There is one catch:...

posted @ Friday, February 22, 2008 12:30 PM | Feedback (1) | Filed Under [ .NET (C# / VB) ]

Does SQL Server Short-Circuit?

I got an email recently regarding one of my early blog posts from the olden days: Steve Kass wrote about your post:"there is no guarantee that WHERE <filter 1> OR <filter 2> will be optimized so that the filters are evaluated in the order typed". I am not certain that optimization changes the priority of the expressions, but I do...

posted @ Friday, February 22, 2008 11:04 AM | Feedback (8) | Filed Under [ T-SQL Efficiency ]

Top N Percent per Group

Here's a good question in the feedback from my post about using the T-SQL 2005 features to return the Top N per Group of a result set: Sani writes: What about Top n Percent per Group??? I would greatly appreciate an input on that as well. That's a good question, and also easily solvable.  One way to do this that I thought of was by using a combination of rank() and a count(*) partitioned aggregate function, which is also a new SQL Server 2005 feature. Simply calculate the rank() of each row in the group, and also the count(*) of all rows in the...

posted @ Thursday, February 21, 2008 1:05 PM | Feedback (6) | Filed Under [ SQL Server 2005 ]

An INT primary key .... yet not an Identity?

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

posted @ Thursday, February 21, 2008 12:08 PM | Feedback (12) | Filed Under [ Miscellaneous Database Design ]

Implementing Table Inheritance with SQL Server

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

posted @ Thursday, February 21, 2008 11:05 AM | Feedback (6) | Filed Under [ SQL Server 2005 Database Design Links ]


Because I feel pretty strongly about this and the entire focus of my blog is writing clear, clean and efficient SQL, I thought I'd repeat my response from a SQLTeam forum question here. smithje asks this, regarding OUTER JOINS: Left/Right, does it matter. Is one better than the other? A few years ago consultant on a project for our company advised me to always write my queries to use Left joins. He had worked on the project to convert the original database application to MS SQL when Microsoft took it over. He claimed the design of the query engine handled Left joins...

posted @ Wednesday, February 13, 2008 11:22 AM | Feedback (10) | Filed Under [ Efficiency Joins/Relations ]

A Follow-Up on Programming Forums ....

I have to wonder: is it a sign of good writing when people interpret your words in multiple ways, or is it a sign of bad writing? I really don't know -- I think it can go either way, I suppose it depends on what your intent is.  If you want to express a clear, concise thought or view point, and people come away with different opinions of just what you are writing, then perhaps in that case it is not very well done.  But, if you just intend to throw some thoughts and ideas and observations out there for general...

posted @ Wednesday, February 06, 2008 9:27 AM | Feedback (5) |

Powered by:
Powered By Subtext Powered By ASP.NET