hi Paul,
Post by ***@GoldCoastThanks Andrea, How do I split the 4GB database? I've tried to add a
new database but it still hits the 4GB limit. If you know how to
split the existing 4GB database in SQL Express then please let me
know?
"splitting" means, for instance, separating a big table into separate lettle
tables... say you have an "orders" table, you can separate it into
Order1990to1995, Orders1995to2000, Order2000to2005, Orders (where Orders
means a reasonably partition or the actual current partition of data, say
the current year or the like)..
we are in fact dealing with horizontal partitioning and the "whole design"
should be modified accordingly to that...
you have than to move partitions across databases to split it.. so you gain
space.. usualy you maintain lookup tables in the "current database" and
modify existing views and stored procedures to mamange the partition as
well.. you have to consider "just" the "modified" name as long as the new
database storing the partition, and you can use for that a 3 part name
allowed by SQL Server,: database.owner/schema/object..
so you can, for instance, have your actual existing retrivial stored
procedure(s) modified from
CREATE PROCEDURE dbo.usp_GetOrdersByDate (@OrderDate)
AS BEGIN
SELECT <col_list>
FROM dbo.Orders
WHERE [Date] = @OrderDate;
END;
in
CREATE PROCEDURE dbo.usp_GetOrdersByDate (@OrderDate)
AS BEGIN
IF YEAR(@OrderDate) >= 2005 -- THIS IS THE "PARTITION' FUNCTION
SELECT <col_list>
FROM dbo.Orders
WHERE [Date] = @OrderDate;
IF YEAR(@OrderDate) >= 2000 AND YEAR(@OrderDate) < 2005
SELECT <col_list>
FROM db_Year200to2005.dbo.Orders2000to2005
WHERE [Date] = @OrderDate;
IF ......
END;
and so on, for all the CRUD operations (that's to say for
insert/update/delete/select)...
this is the base idea.. it's not "difficult" per se, but you have to plan
and test.. ah.. you have to test... and if I did not mention it, you have to
carefully test :)
regards
--
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz http://italy.mvps.org
DbaMgr2k ver 0.21.0 - DbaMgr ver 0.65.0 and further SQL Tools
--------- remove DMO to reply