Skip to main content

How to create an auto-incrementing column in MS SQL Server 2000

Unlike Microsoft SQL Server 2005 and 2008, MS SQL Server 2000 does not have a ROW_NUMBER() function which applies only to the results of a SELECT query as it doesn't store any permanent value in the DB.

The way it works is in keeping with the principles of relational databases. There is no implict ordering of records in an RDBMS. Only by using ORDER BY, a particular ordering (and not sorting) can be enforced only when doing a SELECT.

Since Microsoft SQL Server 2000 doesn't have ROW_NUMBER(), the need for creating a primary key column of format int with the auto-increment functionality can be achieved using the keyword IDENTITY(1,1). IDENTITY means "a unique IDENTITY for the record so long as this table exists".


Usage example:

CREATE TABLE jobs
(
job_id smallint IDENTITY(1,1)PRIMARY KEY CLUSTERED,
job_desc varchar(50) NOT NULL DEFAULT
min_lvl tinyint NOT NULL CHECK (min_lvl >= 10),
max_lvl tinyint NOT NULL CHECK (max_lvl <= 250) ) 





Microsoft's T-SQL reference under SQL Server 2000 documentation at MSDN explains the IDENTITY property in detail at http://msdn.microsoft.com/en-us/library/aa933196(SQL.80).aspx

Comments

Popular posts from this blog

What is .csp extension? C++ Server Pages

C++ Server Pages C++ Server Pages (CSP) is a Web Engine for advanced Web Application Development, that uses blended Markup Language / C++ scripts ( such as HTML/C++, XML/C++, WML/C++ etc.) Similar to ASP and JSP, it provides a great easiness in creating web pages with dynamic content, as well as complex business applications. However, instead of Java, Javascript or VBscript, it uses C++ . This brings some significant advantages: Incredibly high processing efficiency. Benchmarks have shown a range of 80 to 250 times higher processing speed than ASP. The use of pure C++ allows the use of tons of libraries that are currently available. It is important to notice that the libraries written in C++ are tens or hundreds of times more than in any other language. It is widely accepted that the most skilled programmers in the IT market are the C++ ones. However, CGI, ISAPI and other frameworks where C++ applies, do not provide the web developer with facilities for efficient app...

Integrating SwiftUI into Existing AppKit Cocoa Projects: A Step-by-Step Guide

As Apple continues to push SwiftUI as the future of app development across its platforms, many developers find themselves in a position where they need to integrate this modern framework into their existing AppKit Cocoa projects. This integration can breathe new life into older applications, allowing developers to take advantage of SwiftUI's declarative syntax and powerful features while maintaining their existing codebase. In this guide, we'll walk through the process of adding SwiftUI to an existing AppKit Cocoa project, providing you with the knowledge to modernize your macOS applications.