I am getting a check constraint error on the following query.
INSERT TABLEA
( COL1, COL2, COL3)
SELECT dbo.Function1(),-20000, (dbo.Function1() + (-20000) )
There is a check constraint on COL3 ( COL3 >= 0 ) and dbo.Function() is
returning 20000. All columns are of datatype INT as is the return value of
the function.
If i replace the function call with a literal 0, it works, but having the
function in there violates the constraint on COL3, despite the value still
being 0.
Does anyone have any idea why this is happening? I have tried making COL3 a
computed column, but can't have a constraint on a computed column, tried
making the select statement into a derived table and selecting from that int
o
my insert statement.
This is being done on SQL Server 2000 Enterprise.
Thank you
Clint ColefaxHi
What is the error that you received.
and what was displayed when you tried:
SELECT dbo.Function1(),-20000, (dbo.Function1() + (-20000) )
best Regards,
Chandra
http://chanduas.blogspot.com/
http://groups.msn.com/SQLResource/
---
"Clint Colefax" wrote:
> I am getting a check constraint error on the following query.
> INSERT TABLEA
> ( COL1, COL2, COL3)
> SELECT dbo.Function1(),-20000, (dbo.Function1() + (-20000) )
> There is a check constraint on COL3 ( COL3 >= 0 ) and dbo.Function() is
> returning 20000. All columns are of datatype INT as is the return value of
> the function.
> If i replace the function call with a literal 0, it works, but having the
> function in there violates the constraint on COL3, despite the value still
> being 0.
> Does anyone have any idea why this is happening? I have tried making COL3
a
> computed column, but can't have a constraint on a computed column, tried
> making the select statement into a derived table and selecting from that i
nto
> my insert statement.
> This is being done on SQL Server 2000 Enterprise.
> Thank you
> Clint Colefax
>
>|||The error received was a violation of check constraint.
INSERT statement conflicted with COLUMN CHECK constraint...
Execute that select statement returns as expected
20000, -20000, 0
Thank you
Clint Colefax|||Hi
Can you try as
INSERT INTO TABLEA
SELECT dbo.Function1(),-20000, dbo.Function1() + (-20000)
best Regards,
Chandra
http://chanduas.blogspot.com/
http://groups.msn.com/SQLResource/
---
"Clint Colefax" wrote:
> The error received was a violation of check constraint.
> INSERT statement conflicted with COLUMN CHECK constraint...
> Execute that select statement returns as expected
> 20000, -20000, 0
> Thank you
> Clint Colefax
>|||What data types are the columns and the function?
If they are not of an exact type, but e.g. "real", the third expression may
evaluate to a value slightly below 0, although it is displayed as 0. CAST to
integer to avoid this problem.
I hope this helps!
Martin
"Clint Colefax" <ClintColefax@.discussions.microsoft.com> wrote in message
news:42D1D89D-9EA0-4F00-8F91-D8D55732C387@.microsoft.com...
>I am getting a check constraint error on the following query.
> INSERT TABLEA
> ( COL1, COL2, COL3)
> SELECT dbo.Function1(),-20000, (dbo.Function1() + (-20000) )
> There is a check constraint on COL3 ( COL3 >= 0 ) and dbo.Function() is
> returning 20000. All columns are of datatype INT as is the return value of
> the function.
> If i replace the function call with a literal 0, it works, but having the
> function in there violates the constraint on COL3, despite the value still
> being 0.
> Does anyone have any idea why this is happening? I have tried making COL3
> a
> computed column, but can't have a constraint on a computed column, tried
> making the select statement into a derived table and selecting from that
> into
> my insert statement.
> This is being done on SQL Server 2000 Enterprise.
> Thank you
> Clint Colefax
>
>|||On Mon, 30 May 2005 19:19:41 -0700, Clint Colefax wrote:
>I am getting a check constraint error on the following query.
>INSERT TABLEA
>( COL1, COL2, COL3)
>SELECT dbo.Function1(),-20000, (dbo.Function1() + (-20000) )
>There is a check constraint on COL3 ( COL3 >= 0 ) and dbo.Function() is
>returning 20000. All columns are of datatype INT as is the return value of
>the function.
Hi Clint,
I could not reproduce this behaviour (see repro script below). Could you
post a repro script for me to run and reproduce the error?
>I have tried making COL3 a
>computed column, but can't have a constraint on a computed column,
If Col3 is always equal to Col1 - Col2, you should make it a computed
column. You can replace the check constraint wiuth the following
equivalent:
CHECK (Col1 >= Col2)
Here's the script I used to try to reproduce your problem, and the
output I got from it:
create table TableA
(Col1 int not null,
Col2 int not null,
Col3 int not null,
PRIMARY KEY (Col1),
CHECK (Col3 >= 0)
)
go
create function dbo.Function1()
returns int
as
begin
return 20000
end
go
INSERT TableA
( Col1, Col2, Col3)
SELECT dbo.Function1(),-20000, (dbo.Function1() + (-20000) )
go
select * from TableA
go
drop function dbo.Function1
go
drop table TableA
go
Col1 Col2 Col3
-- -- --
20000 -20000 0
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||I gave the earlier example as I didn't want to post all of the infrastructur
e
around this problem. With the following cut down version, I am still able to
replicate the problem.
CREATE TABLE test (
MOVEMENT_NO int IDENTITY (1, 1) NOT NULL ,
FACTORY_STATIONERY_NO int NOT NULL ,
INITIAL_VALUE int NOT NULL ,
MOVEMENT int NOT NULL ,
FINAL_VALUE int NOT NULL ,
CREATION_DATE smalldatetime not null
CONSTRAINT testPK PRIMARY KEY NONCLUSTERED ( MOVEMENT_NO ) ,
CHECK (FINAL_VALUE >= 0),
CHECK (INITIAL_VALUE >= 0)
)
INSERT TEST
VALUES( 46, 0, 50000, 50000, '2004-12-11 11:21:00' )
INSERT TEST
VALUES( 46, 50000, -30000, 20000, '2004-12-13 15:34:00' )
CREATE FUNCTION fntest( @.factory_stationery_no INT )
RETURNS INT AS
BEGIN
RETURN ISNULL( ( SELECT TOP 1 FINAL_VALUE
FROM dbo.test
WHERE FACTORY_STATIONERY_NO = @.factory_stationery_no
ORDER BY creation_date desc, MOVEMENT_NO DESC ), 0 )
END
INSERT TEST
SELECT 46 AS FACTORY_STATIONERY_NO,
DBO.FNTEST(46),
-20000 AS MOVEMENT,
DBO.FNTEST(46) + (-20000),
GETDATE()
DROP FUNCTION FNTEST
DROP TABLE TEST
Thank you
Clint Colefax|||Sorry, I didn't think out my example very well, the following is code that
should reproduce the error.
CREATE TABLE test (
MOVEMENT_NO int IDENTITY (1, 1) NOT NULL ,
FACTORY_STATIONERY_NO int NOT NULL ,
INITIAL_VALUE int NOT NULL ,
MOVEMENT int NOT NULL ,
FINAL_VALUE int NOT NULL ,
CREATION_DATE smalldatetime not null
CONSTRAINT testPK PRIMARY KEY NONCLUSTERED ( MOVEMENT_NO ) ,
CHECK (FINAL_VALUE >= 0),
CHECK (INITIAL_VALUE >= 0)
)
INSERT TEST
VALUES( 46, 0, 50000, 50000, '2004-12-11 11:21:00' )
INSERT TEST
VALUES( 46, 50000, -30000, 20000, '2004-12-13 15:34:00' )
CREATE FUNCTION fntest( @.factory_stationery_no INT )
RETURNS INT AS
BEGIN
RETURN ISNULL( ( SELECT TOP 1 FINAL_VALUE
FROM dbo.test
WHERE FACTORY_STATIONERY_NO = @.factory_stationery_no
ORDER BY creation_date desc, MOVEMENT_NO DESC ), 0 )
END
INSERT TEST
SELECT 46 AS FACTORY_STATIONERY_NO,
DBO.FNTEST(46),
-20000 AS MOVEMENT,
DBO.FNTEST(46) + (-20000),
GETDATE()
DROP FUNCTION FNTEST
DROP TABLE TEST|||Thank you but all datatype are of INT, all match, even using a CAST or
CONVERT statement does not get around the problem (had previouslty attempted
).
Thank you for your input
Clint Colefax|||It's a bug, and a surprising one:
CREATE TABLE TEST (
a int not null,
b smalldatetime not null,
constraint finalv CHECK (a >= 0)
)
go
INSERT TEST(a) SELECT 1
Gives this error:
Server: Msg 515, Level 16, State 2, Line 1
Cannot insert the value NULL into column 'b', table 'tempdb.dbo.TEST';
column does not allow nulls. INSERT fails.
The statement has been terminated.
Can you post the result of
SELECT @.@.version
so we can see what version you're running?
I verified this on 8.00.2039, and will report it to Microsoft.
SK
Clint Colefax wrote:
>there error is a check constraint violation for the FINAL_VALUE constraint.
>Server: Msg 547, Level 16, State 1, Line 1
>INSERT statement conflicted with COLUMN CHECK constraint
>'CK__test__FINAL_VALU__3AA27A0F'. The conflict occurred in database
>'LIPSDev', table 'test', column 'FINAL_VALUE'.
>The statement has been terminated.
>Thank you
>
Showing posts with label col2. Show all posts
Showing posts with label col2. Show all posts
Sunday, March 11, 2012
Sunday, February 12, 2012
char(1) and char(2) take same space?
I create two tables:
create table table1
(
col1 char(1)
)
go
create table table2
(
col2 char(2)
)
go
I add some records to two tables after createing operation completed.
Then i use dbcc page command to oversee the structures of data page in two tables.
I found some interest things:
The rows in two tabes take up same space:9 bytes
You can see the "9" on top of the data, for example:Slot 0, Offset 0x60, Length 9, DumpStyle BYTE
or calculate from the offset array
Any suggestions?
Did you enter same type of data in both tables?|||
yes
And you can found that :the two tables can both contain as many as 699 rows per data page.
|||That surprising. Char(2) suppose to use 2 bytes and Char(1) suppose to use 1.Can you tell me how many records you have in both tables?
|||
the sql script i used:
declare @.a int
set @.a=0
while @.a<4000
begin
insert into table1 values('a')
set @.a=@.a+1
end
-
declare @.a int
set @.a=0
while @.a<4000
begin
insert into table2 values('aa')
set @.a=@.a+1
end
the rows in two tables both take up 9 bytes no matter how many rows you insertd.
|||I assume you're only adding single character values to each table?
If thats the case, then you more than likely have the value SET ANSI_PADDING OFF when you created your tables. With this setting, any nullable char columns will be treated like varchar columns.
HTH!
|||sorry, i don't understand what you say...
|||char(1) takes 1 byte, while char(2) takes up two bytes.However, there is one other thing to consider: The minimum record size that SQL Server uses for data records is 9 bytes. Data records are records that are either records that are stored on leaf-pages on a B-Tree or are records stored in a heap.
The reason that the data record is 9 bytes is that for heaps we need to guarantee that the RID that is used to uniquely identify a row in a heap is never changed. To do this, SQL Server needs to be able to store at least a forwarding pointer on a page. A forwarding pointer is 9 bytes (1 byte header, and 8 byte RID of the actual record.
For B-Trees, the 9 byte limit is used as well. The reason here is that SQL Server quickly likes to convert a B-Tree to a heap without having to touch all the individual records. This is only possible if we can guarantee that the records are at least 9 bytes long.
To really see the difference between char(1) and char(2), create a record that is more than 9 bytes (say add a char(9) to it), and then create one table with the char(1) and another one with char(2). You'll see that the record size will be different.
Thanks,|||
Thats interesting stuff, Marcel.
However, can you clarify one thing. If each record stores a minimum of 9bytes per record, would you not expect the value from dbcc page to show 9 + the length of the data.
eg In this example, 10 (9bytes forwarding pointer and 1byte char(1) or 11 (9bytes forwarding pointer and 2bytes char(2))
Cheers
|||The forwarding pointer is only stored if it is needed. In case a record is not forwarded, we don't store the forwarding pointer.
In a heap we only forward if a record gets updated, and the updated record does not fit on the page anymore. IN this case, we move the record to a new page, but leave a 9 byte forwarding pointer record on the original page.
DBCC PAGE shows the actual length of a record.
Suppose we have the following table:
CREATE TABLE t1 (col1 char(1)).
When a record is created, the actual size is 2 bytes header, 2 bytes for where null bitmap starts, 1 byte for null bit map, and 1 byte for the actual char(1) data. So the record would be 6 bytes. However, because a record needs to be at least 9 bytes, we make the record size 9 bytes.
Suppose now that we had the following table
CREATE TABLE t1 (col1 char(10)).In this case the length of a record would be 2 bytes ehader, 2 byes for where nullbitmap starts, 1 byte for null bit map and 10 bytes for actual data. So the record would be 15 bytes.
Thanks,
|||HI Marcel
Why sql server need forwarding pointer?
Only point the new Page which the original record stored?
We can use IAM to find all the pages of table? why need forwarding pointer?
thanks
|||Forwarding pointers are used for heaps in case an row that was placed on a certain page gets updated and does not fit on the page anymore.
Let's go through an example.
Suppose there is a row on Page 1:100, and the row is placed in slot 0 on that page. In this case, the row ID (RID) is 1:100:0. This rowid is used to uniquely identify the row going forward.
Suppose not that the heap has a number of non-clustered indexes defined on it. The leaf pages of the non-clustered index contain the RID of the actual row. When the non-clustered index is traversed, and a leaf row is reached, SQL Server uses the RID to find the actual data for that row. So in the above example, SQL reads RID 1:100:0, and knows that it has to go to page 1:100, slot 0.
Let's now assume that the original row gets updated, and that because of update the row grows and does not fit on page 1:100 anymore (keep in mind that there are other rows on page 1:100 as well which take up spaces).
At this point, SQL Server could do two things:
1) It could move the row to a new page, and update all the RIDs in the non-clustered indexes for the original row. This is a very expensive operation, and thus SQL Server does not do this.
2) To keep the RID the same, SQL Server moves the row to a new page (for instance, 2:150:0, i.e. page 2:150, slot 0). On the original location (1:100:0), it keeps a very small, 9 byte forwarding pointer that points to slot 2:150:0.
Now when the data is retrieved, SQL Server reads the forwarding record, and uses the infromation in the forwarding record to find the actual record which is places on 2:150:0.
Because of this, SQL Server needs to guarantee that it can put at least a 9-byte forwarding record on any page that is used by an existing row, meaning that if a row is less than 9-byte, it will reserve 9 bytes anyways.
If the explanation is still unclear, it might help to have a look at the book 'Inside SQL Server 2005: the storage engine' by Kalen Delaney (http://www.amazon.com/Inside-Microsoft-SQL-Server-2005/dp/0735621055/ref=pd_bbs_sr_1/002-2072619-2748829?ie=UTF8&s=books&qid=1187755949&sr=8-1).
The book has a better description than I can give here (I think there are about 10 pages or so about different record formats, etc).
Thanks,
|||Marcel
thanks for your reply
char(1) and char(2) take same space?
I create two tables:
create table table1
(
col1 char(1)
)
go
create table table2
(
col2 char(2)
)
go
I add some records to two tables after createing operation completed.
Then i use dbcc page command to oversee the structures of data page in two tables.
I found some interest things:
The rows in two tabes take up same space:9 bytes
You can see the "9" on top of the data, for example:Slot 0, Offset 0x60, Length 9, DumpStyle BYTE
or calculate from the offset array
Any suggestions?
Did you enter same type of data in both tables?|||
yes
And you can found that :the two tables can both contain as many as 699 rows per data page.
|||That surprising. Char(2) suppose to use 2 bytes and Char(1) suppose to use 1.Can you tell me how many records you have in both tables?
|||
the sql script i used:
declare @.a int
set @.a=0
while @.a<4000
begin
insert into table1 values('a')
set @.a=@.a+1
end
-
declare @.a int
set @.a=0
while @.a<4000
begin
insert into table2 values('aa')
set @.a=@.a+1
end
the rows in two tables both take up 9 bytes no matter how many rows you insertd.
|||I assume you're only adding single character values to each table?
If thats the case, then you more than likely have the value SET ANSI_PADDING OFF when you created your tables. With this setting, any nullable char columns will be treated like varchar columns.
HTH!
|||sorry, i don't understand what you say...
|||char(1) takes 1 byte, while char(2) takes up two bytes.However, there is one other thing to consider: The minimum record size that SQL Server uses for data records is 9 bytes. Data records are records that are either records that are stored on leaf-pages on a B-Tree or are records stored in a heap.
The reason that the data record is 9 bytes is that for heaps we need to guarantee that the RID that is used to uniquely identify a row in a heap is never changed. To do this, SQL Server needs to be able to store at least a forwarding pointer on a page. A forwarding pointer is 9 bytes (1 byte header, and 8 byte RID of the actual record.
For B-Trees, the 9 byte limit is used as well. The reason here is that SQL Server quickly likes to convert a B-Tree to a heap without having to touch all the individual records. This is only possible if we can guarantee that the records are at least 9 bytes long.
To really see the difference between char(1) and char(2), create a record that is more than 9 bytes (say add a char(9) to it), and then create one table with the char(1) and another one with char(2). You'll see that the record size will be different.
Thanks,|||
Thats interesting stuff, Marcel.
However, can you clarify one thing. If each record stores a minimum of 9bytes per record, would you not expect the value from dbcc page to show 9 + the length of the data.
eg In this example, 10 (9bytes forwarding pointer and 1byte char(1) or 11 (9bytes forwarding pointer and 2bytes char(2))
Cheers
|||The forwarding pointer is only stored if it is needed. In case a record is not forwarded, we don't store the forwarding pointer.
In a heap we only forward if a record gets updated, and the updated record does not fit on the page anymore. IN this case, we move the record to a new page, but leave a 9 byte forwarding pointer record on the original page.
DBCC PAGE shows the actual length of a record.
Suppose we have the following table:
CREATE TABLE t1 (col1 char(1)).
When a record is created, the actual size is 2 bytes header, 2 bytes for where null bitmap starts, 1 byte for null bit map, and 1 byte for the actual char(1) data. So the record would be 6 bytes. However, because a record needs to be at least 9 bytes, we make the record size 9 bytes.
Suppose now that we had the following table
CREATE TABLE t1 (col1 char(10)).In this case the length of a record would be 2 bytes ehader, 2 byes for where nullbitmap starts, 1 byte for null bit map and 10 bytes for actual data. So the record would be 15 bytes.
Thanks,
|||HI Marcel
Why sql server need forwarding pointer?
Only point the new Page which the original record stored?
We can use IAM to find all the pages of table? why need forwarding pointer?
thanks
|||Forwarding pointers are used for heaps in case an row that was placed on a certain page gets updated and does not fit on the page anymore.
Let's go through an example.
Suppose there is a row on Page 1:100, and the row is placed in slot 0 on that page. In this case, the row ID (RID) is 1:100:0. This rowid is used to uniquely identify the row going forward.
Suppose not that the heap has a number of non-clustered indexes defined on it. The leaf pages of the non-clustered index contain the RID of the actual row. When the non-clustered index is traversed, and a leaf row is reached, SQL Server uses the RID to find the actual data for that row. So in the above example, SQL reads RID 1:100:0, and knows that it has to go to page 1:100, slot 0.
Let's now assume that the original row gets updated, and that because of update the row grows and does not fit on page 1:100 anymore (keep in mind that there are other rows on page 1:100 as well which take up spaces).
At this point, SQL Server could do two things:
1) It could move the row to a new page, and update all the RIDs in the non-clustered indexes for the original row. This is a very expensive operation, and thus SQL Server does not do this.
2) To keep the RID the same, SQL Server moves the row to a new page (for instance, 2:150:0, i.e. page 2:150, slot 0). On the original location (1:100:0), it keeps a very small, 9 byte forwarding pointer that points to slot 2:150:0.
Now when the data is retrieved, SQL Server reads the forwarding record, and uses the infromation in the forwarding record to find the actual record which is places on 2:150:0.
Because of this, SQL Server needs to guarantee that it can put at least a 9-byte forwarding record on any page that is used by an existing row, meaning that if a row is less than 9-byte, it will reserve 9 bytes anyways.
If the explanation is still unclear, it might help to have a look at the book 'Inside SQL Server 2005: the storage engine' by Kalen Delaney (http://www.amazon.com/Inside-Microsoft-SQL-Server-2005/dp/0735621055/ref=pd_bbs_sr_1/002-2072619-2748829?ie=UTF8&s=books&qid=1187755949&sr=8-1).
The book has a better description than I can give here (I think there are about 10 pages or so about different record formats, etc).
Thanks,
|||Marcel
thanks for your reply
Subscribe to:
Posts (Atom)