Hallo

Welkom, Gast. Alsjeblieft inloggen of registreren.

Recent

210 gasten, 0 leden

Welkom, Gast. Alsjeblieft inloggen of registreren.

28 maart 2024, 13:47:26

Login met gebruikersnaam, wachtwoord en sessielengte

Nieuws

Welkom op het vernieuwde NL Computer Forum!

Auteur Topic: SQLserver Null,0 en warning  (gelezen 19853 keer)

0 leden en 1 gast bekijken dit topic.

Offline NLCOMP

  • Forumheld
  • *****
  • Berichten: 14.666
    • NL Computer Forum
SQLserver Null,0 en warning
« Gepost op: 9 november 2009, 19:47:25 »
Bericht 1 van 7

NL Computer Forum ~ SQL & Programmeren
 Van:John Kopmels (Sysop)Datum:21-09-2005
 Aan:AllenMsgID:2489.1
 Onderwerp:SQLserver Null,0 en warningForum:ws-nlcomputer
Hoi Hugo en anderen ,

ik heb 2 vragen:

1) In een tabel heb ik een kolom varchar(8000) gemaakt (dus ietsje ruimte over voor de max is bereikt)

Steeds bij een actie bv index aanmaken od krijg ik nu de waarschuwing :

Warning: The table 'tblMemo' has been created but its maximum row size (8076) 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.

enig idee ?

2) in een query wil ik een stijging en de procentuele stijging weergeven, alleen
    zitten er NULL en 0 waardes in de tabel waarvan ik toch de rijen weer wil geven

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

struikelt nog steeds over de data, hoe los ik dat beter op (liefst in een View)

bvb mijn dank en groetjes -- John





Bericht 2 van 7

NL Computer Forum ~ SQL & Programmeren
 Van:Hugo KornelisDatum:22-09-2005
 Aan:John Kopmels (Sysop)MsgID:2489.2
 Onderwerp:SQLserver Null,0 en warningForum:ws-nlcomputer
Hoi John,
Steeds bij een actie bv index aanmaken od krijg ik nu de waarschuwing :

Warning: The table 'tblMemo' has been created but its maximum row size (8076) 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.

enig idee ?

Al het lezen van disk en schrijven naar disk gaat in SQL Server in eenheden van 8K. Zo'n eenheid heet een page. Een data page (da's dus een page waar de gegevens uit een tabel in staan) heeft het een en ander aan overhead; van de 8K blijven er 8060 bytes over voor de echte gegevens. Als je rijen dus bv. 800 bytes lang zijn, dan kun je 10 rijen in een page kwijt, en de rest van de ruimte blijft onbenut.
SQL Server kan de gegevens van één rij niet over meerdere pages verdelen (uitzondering: datatypes text, ntext en image die een aparte behandeling krijgen en tot 2GB aan gegevens kunnen opslaan). Daarmee is de maximale lengte voor een rij dus 8060 bytes. Dat is inclusief de lengte-bytes voor variabele lengte kolommen en de bit-index voor NULL kolommen.
SQL Server zal je nooit toestaan een tabel te maken waarvan de lengte per definitie deze grens overschrijdt: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.

DROP TABLE SomsTeLang
go
Het verbaast me overigens wel dat je deze melding krijgt tijdens het maken van indexen en zo. Gebruik je wellicht Enterprise Manager voor dergelijke acties? Zo ja, dan is mijn advies: niet meer doen. EM heeft nog al eens de neiging om veranderingen door te voeren door de oude tabel te renamen, een nieuwe tabel aan te maken, alle constraints en triggers opnieuw aan te maken, alle inhoud over te pompen en dan de oude tabel te verwijderen. Niet leuk, vooral niet op een intensief gebruikt productiesysteem met een paar miljoen rijen in de tabel! <g>
2) in een query wil ik een stijging en de procentuele stijging weergeven, alleen
    zitten er NULL en 0 waardes in de tabel waarvan ik toch de rijen weer wil geven

Aan je voorbeeldgegevens te zien wil je NULL als 0 behandelen (geen probleem), en kunnen hierbij ook stijgingen van nul naar iets voorkomen (wel een probleem - want hoeveel procent van 0 is 10? De formule zegt 10/0, en mijn meester van middelbare school zei al: delen door nul is flauwekul).
De volgende query gebruikt wat minder expliciete conversies dan de jouwe en zorgt dat het resultaat NULL wordt als er geen zinnig antwoord bestaat (dus: als de vastgestelde waarde 0 of NULL is).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, Hugo


Bericht 3 van 7

NL Computer Forum ~ SQL & Programmeren
 Van:John Kopmels (Sysop)Datum:23-09-2005
 Aan:Hugo KornelisMsgID:2489.3
 Onderwerp:SQLserver Null,0 en warningForum:ws-nlcomputer
Hoi Hugo

> SQL Server kan de gegevens van één rij niet over meerdere pages verdelen

ok, begrijp ik, maar ik kwam ook niet boven de 8060  (8000 + 49 + overhead?)
hieronder de DDL, mogelijk zitten we met de overhead van de resp kolommen ?

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]

> Het verbaast me overigens wel dat je deze melding krijgt tijdens het maken van indexen en zo.
> Gebruik je wellicht Enterprise Manager voor dergelijke acties?

EM gebruik ik weinig, alleen voor admin doeleinden,
vrijwel altijd gebruik ik QA of werk ik vanuit een Access ADP

Vraag 2 :
> De volgende query gebruikt wat minder expliciete conversies dan de jouwe
> en zorgt dat het resultaat NULL wordt als er geen zinnig antwoord bestaat
> (dus: als de vastgestelde waarde 0 of NULL is).

mooi !
reuze bedankt, weer wat bijgeleerd !

Groetjes --John






Bericht 4 van 7

NL Computer Forum ~ SQL & Programmeren
 Van:Hugo KornelisDatum:23-09-2005
 Aan:John Kopmels (Sysop)MsgID:2489.4
 Onderwerp:SQLserver Null,0 en warningForum:ws-nlcomputer
Hoi John,
ok, begrijp ik, maar ik kwam ook niet boven de 8060  (8000 + 49 + overhead?)
hieronder de DDL, mogelijk zitten we met de overhead van de resp kolommen ?

De methode om de rowsize van een tabel te berekenen kun je in Books Online vinden in het onderwerp "Estimating the Size of a Table". Laten we, met die methode in de hand, de tabel eens kolom voor kolom bekijken:
* WoZnummer bigint NOT NULL. Een bigint heeft 8 bytes nodig. Het bereik van dit datatype is duizelingwekkend. Als je waarden binnen het bereik van -2.147.483.648 tot 2.147.483.647 vallen, gebruik dan een int en bespaar 4 bytes in de tabel, 4 bytes in elke nonclustered index op deze tabel én 4 bytes in elke tabel die naar deze sleutel verwijst.
* Volgnr tinyint NOT NULL. Neemt slechts 1 byte in.
* Wie varchar(25) NULL. Vereist 2 bytes voor de lengte, plus maximaal 25 bytes. Totaal dus maximaal 27.
* Memo varchar(7950) NULL. Ook hier 2 extra bytes voor de lengte. Maximum is 7952 bytes.
* Datum smalldatetime NULL. Een smalldatetime wordt intern opgeslagen als 2 smallint waarden, elk 2 bytes lang. Totaal dus 4 bytes.
* Belang varchar(10) NULL. Kost 10 + 2 = 12 bytes. Maar aan de CHECK constraint kan ik zien dat de langste toegestane waarde slechts 7 bytes is, dus je kunt dit wijzigen in varchar(7). Dat verminderd de maximale lengte tot 9 bytes, een besparing van 3. Als de meerderheid van de rijen een normaal belang heeft, dan kun je ook overwegen om char(7) te gebruiken. Voor hoog en laag worden dan 7 bytes gebruikt (met varchar is dat 4 + 2 = 6), en voor normaal ook 7 bytes (is met varchar 7 + 2 = 9). In dit geval geeft char ipv varchar sowieso een verlaging van de maximale lengte; de werkelijke lengte zal ook verminderen, tenzij minder dan 1/3 van de rijen een normaal belang heeft.
* Afgeschermd char(1) NULL. Kost dus gewoon 1 byte.
Als ik de bovenstaande (maximale) ruimte voor de kolommen optel, dan kom ik op 8 + 1 + 27 + 7952 + 4 + 12 + 1 = 8005 bytes. Daar komt nog de overhead bij:
- Omdat er minstens één NULL-bare kolom gebruikt is, krijg je een NULL bitmap. Je hebt totaal 7 kolommen, dus dat past nog in 1 byte.
- Omdat er minstens één variable-lengte kolom gebruikt is, krijg je nog eens 2 bytes extra overhead.
- En tenslotte is er nog een vaste overhead per row van 6 bytes.
Tel ik dat allemaal bij elkaar op, dan kom ik tot een totaal van 8005 + 1 + 2 + 6 = 8014 bytes. En dan begrijp ik dus inderdaad niet waarom jij die foutmelding krijgt, want 8014 bytes is minder dan 8060.
Omdat ik het niet meer snapte heb ik de CREATE TABLE opdracht uit je bericht gekopieerd en zelf in mijn test database uitgevoerd. En daarbij kreeg ik géén foutmelding of waarschuwing!! Ik moet eerst de tabel groter maken (bv een kolom toevoegen, of de maximale lengte van Memo verhogen naar 8000 tekens) om zelf die melding ook te krijgen.
Heb je wellicht een onjuiste versie in dit bericht gezet?
Groetjes, Hugo
EDIT:
PS: Tijdens het spelen met jouw tabeldefinitie ontdekte ik wel dat er situaties zijn waarin SQL Server de genoemde waarschuwing ten onrechte geeft. Bv in het volgende voorbeeld maak ik een tabel met een maximale lengte van minder dan 8060 bytes; ik krijg toch de waarschuwing. Als ik dan alle kolommen tot de maximale lengte vul, blijkt dat inderdaad gewoon te passen. Zo zie je maar weer....
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



Bericht 5 van 7

NL Computer Forum ~ SQL & Programmeren
 Van:John Kopmels (Sysop)Datum:23-09-2005
 Aan:Hugo KornelisMsgID:2489.5
 Onderwerp:SQLserver Null,0 en warningForum:ws-nlcomputer
Hoi Hugo
> * WoZnummer bigint NOT NULL.

hebben we het in het verleden al over gehad en kan in deze situatie niet anders, rest ok

> Als ik de bovenstaande (maximale) ruimte voor de kolommen optel, dan kom ik op
>  8 + 1 + 27 + 7952 + 4 + 12 + 1 = 8005 bytes. Daar komt nog de overhead bij:
> Tel ik dat allemaal bij elkaar op, dan kom ik tot een totaal van 8005 + 1 + 2 + 6 = 8014 bytes.
>  En dan begrijp ik dus idd niet waarom jij die foutmelding krijgt, want 8014 bytes is minder dan 8060.
> Heb je wellicht een onjuiste versie in dit bericht gezet?

ja , dit was de pas gewijzigde, in de oorspronkelijke was het geen 7950 maar 8000

maar zelfs met 7950 (in alter table om te wijzigen) kreeg ik de waarschuwing nog,
mogelijk omdat er al data in zit ? (die overigens niet het maximum overschrijdt)

> PS: Tijdens het spelen met jouw tabeldefinitie ontdekte ik wel dat er situaties zijn waarin SQL Server
> de genoemde waarschuwing ten onrechte geeft. Bv in het volgende voorbeeld maak ik een tabel met
> een maximale lengte van minder dan 8060 bytes; ik krijg toch de waarschuwing.

waarschijnlijk dat is dan bij mij ook het geval geweest, alleen bij de dotNET man van ons waren
er nog bijkomende ADO connectie problemen, die niet optreden als de kolom Memo 7950 is ....

Groetjes --John



Bericht 6 van 7

NL Computer Forum ~ SQL & Programmeren
 Van:Hugo KornelisDatum:23-09-2005
 Aan:John Kopmels (Sysop)MsgID:2489.6
 Onderwerp:SQLserver Null,0 en warningForum:ws-nlcomputer
Hoi John,
>  En dan begrijp ik dus idd niet waarom jij die foutmelding krijgt, want 8014 bytes is minder dan 8060.
> Heb je wellicht een onjuiste versie in dit bericht gezet?

ja , dit was de pas gewijzigde, in de oorspronkelijke was het geen 7950 maar 8000

Ja, dan heb je nog 50 bytes extra, kom je dus op 8064, en is de waarschuwing correct. Wil je het uiterste er uit halen, gebruik dan varchar(7996). Of volg mijn tips m.b.t. de kolom Belang op om nog eens maximaal 5 bytes te winnen; dan moet varchar(8000) wel lukken.
maar zelfs met 7950 (in alter table om te wijzigen) kreeg ik de waarschuwing nog,
mogelijk omdat er al data in zit ? (die overigens niet het maximum overschrijdt)


Heb ik kunnen reproduceren in een test, ook zonder data in de tabel. Hoe het precies komt weet ik niet, maar het zal wel te maken hebben met hoe een ALTER TABLE intern verwerkt wordt. Als je daarna exact dezelfde ALTER TABLE opdracht nog eens uitvoert (waarbij je dus feitelijk niks verandert!), dan krijg je de melding niet nog eens. Kennelijk wordt er dus ook op de oorspronkelijke maximale lengte gecontroleerd.
> PS: Tijdens het spelen met jouw tabeldefinitie ontdekte ik wel dat er situaties zijn waarin SQL Server
> de genoemde waarschuwing ten onrechte geeft. Bv in het volgende voorbeeld maak ik een tabel met
> een maximale lengte van minder dan 8060 bytes; ik krijg toch de waarschuwing.

waarschijnlijk dat is dan bij mij ook het geval geweest,
Dat denk ik niet - toen je nog varchar(8000) had zat je écht boven het maximum. Die onterechte melding krijg je alleen als je een maximale lengte van de gehele rij van 8049 - 8060 bytes hebt, want SQL Server lijkt het totaal 12 bytes te hoog te berekenen. Een melding van deze bug is inmiddels naar Microsoft onderweg. Ik verwacht echter niet dat dit zal worden hersteld, omdat deze waarschuwing in de volgende versie van SQL Server (releasedatum in november van dit jaar) niet meer zal voorkomen. In SQL Server 2005 mag een rij namelijk meer dan één page gebruiken.
alleen bij de dotNET man van ons waren
er nog bijkomende ADO connectie problemen, die niet optreden als de kolom Memo 7950 is ....

Da's vreemd; dit heb ik nog nooit eerder gehoord. Nu ben ik geen dotNET expert, maar ik lees vrij veel SQL Server gerelateerde nieuwsgroepen, en als dit een bekend probleem zou zijn dan had ik het ongetwijfeld al wel eens gelezen.
Misschien tijd om je dotNET man effe bij dit Forum langs te sturen? <OBIO>
Groetjes, Hugo


Bericht 7 van 7

NL Computer Forum ~ SQL & Programmeren
 Van:John Kopmels (Sysop)Datum:23-09-2005
 Aan:Hugo KornelisMsgID:2489.7
 Onderwerp:SQLserver Null,0 en warningForum:ws-nlcomputer
Hoi Hugo,

> Ja, dan heb je nog 50 bytes extra, kom je dus op 8064, en is de waarschuwing correct.

ok

> Wil je het uiterste er uit halen, gebruik dan varchar(7996). Of volg mijn tips m.b.t. ....

die paar karakters meer of minder maakt in dit geval niet zo veel uit voor ons
maar ik wou duidelijkheid over het hoe en waarom, en de melding vermijden

> Heb ik kunnen reproduceren in een test, ook zonder data in de tabel.
> Hoe het precies komt weet ik niet, maar het zal wel te maken hebben
> met hoe een ALTER TABLE intern verwerkt wordt.

ok

> SQL Server lijkt het totaal 12 bytes te hoog te berekenen.
> Een melding van deze bug is inmiddels naar Microsoft onderweg.

ik heb het gezien :-)  ook het antwoord van Erland

bedankt voor de uitgebreide uitleg en hulp !

Groetjes --John