DAVElopment - Dave's Blog on Coding and Software Development

I wrote this little table and procedure to help me remember what style 104 did, or how to get HH:MM AM/PM out of a DATETIME column. Basically, it populates a table with the valid style numbers, then loops through those, and produces the result (and the syntax for producing that result) for each style, given the current date and time. It uses also a cursor. This is designed to be a helper function, not something you would use as part of a production environment, so I don't think the performance implications should be a big concern. Read more »

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