Alison's SQL Server 2000 Tricks
Like this article? We recommend
Like this article? We recommend
Like this article? We recommend
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.