SQL Smack down is back!
It's been a while since the last installment, but it took me a while to think of some more topics. In this edition we will cover partitioned views, altering tables, and update and delete statements utilizing joins.
A partitioned view joins data from several tables, making the data appear to have come from one source table. Unlike a join, the partitioned view will be used to join tables with the same structure, and instead of joining the columns side by side, we will append the rows together. Generally you will use a partitioned view when you have a very large amount of records that you want to be able to query quickly. With a properly created partitioned view, you can query data from the table in about the time it will take SQL Server to query the individual member table the data is contained in, while avoiding the headache of writing a separate statement to query each table. This also applies to updates, inserts, and deletes if you follow a few additional rules.
I think the best way to explain this is to do an example, so below you will find the table definitions for our member tables. In this example we are going to have a record of sales from prior years, with one year in each table.
CREATE TABLE dbo.Sales99 (
SaleID INT NOT NULL,
SaleYear INT NOT NULL CHECK (SaleYear = '1999'),
Product CHAR(20) NOT NULL,
Quantity INT NOT NULL,
TotalCost MONEY NOT NULL,
CONSTRAINT PK_Sales99 PRIMARY KEY (SaleID, SaleYear)
)
CREATE TABLE dbo.Sales00 (
SaleID INT NOT NULL,
SaleYear INT NOT NULL CHECK (SaleYear = '2000'),
Product CHAR(20) NOT NULL,
Quantity INT NOT NULL,
TotalCost MONEY NOT NULL,
CONSTRAINT PK_Sales00 PRIMARY KEY (SaleID, SaleYear)
)
CREATE TABLE dbo.Sales01 (
SaleID INT NOT NULL,
SaleYear INT NOT NULL CHECK (SaleYear = '2001'),
Product CHAR(20) NOT NULL,
Quantity INT NOT NULL,
TotalCost MONEY NOT NULL,
CONSTRAINT PK_Sales01 PRIMARY KEY (SaleID, SaleYear)
)
Notice that each table has the same columns and primary key definition. The only difference is the check constraint on the SaleYear column, which checks to make sure the year is correct for the table we are in. This makes our SaleYear column the partitioning column, which is used by the partitioned view to determine which member tables to access when you query the view. You probably noticed that the SaleYear is part of the primary key, while normally SaleID would probably be the sole column in the primary key. This is because one of the rules of partitioned views is that the partitioning column must be contained in the primary key.
Creating the view itself is very simple:
CREATE VIEW dbo.SalesHistory AS
SELECT *
FROM Sales99
UNION ALL
SELECT *
FROM Sales00
UNION ALL
SELECT *
FROM Sales01
GO
Pretty simple eh? Now you should be able to query and update the view directly, and the query engine will handle retrieving and updating the underlying tables for you.
There are more rules that I haven't gone over here, however I suggest checking Books Online, as there are far to many for me to cover here.
I know a lot of us use the Enterprise Manager to make changes to table structures, such as adding or removing columns, however in many situations this is very inefficient. There is a much better way to do this, which I will explain here.
The problem with using Enterprise Manager to add a column is how it does it behind the scenes. You have probably at some point added a column to a table in the middle of the field list right? Well, the problem is that SQL Server has no real way of doing this. To get the columns in the order you want, the table must be recreated with the columns added in the correct order behind the scenes. SQL Server does this by creating a copy of the table in TempDB and moving all data to it while it recreates your table with the new structure and moves the data back. As you can probably guess, this is the big problem when tables get large.
The better way to add a column is as follows:
ALTER TABLE Orders
ADD OrderDate DATETIME NOT NULL
This will add a column named OrderDate to the end of the table. I know most of you like your columns to be in an order that makes sense, but concessions must be made in the name of performance.
You can also use this method to change the data type of a column:
ALTER TABLE Orders
ALTER COLUMN OrderDate SMALLDATETIME NOT NULL
To drop a column the syntax is as follows:
ALTER TABLE Orders
DROP COLUMN OrderDate
Using these methods you can save a lot of time and trouble, and make everyone else using the server a lot happier, when you work with larger tables.
Recently I have noticed a lot of statements like this:
DELETE FROM Orders
WHERE OrderID IN (SELECT OrderID
FROM OrdersToDelete)
There is a much simpler way to do this statement, which will make life a lot easier when you need to join against multiple tables. The syntax is as follows:
DELETE Orders
FROM Orders o
INNER JOIN OrdersToDelete d
ON (o.OrderID = d.OrderID)
Doesn't that seem easier? The same concept can be applied to updates:
UPDATE o
SET o.ProductID = p.ProductID
FROM Orders o
INNER JOIN Products p
ON (o.LeadType = p.LeadType)
Notice we only supplied the update statement with the alias of the table we wanted to update in the update clause.
These two methods should make updating and deleting data much easier for you when a join is required.
Well that's it for the edition of SQL Smack down. As always, if you have any questions about these or any other topics, please talk to Barry.