Crystal Reports Vs SSRS

Crystal Decisions vs. SSRS

Below are my observations on some of the differences between CR/CE and SSRS. These
observations are based on the use of these software packages for ad hoc report design and
web delivery of the reports. I don’t do any standalone application development and can’t
speak to their capabilities in these areas. This list is by no means complete and some
things are completely a matter of opinion. I hope this can help you in your decision.

Installation
Both CR and CE are significantly easier to install. No documentation on how to
configure SSRS. This could be because it is a trial version of a new product.
 
Report Designers
1. CR is significantly more user friendly for non programmers.
2. SSRS uses visual studio .NET. If you are intimately familiar with Visual Studio
IDE this is not an issue. For those of us unfamiliar with it requires a learning
curve.
3. CR has easy to use running totals with a simple interface to design. SSRS I am
sure can do this using a VB functions but not as easily. There are running totals
but I have not used them so I do not know how easy to use or if they are as
flexible as CR running totals.
4. SSRS has dynamic pick lists that can be run off your report data or even an
independent table. Multiple parameters can have picklists that are determined by
earlier selections. This is easy to implement and dynamic at run time unlike CE
which saves the picklists to the published file. This is a very big convenience.
5. SSRS allows you to build drill down cross tabs while CR does not. The cross tabs
however do not export to excel as pivot tables that allow drill down. This can be
accomplished by exporting to OWC enabled HTML.
6. When you make changes even just formatting changes the data must be refreshed.
This can slow down development if you have several changes to make. After you
change the layout you can’t preview it using saved data.
7. It is easier to repeat column headings on new pages in SSRS.
8. SSRS has a tabular format option which allows you to arrange items in columns.
This makes it easier to layout reports and improves export to excel functionality.
9. CR handles calculations with summaries a little easier. Particularly if you want
percent of total summaries.
10. SSRS produces multiple group summaries much more easily. Simple cut and
paste.
11. SSRS reports are saved in an XML format. I have not worked at it but it should
be easy to build a documentation database without purchasing third party
software.
12. SSRS Page headers cannot contain Data fields.
13. In SSRS tables if you try to put data in the table heading so it repeats on every
page the data does not change from page to page as the data in the table changes.
14. SSRS handles SQL server stored procedures a little better than CR. I have had
problems with reports that run complicated stored procedures particularly those
that store intermediate data in tables.
15. Drill through reports as well as subreports. Drill through is similar to on demand
sub reports except there is no limit to the levels. Each report can link to another
report.
Designer Conclusion
If you are developing ad hoc reports that don’t require wide or repeated distribution it is
difficult to top CR. It is easy to use and very powerful. SSRS does have a few
advantages but most of those are in deployment the reporting services web pages.

Deployment Comparisons
1. CE has an easy to somewhat customize interface. End User can set colors, and a
couple of other settings like how many reports per page, list view vs. thumbnails.
2. Deploying reports from VS is much easier than publishing them in CE.
a. CE gives every report a cryptic name when it is published. SSRS uses a
standard file structure naming convention.
b. If you made changes to several reports and want to republish them with
the publisher the reports must be deleted then republished. All scheduling
and shortcuts for these reports must be rebuilt. The other option is to save
them into the enterprise folders. This eliminates database passwords and
settings for parameters. This is a huge pain when your users tend to run
reports on demand. You also must be careful to save the report to the
folder the master copy is kept to prevent versioning problems. SSRS you
just select deploy and it overwrites the old file with the new one
preserving all scheduling info. A huge improvement.
3. Scheduling reports in SSRS is more complicated but more powerful.
a. In CE all scheduled reports get saved in the History file. SSRS allows you
to set whether or not an execution gets saved and also lets the user save a
report hey just ran to the History file.
b. Changing the schedule is much easier in SSRS. You select the
subscription and edit the settings. CE requires a new schedule and then
you must delete the old one. This is huge if your distribution lists change
regularly.
4. SSRS has data driven subscriptions. CE does not.
a. This is a huge deal. If you have a set of reports that all go to the same
users you can just create a table with the distribution list and it will send to
everyone on the list. Add people, Remove people it is all done in the table
you created.
b. You can run the report repeatedly with different parameters for each user
all driven by the table and just one subscription.
c. All the settings for the subscription can be set in this table even export
format so different users can get the same report in different formats. A
huge improvement over CE.
5. SSRS has linked reports. If you have a report that runs with a small set of varying
parameters you can create linked reports. They look like new reports but just
have default parameters set differently. Here is an example. We have a report
that can be run for the last workday, last work week, or last work month. In SSRS
I can deploy the report than create three linked reports one for the daily, one for
the weekly, and one for the monthly. When the end users want to run the report
they don’t need to bother with entering parameters, they can just run the selected
report. (it automatically selects the dates based on which type of report you
select.)
6. SSRS can use cached reports. If you use a non production database for reporting
that gets updated once a day you can set reports to cache data for the day. Then
when someone runs the report it saves the data on the report server db. If
someone comes back later and runs the report again with the same parameters it
just pulls the saved data saving on system resources. You can also then set the
reports to run during down times and cache data to further save on system
resources. Much easier than scheduling and searching history of scheduled
reports.
7. I don’t know about other peoples experience but I have had large problems with
scheduled reports in CE. I can’t compare reliability yet but so far no problems
with SSRS.
8. CE allows you to use date pickers which are not available in SSRS

Differences between SSRS and Crystal Reports:

Ease of hosting reports: - Using the URL technology in RS we can host reports more easily than in crystal report where we need to make a UI for the same.

Supporting platforms: - Crystal can run on windows, IBM and sun while RS (reporting services) can run only on windows environment.

Client tools: - In reporting services we have Business intelligence ( BI ) and development studio while in crystal its Report designer.

Caching: - This achieved in crystal by using cache server while in reporting services it’s stored as snapshots in Reportserver database.

Export formats:- In crystal we have HTML,PDF,Excel,XML,Word , PDF , RTF , CSV, text files while in SQL Server 2005 we have all the formats above it also gives capability to extend custom reporting formats.

Data sources:- Crystal support more data sources while RS only supports Microsoft and oracle data sources. Crystal supports ADO, COM, Database excel Access, Exchange, NT, Xbase, JDBC, File system and Paradox. RS supports only SQL Server, Oracle, ODBC, OLEDB and you can also extend additional data sources which does not exists in crystal reports.

Version issues: - One of the main issues faced in crystal is it have different versions which makes it difficult to use, while RS comes with SQL Server minimizing the version issue.

Web server support: - Crystal can run on IIS 5/6, Apache, lotus etc while RS works only with IIS 5.0 and above.

SQL SERVER – Fix : Error 1205 : Transaction (Process ID) was deadlocked on resources with another process and has been chosen as the deadlock victim. Rerun the transaction

Deadlock occurs when two users have locks on separate objects and each user wants a lock on the other’s object. When this happens, SQL Server ends the deadlock by automatically choosing one and aborting the process, allowing the other process to continue. The aborted transaction is rolled back and an error message is sent to the user of the aborted process. Generally, the transaction that requires the least amount of overhead to rollback is the transaction that is aborted.

Fix/Workaround/Solution:
Deadlock priority can be set by user. In other words, user can choose which process should stop to allow other process to continue. SQL Server automatically chooses the process to terminate which is running completes the circular chain of locks. Sometime, it chooses the process which is running the for shorter period then other process.

To reduce the chance of a deadlock:

• Minimize the size of transaction and transaction times.
• Always access server objects in the same order each time in application.
• Avoid cursors, while loops, or process which requires user input while it is running.
• Reduce lock time in application.
• Use query hints to prevent locking if possible (NoLock, RowLock)
• Select deadlock victim by using SET DEADLOCK_PRIORITY.

SQL SERVER 2005 has new priority HIGH as well as numeric-priority.

SQL SERVER 2005 Syntax
SET DEADLOCK_PRIORITY { LOW | NORMAL | HIGH | <numeric-priority> | @deadlock_var | @deadlock_intvar }
<numeric-priority> ::= { -10 | -9 | -8 | … | 0 | … | 8 | 9 | 10 }
Example:
The following example sets the deadlock priority to NORMAL.
SET DEADLOCK_PRIORITY NORMAL;
GO

SQL SERVER – Trace Flags – DBCC TRACEON 

Trace flags are valuable tools as they allow DBA to enable or disable a database function temporarily. Once a trace flag is turned on, it remains on until either manually turned off or SQL Server restarted. Only users in the sysadmin fixed server role can turn on trace flags.

If you want to enable/disable Detailed Deadlock Information (1205), use Query Analyzer and DBCC TRACEON to turn it on.

1205 trace flag sends detailed information about the deadlock to the error log.

Enable Trace at current connection level:

DBCC TRACEON(1205)

Disable Trace:

DBCC TRACEOFF(1205)

Enable Multiple Trace at same time separating each trace with a comma.

DBCC TRACEON(1205,2528)

Disable Multiple Trace at same time separating each trace with a comma.

DBCC TRACEOFF(1205,2528)

To set the trace using the DBCC TRACEON command at a server level, Pass second argument to the function as -1.

DBCC TRACEON (1205, -1)

To enumerate a complete list of traces that are on run following command in query analyzer.

DBCC TRACESTATUS(-1)
 

Avoid deadlocking on your SQL Server

Deadlock:

Deadlocking occurs when two user processes have locks on separate objects and each process is trying to acquire a lock on the object that the other process has. When this happens, SQL Server ends the deadlock by automatically choosing one and aborting the process, allowing the other process to continue. The aborted transaction is rolled back and an error message is sent to the user of the aborted process. Generally, the transaction that requires the least amount of overhead to rollback is the transaction that is aborted.


Some tips for reducing the deadlock:

• Ensure the database design is properly normalized.
• Have the application access server objects in the same order each time.
• During transactions, don't allow any user input. Collect it before the transaction begins.
• Avoid cursors.
• Keep transactions as short as possible. One way to help accomplish this is to reduce the number of round trips between your application and SQL Server by using stored procedures or keeping transactions with a single batch.
• Another way of reducing the time a transaction takes to complete is to make sure you are not performing the same reads over and over again. If you do need to read the same data more than once, cache it by storing it in a variable or an array, and then re-reading it from there.
• Reduce lock time. Try to develop your application so that it grabs locks at the latest possible time, and then releases them at the very earliest time.
• If appropriate, reduce lock escalation by using the ROWLOCK or PAGLOCK.
  Consider using the NOLOCK hint to prevent locking if the data being locked is not modified often.
• If appropriate, use as low of an isolation level as possible for the user connection running the transaction. Consider using bound connections.

Example:
You are a database developer for a clothing retailer. The company has a database named Sales. This
database contains a table named Inventory. The Inventory table contains the list of items for sale and the
quantity available for each of those items. When sales information is inserted into the database, this table
is updated. The stored procedure that updates the Inventory table is shown in the exhibit.
CREATE PROCEDURE UpdateInventory @IntID int
AS
BEGIN
DECLARE @Count int
BEGIN TRAN
SELECT @Count = Available
FROM Inventory WITH (HOLDLOCK)
WHERE InventoryID = @IntID
IF (@Count > 0)
UPDATE Inventory SET Available = @Count – 1
WHERE InventoryID = @IntID
COMMIT TRAN
END
When this procedure executes, the database server occasionally returns the following error message:
Transaction (Process ID 53) was deadlocked on {lock} resources with another
process and has been chosen as the deadlock victim. Rerun the transaction.
You need to prevent the error message from occurring while maintaining data integrity.
Change the table hint to UPDLOCK.
Explanation: This is a deadlock problem. We must resolve this problem. The SQL batch of this scenario
basically consists of an SELECT statement and an UPDATE statement. The SELECT statement includes a table
hint: WITH (HOLDLOCK).
This table hint is very restrictive. The rows are completely locked. We need to remove this restrictive table hint
and replace it with the table hint UPDLOCK, which is less restrictive than HOLDLOCK. It allows reads of the
rows, but no updates.
Note: table hint
A table hint specifies that a table scan, or one or more indexes, must be used by the query optimizer, or a
locking method must be used by the query optimizer with this table and for this SELECT. The query optimizer
can usually pick the best optimization method without hints being specified, therefore it is recommended that
hints only be used as a last resort by experienced developers and database administrators.

Reference Links:

http://www.sql-server-performance.com/tips/deadlocks_p1.aspx

Import CSV File Into SQL Server Using Bulk Insert – Load Comma Delimited File Into SQL Server

This is very common request recently – How to import CSV file into SQL Server? How to load CSV file into SQL Server Database Table? How to load comma delimited file into SQL Server? Let us see the solution in quick steps.

CSV stands for Comma Separated Values, sometimes also called Comma Delimited Values.

Create TestTable

USE TestData
GO
CREATE TABLE CSVTest
(ID INT,
FirstName VARCHAR(40),
LastName VARCHAR(40),
BirthDate SMALLDATETIME)
GO

Create CSV file in drive C: with name csvtest.txt with following content. The location of the file is C:\csvtest.txt

1,James,Smith,19750101

2,Meggie,Smith,19790122

3,Robert,Smith,20071101

4,Alex,Smith,20040202

Now run following script to load all the data from CSV to database table. If there is any error in any row it will be not inserted but other rows will be inserted.

BULK
INSERT CSVTest
FROM 'c:\csvtest.txt'
WITH
(
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n'
)
GO
--Check the content of the table.
SELECT *
FROM CSVTest
GO
--Drop the table to clean up database.
SELECT *
FROM CSVTest
GO

SQL SERVER – 2005 Locking Hints and Examples

Locking Hints and Examples are as follows. The usage of them is same same but effect is different.

ROWLOCK

Use row-level locks when reading or modifying data.

PAGLOCK
Use page-level locks when reading or modifying data.

TABLOCK
Use a table lock when reading or modifying data.

DBLOCK
Use a database lock when reading or modifying data.

UPDLOCK
UPDLOCK reads data without blocking other readers, and update it later with the assurance that the data has not changed since last read.

XLOCK
Use exclusive locks instead of shared locks while reading a table, and use hold locks until the end of the statement or transaction.

HOLDLOCK
Use a hold lock to hold a lock until completion of the transaction, instead of releasing the lock as soon as the required table, row, or data page is no longer required.

NOLOCK
This does not lock any object. This is the default for SELECT operations. It does not apply to INSERT, UPDATE, and DELETE statements.

Examples:
SELECT OrderID
FROM Orders (WITH ROWLOCK)
WHERE OrderID BETWEEN 100
AND 2000

UPDATE Products (WITH NOLOCK)
SET ProductCat = 'Machine'
WHERE ProductSubCat = 'Mac'

Ranking Functions within SQL Server 2005

With SQL Server 2005, Microsoft introduced a number of new features. These new features make it easier for a DBA or SQL Server developer to code against and to maintain SQL Server databases. In this article I will discuss the new ranking functions provided with SQL Server 2005. These new functions make it much easier for you to code your T-SQL to associate a ranking to your result set. As I discuss each of the new ranking functions I will provide one or more examples to help illustrate how these new functions work.

What are Ranking Functions

Ranking functions are functions that allow you to sequentially number your result set. These functions can be used to provide you with a number of different sequential numbering schemes. For example you can number each row in your result set sequentially where the first row has a ranking number of 1, the second row has a ranking of 2, third row has 3, and so on. You can also use these ranking functions to sequentual number groups, so each group would have a numbering scheme of 1,2, 3, and then the next group would start over with 1, 2, 3, etc..

Test Data for My Examples

In order to provide examples of each ranking function I need to have some test data that the ranking function will process against. For my test data I will use a simple “Person” table. This table will consist of three columns “FirstName”, “Age” and “Gender”. Below is the code to create and populate my sample test data file.

SET NOCOUNT ON
CREATE TABLE Person(
FirstName VARCHAR(10),
Age INT,
Gender CHAR(1))
INSERT INTO Person VALUES ('Ted',23,'M')
INSERT INTO Person VALUES ('John',40,'M')
INSERT INTO Person VALUES ('George',6,'M')
INSERT INTO Person VALUES ('Mary',11,'F')
INSERT INTO Person VALUES ('Sam',17,'M')
INSERT INTO Person VALUES ('Doris',6,'F')
INSERT INTO Person VALUES ('Frank',38,'M')
INSERT INTO Person VALUES ('Larry',5,'M')
INSERT INTO Person VALUES ('Sue',29,'F')
INSERT INTO Person VALUES ('Sherry',11,'F')
INSERT INTO Person VALUES ('Marty',23,'F')

ROW_NUMBER Function

The first ranking function I will discuss is the ROW_NUMBER function. This function returns a sequential number starting at 1 for each row or grouping within your result set. The ROW_NUMBER function has the following calling syntax:

ROW_NUMBER ( )  OVER ( [ <partition_by_clause> ] <order_by_clause> )

Where the:
“<partition_by_clause>” is a column or set of columns used to determine the grouping in which the ROW_NUMBER function applies sequential numbering.
<order_by_clause>” is a column or set of columns used to order the result set within the grouping (partition).
To demonstrate how to use the ROW_NUMBER function, my first example below will sequentially number all the rows in my Person table, and order them by Age.

SELECT ROW_NUMBER() OVER (ORDER BY Age) AS [Row Number by Age], 
       FirstName,
       Age 
  FROM Person

Here is the result set for the above T-SQL code:

Row Number by Age    FirstName  Age
-------------------- ---------- -----------
1                    Larry      5
2                    Doris      6
3                    George     6
4                    Mary       11
5                    Sherry     11
6                    Sam        17
7                    Ted        23
8                    Marty      23
9                    Sue        29
10                   Frank      38
11                   John       40

Here you can see I have sequentially numbered all my Person table rows starting from 1, and the result set is ordered by the Age column. This ordering was accomplished by placing the “ORDER BY Age” criteria in the ORDER BY clause of the ROW_NUMBER function.
Suppose you do not want your result set to be ordered, but you want to simply just sequentially number each row. The ROW_NUMBER function requires an ORDER BY clause, so something is needed in this clause. In the following query I specified “SELECT 1” for the ORDER BY clauses, doing this just returned my rows physically how they where stored, and sequentially numbered them starting from 1:

SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 1)) AS [Row Number by Record Set], 
       FirstName, 
       Age 
  FROM Person

Here is the result set from running the above query:

Row Number by Record Set FirstName  Age
------------------------ ---------- -----------
1                        Ted        23
2                        John       40
3                        George     6
4                        Mary       11
5                        Sam        17
6                        Doris      6
7                        Frank      38
8                        Larry      5
9                        Sue        29
10                       Sherry     11
11                       Marty      23

The ROW_NUMBER function not only allows you to order the entire row set, but you can also use the PARTITION clause to sequentially number groups of rows. Rows will be sequentially numbered within each unique partition value. The sequential number will restart at 1 for each new partition value in your record set. Take a look at the following query:

SELECT ROW_NUMBER() OVER (PARTITION BY Gender ORDER BY Age) AS [Partition by Gender], 
       FirstName, 
       Age,
       Gender 
  FROM Person

When I run this query, this is my result set:

Partition by Gender  FirstName  Age         Gender
-------------------- ---------- ----------- ------
1                    Doris      6           F
2                    Mary       11          F
3                    Sherry     11          F
4                    Sue        29          F
1                    Larry      5           M
2                    George     6           M
3                    Sam        17          M
4                    Ted        23          M
5                    Marty      23          M
6                    Frank      38          M
7                    John       40          M

In this example I partitioned by Gender, and ordered by Age. Doing this allowed me to sequentially number the female records in my Person table by age, and then have the sequential numbering start over again for the male group.

RANK Function

Sometimes you want a row that has the same order by column value as another row to have the same ranking. If this is the case then the RANK() function will help you. The RANK function has the following calling syntax:

RANK ( )  OVER ( [ <partition_by_clause> ] <order_by_clause> )

Where the:
“<partition_by_clause>” is a column or set of columns used to determine the grouping in which the RANK function applies sequential numbering.
<order_by_clause>” is a column or set of columns used to order the result set within the grouping (partition).
The RANK function sequentially numbers a record set, but when two rows have the same order by value then they get the same ranking. The ranking value still gets incremented when two rows have the same order by value, so that when a new ranking order by value is encountered the ranking value on that new row will be 1 more than the number of proceeding rows. Let me show you a couple of examples to help you better understand the RANK function.
In this first example I want to rank my record set by Age:

SELECT RANK() OVER (ORDER BY Age) AS [Rank by Age], 
       FirstName, 
       Age
  FROM Person

Here is the output for this command:

Rank by Age          FirstName  Age
-------------------- ---------- -----------
1                    Larry      5
2                    Doris      6
2                    George     6
4                    Mary       11
4                    Sherry     11
6                    Sam        17
7                    Ted        23
7                    Marty      23
9                    Sue        29
10                   Frank      38
11                   John       40

By looking at this output you can see that whenever rows have the same Age value their “Rank by Age” value are the same. You can see this for “Doris” and “George”, “Mary” and “Sherry”, as well as “Ted” and “Marty”. Each of these row pairs have the same “Rank by Age” value. Note that “Doris” and “George” both have a ranking of 2, but the ranking for “Mary” the next unique Age doesn’t have a ranking value of 3, but instead has a ranking of 4. This is because “Mary” is the forth record returned in the record set, and the RANK() functions takes this into account when setting the ranking value of the next unique “Rank by Age” value.
If you want to have multiple rankings in your record set, where each ranking is for a specific group you need to use the “PARTITION BY” clause of the RANK function. Below is an example where I grouped my ranking by Gender, and ordered each ranking by Age:

SELECT RANK() OVER (PARTITION BY Gender ORDER BY Age) AS [Partition by Gender], 
       FirstName, 
       Age,
       Gender 
  FROM Person

Here is the results of running the above query:

Partition by Gender  FirstName  Age         Gender
-------------------- ---------- ----------- ------
1                    Doris      6           F
2                    Mary       11          F
2                    Sherry     11          F
4                    Sue        29          F
1                    Larry      5           M
2                    George     6           M
3                    Sam        17          M
4                    Ted        23          M
4                    Marty      23          M
6                    Frank      38          M
7                    John       40          M

Here you can see that the “F” Gender started ranking at 1 and goes through 4, then the ranking starts over with 1 when the first “M” Gender is encountered.

DENSE_RANK Function

The DENSE_RANK function is similar to the RANK function, although this function doesn’t produce gaps in the ranking numbers. Instead this function sequentially ranks each unique ORDER BY value. With the DENSE_RANK function each row either has the same ranking as the preceeding row, or has a ranking 1 greater then the prior row. The DENSE_RANK function has the same syntax as the RANK function.
Here I use the DENSE_RANK function to rank all my Person records by Age:

SELECT DENSE_RANK() OVER (ORDER BY Age) AS [Dense Rank by Age], 
       FirstName, 
       Age
  FROM Person

This code produces the following output:

Dense Rank by Age    FirstName  Age
-------------------- ---------- -----------
1                    Larry      5
2                    Doris      6
2                    George     6
3                    Mary       11
3                    Sherry     11
4                    Sam        17
5                    Ted        23
5                    Marty      23
6                    Sue        29
7                    Frank      38
8                    John       40

Here you can see the “Dense Rank By Age” column numbers are sequential without any gaps. And when two rows have the same ORDER BY value they have the same ranking like “Ted” and “Marty”.

NTILE Function

The last ranking function is the NTILE function. This function is used to break up a record set into a specific number of groups. The NTILE function also uses similar syntax as the other ranking functions.
In this first example, I want to group my Person records into three different groups of records. I want these groups to be based on the Age column. To do that I would run the following T-SQL:

SELECT FirstName, 
       Age, 
       NTILE(3) OVER (ORDER BY Age) AS [Age Groups]
  FROM Person

Here is my result set from the above T-SQL command:

FirstName  Age         Age Groups
---------- ----------- --------------------
Larry      5           1
Doris      6           1
George     6           1
Mary       11          1
Sherry     11          2
Sam        17          2
Ted        23          2
Marty      23          2
Sue        29          3
Frank      38          3
John       40          3

In my result set I ended up with three different “Age Groups”. The first age group goes from Age 5 to Age 11, the second age group goes from 11 to 23, and the last age group is 29 to 40. The NTILE function just evenly divides your record set into the number of groups the NTILE function requests. By using the NTILE function each record in a group is give the same ranking.
The NTILE function is very useful if you only want to return a specific grouping of records. Below is an example where I returned only the middle group (Age Group = 2) from my prior example:

SELECT FirstName, 
       Age, 
       Age AS [Age Group]
FROM ( SELECT FirstName, 
              Age, 
              NTILE(3) OVER (ORDER BY Age) AS AgeGroup
        FROM Person) A
WHERE AgeGroup = 2

Here you can see I only returned the second Age Group:

FirstName  Age         Age Group
---------- ----------- -----------
Sherry     11          11
Sam        17          17
Ted        23          23
Marty      23          23

Conclusion

Coding a process to sequential number your record sets used to take a number of lines of code. SQL Server 2005 solved this coding dilemma by providing some new ranking functions. Hopefully the next time you need to sequential number a record set, one of the new ranking functions in SQL Server 2005 will make your number task, a trivial task.