DDL DML and programmable objects_SQL Server 2016 Developer's Guide-QQ阅读男生武侠网

书名：SQL Server 2016 Developer's Guide
作者名：Dejan Sarka Milo? Radivojevi? William Durkin
本章字数：2414字
更新时间：2025-04-04 19:39:01

DDL, DML, and programmable objects

As a developer, you are often also responsible to create database objects. Of course, in an application, you also need to insert, update, and delete the data. In order to maintain data integrity, enforcing data complies with business rules, you need to implement constraints. In a quick review of the data definition language (DDL) and data modification language (DML) elements, the following statements are presented:

CREATE for creating tables and programmatic objects
ALTER to add constraints to a table
DROP to drop an object
INSERT to insert new data
UPDATE to change existing data
DELETE to delete the data

In a SQL Server database, you can also use programmatic objects. You can use triggers for advanced constraints or to maintain redundant data like aggregated data. You can use other programmatic objects for data abstraction, for an intermediate layer between the actual data and an application. The following programmatic objects are introduced here:

Triggers
Stored procedures
Views
User-defined functions

Note

It is worth mentioning again that this chapter is just a reminder of the features SQL Server gives to developers. Therefore, this section is not a comprehensive database logical and physical design guide.

Data definition language statements

Let's start with data definition language statements. The following code shows how to create a simple table. This table represents customers' orders. For this demonstration of DDL and DML statements, only a couple of columns are created in the table. The OrderId column uniquely identifies each row in this table, is a primary key for the table, as the PRIMARY KEY constraint specifies. Finally, the code checks whether a table with the name SimpleOrders already exists in the dbo schema, and drops it in such a case:

IF OBJECT_ID(N'dbo.SimpleOrders', N'U') IS NOT NULL 
   DROP TABLE dbo.SimpleOrders; 
CREATE TABLE dbo.SimpleOrders 
( 
  OrderId   INT         NOT NULL, 
  OrderDate DATE        NOT NULL, 
  Customer  NVARCHAR(5) NOT NULL, 
  CONSTRAINT PK_SimpleOrders PRIMARY KEY (OrderId) 
);

For further examples, another table is needed. The following code creates the dbo.SimpleOrderDetails table in a very similar way to how the previous table was created, by checking for existence and dropping it if it exists. The OrderId and ProductId columns form a composite primary key. In addition, a CHECK constraint on the Quantity column prevents inserts or updates of this column to the value zero:

IF OBJECT_ID(N'dbo.SimpleOrderDetails', N'U') IS NOT NULL 
   DROP TABLE dbo.SimpleOrderDetails; 
CREATE TABLE dbo.SimpleOrderDetails 
( 
  OrderId   INT NOT NULL, 
  ProductId INT NOT NULL, 
  Quantity  INT NOT NULL 
   CHECK(Quantity <> 0), 
  CONSTRAINT PK_SimpleOrderDetails 
   PRIMARY KEY (OrderId, ProductId) 
);

The previous two examples show how to add constraints when you create a table. It is always possible to add constraints later too, by using the ALTER TABLE statement. The tables created in the previous two examples are associated through a foreign key. The primary key of the dbo.SimpleOrders table is associating the order details in the dbo.SimpleOrderDetails table with their correspondent order. The code in the following example defines this association:

ALTER TABLE dbo.SimpleOrderDetails ADD CONSTRAINT FK_Details_Orders 
FOREIGN KEY (OrderId) REFERENCES dbo.SimpleOrders(OrderId);

Data modification language statements

The two demo tables are empty at the moment. You add data to them with the INSERT statement. You specify the data values in the VALUES clause. You can insert more than one row in a single statement, as the following code shows by inserting two rows into the dbo.SimpleOrderDetails table in a single statement. You can omit the column names in the INSERT part. However, this is not good practice. Your insert depends on the order of the columns if you don't specify the column names explicitly. Imagine what could happen if somebody later changes the structure of the table. In the event of a bad outcome, the insert would fail. However, you would at least have the information that something went wrong. In an even worse outcome, the insert into the altered table could succeed. You could end up with the wrong data in the wrong columns without even noticing this problem:

INSERT INTO dbo.SimpleOrders 
 (OrderId, OrderDate, Customer) 
VALUES 
 (1, '20160701', N'CustA'); 
INSERT INTO dbo.SimpleOrderDetails 
 (OrderId, ProductId, Quantity) 
VALUES 
 (1, 7, 100), 
 (1, 3, 200);

The following query checks the recently inserted data. As you probably expected, it returns two rows:

SELECT o.OrderId, o.OrderDate, o.Customer, 
  od.ProductId, od.Quantity 
FROM dbo.SimpleOrderDetails AS od 
  INNER JOIN dbo.SimpleOrders AS o 
    ON od.OrderId = o.OrderId 
ORDER BY o.OrderId, od.ProductId;

Here is the result:

OrderId OrderDate Customer ProductId Quantity
----------- ---------- -------- ----------- --------
1 2016-07-01 CustA 3 200
1 2016-07-01 CustA 7 100

The next example shows how to update a row. It updates the Quantity column in the dbo.SimpleOrderDetails table for the order with OrderId equal to 1 and for the product with ProductId equal to 3.

UPDATE dbo.SimpleOrderDetails 
   SET Quantity = 150 
WHERE OrderId = 1 
  AND ProductId = 3;

You can use the same SELECT statement to check the data, whether it has updated correctly, as introduced right after the inserts.

Frequently, you really need to check data right after a modification. For example, you might use the IDENTITY property or the SEQUENCE object to generate identification numbers automatically. When you insert an order, you need to check the generated value of the OrderId column to insert the correct value to the order details table. You can use the OUTPUT clause for this task, as the following code shows:

INSERT INTO dbo.SimpleOrders 
 (OrderId, OrderDate, Customer) 
OUTPUT inserted.* 
VALUES 
 (2, '20160701', N'CustB'); 
INSERT INTO dbo.SimpleOrderDetails 
 (OrderId, ProductId, Quantity) 
OUTPUT inserted.* 
VALUES 
 (2, 4, 200);

The output of the two inserts is as follows:

OrderId OrderDate Customer
----------- ---------- --------
2 2016-07-01 CustB
OrderId ProductId Quantity
----------- ----------- --------
2 4 200

Using triggers

The code for creating the dbo.SimpleOrders table doesn't check the order date value when inserting or updating the data. The following INSERT statement, for example, inserts an order with a pretty old and probably incorrect date.

INSERT INTO dbo.SimpleOrders 
 (OrderId, OrderDate, Customer) 
VALUES 
 (3, '20100701', N'CustC');

You can check that the incorrect date is in the table with the following query:

SELECT o.OrderId, o.OrderDate, o.Customer 
FROM dbo.SimpleOrders AS o 
ORDER BY o.OrderId;

Of course, it would be possible to prevent inserting an order date too far in the past, or updating it to a value that is too old, with a check constraint. However, imagine that you don't want to just reject inserts and updates with an order date value in the past; imagine you need to correct the value to a predefined minimal value, for example, January 1^st, 2016. You can achieve this with a trigger.

SQL Server supports two different kinds of DML triggers and one kind of DDL trigger. DML triggers can fire after or instead of a DML action, and DDL triggers can fire only after a DDL action. For a database developer, the after DML triggers are the most useful. As you already know, you can use them for advanced constraints, for maintaining redundant data, and more. A database administrator (DBA) might use DDL triggers to, for example, check and reject the inappropriate altering of an object and to make a view updateable, instead of DML triggers. Of course, often there is no such strict role separation in place. DDL and instead-of-DML triggers are not forbidden for database developers. Anyway, the following code shows a trigger created on the dbo.SimpleOrders table that fires after an INSERT or an UPDATE to this table. It checks the OrderDate column value. If the date is too far in the past, it replaces it with the default minimum value:

IF OBJECT_ID(N'trg_SimpleOrders_OrdereDate', N'TR') IS NOT NULL 
   DROP TRIGGER trg_SimpleOrders_OrdereDate; 
GO 
CREATE TRIGGER trg_SimpleOrders_OrdereDate 
 ON dbo.SimpleOrders AFTER INSERT, UPDATE 
AS 
 UPDATE dbo.SimpleOrders 
    SET OrderDate = '20160101' 
 WHERE OrderDate < '20160101';

Let's try to insert a low order date, and update an existing value to a value too far in the past:

INSERT INTO dbo.SimpleOrders 
 (OrderId, OrderDate, Customer) 
VALUES 
 (4, '20100701', N'CustD'); 
UPDATE dbo.SimpleOrders 
   SET OrderDate = '20110101' 
 WHERE OrderId = 3;

You can check the data after the updates with the following query:

SELECT o.OrderId, o.OrderDate, o.Customer, 
  od.ProductId, od.Quantity 
FROM dbo.SimpleOrderDetails AS od 
  RIGHT OUTER JOIN dbo.SimpleOrders AS o 
    ON od.OrderId = o.OrderId 
ORDER BY o.OrderId, od.ProductId;

Here is the result. As you can see, the trigger changed the incorrect dates to the predefined minimum date:

OrderId OrderDate Customer ProductId Quantity
----------- ---------- -------- ----------- -----------
1 2016-07-01 CustA 3 150
1 2016-07-01 CustA 7 100
2 2016-07-01 CustB 4 200
3 2016-01-01 CustC NULL NULL
4 2016-01-01 CustD NULL NULL

Note that the query used OUTER JOIN to include the orders without the details in the result set.

Data abstraction - views, functions, and stored procedures

A very good practice is to use SQL Server stored procedures for data modification and data retrieval. Stored procedures provide many benefits. Some of the benefits include:

Data abstraction: Client applications don't need to work with the data directly, rather they call the stored procedures. The underlying schema might even get modified without an impact on an application as long as you change the stored procedures that work with the objects with modified schema appropriately.
Security: Client applications can access data through stored procedures and other programmatic objects only. For example, even if an end user uses their own SQL Server Management Studio instead of the client application that the user should use, the user still cannot modify the data in an uncontrolled way directly in the tables.
Performance: Stored procedures can reduce network traffic, because you can execute many statements inside the procedure within a single call to a stored procedure. In addition, SQL Server has a lot of work with optimization and compilation of the code an application is sending. SQL Server optimizes this by storing the optimized and compiled code in memory. The compiled execution plans for stored procedures are typically held longer in memory than the execution plans for ad hoc queries and thus get reused more frequently.
Usage: Stored procedures accept input and can return output parameters, so they can be easily coded to serve multiple users.

The code in the following example creates a stored procedure to insert a row into the dbo.SimpleOrders table. The procedure accepts one input parameter for each column of the table:

IF OBJECT_ID(N'dbo.InsertSimpleOrder', N'P') IS NOT NULL 
   DROP PROCEDURE dbo.InsertSimpleOrder; 
GO 
CREATE PROCEDURE dbo.InsertSimpleOrder 
(@OrderId AS INT, @OrderDate AS DATE, @Customer AS NVARCHAR(5)) 
AS 
INSERT INTO dbo.SimpleOrders 
 (OrderId, OrderDate, Customer) 
VALUES 
 (@OrderId, @OrderDate, @Customer);

Here is a similar procedure for inserting data into the dbo.SimpleOrderDetails table:

IF OBJECT_ID(N'dbo.InsertSimpleOrderDetail', N'P') IS NOT NULL 
   DROP PROCEDURE dbo.InsertSimpleOrderDetail; 
GO 
CREATE PROCEDURE dbo.InsertSimpleOrderDetail 
(@OrderId AS INT, @ProductId AS INT, @Quantity AS INT) 
AS  
INSERT INTO dbo.SimpleOrderDetails 
 (OrderId, ProductId, Quantity) 
VALUES 
 (@OrderId, @ProductId, @Quantity);

Let's test the procedures. In the first part, the two calls to the dbo.InsertSimpleOrder procedure insert two new orders:

EXEC dbo.InsertSimpleOrder 
 @OrderId = 5, @OrderDate = '20160702', @Customer = N'CustA'; 
EXEC dbo.InsertSimpleOrderDetail 
 @OrderId = 5, @ProductId = 1, @Quantity = 50;

The following code calls the dbo.InsertSimpleOrderDetail procedure four times to insert four order details rows:

EXEC dbo.InsertSimpleOrderDetail 
 @OrderId = 2, @ProductId = 5, @Quantity = 150; 
EXEC dbo.InsertSimpleOrderDetail 
 @OrderId = 2, @ProductId = 6, @Quantity = 250; 
EXEC dbo.InsertSimpleOrderDetail 
 @OrderId = 1, @ProductId = 5, @Quantity = 50; 
EXEC dbo.InsertSimpleOrderDetail 
 @OrderId = 1, @ProductId = 6, @Quantity = 200;

The following query checks the state of the two tables after these calls:

SELECT o.OrderId, o.OrderDate, o.Customer, 
  od.ProductId, od.Quantity 
FROM dbo.SimpleOrderDetails AS od 
  RIGHT OUTER JOIN dbo.SimpleOrders AS o 
    ON od.OrderId = o.OrderId 
ORDER BY o.OrderId, od.ProductId;

Here is the result after the inserts go through the stores procedures:

OrderId OrderDate Customer ProductId Quantity
----------- ---------- -------- ----------- -----------
1 2016-07-01 CustA 3 150
1 2016-07-01 CustA 5 50
1 2016-07-01 CustA 6 200
1 2016-07-01 CustA 7 100
2 2016-07-01 CustB 4 200
2 2016-07-01 CustB 5 150
2 2016-07-01 CustB 6 250
3 2016-01-01 CustC NULL NULL
4 2016-01-01 CustD NULL NULL
5 2016-07-02 CustA 1 50

You can see in the result of the previous query that there are still some orders without order details in your data. Although this might be unwanted, it could happen quite frequently. Your end users might need to quickly find orders without details many times. Instead of executing the same complex query over and over again, you can create a view which encapsulates this complex query. Besides simplifying the code, views are also useful for tightening security. Just like stored procedures, views are securables as well. A DBA can revoke direct access to tables from end users, and give them access to view only.

The following example creates a view that finds the orders without details. Note that a view in SQL Server can consist of a single SELECT statement only, and that it does not accept parameters:

CREATE VIEW dbo.OrdersWithoutDetails 
AS 
SELECT o.OrderId, o.OrderDate, o.Customer 
FROM dbo.SimpleOrderDetails AS od 
  RIGHT OUTER JOIN dbo.SimpleOrders AS o 
    ON od.OrderId = o.OrderId 
WHERE od.OrderId IS NULL;

Now the query that finds the orders without details becomes extremely simple---it just uses the view:

SELECT OrderId, OrderDate, Customer 
FROM dbo.OrdersWithoutDetails;

Here is the result: the two orders without order details.

OrderId OrderDate Customer
----------- ---------- --------
3 2016-01-01 CustC
4 2016-01-01 CustD

If you need to parameterize a view, you have to use an inline table-valued function instead. Such a function serves as a parameterized view. SQL Server also supports multi-statement table-valued functions and scalar functions. The following example shows an inline table-valued function that retrieves top two order details ordered by quantity for an order, where the order ID is a parameter:

CREATE FUNCTION dbo.Top2OrderDetails 
(@OrderId AS INT) 
RETURNS TABLE 
AS RETURN 
SELECT TOP 2 ProductId, Quantity 
FROM dbo.SimpleOrderDetails 
WHERE OrderId = @OrderId 
ORDER BY Quantity DESC;

The following example uses this function to retrieve the top two details for each order with the help of the APPLY operator:

SELECT o.OrderId, o.OrderDate, o.Customer, 
  t2.ProductId, t2.Quantity 
FROM dbo.SimpleOrders AS o 
  OUTER APPLY dbo.Top2OrderDetails(o.OrderId) AS t2 
ORDER BY o.OrderId, t2.Quantity DESC;

Note that another form of the APPLY operator is used, the OUTER APPLY. This form preserves the rows from the left table. As you can see from the following result, the query returns two rows for orders with two or more order details, one for orders with a single order detail, and one with NULL values in the place of the order detail columns for orders without order detail:

OrderId OrderDate Customer ProductId Quantity
----------- ---------- -------- ----------- -----------
1 2016-07-01 CustA 6 200
1 2016-07-01 CustA 3 150
2 2016-07-01 CustB 6 250
2 2016-07-01 CustB 4 200
3 2016-01-01 CustC NULL NULL
4 2016-01-01 CustD NULL NULL
5 2016-07-02 CustA 1 50