Van | : | John Kopmels (Sysop) | Datum | : | 21-09-2005 |
Aan | : | Allen | MsgID | : | 2489.1 |
Onderwerp | : | SQLserver Null,0 en warning | Forum | : | ws-nlcomputer |
CREATE TABLE [ATest] (
[IID] [tinyint] NOT NULL ,
[VASTGESTWAARDE] [int] NULL ,
[GETAXWAARDE] [int] NULL ,
CONSTRAINT [PK_ATest] PRIMARY KEY CLUSTERED
([IID]) ON [PRIMARY]
) ON [PRIMARY]
INSERT INTO [Atest] ([IID],[VASTGESTWAARDE],[GETAXWAARDE])VALUES(1,120567,150500)
INSERT INTO [Atest] ([IID],[VASTGESTWAARDE],[GETAXWAARDE])VALUES(2,128000,NULL)
INSERT INTO [Atest] ([IID],[VASTGESTWAARDE],[GETAXWAARDE])VALUES(3,11,150500)
INSERT INTO [Atest] ([IID],[VASTGESTWAARDE],[GETAXWAARDE])VALUES(4,100000,150500)
INSERT INTO [Atest] ([IID],[VASTGESTWAARDE],[GETAXWAARDE])VALUES(5,0,150500)
INSERT INTO [Atest] ([IID],[VASTGESTWAARDE],[GETAXWAARDE])VALUES(6,NULL,150500)
SELECT IID, VASTGESTWAARDE, GETAXWAARDE,
COALESCE (GETAXWAARDE, 0) - COALESCE (VASTGESTWAARDE, 0) AS S,
CAST(COALESCE (GETAXWAARDE,0) - COALESCE (VASTGESTWAARDE, 0) AS float) /
CAST(CAST(COALESCE (VASTGESTWAARDE, 0) AS float) / 100 AS float) AS P
FROM dbo.ATest
Van | : | Hugo Kornelis | Datum | : | 22-09-2005 |
Aan | : | John Kopmels (Sysop) | MsgID | : | 2489.2 |
Onderwerp | : | SQLserver Null,0 en warning | Forum | : | ws-nlcomputer |
CREATE TABLE TeLang
(Col1 char(200),
Col2 char(8000)
)
go
Server: Msg 1701, Level 16, State 2, Line 1
Creation of table 'SomsTeLang' failed because the row size would be 8221, including internal overhead. This exceeds the maximum allowable table row size, 8060.
Maar met behulp van variabele lengte velden kun je ook een tabel maken met een minimale kolomlengte van minder van 8060 bytes, en een maximale lengte van meer dan 8060 bytes. Dit is toegestaan, maar je krijgt wel een waarschuwing omdat er in het gebruik onverwachte fouten kunnen optreden:CREATE TABLE SomsTeLang
(Col1 char(200),
Col2 varchar(8000)
)
go
Warning: The table 'SomsTeLang' has been created but its maximum row size (8223) exceeds the maximum number of bytes per row (8060). INSERT or UPDATE of a row in this table will fail if the resulting row length exceeds 8060 bytes.
INSERT INTO SomsTeLang (Col1, Col2)
VALUES ('Niet te lang', REPLICATE('A', 800))
go
(1 row(s) affected)
INSERT INTO SomsTeLang (Col1, Col2)
VALUES ('Wel te lang', REPLICATE('A', 8000))
go
Server: Msg 511, Level 16, State 1, Line 1
Cannot create a row of size 8211 which is greater than the allowable maximum of 8060.
The statement has been terminated.
SELECT * FROM SomsTeLang
go
Col1 / / Col2
------------------/ /-- ------------------------------------------------/ /------
Niet te lang / / AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA/ /AAAAAA
NB: Ik heb deze regel handmatig ingekort.SELECT IID, VASTGESTWAARDE, GETAXWAARDE,
COALESCE (GETAXWAARDE, 0) - COALESCE (VASTGESTWAARDE, 0) AS S,
CAST(100 AS float) * (COALESCE (GETAXWAARDE, 0) - COALESCE (VASTGESTWAARDE, 0)) / NULLIF(VASTGESTWAARDE, 0) AS P
FROM dbo.ATest
Groetjes, HugoVan | : | John Kopmels (Sysop) | Datum | : | 23-09-2005 |
Aan | : | Hugo Kornelis | MsgID | : | 2489.3 |
Onderwerp | : | SQLserver Null,0 en warning | Forum | : | ws-nlcomputer |
CREATE TABLE [tblMemo] (
[WoZnummer] [bigint] NOT NULL ,
[Volgnr] [tinyint] NOT NULL CONSTRAINT [DF_tblMemo_Volgnr] DEFAULT (1),
[Wie] [varchar] (25) NULL CONSTRAINT [DF_tblMemo_Wie] DEFAULT (suser_sname()),
[Memo] [varchar] (7950) NULL ,
[Datum] [smalldatetime] NULL CONSTRAINT [DF_tblMemo_Datum] DEFAULT (getdate()),
[Belang] [varchar] (10) NULL CONSTRAINT [DF_tblMemo_Belang] DEFAULT ('Normaal'),
[Afgeschermd] [char] (1) NULL CONSTRAINT [DF_tblMemo_Afgeschermd] DEFAULT ('N'),
CONSTRAINT [PK_tblMemo] PRIMARY KEY CLUSTERED
([WoZnummer],[Volgnr]) ON [PRIMARY] ,
CONSTRAINT [CK_tblMemo] CHECK ([Belang] = 'Hoog' or
([Belang] = 'Normaal' or
[Belang] = 'Laag')),
CONSTRAINT [CK_tblMemo_1] CHECK ([Afgeschermd] = 'J' or
[Afgeschermd] = 'N')
) ON [PRIMARY]
Van | : | Hugo Kornelis | Datum | : | 23-09-2005 |
Aan | : | John Kopmels (Sysop) | MsgID | : | 2489.4 |
Onderwerp | : | SQLserver Null,0 en warning | Forum | : | ws-nlcomputer |
CREATE TABLE test (col1 char(4) NOT NULL PRIMARY KEY,
col2 varchar(8000) NULL,
col3 char(40) NULL)
go
INSERT test (col1, col2, col3)
SELECT 'aaaa', REPLICATE('b', 8000), REPLICATE('c', 40)
go
SELECT * FROM test
go
DROP TABLE test
go
Van | : | John Kopmels (Sysop) | Datum | : | 23-09-2005 |
Aan | : | Hugo Kornelis | MsgID | : | 2489.5 |
Onderwerp | : | SQLserver Null,0 en warning | Forum | : | ws-nlcomputer |
Van | : | Hugo Kornelis | Datum | : | 23-09-2005 |
Aan | : | John Kopmels (Sysop) | MsgID | : | 2489.6 |
Onderwerp | : | SQLserver Null,0 en warning | Forum | : | ws-nlcomputer |
Van | : | John Kopmels (Sysop) | Datum | : | 23-09-2005 |
Aan | : | Hugo Kornelis | MsgID | : | 2489.7 |
Onderwerp | : | SQLserver Null,0 en warning | Forum | : | ws-nlcomputer |