Accessing more than: varchar

Showing posts with label varchar. Show all posts

Sunday, March 11, 2012

Add similar fields to many tables

Folks, i have to create four fields in every user table within my database:

CREATED_BY VARCHAR(25), CREATED_DATE [DATETIME], MODIFIED_BY VARCHAR(25), MODIFIED_DATE [DATETIME]

There are more than hundred tables, so i wanna automate this. i am tryin to do this in a cursor: please guide!

declare @.name VARCHAR (50)
declare cur cursor
fast_forward
for select name from sysobjects where type='u' and status not like '-%'
open cur
WHILE (1=1)
BEGIN
FETCH NEXT
FROM cur
INTO @.name
IF @.@.fetch_status = 0
BEGIN
ALTER TABLE @.name
ADD created_by [VARCHAR] (25)
GO
ALTER TABLE @.name
ADD created_by [VARCHAR] (25)
GO
ALTER TABLE @.name
ADD created_date [DATETIME]
GO
ALTER TABLE @.name
ADD modified_by [VARCHAR] (25)
GO
ALTER TABLE @.name
ADD modified_date [DATETIME]
END
ELSE
BREAK
END
DEALLOCATE cur

I also want that if one column for a table exists; the other columns should be created rather than it quits.

Howdy!Folks, please help!
Or do i have to add the columns manually! :confused:

Howdy!|||Maybe if you are concerned about whether a column already exists it would be easier to run the four different ALTER statements separately rather than coding around possibilities...y'know, four scripts that you run against all the tables instead of one.

Just a suggestion.

But for a one-time modification like this, you have to be careful that you don't spend more time trying to devise the most optimal and elegant solution than it would take you to just knuckle down and do the dirty work.|||declare @.tablename varchar(50)
declare @.add_field varchar(255)

declare cur_tables cursor for
select name from sysobjects where type='u' and status not like '-%'

open cur_tables
fetch next from cur_tables into @.tablename
while @.@.fetch_status = 0
begin
--print 'table name is ' + @.tablename

set @.add_field = 'alter table ' + @.tablename + ' add created_by varchar(25)'
--print '@.add_field is ' + @.add_field
exec (@.add_field)

set @.add_field = 'alter table ' + @.tablename + ' add created_date smalldatetime'
--print '@.add_field is ' + @.add_field
exec (@.add_field)

set @.add_field = 'alter table ' + @.tablename + ' add modified_by varchar(25)'
--print '@.add_field is ' + @.add_field
exec (@.add_field)

set @.add_field = 'alter table ' + @.tablename + ' add modified_date smalldatetime'
--print '@.add_field is ' + @.add_field
exec (@.add_field)

fetch next from cur_tables into @.tablename
end
close cur_tables
deallocate cur_tables|||-- Run this and cut results to query window & run it.
-- Tim S

SET NOCOUNT ON;
SELECT
CASE WHEN col1.COLUMN_NAME IS NULL THEN 'ALTER TABLE ' + tab.TABLE_NAME + ' ADD CREATED_BY VARCHAR(25);' + CHAR(10) ELSE '' END +
CASE WHEN col2.COLUMN_NAME IS NULL THEN 'ALTER TABLE ' + tab.TABLE_NAME + ' ADD CREATED_DATE [DATETIME];'+ CHAR(10) ELSE '' END +
CASE WHEN col3.COLUMN_NAME IS NULL THEN 'ALTER TABLE ' + tab.TABLE_NAME + ' ADD MODIFIED_BY VARCHAR(25);'+ CHAR(10) ELSE '' END +
CASE WHEN col4.COLUMN_NAME IS NULL THEN 'ALTER TABLE ' + tab.TABLE_NAME + ' ADD MODIFIED_DATE [DATETIME];'+ CHAR(10) ELSE '' END
FROM INFORMATION_SCHEMA.TABLES tab
LEFT JOIN INFORMATION_SCHEMA.COLUMNS col1
ON tab.TABLE_NAME = col1.TABLE_NAME AND tab.TABLE_SCHEMA = col1.TABLE_SCHEMA AND 'CREATED_BY' = col1.COLUMN_NAME
LEFT JOIN INFORMATION_SCHEMA.COLUMNS col2
ON tab.TABLE_NAME = col2.TABLE_NAME AND tab.TABLE_SCHEMA = col2.TABLE_SCHEMA AND 'CREATED_DATE' = col2.COLUMN_NAME
LEFT JOIN INFORMATION_SCHEMA.COLUMNS col3
ON tab.TABLE_NAME = col3.TABLE_NAME AND tab.TABLE_SCHEMA = col3.TABLE_SCHEMA AND 'MODIFIED_BY' = col3.COLUMN_NAME
LEFT JOIN INFORMATION_SCHEMA.COLUMNS col4
ON tab.TABLE_NAME = col4.TABLE_NAME AND tab.TABLE_SCHEMA = col4.TABLE_SCHEMA AND 'MODIFIED_DATE' = col4.COLUMN_NAME
WHERE tab.TABLE_TYPE = 'BASE TABLE' AND -- tab.TABLE_NAME = 'test2' AND
( col1.COLUMN_NAME IS NULL OR col2.COLUMN_NAME IS NULL OR col3.COLUMN_NAME IS NULL OR col4.COLUMN_NAME IS NULL)

Thursday, March 8, 2012

Add Preceeding Zero

I have a field in a table, datatype is varchar. The field contains values
such as, 1000000, 1000003, 5000001 (Left most position is always > 0). I
want to change the field to precede all field values with 0 to yield,
01000000, 01000003, 05000001. I have attempted this using the follow but,
does not work returning error
UPDATE NumVal_1
SET NumVal = '0' & NumVal
WHERE Left(NumVal,1) > 0
Invalid operator for data type. Operator equals boolean AND, type equals
varchar
Am I even close to being on the right track?Your datatype for NumVal should be a character type, and you have to use +
instead of &
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"box2003" <box2003@.yahoo.com> wrote in message
news:%23ZIkgDgYFHA.2380@.tk2msftngp13.phx.gbl...
I have a field in a table, datatype is varchar. The field contains values
such as, 1000000, 1000003, 5000001 (Left most position is always > 0). I
want to change the field to precede all field values with 0 to yield,
01000000, 01000003, 05000001. I have attempted this using the follow but,
does not work returning error
UPDATE NumVal_1
SET NumVal = '0' & NumVal
WHERE Left(NumVal,1) > 0
Invalid operator for data type. Operator equals boolean AND, type equals
varchar
Am I even close to being on the right track?|||I had just found the & and + problem, coming in from Access I use the &.
Thank you.
"Narayana Vyas Kondreddi" <answer_me@.hotmail.com> wrote in message
news:OGI49IgYFHA.2520@.TK2MSFTNGP09.phx.gbl...
> Your datatype for NumVal should be a character type, and you have to use +
> instead of &
> --
> HTH,
> Vyas, MVP (SQL Server)
> SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
>
> "box2003" <box2003@.yahoo.com> wrote in message
> news:%23ZIkgDgYFHA.2380@.tk2msftngp13.phx.gbl...
> I have a field in a table, datatype is varchar. The field contains values
> such as, 1000000, 1000003, 5000001 (Left most position is always > 0). I
> want to change the field to precede all field values with 0 to yield,
> 01000000, 01000003, 05000001. I have attempted this using the follow but,
> does not work returning error
> UPDATE NumVal_1
> SET NumVal = '0' & NumVal
> WHERE Left(NumVal,1) > 0
> Invalid operator for data type. Operator equals boolean AND, type equals
> varchar
> Am I even close to being on the right track?
>
>

Add Preceeding Zero

I have a field in a table, datatype is varchar. The field contains values
such as, 1000000, 1000003, 5000001 (Left most position is always > 0). I
want to change the field to precede all field values with 0 to yield,
01000000, 01000003, 05000001. I have attempted this using the follow but,
does not work returning error
UPDATE NumVal_1
SET NumVal = '0' & NumVal
WHERE Left(NumVal,1) > 0
Invalid operator for data type. Operator equals boolean AND, type equals
varchar
Am I even close to being on the right track?
Your datatype for NumVal should be a character type, and you have to use +
instead of &
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"box2003" <box2003@.yahoo.com> wrote in message
news:%23ZIkgDgYFHA.2380@.tk2msftngp13.phx.gbl...
I have a field in a table, datatype is varchar. The field contains values
such as, 1000000, 1000003, 5000001 (Left most position is always > 0). I
want to change the field to precede all field values with 0 to yield,
01000000, 01000003, 05000001. I have attempted this using the follow but,
does not work returning error
UPDATE NumVal_1
SET NumVal = '0' & NumVal
WHERE Left(NumVal,1) > 0
Invalid operator for data type. Operator equals boolean AND, type equals
varchar
Am I even close to being on the right track?
|||I had just found the & and + problem, coming in from Access I use the &.
Thank you.
"Narayana Vyas Kondreddi" <answer_me@.hotmail.com> wrote in message
news:OGI49IgYFHA.2520@.TK2MSFTNGP09.phx.gbl...
> Your datatype for NumVal should be a character type, and you have to use +
> instead of &
> --
> HTH,
> Vyas, MVP (SQL Server)
> SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
>
> "box2003" <box2003@.yahoo.com> wrote in message
> news:%23ZIkgDgYFHA.2380@.tk2msftngp13.phx.gbl...
> I have a field in a table, datatype is varchar. The field contains values
> such as, 1000000, 1000003, 5000001 (Left most position is always > 0). I
> want to change the field to precede all field values with 0 to yield,
> 01000000, 01000003, 05000001. I have attempted this using the follow but,
> does not work returning error
> UPDATE NumVal_1
> SET NumVal = '0' & NumVal
> WHERE Left(NumVal,1) > 0
> Invalid operator for data type. Operator equals boolean AND, type equals
> varchar
> Am I even close to being on the right track?
>
>

Add Preceeding Zero

Sunday, February 19, 2012

Add Column to a View

I am trying to add a NEW Column to a view
Here is the syntax I am using
ALTER VIEW AFS_INDEX
ADD Value varchar(30)
Can some one tell me what I am doing wrong and what is the correct way of
doing this
ThanksPlease refer to BOL for syntax questions. ALTER VIEW cannot be used in the
same manner as ALTER TABLE.|||You should be adding to one of the base table that define the view, not
altering the view itself.
"Chrismkr" wrote:

> I am trying to add a NEW Column to a view
> Here is the syntax I am using
> ALTER VIEW AFS_INDEX
> ADD Value varchar(30)
> Can some one tell me what I am doing wrong and what is the correct way of
> doing this
> Thanks
>|||Alter view works exactly the same as create view, but completely different
from alter table.
When you alter a view or stored procedure you actually recreate it, but
leave the permissions in place. So use alter view just as you would create
view.
"Chrismkr" <Chrismkr@.discussions.microsoft.com> wrote in message
news:7B76CA24-E9C3-423D-9C22-0F6A6B58D8A8@.microsoft.com...
> I am trying to add a NEW Column to a view
> Here is the syntax I am using
> ALTER VIEW AFS_INDEX
> ADD Value varchar(30)
> Can some one tell me what I am doing wrong and what is the correct way of
> doing this
> Thanks
>

Thursday, February 9, 2012

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 number of bytes used

Is there a way in SQL to ask what the actual record size of a specific record is in a table that has multiple varchar fields? I'm trying to do some statistics on bytes/record and can't seem to find an easy way to see what the actual storage space is.
Thanks!
SELECT DATALENGTH(col1) + DATALENGTH(col2) ...
FROM ...
WHERE ...
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Sue D" <anonymous@.discussions.microsoft.com> wrote in message
news:14EBA0E1-C638-4B17-8DF3-E84FE206BBC2@.microsoft.com...
> Is there a way in SQL to ask what the actual record size of a specific
> record is in a table that has multiple varchar fields? I'm trying to do
> some statistics on bytes/record and can't seem to find an easy way to see
> what the actual storage space is.
> Thanks!

Actual number of bytes used

Is there a way in SQL to ask what the actual record size of a specific recor
d is in a table that has multiple varchar fields? I'm trying to do some sta
tistics on bytes/record and can't seem to find an easy way to see what the a
ctual storage space is.
Thanks!SELECT DATALENGTH(col1) + DATALENGTH(col2) ...
FROM ...
WHERE ...
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Sue D" <anonymous@.discussions.microsoft.com> wrote in message
news:14EBA0E1-C638-4B17-8DF3-E84FE206BBC2@.microsoft.com...
> Is there a way in SQL to ask what the actual record size of a specific
> record is in a table that has multiple varchar fields? I'm trying to do
> some statistics on bytes/record and can't seem to find an easy way to see
> what the actual storage space is.
> Thanks!

Actual number of bytes used

Is there a way in SQL to ask what the actual record size of a specific record is in a table that has multiple varchar fields? I'm trying to do some statistics on bytes/record and can't seem to find an easy way to see what the actual storage space is
Thanks!SELECT DATALENGTH(col1) + DATALENGTH(col2) ...
FROM ...
WHERE ...
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Sue D" <anonymous@.discussions.microsoft.com> wrote in message
news:14EBA0E1-C638-4B17-8DF3-E84FE206BBC2@.microsoft.com...
> Is there a way in SQL to ask what the actual record size of a specific
> record is in a table that has multiple varchar fields? I'm trying to do
> some statistics on bytes/record and can't seem to find an easy way to see
> what the actual storage space is.
> Thanks!

Sunday, March 11, 2012

Add similar fields to many tables

Thursday, March 8, 2012

Add Preceeding Zero

Add Preceeding Zero

Add Preceeding Zero

Sunday, February 19, 2012

Add Column to a View

Thursday, February 9, 2012

Actual storage of Varchar values

Actual storage of Varchar values

Actual number of bytes used

Actual number of bytes used

Actual number of bytes used

Accessing more than

Blog Archive

About Me