MS SQL Server / SELECT clause with a CASE expression

In SQL Server, if you have a column which has NULLs and instead of nulls, if you want to display ‘Nothing’, what would you do?

The following query

SELECT CASE Dept_Name WHEN NULL THEN 'Nothing' ELSE Dept_Name END Dept_Name
FROM Inventory

would still display the nulls and not ‘Nothing’.

Workaround:

Continue reading “MS SQL Server / SELECT clause with a CASE expression”

SQL SELECT INTO Statement

The SQL SELECT INTO statement can be used to create backup copies of tables.

The SQL SELECT INTO Statement

The SELECT INTO statement selects data from one table, creates a new table with the exact structure and size and inserts automatically the selected data into the new table.

SQL SELECT INTO Syntax

We can select all columns into the new table:

SELECT *
INTO new_table_name [IN externaldatabase]
FROM old_tablename

Or we can select only the columns we want into the new table:

SELECT column_name(s)
INTO new_table_name [IN externaldatabase]
FROM old_tablename

Continue reading “SQL SELECT INTO Statement”

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