Add rows to a DataSet without updating the MS SQL Server?

I am using ASP.NET 2.0 WebForms and I was trying to use a DataSet to add rows programatically without adding the actual records to the MS SQL Server Databases. Is this possible or should I be doing this another way?

DataSet myDS =newDataSet();
DataTable myTable =newDataTable("table1");
myTable.Columns.Add("col1",typeof(string));
myDS.Tables.Add(myTable);
myTable.Rows.Add("MyValue");

Thanks.

Yes it is possible.

Actual vs. Estimated Row Counts

I have a stored procedure that contains a complex query of approximately 16
joins, (a mixture of inner and left joins). These joins are mostly on
standard user tables, but includes joins on two temporary tables and two non-
indexable views.
I have rewritten the query so as to reduce the reads from 5 million plus, to
about 1.6 million reads using some dynamic sql and adding additional indexes.
Still, in the execution plan, several of the user tables have estimated vs.
actual row counts that are quite different, such as 1 Estimated row versus
135,000 actual rows. These tables are using, what I consider the appropriate
index, but, as stated, the actual vs. estimated are way off.
The procedure takes several arguments and I set those arguments as local
variables in the procedure to prevent parameter sniffing.
I was hoping to get a better plan by getting the actual versus estimated row
counts closer. I have ran "update statistics <table_name> with fullscan" on
all the major tables used in the query and especially on those tables whose
actual vs. estimated are way off. This had no effect on the actual vs.
estimated row counts. I have also run sp_updatestats, dbcc dbreindex on the
tables, sp_recompile, but in each case, the estimated vs. actual is still way
off.
I have considered reordering some of the inner and left joins, but was not
sure if that would have any affect.
Would you be able to advise on what else one might do to get the actual vs.
estimated more in line?
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums...erver/200610/1
its difficult to get a better and more accurate plan after your actions
(updating stats etc...)
Specially with left joins...
the only way is to force the way you want to do your joins (manually
specify the hash/ merge option in the joins, setup the better order for your
joins etc...)
in this case you force SQL Server to use your hints instead of using its own
generated plan.
"cbrichards via droptable.com" <u3288@.uwe> wrote in message
news:679360284efd2@.uwe...
>I have a stored procedure that contains a complex query of approximately 16
> joins, (a mixture of inner and left joins). These joins are mostly on
> standard user tables, but includes joins on two temporary tables and two
> non-
> indexable views.
> I have rewritten the query so as to reduce the reads from 5 million plus,
> to
> about 1.6 million reads using some dynamic sql and adding additional
> indexes.
> Still, in the execution plan, several of the user tables have estimated
> vs.
> actual row counts that are quite different, such as 1 Estimated row versus
> 135,000 actual rows. These tables are using, what I consider the
> appropriate
> index, but, as stated, the actual vs. estimated are way off.
> The procedure takes several arguments and I set those arguments as local
> variables in the procedure to prevent parameter sniffing.
> I was hoping to get a better plan by getting the actual versus estimated
> row
> counts closer. I have ran "update statistics <table_name> with fullscan"
> on
> all the major tables used in the query and especially on those tables
> whose
> actual vs. estimated are way off. This had no effect on the actual vs.
> estimated row counts. I have also run sp_updatestats, dbcc dbreindex on
> the
> tables, sp_recompile, but in each case, the estimated vs. actual is still
> way
> off.
> I have considered reordering some of the inner and left joins, but was not
> sure if that would have any affect.
> Would you be able to advise on what else one might do to get the actual
> vs.
> estimated more in line?
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forums...erver/200610/1
>
|||> The procedure takes several arguments and I set those arguments as local
> variables in the procedure to prevent parameter sniffing.
That way, the optimizer uses hard-coded values to determine selectivity. Something like:
= 10% (actually, it might go by density in the statistics, if available)
> 30%
BETWEEN 25%
The percentage for the values differs from release to release, and can change even between service
packs. So, in other words, a variable is *not* the same thing as the optimizer knowing the value you
are looking for.
For the optimizer to *know* the value and have a static plan, you have to rely on parameter
sniffing. This value in the plan might of course be off for the subsequent usages of the plan.
Or, you would have to get a new plan for each execution. Either break out the critical parts of the
procedure to its own procedures and create them WITH RECOMPILE, or in 2005 you can add RECOMPILE
hint at the query level. Or you can in 2005 even say OPTIMIZE FOR a certain value.
You can of course add WITH RECOMPILE for the whole procedure, but then none of the DML statements in
the procedure will be pre-optimized.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"cbrichards via droptable.com" <u3288@.uwe> wrote in message news:679360284efd2@.uwe...
>I have a stored procedure that contains a complex query of approximately 16
> joins, (a mixture of inner and left joins). These joins are mostly on
> standard user tables, but includes joins on two temporary tables and two non-
> indexable views.
> I have rewritten the query so as to reduce the reads from 5 million plus, to
> about 1.6 million reads using some dynamic sql and adding additional indexes.
> Still, in the execution plan, several of the user tables have estimated vs.
> actual row counts that are quite different, such as 1 Estimated row versus
> 135,000 actual rows. These tables are using, what I consider the appropriate
> index, but, as stated, the actual vs. estimated are way off.
> The procedure takes several arguments and I set those arguments as local
> variables in the procedure to prevent parameter sniffing.
> I was hoping to get a better plan by getting the actual versus estimated row
> counts closer. I have ran "update statistics <table_name> with fullscan" on
> all the major tables used in the query and especially on those tables whose
> actual vs. estimated are way off. This had no effect on the actual vs.
> estimated row counts. I have also run sp_updatestats, dbcc dbreindex on the
> tables, sp_recompile, but in each case, the estimated vs. actual is still way
> off.
> I have considered reordering some of the inner and left joins, but was not
> sure if that would have any affect.
> Would you be able to advise on what else one might do to get the actual vs.
> estimated more in line?
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forums...erver/200610/1
>
|||Thanks for your reply Jeje.
In regards to your comment of setting up a better order on my joins, is the
guide to setting up joins...ordering joins with the most selective join
first, followed by the next most selective join, etc., etc.?
Jeje wrote:[vbcol=seagreen]
>its difficult to get a better and more accurate plan after your actions
>(updating stats etc...)
>Specially with left joins...
>the only way is to force the way you want to do your joins (manually
>specify the hash/ merge option in the joins, setup the better order for your
>joins etc...)
>in this case you force SQL Server to use your hints instead of using its own
>generated plan.
>[quoted text clipped - 36 lines]
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums...erver/200610/1

Actual vs. Estimated Row Counts

I have a stored procedure that contains a complex query of approximately 16
joins, (a mixture of inner and left joins). These joins are mostly on
standard user tables, but includes joins on two temporary tables and two non
-
indexable views.
I have rewritten the query so as to reduce the reads from 5 million plus, to
about 1.6 million reads using some dynamic sql and adding additional indexes
.
Still, in the execution plan, several of the user tables have estimated vs.
actual row counts that are quite different, such as 1 Estimated row versus
135,000 actual rows. These tables are using, what I consider the appropriate
index, but, as stated, the actual vs. estimated are way off.
The procedure takes several arguments and I set those arguments as local
variables in the procedure to prevent parameter sniffing.
I was hoping to get a better plan by getting the actual versus estimated row
counts closer. I have ran "update statistics <table_name> with fullscan" on
all the major tables used in the query and especially on those tables whose
actual vs. estimated are way off. This had no effect on the actual vs.
estimated row counts. I have also run sp_updatestats, dbcc dbreindex on the
tables, sp_recompile, but in each case, the estimated vs. actual is still wa
y
off.
I have considered reordering some of the inner and left joins, but was not
sure if that would have any affect.
Would you be able to advise on what else one might do to get the actual vs.
estimated more in line?
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200610/1its difficult to get a better and more accurate plan after your actions
(updating stats etc...)
Specially with left joins...
the only way is to force the way you want to do your joins (manually
specify the hash/ merge option in the joins, setup the better order for your
joins etc...)
in this case you force SQL Server to use your hints instead of using its own
generated plan.
"cbrichards via droptable.com" <u3288@.uwe> wrote in message
news:679360284efd2@.uwe...
>I have a stored procedure that contains a complex query of approximately 16
> joins, (a mixture of inner and left joins). These joins are mostly on
> standard user tables, but includes joins on two temporary tables and two
> non-
> indexable views.
> I have rewritten the query so as to reduce the reads from 5 million plus,
> to
> about 1.6 million reads using some dynamic sql and adding additional
> indexes.
> Still, in the execution plan, several of the user tables have estimated
> vs.
> actual row counts that are quite different, such as 1 Estimated row versus
> 135,000 actual rows. These tables are using, what I consider the
> appropriate
> index, but, as stated, the actual vs. estimated are way off.
> The procedure takes several arguments and I set those arguments as local
> variables in the procedure to prevent parameter sniffing.
> I was hoping to get a better plan by getting the actual versus estimated
> row
> counts closer. I have ran "update statistics <table_name> with fullscan"
> on
> all the major tables used in the query and especially on those tables
> whose
> actual vs. estimated are way off. This had no effect on the actual vs.
> estimated row counts. I have also run sp_updatestats, dbcc dbreindex on
> the
> tables, sp_recompile, but in each case, the estimated vs. actual is still
> way
> off.
> I have considered reordering some of the inner and left joins, but was not
> sure if that would have any affect.
> Would you be able to advise on what else one might do to get the actual
> vs.
> estimated more in line?
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forum...server/200610/1
>|||> The procedure takes several arguments and I set those arguments as local
> variables in the procedure to prevent parameter sniffing.
That way, the optimizer uses hard-coded values to determine selectivity. Som
ething like:
= 10% (actually, it might go by density in the statistics, if available)
> 30%
BETWEEN 25%
The percentage for the values differs from release to release, and can chang
e even between service
packs. So, in other words, a variable is *not* the same thing as the optimiz
er knowing the value you
are looking for.
For the optimizer to *know* the value and have a static plan, you have to re
ly on parameter
sniffing. This value in the plan might of course be off for the subsequent u
sages of the plan.
Or, you would have to get a new plan for each execution. Either break out th
e critical parts of the
procedure to its own procedures and create them WITH RECOMPILE, or in 2005 y
ou can add RECOMPILE
hint at the query level. Or you can in 2005 even say OPTIMIZE FOR a certain
value.
You can of course add WITH RECOMPILE for the whole procedure, but then none
of the DML statements in
the procedure will be pre-optimized.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"cbrichards via droptable.com" <u3288@.uwe> wrote in message news:679360284efd2@.uwe...[vbcol
=seagreen]
>I have a stored procedure that contains a complex query of approximately 16
> joins, (a mixture of inner and left joins). These joins are mostly on
> standard user tables, but includes joins on two temporary tables and two n
on-
> indexable views.
> I have rewritten the query so as to reduce the reads from 5 million plus,
to
> about 1.6 million reads using some dynamic sql and adding additional index
es.
> Still, in the execution plan, several of the user tables have estimated vs
.
> actual row counts that are quite different, such as 1 Estimated row versus
> 135,000 actual rows. These tables are using, what I consider the appropria
te
> index, but, as stated, the actual vs. estimated are way off.
> The procedure takes several arguments and I set those arguments as local
> variables in the procedure to prevent parameter sniffing.
> I was hoping to get a better plan by getting the actual versus estimated r
ow
> counts closer. I have ran "update statistics <table_name> with fullscan" o
n
> all the major tables used in the query and especially on those tables whos
e
> actual vs. estimated are way off. This had no effect on the actual vs.
> estimated row counts. I have also run sp_updatestats, dbcc dbreindex on th
e
> tables, sp_recompile, but in each case, the estimated vs. actual is still
way
> off.
> I have considered reordering some of the inner and left joins, but was not
> sure if that would have any affect.
> Would you be able to advise on what else one might do to get the actual vs
.
> estimated more in line?
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forum...server/200610/1
>[/vbcol]|||Thanks for your reply Jeje.
In regards to your comment of setting up a better order on my joins, is the
guide to setting up joins...ordering joins with the most selective join
first, followed by the next most selective join, etc., etc.?
Jeje wrote:[vbcol=seagreen]
>its difficult to get a better and more accurate plan after your actions
>(updating stats etc...)
>Specially with left joins...
>the only way is to force the way you want to do your joins (manually
>specify the hash/ merge option in the joins, setup the better order for you
r
>joins etc...)
>in this case you force SQL Server to use your hints instead of using its ow
n
>generated plan.
>
>[quoted text clipped - 36 lines]
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200610/1

Actual vs. Estimated Row Counts

I have a stored procedure that contains a complex query of approximately 16
joins, (a mixture of inner and left joins). These joins are mostly on
standard user tables, but includes joins on two temporary tables and two non-
indexable views.
I have rewritten the query so as to reduce the reads from 5 million plus, to
about 1.6 million reads using some dynamic sql and adding additional indexes.
Still, in the execution plan, several of the user tables have estimated vs.
actual row counts that are quite different, such as 1 Estimated row versus
135,000 actual rows. These tables are using, what I consider the appropriate
index, but, as stated, the actual vs. estimated are way off.
The procedure takes several arguments and I set those arguments as local
variables in the procedure to prevent parameter sniffing.
I was hoping to get a better plan by getting the actual versus estimated row
counts closer. I have ran "update statistics <table_name> with fullscan" on
all the major tables used in the query and especially on those tables whose
actual vs. estimated are way off. This had no effect on the actual vs.
estimated row counts. I have also run sp_updatestats, dbcc dbreindex on the
tables, sp_recompile, but in each case, the estimated vs. actual is still way
off.
I have considered reordering some of the inner and left joins, but was not
sure if that would have any affect.
Would you be able to advise on what else one might do to get the actual vs.
estimated more in line?
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200610/1its difficult to get a better and more accurate plan after your actions
(updating stats etc...)
Specially with left joins...
the only way is to force the way you want to do your joins (manually
specify the hash/ merge option in the joins, setup the better order for your
joins etc...)
in this case you force SQL Server to use your hints instead of using its own
generated plan.
"cbrichards via SQLMonster.com" <u3288@.uwe> wrote in message
news:679360284efd2@.uwe...
>I have a stored procedure that contains a complex query of approximately 16
> joins, (a mixture of inner and left joins). These joins are mostly on
> standard user tables, but includes joins on two temporary tables and two
> non-
> indexable views.
> I have rewritten the query so as to reduce the reads from 5 million plus,
> to
> about 1.6 million reads using some dynamic sql and adding additional
> indexes.
> Still, in the execution plan, several of the user tables have estimated
> vs.
> actual row counts that are quite different, such as 1 Estimated row versus
> 135,000 actual rows. These tables are using, what I consider the
> appropriate
> index, but, as stated, the actual vs. estimated are way off.
> The procedure takes several arguments and I set those arguments as local
> variables in the procedure to prevent parameter sniffing.
> I was hoping to get a better plan by getting the actual versus estimated
> row
> counts closer. I have ran "update statistics <table_name> with fullscan"
> on
> all the major tables used in the query and especially on those tables
> whose
> actual vs. estimated are way off. This had no effect on the actual vs.
> estimated row counts. I have also run sp_updatestats, dbcc dbreindex on
> the
> tables, sp_recompile, but in each case, the estimated vs. actual is still
> way
> off.
> I have considered reordering some of the inner and left joins, but was not
> sure if that would have any affect.
> Would you be able to advise on what else one might do to get the actual
> vs.
> estimated more in line?
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200610/1
>|||> The procedure takes several arguments and I set those arguments as local
> variables in the procedure to prevent parameter sniffing.
That way, the optimizer uses hard-coded values to determine selectivity. Something like:
= 10% (actually, it might go by density in the statistics, if available)
> 30%
BETWEEN 25%
The percentage for the values differs from release to release, and can change even between service
packs. So, in other words, a variable is *not* the same thing as the optimizer knowing the value you
are looking for.
For the optimizer to *know* the value and have a static plan, you have to rely on parameter
sniffing. This value in the plan might of course be off for the subsequent usages of the plan.
Or, you would have to get a new plan for each execution. Either break out the critical parts of the
procedure to its own procedures and create them WITH RECOMPILE, or in 2005 you can add RECOMPILE
hint at the query level. Or you can in 2005 even say OPTIMIZE FOR a certain value.
You can of course add WITH RECOMPILE for the whole procedure, but then none of the DML statements in
the procedure will be pre-optimized.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"cbrichards via SQLMonster.com" <u3288@.uwe> wrote in message news:679360284efd2@.uwe...
>I have a stored procedure that contains a complex query of approximately 16
> joins, (a mixture of inner and left joins). These joins are mostly on
> standard user tables, but includes joins on two temporary tables and two non-
> indexable views.
> I have rewritten the query so as to reduce the reads from 5 million plus, to
> about 1.6 million reads using some dynamic sql and adding additional indexes.
> Still, in the execution plan, several of the user tables have estimated vs.
> actual row counts that are quite different, such as 1 Estimated row versus
> 135,000 actual rows. These tables are using, what I consider the appropriate
> index, but, as stated, the actual vs. estimated are way off.
> The procedure takes several arguments and I set those arguments as local
> variables in the procedure to prevent parameter sniffing.
> I was hoping to get a better plan by getting the actual versus estimated row
> counts closer. I have ran "update statistics <table_name> with fullscan" on
> all the major tables used in the query and especially on those tables whose
> actual vs. estimated are way off. This had no effect on the actual vs.
> estimated row counts. I have also run sp_updatestats, dbcc dbreindex on the
> tables, sp_recompile, but in each case, the estimated vs. actual is still way
> off.
> I have considered reordering some of the inner and left joins, but was not
> sure if that would have any affect.
> Would you be able to advise on what else one might do to get the actual vs.
> estimated more in line?
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200610/1
>|||Thanks for your reply Jeje.
In regards to your comment of setting up a better order on my joins, is the
guide to setting up joins...ordering joins with the most selective join
first, followed by the next most selective join, etc., etc.?
Jeje wrote:
>its difficult to get a better and more accurate plan after your actions
>(updating stats etc...)
>Specially with left joins...
>the only way is to force the way you want to do your joins (manually
>specify the hash/ merge option in the joins, setup the better order for your
>joins etc...)
>in this case you force SQL Server to use your hints instead of using its own
>generated plan.
>>I have a stored procedure that contains a complex query of approximately 16
>> joins, (a mixture of inner and left joins). These joins are mostly on
>[quoted text clipped - 36 lines]
>> vs.
>> estimated more in line?
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200610/1

actual syntax by tracing

Hello

I am running 6.5 sql and work with a traffic and billing software ( called NOvar) from another company(encoda system) which does a lot of scheduling, reporting etc

I dont know the contents of table (100 table ) and their column
or which table its querying to take out reports

Can i create a trace to know the syntax each time some thing is executed.

I also need to create customized reports, can this be done by sql reporting or does i need to go from crystal reports or someone else
For i dont know any language except sql and HTML

sejDO you have SQL Server Client side tools installed?|||Yes i do have that installed

Actual storage of Varchar values

I have a table with a definition similar to the following.
create table sizetest (pk int identity(1,1)
, V1 Varchar(1000) , V2 varchar(1000) , V3 varchar(1000)
, V4 Varchar(1000) , V5 varchar(1000) , V6 varchar(1000)
, V7 Varchar(1000) , V8 varchar(1000) , V9 varchar(x)
I have varied the length of column V9 between 16 and 40. When X <= 16 the
table is created without warning. When x = 40 I am told that the row length
is 8084 or 24 bytes too big. X= 20 says 8064 or 4 bytes too big. I have not
been able to come up with a definition for varchar storage that matches the
results I am seeing. This Reference
(http://msdn2.microsoft.com/en-us/library/ms176089(SQL.100).aspx) says that n
+ 2 is the answer but that does not work with any of this data I have
generated. For Example (8 * 1002) + 42 + 4 (for the pk) = 8062 which is not
equal to 8084 and is more than 8060 which BOL says is the max allowed. So I
understand the warning but not the numbers. On the other hand (8 * 1002) + 20
+ 4 = 8040 which is not equal to 8064 and is less than 8060. So why is this
one getting the warning?
I changed the table definition to 3 varchar fields 4000, 4000, and x and got
similar results but different numbers. Still no predicatble results.
How can I determine exactly what potential strorage is required if all of
the varchar fields are full.
My C++ developer won't believe me unless I can give him the details and show
a way to reliably predict the row length for any combination of column
types/sizes.
Thanks
Ray Herring
n+2 is the storage for the varchar fields themselves, but there is other
overhead required in each row.
You did not say what version you are using, but the documentation you
referred to is for SQL Server 2008, which is not in production yet, and the
docs are incomplete.
Take a look here for some information about additional overhead bytes:
http://msdn2.microsoft.com/en-us/library/ms189124.aspx
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://DVD.kalendelaney.com
"Ray" <Ray@.discussions.microsoft.com> wrote in message
news:5B7540FA-BBC4-4B77-BC73-EA6FFE1524BE@.microsoft.com...
> I have a table with a definition similar to the following.
> create table sizetest (pk int identity(1,1)
> , V1 Varchar(1000) , V2 varchar(1000) , V3 varchar(1000)
> , V4 Varchar(1000) , V5 varchar(1000) , V6 varchar(1000)
> , V7 Varchar(1000) , V8 varchar(1000) , V9 varchar(x)
> I have varied the length of column V9 between 16 and 40. When X <= 16 the
> table is created without warning. When x = 40 I am told that the row
> length
> is 8084 or 24 bytes too big. X= 20 says 8064 or 4 bytes too big. I have
> not
> been able to come up with a definition for varchar storage that matches
> the
> results I am seeing. This Reference
> (http://msdn2.microsoft.com/en-us/library/ms176089(SQL.100).aspx) says
> that n
> + 2 is the answer but that does not work with any of this data I have
> generated. For Example (8 * 1002) + 42 + 4 (for the pk) = 8062 which is
> not
> equal to 8084 and is more than 8060 which BOL says is the max allowed. So
> I
> understand the warning but not the numbers. On the other hand (8 * 1002) +
> 20
> + 4 = 8040 which is not equal to 8064 and is less than 8060. So why is
> this
> one getting the warning?
> I changed the table definition to 3 varchar fields 4000, 4000, and x and
> got
> similar results but different numbers. Still no predicatble results.
> How can I determine exactly what potential strorage is required if all of
> the varchar fields are full.
> My C++ developer won't believe me unless I can give him the details and
> show
> a way to reliably predict the row length for any combination of column
> types/sizes.
> Thanks
> --
> Ray Herring
|||Thanks Kalen
I thought about the version a little after I posted. I am using 2000 right
now.
I found an additional reference (I think 2k5) that mentions a 24 byte over
head that occurs when a column overflows a page and gets forwarded.
When I add piece in I come within +/- 1 byte of the correct numbers.
Ray Herring
"Kalen Delaney" wrote:

> n+2 is the storage for the varchar fields themselves, but there is other
> overhead required in each row.
> You did not say what version you are using, but the documentation you
> referred to is for SQL Server 2008, which is not in production yet, and the
> docs are incomplete.
> Take a look here for some information about additional overhead bytes:
> http://msdn2.microsoft.com/en-us/library/ms189124.aspx
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.InsideSQLServer.com
> http://DVD.kalendelaney.com
>
> "Ray" <Ray@.discussions.microsoft.com> wrote in message
> news:5B7540FA-BBC4-4B77-BC73-EA6FFE1524BE@.microsoft.com...
>
>
|||This overflow doesn't happen on SQL 2000 though. Row overflow is new in
2005. And you are getting your errors because the overflow is not allowed;
if you were using 2005, you wouldn't get these errors.
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://DVD.kalendelaney.com
"Ray" <Ray@.discussions.microsoft.com> wrote in message
news:41896F83-7882-4045-9174-E239F1D25308@.microsoft.com...[vbcol=seagreen]
> Thanks Kalen
> I thought about the version a little after I posted. I am using 2000
> right
> now.
> I found an additional reference (I think 2k5) that mentions a 24 byte over
> head that occurs when a column overflows a page and gets forwarded.
> When I add piece in I come within +/- 1 byte of the correct numbers.
> --
> Ray Herring
>
> "Kalen Delaney" wrote:
|||This is SQL 2000. I have about 100 client sites that have not moved to 2005.
The tables are being created and/or altered from scripts run in Isqlw.
This is the warning message.
"Warning: The table 'xxxxx' has been created but its maximum row size (8065)
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."
I have not received a runtime error on an insert or update so far but I
figure that is only a matter of time.
The table definition has 13 colums including a BigInt(primary key), 2 ints
, 1 datetime, 2 varchar(500) and 7 varchar(1000).
The pk is a clusterd index. The pk and the 2 ints are also foreign keys.
There are no other constraints or indexes.
So using what I have found the row size should be 8 + 4 + 4 + 8 + (7*1002) +
(2*502) = 8042. This should fit according to the documentation I have on 2K.
So where do the additional 23 bytes come from?
If you add the 24 bytes for a column overflow (I understand that is 2K5)
then the total is 8066 which is over the limit but not equal to the total
cited in the warning message.
Ray Herring
"Kalen Delaney" wrote:

> This overflow doesn't happen on SQL 2000 though. Row overflow is new in
> 2005. And you are getting your errors because the overflow is not allowed;
> if you were using 2005, you wouldn't get these errors.
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.InsideSQLServer.com
> http://DVD.kalendelaney.com
>
> "Ray" <Ray@.discussions.microsoft.com> wrote in message
> news:41896F83-7882-4045-9174-E239F1D25308@.microsoft.com...
>
>
|||I imagine I got your table script wrong because there is an extra byte you
didn't tell us about? When I run this I get the same message but 8065, not
8064 (this is on 8.00.2039):
CREATE TABLE dbo.splunge
(
BigID BIGINT,
FooID INT,
BarID INT,
EventDate DATETIME,
vc500_1 VARCHAR(500),
vc500_2 VARCHAR(500),
vc1000_1 VARCHAR(1000),
vc1000_2 VARCHAR(1000),
vc1000_3 VARCHAR(1000),
vc1000_4 VARCHAR(1000),
vc1000_5 VARCHAR(1000),
vc1000_6 VARCHAR(1000),
vc1000_7 VARCHAR(1000)
);
GO
DROP TABLE dbo.splunge;
Warning: The table 'splunge' has been created but its maximum row size
(8064) 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.
Some interesting stuff about this here, including 14 bytes of overhead that
are not documented in 2000 BOL or the Inside SQL Server 2000 book (though
Steve says the content was presented on Kalen's site
www.insidesqlserver.com, but I cannot find it now, since it has been
replaced with the 2005 version):
http://www.sqlservercentral.com/articles/Miscellaneous/pagesize/497/
So if we use Steve's information, we have:
2 bytes for status bits
2 bytes for # of columns
24 bytes for fixed length data
2 bytes for storing the length of fixed length data
8000 bytes for variable length data
18 bytes for variable length overhead
2 bytes for null bitmap (9-16 nullable columns)
8050
+14 bytes for reserved pointer space = 8064
|||>I imagine I got your table script wrong because there is an extra byte you
>didn't tell us about? When I run this I get the same message but 8065, not
>8064 (this is on 8.00.2039):
Sorry, I got that backwards, obviously. You claim 8065 but I get 8064.
And it made no difference if I made BigID PK and/or identity, or the ints
nullable, or the datetime nullable...
|||Thanks Aaron
I don't know why there is a one byte difference in our numbers. Actually my
calculations also differ by one from the warning message.
The columns in the table are in a different order. Do you think that might
be related?
Anyway the detail you have provided is sufficient for what I need to pass on
to the developer.
Thanks again.
Ray Herring
"Aaron Bertrand [SQL Server MVP]" wrote:

> Sorry, I got that backwards, obviously. You claim 8065 but I get 8064.
> And it made no difference if I made BigID PK and/or identity, or the ints
> nullable, or the datetime nullable...
>
|||There are another two bytes needed for the pointer in the slot array at the
end of the page, but this is counted against the size of the row.
And another two bytes for the first variable length column. I can't get the
numbers to add up right now either, but I'm not going to worry about it.
Also, I don't think the space for the pointer is counted against the total.
With Snapshot Isolation in SQL 2005, the max row size is just increased.
FYI, the null bitmap has a bit for every column, not just the nullable ones.
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://DVD.kalendelaney.com
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:6C3D5975-0885-4BE5-94EB-33D1B5312130@.microsoft.com...
>I imagine I got your table script wrong because there is an extra byte you
>didn't tell us about? When I run this I get the same message but 8065, not
>8064 (this is on 8.00.2039):
> CREATE TABLE dbo.splunge
> (
> BigID BIGINT,
> FooID INT,
> BarID INT,
> EventDate DATETIME,
> vc500_1 VARCHAR(500),
> vc500_2 VARCHAR(500),
> vc1000_1 VARCHAR(1000),
> vc1000_2 VARCHAR(1000),
> vc1000_3 VARCHAR(1000),
> vc1000_4 VARCHAR(1000),
> vc1000_5 VARCHAR(1000),
> vc1000_6 VARCHAR(1000),
> vc1000_7 VARCHAR(1000)
> );
> GO
> DROP TABLE dbo.splunge;
> --
> Warning: The table 'splunge' has been created but its maximum row size
> (8064) 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.
> Some interesting stuff about this here, including 14 bytes of overhead
> that are not documented in 2000 BOL or the Inside SQL Server 2000 book
> (though Steve says the content was presented on Kalen's site
> www.insidesqlserver.com, but I cannot find it now, since it has been
> replaced with the 2005 version):
> http://www.sqlservercentral.com/articles/Miscellaneous/pagesize/497/
> So if we use Steve's information, we have:
> 2 bytes for status bits
> 2 bytes for # of columns
> 24 bytes for fixed length data
> 2 bytes for storing the length of fixed length data
> 8000 bytes for variable length data
> 18 bytes for variable length overhead
> 2 bytes for null bitmap (9-16 nullable columns)
> 8050
> +14 bytes for reserved pointer space = 8064
|||Thanks Kalen and Arron.
I had just found a reference
"http://msdn2.microsoft.com/en-us/library/ms189124.aspx" (2005 again) that
mentions the null bit map and also other over head bytes.
I guess the row size limit is not simply 8K, 8060, 8092, xxxx but rather
depends on how you define it. It seems that MS would have rolled all of the
basic overhead (null bit map, etc) into the basic page size so that we would
only need to work out the variable and fixed lenght columns.
I guess I will start falling back on the =/-10% sort of estimate. It sounds
like even the column over flow is a mixed blessing performance wise.
Thanks for the assistance guys.
Ray Herring
"Kalen Delaney" wrote:

> There are another two bytes needed for the pointer in the slot array at the
> end of the page, but this is counted against the size of the row.
> And another two bytes for the first variable length column. I can't get the
> numbers to add up right now either, but I'm not going to worry about it.
> Also, I don't think the space for the pointer is counted against the total.
> With Snapshot Isolation in SQL 2005, the max row size is just increased.
> FYI, the null bitmap has a bit for every column, not just the nullable ones.
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.InsideSQLServer.com
> http://DVD.kalendelaney.com
>
> "Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
> news:6C3D5975-0885-4BE5-94EB-33D1B5312130@.microsoft.com...
>
>

Actual storage of Varchar values

I have a table with a definition similar to the following.
create table sizetest (pk int identity(1,1)
, V1 Varchar(1000) , V2 varchar(1000) , V3 varchar(1000)
, V4 Varchar(1000) , V5 varchar(1000) , V6 varchar(1000)
, V7 Varchar(1000) , V8 varchar(1000) , V9 varchar(x)
I have varied the length of column V9 between 16 and 40. When X <= 16 the
table is created without warning. When x = 40 I am told that the row length
is 8084 or 24 bytes too big. X= 20 says 8064 or 4 bytes too big. I have not
been able to come up with a definition for varchar storage that matches the
results I am seeing. This Reference
(http://msdn2.microsoft.com/en-us/library/ms176089(SQL.100).aspx) says that n
+ 2 is the answer but that does not work with any of this data I have
generated. For Example (8 * 1002) + 42 + 4 (for the pk) = 8062 which is not
equal to 8084 and is more than 8060 which BOL says is the max allowed. So I
understand the warning but not the numbers. On the other hand (8 * 1002) + 20
+ 4 = 8040 which is not equal to 8064 and is less than 8060. So why is this
one getting the warning?
I changed the table definition to 3 varchar fields 4000, 4000, and x and got
similar results but different numbers. Still no predicatble results.
How can I determine exactly what potential strorage is required if all of
the varchar fields are full.
My C++ developer won't believe me unless I can give him the details and show
a way to reliably predict the row length for any combination of column
types/sizes.
Thanks
--
Ray Herringn+2 is the storage for the varchar fields themselves, but there is other
overhead required in each row.
You did not say what version you are using, but the documentation you
referred to is for SQL Server 2008, which is not in production yet, and the
docs are incomplete.
Take a look here for some information about additional overhead bytes:
http://msdn2.microsoft.com/en-us/library/ms189124.aspx
--
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://DVD.kalendelaney.com
"Ray" <Ray@.discussions.microsoft.com> wrote in message
news:5B7540FA-BBC4-4B77-BC73-EA6FFE1524BE@.microsoft.com...
> I have a table with a definition similar to the following.
> create table sizetest (pk int identity(1,1)
> , V1 Varchar(1000) , V2 varchar(1000) , V3 varchar(1000)
> , V4 Varchar(1000) , V5 varchar(1000) , V6 varchar(1000)
> , V7 Varchar(1000) , V8 varchar(1000) , V9 varchar(x)
> I have varied the length of column V9 between 16 and 40. When X <= 16 the
> table is created without warning. When x = 40 I am told that the row
> length
> is 8084 or 24 bytes too big. X= 20 says 8064 or 4 bytes too big. I have
> not
> been able to come up with a definition for varchar storage that matches
> the
> results I am seeing. This Reference
> (http://msdn2.microsoft.com/en-us/library/ms176089(SQL.100).aspx) says
> that n
> + 2 is the answer but that does not work with any of this data I have
> generated. For Example (8 * 1002) + 42 + 4 (for the pk) = 8062 which is
> not
> equal to 8084 and is more than 8060 which BOL says is the max allowed. So
> I
> understand the warning but not the numbers. On the other hand (8 * 1002) +
> 20
> + 4 = 8040 which is not equal to 8064 and is less than 8060. So why is
> this
> one getting the warning?
> I changed the table definition to 3 varchar fields 4000, 4000, and x and
> got
> similar results but different numbers. Still no predicatble results.
> How can I determine exactly what potential strorage is required if all of
> the varchar fields are full.
> My C++ developer won't believe me unless I can give him the details and
> show
> a way to reliably predict the row length for any combination of column
> types/sizes.
> Thanks
> --
> Ray Herring|||Thanks Kalen
I thought about the version a little after I posted. I am using 2000 right
now.
I found an additional reference (I think 2k5) that mentions a 24 byte over
head that occurs when a column overflows a page and gets forwarded.
When I add piece in I come within +/- 1 byte of the correct numbers.
--
Ray Herring
"Kalen Delaney" wrote:
> n+2 is the storage for the varchar fields themselves, but there is other
> overhead required in each row.
> You did not say what version you are using, but the documentation you
> referred to is for SQL Server 2008, which is not in production yet, and the
> docs are incomplete.
> Take a look here for some information about additional overhead bytes:
> http://msdn2.microsoft.com/en-us/library/ms189124.aspx
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.InsideSQLServer.com
> http://DVD.kalendelaney.com
>
> "Ray" <Ray@.discussions.microsoft.com> wrote in message
> news:5B7540FA-BBC4-4B77-BC73-EA6FFE1524BE@.microsoft.com...
> > I have a table with a definition similar to the following.
> > create table sizetest (pk int identity(1,1)
> > , V1 Varchar(1000) , V2 varchar(1000) , V3 varchar(1000)
> > , V4 Varchar(1000) , V5 varchar(1000) , V6 varchar(1000)
> > , V7 Varchar(1000) , V8 varchar(1000) , V9 varchar(x)
> > I have varied the length of column V9 between 16 and 40. When X <= 16 the
> > table is created without warning. When x = 40 I am told that the row
> > length
> > is 8084 or 24 bytes too big. X= 20 says 8064 or 4 bytes too big. I have
> > not
> > been able to come up with a definition for varchar storage that matches
> > the
> > results I am seeing. This Reference
> > (http://msdn2.microsoft.com/en-us/library/ms176089(SQL.100).aspx) says
> > that n
> > + 2 is the answer but that does not work with any of this data I have
> > generated. For Example (8 * 1002) + 42 + 4 (for the pk) = 8062 which is
> > not
> > equal to 8084 and is more than 8060 which BOL says is the max allowed. So
> > I
> > understand the warning but not the numbers. On the other hand (8 * 1002) +
> > 20
> > + 4 = 8040 which is not equal to 8064 and is less than 8060. So why is
> > this
> > one getting the warning?
> > I changed the table definition to 3 varchar fields 4000, 4000, and x and
> > got
> > similar results but different numbers. Still no predicatble results.
> > How can I determine exactly what potential strorage is required if all of
> > the varchar fields are full.
> >
> > My C++ developer won't believe me unless I can give him the details and
> > show
> > a way to reliably predict the row length for any combination of column
> > types/sizes.
> > Thanks
> >
> > --
> > Ray Herring
>
>|||This overflow doesn't happen on SQL 2000 though. Row overflow is new in
2005. And you are getting your errors because the overflow is not allowed;
if you were using 2005, you wouldn't get these errors.
--
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://DVD.kalendelaney.com
"Ray" <Ray@.discussions.microsoft.com> wrote in message
news:41896F83-7882-4045-9174-E239F1D25308@.microsoft.com...
> Thanks Kalen
> I thought about the version a little after I posted. I am using 2000
> right
> now.
> I found an additional reference (I think 2k5) that mentions a 24 byte over
> head that occurs when a column overflows a page and gets forwarded.
> When I add piece in I come within +/- 1 byte of the correct numbers.
> --
> Ray Herring
>
> "Kalen Delaney" wrote:
>> n+2 is the storage for the varchar fields themselves, but there is other
>> overhead required in each row.
>> You did not say what version you are using, but the documentation you
>> referred to is for SQL Server 2008, which is not in production yet, and
>> the
>> docs are incomplete.
>> Take a look here for some information about additional overhead bytes:
>> http://msdn2.microsoft.com/en-us/library/ms189124.aspx
>> --
>> HTH
>> Kalen Delaney, SQL Server MVP
>> www.InsideSQLServer.com
>> http://DVD.kalendelaney.com
>>
>> "Ray" <Ray@.discussions.microsoft.com> wrote in message
>> news:5B7540FA-BBC4-4B77-BC73-EA6FFE1524BE@.microsoft.com...
>> > I have a table with a definition similar to the following.
>> > create table sizetest (pk int identity(1,1)
>> > , V1 Varchar(1000) , V2 varchar(1000) , V3 varchar(1000)
>> > , V4 Varchar(1000) , V5 varchar(1000) , V6 varchar(1000)
>> > , V7 Varchar(1000) , V8 varchar(1000) , V9 varchar(x)
>> > I have varied the length of column V9 between 16 and 40. When X <= 16
>> > the
>> > table is created without warning. When x = 40 I am told that the row
>> > length
>> > is 8084 or 24 bytes too big. X= 20 says 8064 or 4 bytes too big. I
>> > have
>> > not
>> > been able to come up with a definition for varchar storage that matches
>> > the
>> > results I am seeing. This Reference
>> > (http://msdn2.microsoft.com/en-us/library/ms176089(SQL.100).aspx) says
>> > that n
>> > + 2 is the answer but that does not work with any of this data I have
>> > generated. For Example (8 * 1002) + 42 + 4 (for the pk) = 8062 which
>> > is
>> > not
>> > equal to 8084 and is more than 8060 which BOL says is the max allowed.
>> > So
>> > I
>> > understand the warning but not the numbers. On the other hand (8 *
>> > 1002) +
>> > 20
>> > + 4 = 8040 which is not equal to 8064 and is less than 8060. So why
>> > is
>> > this
>> > one getting the warning?
>> > I changed the table definition to 3 varchar fields 4000, 4000, and x
>> > and
>> > got
>> > similar results but different numbers. Still no predicatble results.
>> > How can I determine exactly what potential strorage is required if all
>> > of
>> > the varchar fields are full.
>> >
>> > My C++ developer won't believe me unless I can give him the details and
>> > show
>> > a way to reliably predict the row length for any combination of column
>> > types/sizes.
>> > Thanks
>> >
>> > --
>> > Ray Herring
>>|||This is SQL 2000. I have about 100 client sites that have not moved to 2005.
The tables are being created and/or altered from scripts run in Isqlw.
This is the warning message.
"Warning: The table 'xxxxx' has been created but its maximum row size (8065)
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."
I have not received a runtime error on an insert or update so far but I
figure that is only a matter of time.
The table definition has 13 colums including a BigInt(primary key), 2 ints
, 1 datetime, 2 varchar(500) and 7 varchar(1000).
The pk is a clusterd index. The pk and the 2 ints are also foreign keys.
There are no other constraints or indexes.
So using what I have found the row size should be 8 + 4 + 4 + 8 + (7*1002) +
(2*502) = 8042. This should fit according to the documentation I have on 2K.
So where do the additional 23 bytes come from?
If you add the 24 bytes for a column overflow (I understand that is 2K5)
then the total is 8066 which is over the limit but not equal to the total
cited in the warning message.
--
Ray Herring
"Kalen Delaney" wrote:
> This overflow doesn't happen on SQL 2000 though. Row overflow is new in
> 2005. And you are getting your errors because the overflow is not allowed;
> if you were using 2005, you wouldn't get these errors.
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.InsideSQLServer.com
> http://DVD.kalendelaney.com
>
> "Ray" <Ray@.discussions.microsoft.com> wrote in message
> news:41896F83-7882-4045-9174-E239F1D25308@.microsoft.com...
> > Thanks Kalen
> > I thought about the version a little after I posted. I am using 2000
> > right
> > now.
> > I found an additional reference (I think 2k5) that mentions a 24 byte over
> > head that occurs when a column overflows a page and gets forwarded.
> > When I add piece in I come within +/- 1 byte of the correct numbers.
> > --
> > Ray Herring
> >
> >
> > "Kalen Delaney" wrote:
> >
> >> n+2 is the storage for the varchar fields themselves, but there is other
> >> overhead required in each row.
> >>
> >> You did not say what version you are using, but the documentation you
> >> referred to is for SQL Server 2008, which is not in production yet, and
> >> the
> >> docs are incomplete.
> >>
> >> Take a look here for some information about additional overhead bytes:
> >> http://msdn2.microsoft.com/en-us/library/ms189124.aspx
> >>
> >> --
> >> HTH
> >> Kalen Delaney, SQL Server MVP
> >> www.InsideSQLServer.com
> >> http://DVD.kalendelaney.com
> >>
> >>
> >> "Ray" <Ray@.discussions.microsoft.com> wrote in message
> >> news:5B7540FA-BBC4-4B77-BC73-EA6FFE1524BE@.microsoft.com...
> >> > I have a table with a definition similar to the following.
> >> > create table sizetest (pk int identity(1,1)
> >> > , V1 Varchar(1000) , V2 varchar(1000) , V3 varchar(1000)
> >> > , V4 Varchar(1000) , V5 varchar(1000) , V6 varchar(1000)
> >> > , V7 Varchar(1000) , V8 varchar(1000) , V9 varchar(x)
> >> > I have varied the length of column V9 between 16 and 40. When X <= 16
> >> > the
> >> > table is created without warning. When x = 40 I am told that the row
> >> > length
> >> > is 8084 or 24 bytes too big. X= 20 says 8064 or 4 bytes too big. I
> >> > have
> >> > not
> >> > been able to come up with a definition for varchar storage that matches
> >> > the
> >> > results I am seeing. This Reference
> >> > (http://msdn2.microsoft.com/en-us/library/ms176089(SQL.100).aspx) says
> >> > that n
> >> > + 2 is the answer but that does not work with any of this data I have
> >> > generated. For Example (8 * 1002) + 42 + 4 (for the pk) = 8062 which
> >> > is
> >> > not
> >> > equal to 8084 and is more than 8060 which BOL says is the max allowed.
> >> > So
> >> > I
> >> > understand the warning but not the numbers. On the other hand (8 *
> >> > 1002) +
> >> > 20
> >> > + 4 = 8040 which is not equal to 8064 and is less than 8060. So why
> >> > is
> >> > this
> >> > one getting the warning?
> >> > I changed the table definition to 3 varchar fields 4000, 4000, and x
> >> > and
> >> > got
> >> > similar results but different numbers. Still no predicatble results.
> >> > How can I determine exactly what potential strorage is required if all
> >> > of
> >> > the varchar fields are full.
> >> >
> >> > My C++ developer won't believe me unless I can give him the details and
> >> > show
> >> > a way to reliably predict the row length for any combination of column
> >> > types/sizes.
> >> > Thanks
> >> >
> >> > --
> >> > Ray Herring
> >>
> >>
> >>
>
>|||I imagine I got your table script wrong because there is an extra byte you
didn't tell us about? When I run this I get the same message but 8065, not
8064 (this is on 8.00.2039):
CREATE TABLE dbo.splunge
(
BigID BIGINT,
FooID INT,
BarID INT,
EventDate DATETIME,
vc500_1 VARCHAR(500),
vc500_2 VARCHAR(500),
vc1000_1 VARCHAR(1000),
vc1000_2 VARCHAR(1000),
vc1000_3 VARCHAR(1000),
vc1000_4 VARCHAR(1000),
vc1000_5 VARCHAR(1000),
vc1000_6 VARCHAR(1000),
vc1000_7 VARCHAR(1000)
);
GO
DROP TABLE dbo.splunge;
--
Warning: The table 'splunge' has been created but its maximum row size
(8064) 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.
Some interesting stuff about this here, including 14 bytes of overhead that
are not documented in 2000 BOL or the Inside SQL Server 2000 book (though
Steve says the content was presented on Kalen's site
www.insidesqlserver.com, but I cannot find it now, since it has been
replaced with the 2005 version):
http://www.sqlservercentral.com/articles/Miscellaneous/pagesize/497/
So if we use Steve's information, we have:
2 bytes for status bits
2 bytes for # of columns
24 bytes for fixed length data
2 bytes for storing the length of fixed length data
8000 bytes for variable length data
18 bytes for variable length overhead
2 bytes for null bitmap (9-16 nullable columns)
8050
+14 bytes for reserved pointer space = 8064|||>I imagine I got your table script wrong because there is an extra byte you
>didn't tell us about? When I run this I get the same message but 8065, not
>8064 (this is on 8.00.2039):
Sorry, I got that backwards, obviously. You claim 8065 but I get 8064.
And it made no difference if I made BigID PK and/or identity, or the ints
nullable, or the datetime nullable...|||Thanks Aaron
I don't know why there is a one byte difference in our numbers. Actually my
calculations also differ by one from the warning message.
The columns in the table are in a different order. Do you think that might
be related?
Anyway the detail you have provided is sufficient for what I need to pass on
to the developer.
Thanks again.
--
Ray Herring
"Aaron Bertrand [SQL Server MVP]" wrote:
> >I imagine I got your table script wrong because there is an extra byte you
> >didn't tell us about? When I run this I get the same message but 8065, not
> >8064 (this is on 8.00.2039):
> Sorry, I got that backwards, obviously. You claim 8065 but I get 8064.
> And it made no difference if I made BigID PK and/or identity, or the ints
> nullable, or the datetime nullable...
>|||There are another two bytes needed for the pointer in the slot array at the
end of the page, but this is counted against the size of the row.
And another two bytes for the first variable length column. I can't get the
numbers to add up right now either, but I'm not going to worry about it.
Also, I don't think the space for the pointer is counted against the total.
With Snapshot Isolation in SQL 2005, the max row size is just increased.
FYI, the null bitmap has a bit for every column, not just the nullable ones.
--
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://DVD.kalendelaney.com
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:6C3D5975-0885-4BE5-94EB-33D1B5312130@.microsoft.com...
>I imagine I got your table script wrong because there is an extra byte you
>didn't tell us about? When I run this I get the same message but 8065, not
>8064 (this is on 8.00.2039):
> CREATE TABLE dbo.splunge
> (
> BigID BIGINT,
> FooID INT,
> BarID INT,
> EventDate DATETIME,
> vc500_1 VARCHAR(500),
> vc500_2 VARCHAR(500),
> vc1000_1 VARCHAR(1000),
> vc1000_2 VARCHAR(1000),
> vc1000_3 VARCHAR(1000),
> vc1000_4 VARCHAR(1000),
> vc1000_5 VARCHAR(1000),
> vc1000_6 VARCHAR(1000),
> vc1000_7 VARCHAR(1000)
> );
> GO
> DROP TABLE dbo.splunge;
> --
> Warning: The table 'splunge' has been created but its maximum row size
> (8064) 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.
> Some interesting stuff about this here, including 14 bytes of overhead
> that are not documented in 2000 BOL or the Inside SQL Server 2000 book
> (though Steve says the content was presented on Kalen's site
> www.insidesqlserver.com, but I cannot find it now, since it has been
> replaced with the 2005 version):
> http://www.sqlservercentral.com/articles/Miscellaneous/pagesize/497/
> So if we use Steve's information, we have:
> 2 bytes for status bits
> 2 bytes for # of columns
> 24 bytes for fixed length data
> 2 bytes for storing the length of fixed length data
> 8000 bytes for variable length data
> 18 bytes for variable length overhead
> 2 bytes for null bitmap (9-16 nullable columns)
> 8050
> +14 bytes for reserved pointer space = 8064|||Thanks Kalen and Arron.
I had just found a reference
"http://msdn2.microsoft.com/en-us/library/ms189124.aspx" (2005 again) that
mentions the null bit map and also other over head bytes.
I guess the row size limit is not simply 8K, 8060, 8092, xxxx but rather
depends on how you define it. It seems that MS would have rolled all of the
basic overhead (null bit map, etc) into the basic page size so that we would
only need to work out the variable and fixed lenght columns.
I guess I will start falling back on the =/-10% sort of estimate. It sounds
like even the column over flow is a mixed blessing performance wise.
Thanks for the assistance guys.
--
Ray Herring
"Kalen Delaney" wrote:
> There are another two bytes needed for the pointer in the slot array at the
> end of the page, but this is counted against the size of the row.
> And another two bytes for the first variable length column. I can't get the
> numbers to add up right now either, but I'm not going to worry about it.
> Also, I don't think the space for the pointer is counted against the total.
> With Snapshot Isolation in SQL 2005, the max row size is just increased.
> FYI, the null bitmap has a bit for every column, not just the nullable ones.
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.InsideSQLServer.com
> http://DVD.kalendelaney.com
>
> "Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
> news:6C3D5975-0885-4BE5-94EB-33D1B5312130@.microsoft.com...
> >I imagine I got your table script wrong because there is an extra byte you
> >didn't tell us about? When I run this I get the same message but 8065, not
> >8064 (this is on 8.00.2039):
> >
> > CREATE TABLE dbo.splunge
> > (
> > BigID BIGINT,
> > FooID INT,
> > BarID INT,
> > EventDate DATETIME,
> > vc500_1 VARCHAR(500),
> > vc500_2 VARCHAR(500),
> > vc1000_1 VARCHAR(1000),
> > vc1000_2 VARCHAR(1000),
> > vc1000_3 VARCHAR(1000),
> > vc1000_4 VARCHAR(1000),
> > vc1000_5 VARCHAR(1000),
> > vc1000_6 VARCHAR(1000),
> > vc1000_7 VARCHAR(1000)
> > );
> > GO
> > DROP TABLE dbo.splunge;
> >
> > --
> > Warning: The table 'splunge' has been created but its maximum row size
> > (8064) 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.
> >
> > Some interesting stuff about this here, including 14 bytes of overhead
> > that are not documented in 2000 BOL or the Inside SQL Server 2000 book
> > (though Steve says the content was presented on Kalen's site
> > www.insidesqlserver.com, but I cannot find it now, since it has been
> > replaced with the 2005 version):
> >
> > http://www.sqlservercentral.com/articles/Miscellaneous/pagesize/497/
> >
> > So if we use Steve's information, we have:
> >
> > 2 bytes for status bits
> > 2 bytes for # of columns
> > 24 bytes for fixed length data
> > 2 bytes for storing the length of fixed length data
> > 8000 bytes for variable length data
> > 18 bytes for variable length overhead
> > 2 bytes for null bitmap (9-16 nullable columns)
> >
> > 8050
> >
> > +14 bytes for reserved pointer space = 8064
>
>

actual space used in data file

Hi all,
I'm having a problem need your help:
I can see all properties of datafile (FilegroupName, FileID, FileType,
Location, CurrentSize, Space Used) by Enterpise Manager.
Please tell me how do I see all those properties by SQL Query Analyzer.
I want to know actual space used in data file to shrink file.
Thanks in advanced,
TN
sp_helpdb <your db>
can give you alot of this info
"TN" <TN@.discussions.microsoft.com> wrote in message
news:A86BD7C1-BC03-4163-80C9-EFB6A688D0B3@.microsoft.com...
> Hi all,
> I'm having a problem need your help:
> I can see all properties of datafile (FilegroupName, FileID, FileType,
> Location, CurrentSize, Space Used) by Enterpise Manager.
> Please tell me how do I see all those properties by SQL Query Analyzer.
> I want to know actual space used in data file to shrink file.
> Thanks in advanced,
> TN
|||Thanks for your help.
But this Proc does not tell me space used in data file.
I can see space used in data file
right click on DB
select All Tasks menu item
select Shrink Database..
click Files button
I can see all
I want to know like this in SQL Query Analyzer
Thanks
TN
"Armando Prato" wrote:

> sp_helpdb <your db>
> can give you alot of this info
> "TN" <TN@.discussions.microsoft.com> wrote in message
> news:A86BD7C1-BC03-4163-80C9-EFB6A688D0B3@.microsoft.com...
>
>
|||Enterprise Mangler uses an undocumented DBCC function called DBCC
SHOWFILESTATS. You will need to build a tool around it if you want more
than absolute bare-bones information.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"TN" <TN@.discussions.microsoft.com> wrote in message
news:305ECE55-D52D-4326-995C-221F8EE005D2@.microsoft.com...[vbcol=seagreen]
> Thanks for your help.
> But this Proc does not tell me space used in data file.
> I can see space used in data file
> right click on DB
> select All Tasks menu item
> select Shrink Database..
> click Files button
> I can see all
> I want to know like this in SQL Query Analyzer
> Thanks
> TN
> "Armando Prato" wrote:
Analyzer.[vbcol=seagreen]
|||Use profiler - you can see exactly what queries EM uses to do this.
"TN" <TN@.discussions.microsoft.com> wrote in message
news:305ECE55-D52D-4326-995C-221F8EE005D2@.microsoft.com...[vbcol=seagreen]
> Thanks for your help.
> But this Proc does not tell me space used in data file.
> I can see space used in data file
> right click on DB
> select All Tasks menu item
> select Shrink Database..
> click Files button
> I can see all
> I want to know like this in SQL Query Analyzer
> Thanks
> TN
> "Armando Prato" wrote:
Analyzer.[vbcol=seagreen]
|||Thanks Scott,
I do not know what is profiler.
Please tell me how to use this tool.
Thanks
TN
"Scott Morris" wrote:

> Use profiler - you can see exactly what queries EM uses to do this.
> "TN" <TN@.discussions.microsoft.com> wrote in message
> news:305ECE55-D52D-4326-995C-221F8EE005D2@.microsoft.com...
> Analyzer.
>
>
|||Perhaps now would be a good time to spend a little effort familiarizing
yourself with the tools provided with SQL Server (of which profiler is one).
Each is documented in the SQL Server documentation; the appropriate section
of the documentation can be accessed directly from each application (for
those that are not command-line based) using the Help menu. In addition,
you can find answers to many basic and advanced questions by searching the
index or by searching the documentation just by guessing with appropriate
words. The online documentation shipped with SQL server is generally
referred to as BOL in the newsgroups (Books OnLine - which is the text of
the corresponding item in the Start menu).
"TN" <TN@.discussions.microsoft.com> wrote in message
news:E10E2A99-F3FF-4E90-A253-EBE562FD1393@.microsoft.com...[vbcol=seagreen]
> Thanks Scott,
> I do not know what is profiler.
> Please tell me how to use this tool.
> Thanks
> TN
> "Scott Morris" wrote:
FileType,[vbcol=seagreen]

actual space used in data file

Hi all,
I'm having a problem need your help:
I can see all properties of datafile (FilegroupName, FileID, FileType,
Location, CurrentSize, Space Used) by Enterpise Manager.
Please tell me how do I see all those properties by SQL Query Analyzer.
I want to know actual space used in data file to shrink file.
Thanks in advanced,
TNsp_helpdb <your db>
can give you alot of this info
"TN" <TN@.discussions.microsoft.com> wrote in message
news:A86BD7C1-BC03-4163-80C9-EFB6A688D0B3@.microsoft.com...
> Hi all,
> I'm having a problem need your help:
> I can see all properties of datafile (FilegroupName, FileID, FileType,
> Location, CurrentSize, Space Used) by Enterpise Manager.
> Please tell me how do I see all those properties by SQL Query Analyzer.
> I want to know actual space used in data file to shrink file.
> Thanks in advanced,
> TN|||Thanks for your help.
But this Proc does not tell me space used in data file.
I can see space used in data file
right click on DB
select All Tasks menu item
select Shrink Database..
click Files button
I can see all
I want to know like this in SQL Query Analyzer
Thanks
TN
"Armando Prato" wrote:

> sp_helpdb <your db>
> can give you alot of this info
> "TN" <TN@.discussions.microsoft.com> wrote in message
> news:A86BD7C1-BC03-4163-80C9-EFB6A688D0B3@.microsoft.com...
>
>|||Enterprise Mangler uses an undocumented DBCC function called DBCC
SHOWFILESTATS. You will need to build a tool around it if you want more
than absolute bare-bones information.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"TN" <TN@.discussions.microsoft.com> wrote in message
news:305ECE55-D52D-4326-995C-221F8EE005D2@.microsoft.com...[vbcol=seagreen]
> Thanks for your help.
> But this Proc does not tell me space used in data file.
> I can see space used in data file
> right click on DB
> select All Tasks menu item
> select Shrink Database..
> click Files button
> I can see all
> I want to know like this in SQL Query Analyzer
> Thanks
> TN
> "Armando Prato" wrote:
>
Analyzer.[vbcol=seagreen]|||Use profiler - you can see exactly what queries EM uses to do this.
"TN" <TN@.discussions.microsoft.com> wrote in message
news:305ECE55-D52D-4326-995C-221F8EE005D2@.microsoft.com...[vbcol=seagreen]
> Thanks for your help.
> But this Proc does not tell me space used in data file.
> I can see space used in data file
> right click on DB
> select All Tasks menu item
> select Shrink Database..
> click Files button
> I can see all
> I want to know like this in SQL Query Analyzer
> Thanks
> TN
> "Armando Prato" wrote:
>
Analyzer.[vbcol=seagreen]|||Thanks Scott,
I do not know what is profiler.
Please tell me how to use this tool.
Thanks
TN
"Scott Morris" wrote:

> Use profiler - you can see exactly what queries EM uses to do this.
> "TN" <TN@.discussions.microsoft.com> wrote in message
> news:305ECE55-D52D-4326-995C-221F8EE005D2@.microsoft.com...
> Analyzer.
>
>|||Perhaps now would be a good time to spend a little effort familiarizing
yourself with the tools provided with SQL Server (of which profiler is one).
Each is documented in the SQL Server documentation; the appropriate section
of the documentation can be accessed directly from each application (for
those that are not command-line based) using the Help menu. In addition,
you can find answers to many basic and advanced questions by searching the
index or by searching the documentation just by guessing with appropriate
words. The online documentation shipped with SQL server is generally
referred to as BOL in the newsgroups (Books OnLine - which is the text of
the corresponding item in the Start menu).
"TN" <TN@.discussions.microsoft.com> wrote in message
news:E10E2A99-F3FF-4E90-A253-EBE562FD1393@.microsoft.com...[vbcol=seagreen]
> Thanks Scott,
> I do not know what is profiler.
> Please tell me how to use this tool.
> Thanks
> TN
> "Scott Morris" wrote:
>
FileType,[vbcol=seagreen]

actual space used in data file

Hi all,
I'm having a problem need your help:
I can see all properties of datafile (FilegroupName, FileID, FileType,
Location, CurrentSize, Space Used) by Enterpise Manager.
Please tell me how do I see all those properties by SQL Query Analyzer.
I want to know actual space used in data file to shrink file.
Thanks in advanced,
TNsp_helpdb <your db>
can give you alot of this info
"TN" <TN@.discussions.microsoft.com> wrote in message
news:A86BD7C1-BC03-4163-80C9-EFB6A688D0B3@.microsoft.com...
> Hi all,
> I'm having a problem need your help:
> I can see all properties of datafile (FilegroupName, FileID, FileType,
> Location, CurrentSize, Space Used) by Enterpise Manager.
> Please tell me how do I see all those properties by SQL Query Analyzer.
> I want to know actual space used in data file to shrink file.
> Thanks in advanced,
> TN|||Thanks for your help.
But this Proc does not tell me space used in data file.
I can see space used in data file
right click on DB
select All Tasks menu item
select Shrink Database..
click Files button
I can see all
I want to know like this in SQL Query Analyzer
Thanks
TN
"Armando Prato" wrote:
> sp_helpdb <your db>
> can give you alot of this info
> "TN" <TN@.discussions.microsoft.com> wrote in message
> news:A86BD7C1-BC03-4163-80C9-EFB6A688D0B3@.microsoft.com...
> > Hi all,
> >
> > I'm having a problem need your help:
> >
> > I can see all properties of datafile (FilegroupName, FileID, FileType,
> > Location, CurrentSize, Space Used) by Enterpise Manager.
> >
> > Please tell me how do I see all those properties by SQL Query Analyzer.
> > I want to know actual space used in data file to shrink file.
> >
> > Thanks in advanced,
> > TN
>
>|||Enterprise Mangler uses an undocumented DBCC function called DBCC
SHOWFILESTATS. You will need to build a tool around it if you want more
than absolute bare-bones information.
--
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"TN" <TN@.discussions.microsoft.com> wrote in message
news:305ECE55-D52D-4326-995C-221F8EE005D2@.microsoft.com...
> Thanks for your help.
> But this Proc does not tell me space used in data file.
> I can see space used in data file
> right click on DB
> select All Tasks menu item
> select Shrink Database..
> click Files button
> I can see all
> I want to know like this in SQL Query Analyzer
> Thanks
> TN
> "Armando Prato" wrote:
> > sp_helpdb <your db>
> >
> > can give you alot of this info
> >
> > "TN" <TN@.discussions.microsoft.com> wrote in message
> > news:A86BD7C1-BC03-4163-80C9-EFB6A688D0B3@.microsoft.com...
> > > Hi all,
> > >
> > > I'm having a problem need your help:
> > >
> > > I can see all properties of datafile (FilegroupName, FileID, FileType,
> > > Location, CurrentSize, Space Used) by Enterpise Manager.
> > >
> > > Please tell me how do I see all those properties by SQL Query
Analyzer.
> > > I want to know actual space used in data file to shrink file.
> > >
> > > Thanks in advanced,
> > > TN
> >
> >
> >|||Use profiler - you can see exactly what queries EM uses to do this.
"TN" <TN@.discussions.microsoft.com> wrote in message
news:305ECE55-D52D-4326-995C-221F8EE005D2@.microsoft.com...
> Thanks for your help.
> But this Proc does not tell me space used in data file.
> I can see space used in data file
> right click on DB
> select All Tasks menu item
> select Shrink Database..
> click Files button
> I can see all
> I want to know like this in SQL Query Analyzer
> Thanks
> TN
> "Armando Prato" wrote:
> > sp_helpdb <your db>
> >
> > can give you alot of this info
> >
> > "TN" <TN@.discussions.microsoft.com> wrote in message
> > news:A86BD7C1-BC03-4163-80C9-EFB6A688D0B3@.microsoft.com...
> > > Hi all,
> > >
> > > I'm having a problem need your help:
> > >
> > > I can see all properties of datafile (FilegroupName, FileID, FileType,
> > > Location, CurrentSize, Space Used) by Enterpise Manager.
> > >
> > > Please tell me how do I see all those properties by SQL Query
Analyzer.
> > > I want to know actual space used in data file to shrink file.
> > >
> > > Thanks in advanced,
> > > TN
> >
> >
> >|||Thanks Scott,
I do not know what is profiler.
Please tell me how to use this tool.
Thanks
TN
"Scott Morris" wrote:
> Use profiler - you can see exactly what queries EM uses to do this.
> "TN" <TN@.discussions.microsoft.com> wrote in message
> news:305ECE55-D52D-4326-995C-221F8EE005D2@.microsoft.com...
> > Thanks for your help.
> > But this Proc does not tell me space used in data file.
> > I can see space used in data file
> >
> > right click on DB
> > select All Tasks menu item
> > select Shrink Database..
> > click Files button
> >
> > I can see all
> >
> > I want to know like this in SQL Query Analyzer
> >
> > Thanks
> > TN
> >
> > "Armando Prato" wrote:
> >
> > > sp_helpdb <your db>
> > >
> > > can give you alot of this info
> > >
> > > "TN" <TN@.discussions.microsoft.com> wrote in message
> > > news:A86BD7C1-BC03-4163-80C9-EFB6A688D0B3@.microsoft.com...
> > > > Hi all,
> > > >
> > > > I'm having a problem need your help:
> > > >
> > > > I can see all properties of datafile (FilegroupName, FileID, FileType,
> > > > Location, CurrentSize, Space Used) by Enterpise Manager.
> > > >
> > > > Please tell me how do I see all those properties by SQL Query
> Analyzer.
> > > > I want to know actual space used in data file to shrink file.
> > > >
> > > > Thanks in advanced,
> > > > TN
> > >
> > >
> > >
>
>|||Perhaps now would be a good time to spend a little effort familiarizing
yourself with the tools provided with SQL Server (of which profiler is one).
Each is documented in the SQL Server documentation; the appropriate section
of the documentation can be accessed directly from each application (for
those that are not command-line based) using the Help menu. In addition,
you can find answers to many basic and advanced questions by searching the
index or by searching the documentation just by guessing with appropriate
words. The online documentation shipped with SQL server is generally
referred to as BOL in the newsgroups (Books OnLine - which is the text of
the corresponding item in the Start menu).
"TN" <TN@.discussions.microsoft.com> wrote in message
news:E10E2A99-F3FF-4E90-A253-EBE562FD1393@.microsoft.com...
> Thanks Scott,
> I do not know what is profiler.
> Please tell me how to use this tool.
> Thanks
> TN
> "Scott Morris" wrote:
> > Use profiler - you can see exactly what queries EM uses to do this.
> >
> > "TN" <TN@.discussions.microsoft.com> wrote in message
> > news:305ECE55-D52D-4326-995C-221F8EE005D2@.microsoft.com...
> > > Thanks for your help.
> > > But this Proc does not tell me space used in data file.
> > > I can see space used in data file
> > >
> > > right click on DB
> > > select All Tasks menu item
> > > select Shrink Database..
> > > click Files button
> > >
> > > I can see all
> > >
> > > I want to know like this in SQL Query Analyzer
> > >
> > > Thanks
> > > TN
> > >
> > > "Armando Prato" wrote:
> > >
> > > > sp_helpdb <your db>
> > > >
> > > > can give you alot of this info
> > > >
> > > > "TN" <TN@.discussions.microsoft.com> wrote in message
> > > > news:A86BD7C1-BC03-4163-80C9-EFB6A688D0B3@.microsoft.com...
> > > > > Hi all,
> > > > >
> > > > > I'm having a problem need your help:
> > > > >
> > > > > I can see all properties of datafile (FilegroupName, FileID,
FileType,
> > > > > Location, CurrentSize, Space Used) by Enterpise Manager.
> > > > >
> > > > > Please tell me how do I see all those properties by SQL Query
> > Analyzer.
> > > > > I want to know actual space used in data file to shrink file.
> > > > >
> > > > > Thanks in advanced,
> > > > > TN
> > > >
> > > >
> > > >
> >
> >
> >

Actual size of Getdate()

what is the actual length of the result returned by getdate()?
When you set the mode Results in text in QA and run this ,
declare @.s1 varchar(20)
declare @.s2 varchar(30)
set @.s1='no'
set @.s2='the'
select @.s1,@.s2
select getdate()
you get the following result
-- --
no the
(1 row(s) affected)
---
2006-03-16 14:51:26.140
(1 row(s) affected)
The number of '-' is the length of the column or varialbe which
correctly matches with the size of @.s1 and @.s2
But for getdate(), why does it display 54 '-'s? Does it mean the
maximum length is 54?
MadhivananGETDATE returns datetime, not a string. It is the client that does the forma
tting from datetime
(binary information) to something which is readable for us humans. The clien
t application can adapt
to regional settings on the client machine. SQL Server has no control of how
this is presented at
the client. I imagine that some locales can end up with pretty long strings
to represent a datetime,
hence the rather long meta-data definition of such a column.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Madhivanan" <madhivanan2001@.gmail.com> wrote in message
news:1142950451.878083.249060@.u72g2000cwu.googlegroups.com...
> what is the actual length of the result returned by getdate()?
> When you set the mode Results in text in QA and run this ,
> declare @.s1 varchar(20)
> declare @.s2 varchar(30)
> set @.s1='no'
> set @.s2='the'
> select @.s1,@.s2
> select getdate()
> you get the following result
>
> -- --
> no the
> (1 row(s) affected)
>
> ---
> 2006-03-16 14:51:26.140
> (1 row(s) affected)
> The number of '-' is the length of the column or varialbe which
> correctly matches with the size of @.s1 and @.s2
> But for getdate(), why does it display 54 '-'s? Does it mean the
> maximum length is 54?
> Madhivanan
>|||That is interesting
run these 2
select convert(varchar,getdate(),109)
select getdate()
first one is longer but has less -
Denis the SQL Menace
http://sqlservercode.blogspot.com/|||> select convert(varchar,getdate(),109)
You didn't specify a length for the varchar. In all but two cases, that will
result in varchar(1).
The two exceptions are inside CAST and CONVERT where you get varchar(30). Th
is is why you see a
length of 30 for this column.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"SQL" <denis.gobo@.gmail.com> wrote in message
news:1142951590.221838.143270@.g10g2000cwb.googlegroups.com...
> That is interesting
> run these 2
> select convert(varchar,getdate(),109)
> select getdate()
> first one is longer but has less -
>
> Denis the SQL Menace
> http://sqlservercode.blogspot.com/
>|||> You didn't specify a length for the varchar. In all but two cases, that
> will result in varchar(1). The two exceptions are inside CAST and CONVERT
> where you get varchar(30). This is why you see a length of 30 for this
> column.
Hey Tibor, do you think they will ever deprecate this silly syntax? Or at
least make the default length consistent across all methods?|||> Hey Tibor, do you think they will ever deprecate this silly syntax? Or at least make the
default
> length consistent across all methods?
I wish it would be deprecated, Aaron. We all se how much trouble it causes.
I'd prefer an error if
omitting length. Next best would be consistent length of 1 (easier to catch
the mistakes).
I checked ANSI SQL a while back, and for char, you should get 1 if you omit.
But for varchar, you
have to specify a length. (http://www.karaszi.com/SQLServer/in.../>
tatypes.asp)
I haven't vented this with MS, though...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:e6o6KfQTGHA.4452@.TK2MSFTNGP12.phx.gbl...
> Hey Tibor, do you think they will ever deprecate this silly syntax? Or at
least make the default
> length consistent across all methods?
>

Actual size of database

My database size is of 1500 mb and actual data is not of 1500 mb the data
space available is showing 100 mb , i have done dbcc checkbd on the database
but still the it isnot showing actual size there is one more commond to
resize the database can anbody tell me
Yousuf Khan
Programmer
Yousuf wrote:
> My database size is of 1500 mb and actual data is not of 1500 mb the
> data space available is showing 100 mb , i have done dbcc checkbd on
> the database but still the it isnot showing actual size there is one
> more commond to resize the database can anbody tell me
If you need to shrink the data of log files because they have grown much
too large for what your database requires, you can use DBCC SHRINKFILE.
Having extra space in the data and log files is a good idea as auto-grow
operations are very expesinsive.
David Gugick
Quest Software
www.imceda.com
www.quest.com
|||Hi,
Use the below command to get the actual free space:-
For Data and Index
use dbname
go
sp_spaceused @.updateusage='true'
For Transaction log
DBCC SQLPERF(LOGSPACE)
Based on the outcome you can shrink the MDF and LDF file seperately.
Steps:-
1. Backup the transaction log (Backup Log in books online)
2. Now shrink the files
dbcc shrinkfile('logical_mdf_name',Xsize)
3. Shrink the LDF file
dbcc shrinkfile('logical_ldf_name',Xsize)
4. After this check the size again
use dbname
go
sp_spaceused @.updateusage='true'
For Transaction log
DBCC SQLPERF(LOGSPACE)
Thanks
Hari
SQL Server MVP
"Yousuf" <yousuf.yk@.gmail.com> wrote in message
news:B062771E-F552-4EBE-8F6E-2E2F0BD359EA@.microsoft.com...
> My database size is of 1500 mb and actual data is not of 1500 mb the data
> space available is showing 100 mb , i have done dbcc checkbd on the
> database
> but still the it isnot showing actual size there is one more commond to
> resize the database can anbody tell me
> --
> Yousuf Khan
> Programmer
|||thanks hari
being in the current database
if use DBCC UPDATEUSAGE will it be ok
or shall i go as per your advise
Yousuf Khan
Programmer
"Hari Prasad" wrote:

> Hi,
> Use the below command to get the actual free space:-
> For Data and Index
> use dbname
> go
> sp_spaceused @.updateusage='true'
> For Transaction log
> DBCC SQLPERF(LOGSPACE)
> Based on the outcome you can shrink the MDF and LDF file seperately.
>
> Steps:-
> 1. Backup the transaction log (Backup Log in books online)
> 2. Now shrink the files
> dbcc shrinkfile('logical_mdf_name',Xsize)
> 3. Shrink the LDF file
> dbcc shrinkfile('logical_ldf_name',Xsize)
> 4. After this check the size again
> use dbname
> go
> sp_spaceused @.updateusage='true'
>
> For Transaction log
> DBCC SQLPERF(LOGSPACE)
> --
> Thanks
> Hari
> SQL Server MVP
> "Yousuf" <yousuf.yk@.gmail.com> wrote in message
> news:B062771E-F552-4EBE-8F6E-2E2F0BD359EA@.microsoft.com...
>
>

Actual size of database

My database size is of 1500 mb and actual data is not of 1500 mb the data
space available is showing 100 mb , i have done dbcc checkbd on the database
but still the it isnot showing actual size there is one more commond to
resize the database can anbody tell me
--
Yousuf Khan
ProgrammerYousuf wrote:
> My database size is of 1500 mb and actual data is not of 1500 mb the
> data space available is showing 100 mb , i have done dbcc checkbd on
> the database but still the it isnot showing actual size there is one
> more commond to resize the database can anbody tell me
If you need to shrink the data of log files because they have grown much
too large for what your database requires, you can use DBCC SHRINKFILE.
Having extra space in the data and log files is a good idea as auto-grow
operations are very expesinsive.
--
David Gugick
Quest Software
www.imceda.com
www.quest.com|||Hi,
Use the below command to get the actual free space:-
For Data and Index
use dbname
go
sp_spaceused @.updateusage='true'
For Transaction log
DBCC SQLPERF(LOGSPACE)
Based on the outcome you can shrink the MDF and LDF file seperately.
Steps:-
1. Backup the transaction log (Backup Log in books online)
2. Now shrink the files
dbcc shrinkfile('logical_mdf_name',­size)
3. Shrink the LDF file
dbcc shrinkfile('logical_ldf_name',­size)
4. After this check the size again
use dbname
go
sp_spaceused @.updateusage='true'
For Transaction log
DBCC SQLPERF(LOGSPACE)
--
Thanks
Hari
SQL Server MVP
"Yousuf" <yousuf.yk@.gmail.com> wrote in message
news:B062771E-F552-4EBE-8F6E-2E2F0BD359EA@.microsoft.com...
> My database size is of 1500 mb and actual data is not of 1500 mb the data
> space available is showing 100 mb , i have done dbcc checkbd on the
> database
> but still the it isnot showing actual size there is one more commond to
> resize the database can anbody tell me
> --
> Yousuf Khan
> Programmer|||thanks hari
being in the current database
if use DBCC UPDATEUSAGE will it be ok
or shall i go as per your advise
Yousuf Khan
Programmer
"Hari Prasad" wrote:
> Hi,
> Use the below command to get the actual free space:-
> For Data and Index
> use dbname
> go
> sp_spaceused @.updateusage='true'
> For Transaction log
> DBCC SQLPERF(LOGSPACE)
> Based on the outcome you can shrink the MDF and LDF file seperately.
>
> Steps:-
> 1. Backup the transaction log (Backup Log in books online)
> 2. Now shrink the files
> dbcc shrinkfile('logical_mdf_name',­size)
> 3. Shrink the LDF file
> dbcc shrinkfile('logical_ldf_name',­size)
> 4. After this check the size again
> use dbname
> go
> sp_spaceused @.updateusage='true'
>
> For Transaction log
> DBCC SQLPERF(LOGSPACE)
> --
> Thanks
> Hari
> SQL Server MVP
> "Yousuf" <yousuf.yk@.gmail.com> wrote in message
> news:B062771E-F552-4EBE-8F6E-2E2F0BD359EA@.microsoft.com...
> > My database size is of 1500 mb and actual data is not of 1500 mb the data
> > space available is showing 100 mb , i have done dbcc checkbd on the
> > database
> > but still the it isnot showing actual size there is one more commond to
> > resize the database can anbody tell me
> > --
> > Yousuf Khan
> > Programmer
>
>

Actual size of database

My database size is of 1500 mb and actual data is not of 1500 mb the data
space available is showing 100 mb , i have done dbcc checkbd on the database
but still the it isnot showing actual size there is one more commond to
resize the database can anbody tell me
--
Yousuf Khan
ProgrammerYousuf wrote:
> My database size is of 1500 mb and actual data is not of 1500 mb the
> data space available is showing 100 mb , i have done dbcc checkbd on
> the database but still the it isnot showing actual size there is one
> more commond to resize the database can anbody tell me
If you need to shrink the data of log files because they have grown much
too large for what your database requires, you can use DBCC SHRINKFILE.
Having extra space in the data and log files is a good idea as auto-grow
operations are very expesinsive.
David Gugick
Quest Software
www.imceda.com
www.quest.com|||Hi,
Use the below command to get the actual free space:-
For Data and Index
use dbname
go
sp_spaceused @.updateusage='true'
For Transaction log
DBCC SQLPERF(LOGSPACE)
Based on the outcome you can shrink the MDF and LDF file seperately.
Steps:-
1. Backup the transaction log (Backup Log in books online)
2. Now shrink the files
dbcc shrinkfile('logical_mdf_name',_size)
3. Shrink the LDF file
dbcc shrinkfile('logical_ldf_name',_size)
4. After this check the size again
use dbname
go
sp_spaceused @.updateusage='true'
For Transaction log
DBCC SQLPERF(LOGSPACE)
Thanks
Hari
SQL Server MVP
"Yousuf" <yousuf.yk@.gmail.com> wrote in message
news:B062771E-F552-4EBE-8F6E-2E2F0BD359EA@.microsoft.com...
> My database size is of 1500 mb and actual data is not of 1500 mb the data
> space available is showing 100 mb , i have done dbcc checkbd on the
> database
> but still the it isnot showing actual size there is one more commond to
> resize the database can anbody tell me
> --
> Yousuf Khan
> Programmer|||thanks hari
being in the current database
if use DBCC UPDATEUSAGE will it be ok
or shall i go as per your advise
Yousuf Khan
Programmer
"Hari Prasad" wrote:

> Hi,
> Use the below command to get the actual free space:-
> For Data and Index
> use dbname
> go
> sp_spaceused @.updateusage='true'
> For Transaction log
> DBCC SQLPERF(LOGSPACE)
> Based on the outcome you can shrink the MDF and LDF file seperately.
>
> Steps:-
> 1. Backup the transaction log (Backup Log in books online)
> 2. Now shrink the files
> dbcc shrinkfile('logical_mdf_name',_size)
> 3. Shrink the LDF file
> dbcc shrinkfile('logical_ldf_name',_size)
> 4. After this check the size again
> use dbname
> go
> sp_spaceused @.updateusage='true'
>
> For Transaction log
> DBCC SQLPERF(LOGSPACE)
> --
> Thanks
> Hari
> SQL Server MVP
> "Yousuf" <yousuf.yk@.gmail.com> wrote in message
> news:B062771E-F552-4EBE-8F6E-2E2F0BD359EA@.microsoft.com...
>
>