Tag Archive for: database

Row Number in SQL Server

Every once in a while there is a need to pull the rows with a row number. Here are a few solutions that I have seen to work well.

Below is a sample table with employees that has employee name and employee address columns:

CREATE TABLE Employees (

 employee_name VARCHAR(50) PRIMARY KEY,

 employee_address VARCHAR(100));


INSERT INTO Employees (employee_name, employee_address)

VALUES ('Blake Anderson', '2048 River View Rd.');

INSERT INTO Employees (employee_name, employee_address)

VALUES ('Ana Williams', '9055 East Blvd.');

INSERT INTO Employees (employee_name, employee_address)

VALUES ('Robert Schmidt', '3400 Windsor Street');

INSERT INTO Employees (employee_name, employee_address)

VALUES ('Sarah Reese', '1045 Coral Rd.');

SQL Server 2000 and SQL Server 2005

Using an IDENTITY column and a temporary table

This solution is based on creating a temporary table with IDENTITY column used to provide a row number. This approach provides very good performance. Here are the steps:

-- Create the temp table

CREATE TABLE #EmployeeRowNumber (

 rn INT IDENTITY (1, 1),

 employee_name VARCHAR(50),

 employee_address VARCHAR(100));


-- Generate the row number

-- To achieve an ordered list the names are sorted

INSERT #EmployeeRowNumber (employee_name, employee_address)

SELECT employee_name, employee_address

FROM Employees

ORDER BY employee_name;


-- Select the row number

SELECT employee_name, employee_address, rn

FROM #EmployeeRowNumber


-- Results

employee_name employee_address rn
---------------- ------------------------ -----------
Ana Williams 9055 East Blvd. 1
Blake Anderson 2048 River View Rd. 2
Robert Schmidt 3400 Windsor Street 3
Sarah Reese 1045 Coral Rd. 4

Using a subquery to count the number of rows

This solution is based on using a subquery on a unique column (or combination of columns) to count the number of rows. Here is how it looks with the sample data provided above:

SELECT employee_name, employee_address,

      (SELECT COUNT(*)

       FROM Employees AS E2

       WHERE E2.employee_name <= E1.employee_name) AS rn 

FROM Employees AS E1 

ORDER BY employee_name;

If the values in the column are not unique then duplicate row numbers will be generated. That can be resolved by adding a tiebreaker column that will guarantee the uniqueness. This approach is a slower method than using an IDENTITY column and a temporary table. Since it will incur (n + n2) /2 row scans it may not be practical to use on a large table.

SQL Server 2005

Using the ROW_NUMBER function

In SQL Server 2005 the new function ROW_NUMBER provides the fastest approach to solve the problem:

SELECT employee_name, employee_address,

       ROW_NUMBER() OVER(ORDER BY employee_name) AS rn

FROM Employees

ORDER BY employee_name;

Additional resources:

How to dynamically number rows in a SELECT Transact-SQL statement: http://support.microsoft.com/kb/186133

SQL Server 2005 Ranking Functions: http://msdn2.microsoft.com/en-us/library/ms189798.aspx

Book: “Inside Microsoft SQL Server 2005: T-SQL Querying” by Itzik Ben-Gan, Lubor Kollar and Dejan Sarka

SQL Server is The Safest Database

To have a secure and safe place to store data has always been a great desire. For years SQL Server has been improving in that area and with the release of SQL Server 2005 it has reached a special milestone. Seems the Microsoft SDL (Security Development Lifecycle) approach has helped greatly to make SQL Server a better product!

Here are two articles from independent sources that just confirm that:

1). SQL Server vs. Oracle – Which Database is More Secure?
A very good and interesting white paper by David Litchfield from Next Generation Security Software comparing the numbers of security flaws identified by external security researchers and subsequently fixed by Oracle and Microsoft in regard to their database products.

2). SQL Server is The Safest Database
According to this study from the Enterprise Strategy Group, Oracle has 70 vulnerabilities, MySQL has 59, Sybase has seven, IBM’s DB2 has four, and SQL Server has just two.

Great job by the SQL Server 2005 team!

64-bit Architectures for SQL Server 2005

Seems there is a lot of confusion about the different 64 bit architectures and which are supported for SQL Server 2005 to run on. And the different vendor acronyms to describe the architectures just add to the confusion.

Here is a brief note trying to sort this out.

There are only two 64 bit architectures supported by SQL Server (and actually driven by the supported 64-bit architectures for Windows). The separation is based on the underlying processor architecture.

The first one is IA-64 (or Itanium) and it is based on the EPIC (Explicitly Parallel Instruction Computing) instruction set. This is Intel’s first true 64-bit chip.

The second 64 bit architecture is x64 (or x86-64) which is essentially the x86 architecture with extensions to include 64 bit functionality. All Intel x64 and AMD64 processors fall in this category. It was first introduced with AMD’s Athlon 64.

There is no 32 bit equivalent for IA-64 as it was designed to be 64 bit from the beginning.

It is more correct to say x86-32 for the 32-bit equivalent, as x86 is the generic name for the architecture which started with Intel’s 8086 processor which was 16-bit. The x86-32 was introduced with the Intel 386 processor.

More details on 64-bit computing with SQL Server 2005 are available here:

SQL Server Express Installation and Connectivity Configuration

Since SQL Server 2005 Express is the successor to MSDE there is a lot of interest from developers. There are a few questions that keep being asked about the installation and configuration process:

How to automate the installation of SQL Server Express?
How to allow remote connections?
How to set up a custom named instance for installation?
How to configure SQL security?
How to troubleshoot connection problems?

Here is a brief summary addressing those questions.

Installation Options

SQL Server Express does support most of the same options used with MSDE to install from the command line and using INI files. Here are the most frequently used:

INSTANCENAME – allow to specify a custom instance name
DISABLENETWORKPROTOCOLS – allows to enable the TCP/IP protocol for remote access (0 – All protocols are enabled; 2 – Shared Memory and TCP/IP are enabled)
SECURITYMODE – allows to switch to SQL security mode (when SECURITYMODE=SQL is used to switch to SQL Security a strong password needs to be provides with the SAPWD option)

The complete list of options and values to set for each option is available here:

If SQL Server Express will be distributed via the bootstrapper all those parameters can be added to the product manifest (in the <Command> tag). Here is a detailed description of using the bootstrapper:

Remote Connections and Troubleshooting Connectivity

To troubleshoot connectivity issues it is important to understand that SQL Server Express installs by default with remote access disabled. This closes some security holes in prior versions. By default only the Shared Memory protocol is enabled. That allows making local connections. However, TCP/IP or Named Pipes connections (local or remote) are disabled. The TCP/IP and Named Pipes protocols can be enabled via the DISABLENETWORKPROTOCOLS option on install, or later following the steps in this KB article:

Troubleshooting connectivity normally starts with looking at the connection string. Assuming the default installation options are used (SQL Server Express installs as a named instance SQLEXPRESS by default) the connections string should be something like this:

Initial Catalog=master;Integrated Security=SSPI"

In the above <MachineName> could be “.”, “(local)”,”localhost”, or “<localhostname>”.

When connecting to named instances it is required to have the SQL Browser service running or to specify the TCP/IP port number in the connection string.

Next is to follow the steps as outlined in this article:


Here are a few more good resources for SQL Server Express:

SQL Server Express Home: http://www.microsoft.com/sql/editions/express/default.mspx
SQL Server Express Download: http://msdn.microsoft.com/vstudio/express/sql/download/
Redistribution Rights Registration: http://www.microsoft.com/sql/editions/express/redistregister.mspx
SQL Server Express WebLog: http://blogs.msdn.com/sqlexpress/default.aspx
Configuring During Installation: http://blogs.msdn.com/sqlexpress/archive/2006/09/20/configuring-sql-express-during-installation.aspx
Troubleshooting SQL Server 2005 Connectivity Issues (Part 1): http://blogs.msdn.com/sql_protocols/archive/2005/10/22/483684.aspx
Troubleshooting SQL Server 2005 Connectivity Issues (Part 2):
Troubleshooting SQL Server 2005 Connectivity Issues (Part 3):

The First One

This Web log will be dedicated primarily to technology postings. More specifically to topics related to SQL Server and databases, as well as .NET applications, algorithms and best practices. I will post some solutions that I have seen to work, some questions and answers about particular problems, as well as reviews of new technologies.

Although I will try to follow the best practices available, there is no guarantee those solutions will work in all cases. I will do my best to describe the platforms and versions tested as well as any issues that I am aware of. Any comments will be appreciated.

Let’s get it started!