SQL Server Performance Forum – Threads Archive

How to handle this problem in partition table ?

Could you tell what’s wrong when I split table to the objective partition table?
USE TEST –ADD FILEGROUP———————————————————————
ALTER DATABASE TEST ADD FILEGROUP FG_01
ALTER DATABASE TEST ADD FILEGROUP FG_02
ALTER DATABASE TEST ADD FILEGROUP FG_03 –ADD FILE————————————————————————–
ALTER DATABASE TEST ADD FILE (NAME = DF_01,
FILENAME = ‘D:TESTDF_01.ndf’,
SIZE = 10MB,
MAXSIZE = UNLIMITED,
FILEGROWTH = 10MB)
TO FILEGROUP FG_01 ALTER DATABASE TEST ADD FILE (NAME = DF_02,
FILENAME = ‘D:TESTDF_02.ndf’,
SIZE = 10MB,
MAXSIZE = UNLIMITED,
FILEGROWTH = 10MB)
TO FILEGROUP FG_02 ALTER DATABASE TEST ADD FILE (NAME = DF_03,
FILENAME = ‘D:TESTDF_03.ndf’,
SIZE = 10MB,
MAXSIZE = UNLIMITED,
FILEGROWTH = 10MB)
TO FILEGROUP FG_03
–CREATE PARTITION FUNCTION———————————————————
CREATE PARTITION FUNCTION PF_HIS_HTTP_LOG(datetime)
AS
RANGE LEFT FOR VALUES (
‘20070101 23:59:59.997’,
‘20070102 23:59:59.997’) –CREATE PARTITION SCHEME———————————————————–
CREATE PARTITION SCHEME PS_HIS_HTTP_LOG
AS
PARTITION PF_HIS_HTTP_LOG TO
( FG_01, FG_02, [PRIMARY]) –CREATE PARTITION TABLE ———————————————————–
CREATE TABLE HIS_HTTP_LOG
( USERID varchar(32) ,
USERIP varchar(15) ,
USERPORT numeric(5,0) ,
OBJECTIP varchar(15) ,
OBJECTPORT numeric(5,0) ,
URL varchar(256) ,
HOST varchar(64) ,
DN varchar(64) ,
VISITIME numeric(5,0) ,
STARTIME datetime ,
ENDTIME datetime
) ON PS_HIS_HTTP_LOG(STARTIME) –INSERT DATA£¬PARTITION 1 20070101————————————————-
DECLARE @i int
SET @i = 1
WHILE @i <= 100
BEGIN
INSERT INTO HIS_HTTP_LOG VALUES(CAST(@i AS varchar(32)),’192.168.1.1′,5,’202.103.1.57′,
6,’http://sina.com.cn’,”,’www.sohu.com’,11,CONVERT(datetime,’20070101 13:25:26.100′,121),GETDATE())
SET @i = @i +1
END –INSERT DATA ,PARTITION 2 20070102————————————————-
SET @i = 1
WHILE @i <= 200
BEGIN
INSERT INTO HIS_HTTP_LOG VALUES(CAST(@i AS varchar(32)),’192.168.1.1′,5,’202.103.1.57′,
6,’http://sina.com.cn’,”,’www.sohu.com’,11,CONVERT(datetime,’20070102 11:25:26.100′,121),GETDATE())
SET @i = @i +1
END –CREATE A TABLE ——————————————————————-
CREATE TABLE TMP_HTTP_LOG
( USERID varchar(32) ,
USERIP varchar(15) ,
USERPORT numeric(5,0) ,
OBJECTIP varchar(15) ,
OBJECTPORT numeric(5,0) ,
URL varchar(256) ,
HOST varchar(64) ,
DN varchar(64) ,
VISITIME numeric(5,0) ,
STARTIME datetime ,
ENDTIME datetime
) ON FG_03 –INSERT DATA TO TMP_HTTP_LOG 20070103———————————————
–DECLARE @i int
SET @i = 1
WHILE @i <= 400
BEGIN
INSERT INTO TMP_HTTP_LOG VALUES(CAST(@i AS varchar(32)),’192.168.1.1′,5,’202.103.1.57′, 6,’http://sina.com.cn’,”,’www.sohu.com’,11,CONVERT(datetime,’20070103 09:25:26.100′,121),GETDATE())
SET @i = @i +1
END –ADD CONSTRAINT——————————————————————–
ALTER TABLE TMP_HTTP_LOG
WITH CHECK
ADD CONSTRAINT CK001
CHECK (STARTIME >= ‘20070103 00:00:00.000’
AND STARTIME <= ‘20070103 23:59:59.997’) –SPLIT RANGE ,SWITCH DATA———————————————————-
ALTER PARTITION SCHEME PS_HIS_HTTP_LOG NEXT USED FG_03
ALTER PARTITION FUNCTION PF_HIS_HTTP_LOG() SPLIT RANGE (‘20070103 23:59:59.997’)
ALTER TABLE TMP_HTTP_LOG SWITCH TO HIS_HTTP_LOG PARTITION 3 –==========================================£½======================================
Why is the error in step of¡°ALTER TABLE TMP_HTTP_LOG SWITCH TO HIS_HTTP_LOG PARTITION 3¡±
error infomation£º
message_id 4972£¬level 16£¬severity 1
ALTER TABLE SWITCH statement failed. Check constraints or partition function of source table ‘TEST.dbo.TMP_HTTP_LOG’ allows values that are not allowed by check constraints or partition function on target table ‘TEST.dbo.HIS_HTTP_LOG’.
Please tell me why ?
Thank you very much !

---

Your StartTime column on both the parttitioned table and the temp table must NOT accept NULL’s. Ie change the create table statement’s to "STARTTIME datetime NOT NULL," Everything else looks fine. Simon

---

My friend ,simondm:
thank you very much !
the problem is handled,just as you said,"STARTIME datetime null" => "STARTIME datetime not null" appreciate your answer!

---

]]>