June 25, 2012 at 6:42 am
Hi
Could any one please help me?
I have a test table .it has a olumn named PersonelImage,and it keeps images of all the personel.because of the larg number of records in table and the large size of the image files,i want to use partitioning and keep the images in another drive.
Is it true to use vertical partitioning?
I create a new filegroup called image and placed it in another drive,but i dont know how to create a vertical partition?!
I know the usage of horizental partitioning and the script for creating it but not about vertical partitioning.
June 25, 2012 at 7:16 am
Actually you can define a different file group for BLOBS when you create the table. Take a look at CREATE TABLE statement and check the meaning of TEXTIMAGE_ON. Unfortunately I don't think that it can be done with ALTER TABLE.
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
June 26, 2012 at 12:17 pm
Keeping all data in one table, but using a separate filegroup for blobs is a good way to go, and simple.
CREATE TABLE dbo.Personnel
(
PersonnelID INT NOT NULL
IDENTITY(1, 1)
CONSTRAINT [pk_dbo.Personnel] PRIMARY KEY CLUSTERED,
FirstName VARCHAR(100) NULL,
LastName VARCHAR(100) NULL,
PersonnelImage VARBINARY(MAX)
)
ON [FileGroupForNonBlobData]
TEXTIMAGE_ON [FileGroupForBlobData];
But, it means you have to manage the table as a single entity which can affect things like index maintenance and backups. If you want to implement vertical partitioning you will end up with more than one table. From 2008 R2 BOL article Partitioning
Vertical partitioning divides a table into multiple tables that contain fewer columns. The two types of vertical partitioning are normalization and row splitting:
- Normalization is the standard database process of removing redundant columns from a table and putting them in secondary tables that are linked to the primary table by primary key and foreign key relationships.
- Row splitting divides the original table vertically into tables with fewer columns. Each logical row in a split table matches the same logical row in the other tables as identified by a UNIQUE KEY column that is identical in all of the partitioned tables. For example, joining the row with ID 712 from each split table re-creates the original row.
I think you would be looking at the row-splitting option here. You could abstract the split by implementing a VIEW in place of your current table and adding a few INSTEAD OF triggers to the VIEW to support INSERTS, UPDATES and DELETES.
So, if your table were named Personnel and were defined as shown above we could split it into at two tables named PersonnelDetail and PersonnelImage:
CREATE TABLE dbo.PersonnelDetail
(
PersonnelID INT NOT NULL
IDENTITY(1, 1)
CONSTRAINT [pk_dbo.Personnel] PRIMARY KEY CLUSTERED, -- keep the same pk name unless you know it is not used in any hints (or other code)
FirstName VARCHAR(100) NULL,
LastName VARCHAR(100) NULL
)
ON [FileGroupForNonBlobData];
GO
CREATE TABLE dbo.PersonnelImage
(
PersonnelID INT NOT NULL,
PersonnelImage VARBINARY(MAX),
CONSTRAINT [fk_dbo.PersonnelImage_dbo.PersonnelDetail_PersonnelID] FOREIGN KEY (PersonnelID) REFERENCES dbo.PersonnelDetail (PersonnelID)
)
ON [FileGroupForBlobData];
GO
Your new VIEW would also be named dbo.Personnel and would implement a JOIN to recreate the original resultset currently stored in the Personnel table. It is implied that you would have to first copy the data from the table dbo.Personnel into dbo.PersonnelDetail and dbo.PersonnelImage and then drop the table dbo.Personnel, prior to implementing this VIEW.
CREATE VIEW dbo.Personnel
AS
SELECT pd.PersonnelID,
pd.FirstName,
pd.LastName,
pimg.PersonnelImage
FROM dbo.PersonnelDetail pd
JOIN dbo.PersonnelImage pimg ON pd.PersonnelID = pimg.PersonnelID;
GO
You would then need to add a set of INSTEAD OF triggers to the VIEW to abstract the fact that one entity, dbo.Personnel, is now implemented as multiple tables and operations like INSERT, UPDATE and DELETE can continue to function as they currently do:
CREATE TRIGGER dbo.Personnel_i ON dbo.Personnel
INSTEAD OF INSERT
AS
BEGIN
SET NOCOUNT ON;
-- logic to insert into dbo.PersonnelDetail and dbo.PersonnelImage separately from the INSERTED table
END
GO
CREATE TRIGGER dbo.Personnel_u ON dbo.Personnel
INSTEAD OF UPDATE
AS
BEGIN
SET NOCOUNT ON;
-- logic to update dbo.PersonnelDetail and dbo.PersonnelImage separately from the INSERTED and DELETED tables
END
GO
CREATE TRIGGER dbo.Personnel_d ON dbo.Personnel
INSTEAD OF DELETE
AS
BEGIN
SET NOCOUNT ON;
-- logic to delete from dbo.PersonnelDetail and dbo.PersonnelImage separately from the DELETED table
END
GO
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
June 26, 2012 at 3:45 pm
opc.three (6/26/2012)
Keeping all data in one table, but using a separate filegroup for blobs is a good way to go, and simple.
Actually, it's not. You can't rebuild an index using the "ONLINE" option if a blob is presennt in the table.
--Jeff Moden
Change is inevitable... Change for the better is not.
June 26, 2012 at 3:47 pm
Jeff Moden (6/26/2012)
opc.three (6/26/2012)
Keeping all data in one table, but using a separate filegroup for blobs is a good way to go, and simple.Actually, it's not. I know you brought up "index maintenance" but I want to be sure folks know... You can't rebuild an index using the "ONLINE" option if a blob is presennt in the table.
--Jeff Moden
Change is inevitable... Change for the better is not.
June 26, 2012 at 4:32 pm
Jeff Moden (6/26/2012)
Jeff Moden (6/26/2012)
opc.three (6/26/2012)
Keeping all data in one table, but using a separate filegroup for blobs is a good way to go, and simple.Actually, it's not. I know you brought up "index maintenance" but I want to be sure folks know... You can't rebuild an index using the "ONLINE" option if a blob is presennt in the table.
You're absolutely right. "Online" index maintenance is exactly what I had in mind when I said this:
But, it means you have to manage the table as a single entity which can affect things like index maintenance and backups.
Thank you for highlighting that point, it is a very important one when making the decision.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply