Guest Lecture: Emerging Technologies covering database

I had done a guest lecture for INFOSYS330 in University of Auckland this afternoon. It was good... I think? Usually in the second last week of the semester there are not many students attending class, but this lecture marks attendance, so nearly everyone come to lecture, ha-ha. That made me feel better. :D

Anyway, just want to put up some slides and code. 

Here: slides and codes

 

P.S. I have polygon data for NZ map - and yes, I traced it. :P

Alter Column to become Case Sensitive

SQL Server 2000 (or above) allows individual columns become case sensitive by identifying collation. For example:

ALTER TABLE TblName 
ALTER COLUMN ColName VARCHAR(100) COLLATE Latin1_General_CS_AS NULL

Where _CS_ indicates it is a Case Sensitive column.

Checking object existence in SQL 2000 and SQL 2005

I have SQL 2005 on my machine but sometimes I am connecting to SQL 2000 database. If you generate drop object script from SQL 2005 Server Management Studio, it cannot be applied to the SQL 2000 database because they do not have the same system tables and views. For example dropping a stored procedure:

SQL 2005 syntax:

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[StoredProc_Name]') AND type in (N'P', N'PC')) DROP PROCEDURE [dbo].[StoredProc_Name]

However SQL 2000 database does not have sys.objects table. In SQL 2000 syntax:

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[StoredProc_Name]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1) DROP PROCEDURE [dbo].[StoredProc_Name]

This will work in SQL 2005 database as well because SQL 2005 database has a view named sysobjects (for backward compatibility?!).

To check function existence in SQL 2000 syntax:

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[Function_Name]') AND xtype in (N'FN', N'IF', N'TF')) DROP FUNCTION [dbo].[Function_Name]

To check table existence in SQL 2000 syntax:

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[Table_Name]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1) DROP TABLE [dbo].[TableName]

T-SQL Cursor syntax

I've been asked a few times about the cursor syntax (and actually myself have been forgetting too). I know the SQL books online has syntax for everything, but apparently it is a bit hard to read some times. Anyway, post this here so it's handy for myself and the others. :)

DECLARE some_cur CURSOR FOR SELECT Column1, Column2, Column3 FROM TableName OPEN some_cur DECLARE @col1 INT, @col2 NVARCHAR(50), @col3 BIT FETCH NEXT FROM some_cur INTO @col1, @col2, @col3 WHILE (@@fetch_status <> -1) BEGIN -- Do whatever you want FETCH NEXT FROM some_cur INTO @col1, @col2, @col3 END CLOSE some_cur DEALLOCATE some_cur

Check/find default

If you know the name of the default and you always have good naming convention that include table and column name in your default name, then you can simple check against the sysobjects table to find out if a default exists:

SELECT * FROM SysObjects O WHERE name = 'DF_DEFAULTNAME' AND xtype = 'D'

If you want to know a particular column has a default defined or not:

SELECT * FROM SysObjects O INNER JOIN SysColumns C ON O.ID=C.ID INNER JOIN SysConstraints T ON C.CDEFAULT = T.CONSTID WHERE ObjectProperty(O.ID,'IsUserTable')=1 AND O.Name='TableName' AND C.Name='ColumnName'

If the name of the default is what you are after:

SELECT O2.Name FROM SysObjects O INNER JOIN SysColumns C ON O.ID=C.ID INNER JOIN SysConstraints T ON C.CDEFAULT = T.CONSTID INNER JOIN SysObjects O2 ON T.CONSTID = O2.ID WHERE ObjectProperty(O.ID,'IsUserTable')=1 AND O.Name='TableName' AND C.Name='ColumnName'

Check exists column

You may want to check if a column exists before adding the column or performing some action on that column. Here is the clause to check column existence.

IF EXISTS (SELECT * FROM SysObjects O INNER JOIN SysColumns C ON O.ID=C.ID WHERE ObjectProperty(O.ID,'IsUserTable')=1 AND O.Name='TableName' AND C.Name='ColumnName')

General alter table syntax

To add a primary key:

ALTER TABLE TableName ADD CONSTRAINT ConstraintName PRIMARY KEY (ColumnName)

For composite key, simply specify mutiple columns:

ALTER TABLE TableName ADD CONSTRAINT ConstraintName PRIMARY KEY (ColumnName1, ColumnName2)

To add a foreign key:

ALTER TABLE ChildTableName ADD CONSTRAINT FK_NAME FOREIGN KEY (ChildTableColumnName) REFERENCES ParentTable(ParentTableColumnName)

To add a unique key:

ALTER TABLE TableName ADD CONSTRAINT UNIQUE_NAME UNIQUE (ColumnName)

To add a column:

ALTER TABLE TableName ADD ColumnName int NULL ALTER TABLE TableName ADD ColumnName int NOT NULL DEFAULT(0)

Note thate when you are adding a NOT NULL column which does not have a default value the statement would fail. If it has to be a NOT NULL column, you should add it as a NULL column first, update the table and set values of that column, then change it to NOT NULL with alter table statement. For example,

ALTER TABLE TableName ADD ColumnName int NULL UPDATE TableName SET ColumnName = ColumnName2 + 1 ALTER TABLE TableName ALTER COLUMN ColumnName int NOT NULL

Add default value to existing column

I have spent quite a bit of time trying to figure out how to add a default constraint to an existing column in SQL Server (in T-SQL). I found the following from the SQL Server 2005 Books Online:

ALTER TABLE MyCustomers ALTER COLUMN CompanyName SET DEFAULT 'A. Datum Corporation'

Appoint checking the syntax, SQL Server 2005 gives me the following error:

Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'set'.

I was so frustrated and have no idea why would this happened (until now I have completely no idea so please tell me if you know). Turn out I have written the following instead and it's working like a charm!

ALTER TABLE TableName WITH NOCHECK ADD CONSTRAINT DF_DefaultName DEFAULT 'Default Value' FOR ColumnName