Saving space by using nulls?

Say that I have 2 identical tables with the only difference that one table
allows nulls on all of its *numeric* columns and the other one does not.
Now say that I add the same amount of rows to both tables with the only
difference that on the table that does not allow nulls in its numeric
columns I insert numeric values while in the one that does allow nulls I
insert <<Null>> value.
Will both tables use the same storage space or would the table that allows
nulls use less space because I am insetting Null values on it?
Thanks
Same. As of 7.0, space requirement for fixed length column is the same regardless if a column of a
row has null or an actual value.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Rene" <nospam@.nospam.com> wrote in message news:%23m6dfC8zFHA.1032@.TK2MSFTNGP12.phx.gbl...
> Say that I have 2 identical tables with the only difference that one table allows nulls on all of
> its *numeric* columns and the other one does not.
> Now say that I add the same amount of rows to both tables with the only difference that on the
> table that does not allow nulls in its numeric columns I insert numeric values while in the one
> that does allow nulls I insert <<Null>> value.
> Will both tables use the same storage space or would the table that allows nulls use less space
> because I am insetting Null values on it?
> Thanks
>
>
|||Thanks Tibor
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:eemuGL8zFHA.1968@.TK2MSFTNGP10.phx.gbl...
> Same. As of 7.0, space requirement for fixed length column is the same
> regardless if a column of a row has null or an actual value.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Rene" <nospam@.nospam.com> wrote in message
> news:%23m6dfC8zFHA.1032@.TK2MSFTNGP12.phx.gbl...
>

Saving space by using nulls?

Say that I have 2 identical tables with the only difference that one table
allows nulls on all of its *numeric* columns and the other one does not.
Now say that I add the same amount of rows to both tables with the only
difference that on the table that does not allow nulls in its numeric
columns I insert numeric values while in the one that does allow nulls I
insert <<Null>> value.
Will both tables use the same storage space or would the table that allows
nulls use less space because I am insetting Null values on it?
ThanksSame. As of 7.0, space requirement for fixed length column is the same regardless if a column of a
row has null or an actual value.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Rene" <nospam@.nospam.com> wrote in message news:%23m6dfC8zFHA.1032@.TK2MSFTNGP12.phx.gbl...
> Say that I have 2 identical tables with the only difference that one table allows nulls on all of
> its *numeric* columns and the other one does not.
> Now say that I add the same amount of rows to both tables with the only difference that on the
> table that does not allow nulls in its numeric columns I insert numeric values while in the one
> that does allow nulls I insert <<Null>> value.
> Will both tables use the same storage space or would the table that allows nulls use less space
> because I am insetting Null values on it?
> Thanks
>
>|||Thanks Tibor
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:eemuGL8zFHA.1968@.TK2MSFTNGP10.phx.gbl...
> Same. As of 7.0, space requirement for fixed length column is the same
> regardless if a column of a row has null or an actual value.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Rene" <nospam@.nospam.com> wrote in message
> news:%23m6dfC8zFHA.1032@.TK2MSFTNGP12.phx.gbl...
>> Say that I have 2 identical tables with the only difference that one
>> table allows nulls on all of its *numeric* columns and the other one does
>> not.
>> Now say that I add the same amount of rows to both tables with the only
>> difference that on the table that does not allow nulls in its numeric
>> columns I insert numeric values while in the one that does allow nulls I
>> insert <<Null>> value.
>> Will both tables use the same storage space or would the table that
>> allows nulls use less space because I am insetting Null values on it?
>> Thanks
>>
>

Saving space by using nulls?

Say that I have 2 identical tables with the only difference that one table
allows nulls on all of its *numeric* columns and the other one does not.
Now say that I add the same amount of rows to both tables with the only
difference that on the table that does not allow nulls in its numeric
columns I insert numeric values while in the one that does allow nulls I
insert <<Null>> value.
Will both tables use the same storage space or would the table that allows
nulls use less space because I am insetting Null values on it?
ThanksSame. As of 7.0, space requirement for fixed length column is the same regar
dless if a column of a
row has null or an actual value.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Rene" <nospam@.nospam.com> wrote in message news:%23m6dfC8zFHA.1032@.TK2MSFTNGP12.phx.gbl...[
vbcol=seagreen]
> Say that I have 2 identical tables with the only difference that one table
allows nulls on all of
> its *numeric* columns and the other one does not.
> Now say that I add the same amount of rows to both tables with the only di
fference that on the
> table that does not allow nulls in its numeric columns I insert numeric va
lues while in the one
> that does allow nulls I insert <<Null>> value.
> Will both tables use the same storage space or would the table that allows
nulls use less space
> because I am insetting Null values on it?
> Thanks
>
>[/vbcol]|||Thanks Tibor
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:eemuGL8zFHA.1968@.TK2MSFTNGP10.phx.gbl...
> Same. As of 7.0, space requirement for fixed length column is the same
> regardless if a column of a row has null or an actual value.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Rene" <nospam@.nospam.com> wrote in message
> news:%23m6dfC8zFHA.1032@.TK2MSFTNGP12.phx.gbl...
>sql

Saving large text file in table

Have created an SQL Server 2000 table which includes two columns each data type varchar(8000) in order to accomodate a semi-large amount of text (want to avoid using text data type). I enter the max in the first column but I keep getting an error of "Cannot create a row of size 12066 which is greater than the allowable maximum of 8060" when I try to save any more in the second column of the table. What am I doing wrong?

SQL Server stores rows (and indexes) in pages. Pages are 8K in size. There is some required header information (i.e. next block in chain, etc.), which drops the usable space for rows to 8060. So a single row cannot exceed 8060 bytes and rows cannot span multiple pages. You have two choices:

Split the table into two tables:
Table1
Id int (PK)
Text1 varchar(8000)

Table2
Id int (PK and FK to Table1)
Text2 varchar(8000)

Use the text data type:
The text data type stores the data in separate data pages which can span multiple pages. In the row, you simply store a pointer to the first data page. You can optimize the table so that the text type is stored inline with the row so long as it doesn't exceed a certain size.

|||Only had a couple of pages that were over the 8060 limit and using two tables works perfect. Thanks for the advice.|||

Glad I could help. You can always throw an updatable view overtop of the two tables so that they appear as one table to queries. Just write a view that INNER JOINs the two tables and then write INSTEAD OF INSERT, UPDATE, and DELETE triggers.

Saving Datetime to variable

Hi im working on a simple function. I have a table with two columns
(datetime) and I need to extract date rom one of them and time from another
one - and then to join it and save it in the variable (which is declered as a
datetime). Please, can somebody help me or advice another waz how to get it?
many thanks
CREATE function dbo.dej_rozdil (@.vz int, @.r int) returns char(100)
as
begin
declare @.in_date as varchar
declare @.in_time as varchar
declare @.in as char(100)
set @.in_date = ( select convert(varchar,pol1,102) from dbo.vzorky_osr where
kod=@.vz and rok=@.r )
set @.in_time = ( select convert(varchar,pol2, 108) from dbo.vzorky_osr where
kod=@.vz and rok=@.r )
set @.in = @.in_date +' '+ @.in_time
return @.ret
end
On Sun, 22 Jan 2006 06:14:02 -0800, pietro wrote:

>Hi im working on a simple function. I have a table with two columns
>(datetime) and I need to extract date rom one of them and time from another
>one - and then to join it and save it in the variable (which is declered as a
>datetime). Please, can somebody help me or advice another waz how to get it?
>many thanks
Hi Pietro,
Are these date and time columns stored as datetime or as char/varchar?
If the former, you might consider storing them together in one column.
If the latter, you might consider storing them as datetime (much better
integrity checks, much better possibilities for doing calculations) AND
consider storing them together in one column.

>CREATE function dbo.dej_rozdil (@.vz int, @.r int) returns char(100)
Why are you not returning a datetime? That's what you want to store the
combined value in, after all!

>as
>begin
>declare @.in_date as varchar
>declare @.in_time as varchar
Since you don;t specify length here, these variables are now defined at
the default length: varchar(1).

>declare @.in as char(100)
>set @.in_date = ( select convert(varchar,pol1,102) from dbo.vzorky_osr where
>kod=@.vz and rok=@.r )
>set @.in_time = ( select convert(varchar,pol2, 108) from dbo.vzorky_osr where
>kod=@.vz and rok=@.r )
The use of convert in this statement suggests that the columns are
already of the datetime datatype. All this converting from datetime to
string and back won't do your performance any good - and the fact that
you didn't choose one of the recommended formats means that you're not
even assured that this will never return unexpected results.
See below for some better suggestions.

>set @.in = @.in_date +' '+ @.in_time
>return @.ret
>end
Your choice to use a user-defined functions has some performance
implications as well. I am aware of the benefits of UDF for readability,
reuseability and documentation - but are you aware of the implications
on performance?
Anyway, I promised you some alternatives. The first alternative is what
you should use if pol1 and pol2 are defined as datetime or smalldatetime
columns:
DECLARE @.result datetime -- or smalldatetime
SET @.result = (SELECT DATEADD(ms, DATEDIFF(ms, '0:00', pol2), pol1)
FROM dbo.vzorky_osr
WHERE kod = @.vz
AND rok = @.r)
The second alternative is what you should use if ALL of the following
conditions are met:
* pol1 and pol2 are both char or varchar
* pol1 is formatted yyyy-mm-dd
* pol2 is formatted hh:mm:ss or hh:mm:ss.mmm (where .mmm denotes the
milliseconds)
DECLARE @.result datetime -- or smalldatetime
SET @.result = (SELECT CAST(pol1 + 'T' + pol2 AS datetime) -- or
smalldatetime
FROM dbo.vzorky_osr
WHERE kod = @.vz
AND rok = @.r)
If col1 and col2 are (var)char but not in the format noted above, you
can either
a) use string manipulation to build a yyyy-mm-ddThh:mm:ss[.mmm] format
from whatever the current format is, or
b) concatenate them anyway, then use CONVERT with a style parameter to
ensure that SQL Server can't misinterpret the format.
Example for b:
CAST ('3/4/5' AS datetime) is dangerous, because is can be interpreted
as 3rd of april 2005, of march 4, 2005, 2003, april 5 or m,aybe yet
something else
CONVERT(datetime, '3/4/5', 11) is unambitious, since the style parameter
11 says that the japanese format (yy/mm/dd) is used.
If you need further help, then you'll have to provide more information.
See www.aspfaq.com/5006 for that.
Hugo Kornelis, SQL Server MVP

save XML in a table

Im not talking about storing the attributes of the elements of an XML file
into different columns of tables(s) but saving the entire XML into a single
row and column as a blob ?
What would be the pros and cons of doing it as such ?In SQL Server 2005, there is an XML datatype, which handles this quite well.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
"Hassan" <Hassan@.hotmail.com> wrote in message
news:uLY662iJGHA.516@.TK2MSFTNGP15.phx.gbl...
Im not talking about storing the attributes of the elements of an XML file
into different columns of tables(s) but saving the entire XML into a single
row and column as a blob ?
What would be the pros and cons of doing it as such ?|||But curious about SQL 2000 ?
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:eiU9scmJGHA.648@.TK2MSFTNGP14.phx.gbl...
> In SQL Server 2005, there is an XML datatype, which handles this quite
> well.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> "Hassan" <Hassan@.hotmail.com> wrote in message
> news:uLY662iJGHA.516@.TK2MSFTNGP15.phx.gbl...
> Im not talking about storing the attributes of the elements of an XML file
> into different columns of tables(s) but saving the entire XML into a
> single
> row and column as a blob ?
> What would be the pros and cons of doing it as such ?
>
>|||Check "XML best practices" in BOL.
In short, when you don't need xml data type you need blob. And when you need
identical copy of the XML string, such as legal document, you need blob. I
mean varchar(max) by blob.
Pohwan Han. Seoul. Have a nice day.
"Hassan" <Hassan@.hotmail.com> wrote in message
news:uLY662iJGHA.516@.TK2MSFTNGP15.phx.gbl...
> Im not talking about storing the attributes of the elements of an XML file
> into different columns of tables(s) but saving the entire XML into a
> single row and column as a blob ?
> What would be the pros and cons of doing it as such ?
>|||Some of the problems:
-How will you search for data in the column/row within the XML?
-If the XML document is 50MB, how will you insert the data? OPENXML will
require you to load the document into a variable, using a huge amount of
resources.
-In a table with 50,000 rows, how do you identify each XML document?
"Han" wrote:

> Check "XML best practices" in BOL.
> In short, when you don't need xml data type you need blob. And when you ne
ed
> identical copy of the XML string, such as legal document, you need blob. I
> mean varchar(max) by blob.
> --
> Pohwan Han. Seoul. Have a nice day.
> "Hassan" <Hassan@.hotmail.com> wrote in message
> news:uLY662iJGHA.516@.TK2MSFTNGP15.phx.gbl...
>|||If you want to do any of that, I really recommend to upgrade to SQL Server
2005. SQL Server 2000 was not designed for managing XML documents as a unit
but for enabling relational integration into XML.
Best regards
Michael
"Sal Young" <SalYoung@.discussions.microsoft.com> wrote in message
news:3427C6A2-CD47-486F-A21A-646CAB18662D@.microsoft.com...
> Some of the problems:
> -How will you search for data in the column/row within the XML?
> -If the XML document is 50MB, how will you insert the data? OPENXML will
> require you to load the document into a variable, using a huge amount of
> resources.
> -In a table with 50,000 rows, how do you identify each XML document?
> "Han" wrote:
>sql

save XML in a table

Im not talking about storing the attributes of the elements of an XML file
into different columns of tables(s) but saving the entire XML into a single
row and column as a blob ?
What would be the pros and cons of doing it as such ?
In SQL Server 2005, there is an XML datatype, which handles this quite well.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
"Hassan" <Hassan@.hotmail.com> wrote in message
news:uLY662iJGHA.516@.TK2MSFTNGP15.phx.gbl...
Im not talking about storing the attributes of the elements of an XML file
into different columns of tables(s) but saving the entire XML into a single
row and column as a blob ?
What would be the pros and cons of doing it as such ?
|||But curious about SQL 2000 ?
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:eiU9scmJGHA.648@.TK2MSFTNGP14.phx.gbl...
> In SQL Server 2005, there is an XML datatype, which handles this quite
> well.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> "Hassan" <Hassan@.hotmail.com> wrote in message
> news:uLY662iJGHA.516@.TK2MSFTNGP15.phx.gbl...
> Im not talking about storing the attributes of the elements of an XML file
> into different columns of tables(s) but saving the entire XML into a
> single
> row and column as a blob ?
> What would be the pros and cons of doing it as such ?
>
>
|||Check "XML best practices" in BOL.
In short, when you don't need xml data type you need blob. And when you need
identical copy of the XML string, such as legal document, you need blob. I
mean varchar(max) by blob.
Pohwan Han. Seoul. Have a nice day.
"Hassan" <Hassan@.hotmail.com> wrote in message
news:uLY662iJGHA.516@.TK2MSFTNGP15.phx.gbl...
> Im not talking about storing the attributes of the elements of an XML file
> into different columns of tables(s) but saving the entire XML into a
> single row and column as a blob ?
> What would be the pros and cons of doing it as such ?
>
|||Some of the problems:
-How will you search for data in the column/row within the XML?
-If the XML document is 50MB, how will you insert the data? OPENXML will
require you to load the document into a variable, using a huge amount of
resources.
-In a table with 50,000 rows, how do you identify each XML document?
"Han" wrote:

> Check "XML best practices" in BOL.
> In short, when you don't need xml data type you need blob. And when you need
> identical copy of the XML string, such as legal document, you need blob. I
> mean varchar(max) by blob.
> --
> Pohwan Han. Seoul. Have a nice day.
> "Hassan" <Hassan@.hotmail.com> wrote in message
> news:uLY662iJGHA.516@.TK2MSFTNGP15.phx.gbl...
>
|||If you want to do any of that, I really recommend to upgrade to SQL Server
2005. SQL Server 2000 was not designed for managing XML documents as a unit
but for enabling relational integration into XML.
Best regards
Michael
"Sal Young" <SalYoung@.discussions.microsoft.com> wrote in message
news:3427C6A2-CD47-486F-A21A-646CAB18662D@.microsoft.com...[vbcol=seagreen]
> Some of the problems:
> -How will you search for data in the column/row within the XML?
> -If the XML document is 50MB, how will you insert the data? OPENXML will
> require you to load the document into a variable, using a huge amount of
> resources.
> -In a table with 50,000 rows, how do you identify each XML document?
> "Han" wrote: