Home > Articles > Data > SQL Server

Alison's SQL Server 2000 Tricks

  • Print
  • + Share This
  • 💬 Discuss
Learn a few SQL Server 2000 tricks from expert Alison Balter.

Use the new SQL server table data type

An alternative to a temporary table is the new Table data type. You use the Table data type to store a table in memory. You create a Table variable as follows:

DECLARE @tablename TABLE

Table variables are used to select, insert, update, and delete data. You can use them for validation tables, or as temporary storage in stored procedures. They behave like local variables. They therefore have a well-defined scope. An advantage of the table data type over temporary tables is that they require fewer recompilations of stored procedures. They also cause less locking and logging. Here's an example of the use of the Table data type

CREATE FUNCTION dbo.CustGetByTitle

		(@Title nVarChar(30))

	RETURNS Table

	AS

	RETURN SELECT CustomerID, CompanyName, ContactName, 
City, Region

	FROM Customers WHERE ContactTitle = @Title

	Table Data Type

	SELECT CustomerID, CompanyName
FROM CustGetByTitle('Owner')

What's new with referential integrity in SQL 2000

In SQL Server 2000, you enforceb referential integrity using DRI (Declarative Referential Integrity). You can set up DRI using either database diagrams, or using the Relationships tab of the Table Properties window. New to SQL Server 2000, you can now implement cascade update and cascade delete. Cascade update means that when the primary key value is changed, the change cascades down to any foreign keys. Cascade delete means that when the parent row is deleted, the children rows are deleted.

The T-SQL used to invoke the cascade update feature is:

ON UPDATE CASCADE

An example of the full syntax is:

ALTER TABLE dbo.[Order Details] WITH NOCHECK ADD CONSTRAINT
	FK_Order_Details_Orders FOREIGN KEY
	(
	OrderID
	) REFERENCES dbo.Orders
	(
	OrderID
	) ON UPDATE CASCADE

In the example, the OrderID in the OrderDetails table is automatically updated if the OrderID in the orders table is changed.

The T-SQL used to invoke the cascade delete feature is:

ON DELETE CASCADE

An example of the full syntax is:

ALTER TABLE dbo.Orders WITH NOCHECK ADD CONSTRAINT
	FK_Orders_Customers FOREIGN KEY
	(
	CustomerID
	) REFERENCES dbo.Customers
	(
	CustomerID
	) ON DELETE CASCADE

In the example, the orders with matching OrderIDs in the OrderDetails table are automatically deleted if the OrderID in the orders table is deleted.

  • + Share This
  • 🔖 Save To Your Account

Discussions

comments powered by Disqus