Tony Rogerson's ramblings on SQL Server

Introduction

Application programmers and Business Intelligent professionals are faced with having to format data - taylored into what the users want. We have two choices as to where we do this processing, keep it in the database using the facilities of the database engine, for instance T-SQL, standard SQL dialect, CLR, XML or whatever the product has to offer or we can bring the data out of the database and down into the front end application or middle tier and format the data there (keep the database for store and retreive only).

My Opinion

The IT industry is full of rules and best practices unfortunetly some of these rules and best practices aren't based on current technology or business problems, in fact some of the rules and best practices are based on techniques adopted in the 70's and 80's on mainframes or early client server architecture.

No product is just a database anymore, sure SQL Server stores and retrieves data but it also offers us a lot more, in fact its moving more towards being the middle and data tiers in the three tier architecture now that SQL Server can be a web service and the inclusion of CLR.

Data formatting, be it paging, value concatenation should always been done where it is most efficient to do it. Consider (and benchmark) where its most efficient to do this, would you really drag 1 million rows into the middle tier or client browser only to get page 2 of 20 rows? It doesn't make sense.

Relating this to a well known expert, --CELKO--, he states that you should NEVER do formatting in the database and it should always be done in the front end. Think this through, take value concatenation for instance, say you need to create a list of values for a given product category, for instance for a given person show the mailing lists they belong to. In the database this will be held in rows, so if a person belongs to 5 mailing lists there will be 5 rows, now, say the user requires the values to be normalised so they are displayed on just one line entry. We have two choices, drag the 5 rows down to the front end or middle tier and use a 4GL to process the data or we can use some of the extensions available in SQL Server to do this.

Example

create table mailing_list (
   individual_name nvarchar(100) not null,
   list_name nvarchar(10) not null
)

insert mailing_list ( individual_name, list_name ) values( 'tony r', 'List A' )
insert mailing_list ( individual_name, list_name ) values( 'tony r', 'List B' )
insert mailing_list ( individual_name, list_name ) values( 'tony r', 'List C' )
insert mailing_list ( individual_name, list_name ) values( 'joe r', 'List A' )
insert mailing_list ( individual_name, list_name ) values( 'joe r', 'List B' )
insert mailing_list ( individual_name, list_name ) values( 'alex r', 'List A' )

select distinct 
   individual_name,
   list = substring( 
      ( select ', ' + list_name as [text()]
         from mailing_list m2
         where m2.individual_name = m1.individual_name
         for xml path(''), elements )
      , 3, 100 )
from mailing_list m1

alex r      List A

joe r       List A, List B

tony r      List A, List B, List C

Now, just how easy was that! It only takes a few lines of SQL and you have also saved a lot of network traffic back out to the middle tier or front end.

So, my point is this: whatever you do - always think through what you are doing, don't just follow 'rules' blindly!

Quote from 'Row Numbering Unpredicable', Apr 11th, 2006

Original post by Chris Smith

I need to create a stored procedure that returns the row number (for
paging) AFTER the data has been sorted with an order by.  The source is
a view.  The code I have is:

SELECT rownum = IDENTITY(1,1,bigint), *
INTO #tmp
FROM viewName
ORDER BY CustomerName -- field name I'm ordering by

When I recieve the results back, the rownum column is not the same
order as the customername (it jumps half way to a high number?!?),
which means I can't page it based on rownum without jumping all over
the dataset.

Anyone got any ideas on how to solve that other than client side paging
(in ADO :-P)
This is SQL 2000 SP3 (pah!)