How to change the data type of an IDENTITY column

Hi.
I have an existing table with an INT IDENTITY column; it contains some data
rows.
I want to change the identity column type to smallint (the data values are
within range) but am stumped. Here's what I'm trying to do (assume the
identity column is called Id):
Create tempId smallint column
save Id values in tempId
Drop Id column
Recreate Id column as identity smallint.
*** Update Id column with values from tempId
Drop tempId column
As you know, it fails on the Update step above: cannot update identity
column (I've tried set identity_insert on and that makes no difference - now
where's the set identity_update function...)
Can anyone suggest a way of doing it?
Thanks
Michael
Why are you re-creating the column as an IDENTITY? Just create it as
SMALLINT; you can't have it both ways.
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
"Mic" <micspam@.jadegroup.co.uk> wrote in message
news:223A4008-4C07-4AF3-BA56-20D93FA17570@.microsoft.com...
> Hi.
> I have an existing table with an INT IDENTITY column; it contains some
data
> rows.
> I want to change the identity column type to smallint (the data values are
> within range) but am stumped. Here's what I'm trying to do (assume the
> identity column is called Id):
> Create tempId smallint column
> save Id values in tempId
> Drop Id column
> Recreate Id column as identity smallint.
> *** Update Id column with values from tempId
> Drop tempId column
> As you know, it fails on the Update step above: cannot update identity
> column (I've tried set identity_insert on and that makes no difference -
now
> where's the set identity_update function...)
> Can anyone suggest a way of doing it?
> Thanks
> Michael
>
>
|||Adam,
Thanks, but it needs to be an identity column. And I can have it both ways!
I've just found a way to do what I want:
Save the data into a temp table
delete the data
drop the int identity column
recreate the identity column as smallint
identity_insert on
insert rows from temp table
identity_insert off
drop temp table.
A bit long winded, but it works. (I've left out minor detail re. constraints
etc.)
Thanks
Michael
"Adam Machanic" wrote:

> Why are you re-creating the column as an IDENTITY? Just create it as
> SMALLINT; you can't have it both ways.
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.sqljunkies.com/weblog/amachanic
> --
>
> "Mic" <micspam@.jadegroup.co.uk> wrote in message
> news:223A4008-4C07-4AF3-BA56-20D93FA17570@.microsoft.com...
> data
> now
>
>
|||Yes, that works.
Keep in mind that you'll have to insert those rows one-by-one with a cursor
or loop if you want to guarantee the same order.
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
"Mic" <micspam@.jadegroup.co.uk> wrote in message
news:419FB142-5006-4418-8C59-CCEAFBD440A8@.microsoft.com...
> Adam,
> Thanks, but it needs to be an identity column. And I can have it both
ways!
> I've just found a way to do what I want:
> Save the data into a temp table
> delete the data
> drop the int identity column
> recreate the identity column as smallint
> identity_insert on
> insert rows from temp table
> identity_insert off
> drop temp table.
> A bit long winded, but it works. (I've left out minor detail re.
constraints
> etc.)
> Thanks
> Michael
>
|||Adam,
Sorry I don't understand.
What do you mean by order and why would this be important to me?
Thanks Michael
"Adam Machanic" wrote:

> Yes, that works.
> Keep in mind that you'll have to insert those rows one-by-one with a cursor
> or loop if you want to guarantee the same order.
> --
> Adam Machanic
> SQL Server MVP
> http://www.sqljunkies.com/weblog/amachanic
> --
>
> "Mic" <micspam@.jadegroup.co.uk> wrote in message
> news:419FB142-5006-4418-8C59-CCEAFBD440A8@.microsoft.com...
> ways!
> constraints
>
>
|||"Mic" <micspam@.jadegroup.co.uk> wrote in message
news:BE612CBA-EA74-492B-B157-30B17F4DB131@.microsoft.com...
> Adam,
> Sorry I don't understand.
> What do you mean by order and why would this be important to me?
Sorry, I was thinking of something else totally unrelated! Disregard.
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
sql

How to change the data type of an IDENTITY column

Hi.
I have an existing table with an INT IDENTITY column; it contains some data
rows.
I want to change the identity column type to smallint (the data values are
within range) but am stumped. Here's what I'm trying to do (assume the
identity column is called Id):
Create tempId smallint column
save Id values in tempId
Drop Id column
Recreate Id column as identity smallint.
*** Update Id column with values from tempId
Drop tempId column
As you know, it fails on the Update step above: cannot update identity
column (I've tried set identity_insert on and that makes no difference - now
where's the set identity_update function...)
Can anyone suggest a way of doing it?
Thanks
MichaelWhy are you re-creating the column as an IDENTITY? Just create it as
SMALLINT; you can't have it both ways.
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"Mic" <micspam@.jadegroup.co.uk> wrote in message
news:223A4008-4C07-4AF3-BA56-20D93FA17570@.microsoft.com...
> Hi.
> I have an existing table with an INT IDENTITY column; it contains some
data
> rows.
> I want to change the identity column type to smallint (the data values are
> within range) but am stumped. Here's what I'm trying to do (assume the
> identity column is called Id):
> Create tempId smallint column
> save Id values in tempId
> Drop Id column
> Recreate Id column as identity smallint.
> *** Update Id column with values from tempId
> Drop tempId column
> As you know, it fails on the Update step above: cannot update identity
> column (I've tried set identity_insert on and that makes no difference -
now
> where's the set identity_update function...)
> Can anyone suggest a way of doing it?
> Thanks
> Michael
>
>|||Adam,
Thanks, but it needs to be an identity column. And I can have it both ways!
I've just found a way to do what I want:
Save the data into a temp table
delete the data
drop the int identity column
recreate the identity column as smallint
identity_insert on
insert rows from temp table
identity_insert off
drop temp table.
A bit long winded, but it works. (I've left out minor detail re. constraints
etc.)
Thanks
Michael
"Adam Machanic" wrote:
> Why are you re-creating the column as an IDENTITY? Just create it as
> SMALLINT; you can't have it both ways.
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.sqljunkies.com/weblog/amachanic
> --
>
> "Mic" <micspam@.jadegroup.co.uk> wrote in message
> news:223A4008-4C07-4AF3-BA56-20D93FA17570@.microsoft.com...
> > Hi.
> > I have an existing table with an INT IDENTITY column; it contains some
> data
> > rows.
> > I want to change the identity column type to smallint (the data values are
> > within range) but am stumped. Here's what I'm trying to do (assume the
> > identity column is called Id):
> >
> > Create tempId smallint column
> > save Id values in tempId
> > Drop Id column
> > Recreate Id column as identity smallint.
> > *** Update Id column with values from tempId
> > Drop tempId column
> >
> > As you know, it fails on the Update step above: cannot update identity
> > column (I've tried set identity_insert on and that makes no difference -
> now
> > where's the set identity_update function...)
> >
> > Can anyone suggest a way of doing it?
> > Thanks
> > Michael
> >
> >
> >
>
>|||Yes, that works.
Keep in mind that you'll have to insert those rows one-by-one with a cursor
or loop if you want to guarantee the same order.
--
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"Mic" <micspam@.jadegroup.co.uk> wrote in message
news:419FB142-5006-4418-8C59-CCEAFBD440A8@.microsoft.com...
> Adam,
> Thanks, but it needs to be an identity column. And I can have it both
ways!
> I've just found a way to do what I want:
> Save the data into a temp table
> delete the data
> drop the int identity column
> recreate the identity column as smallint
> identity_insert on
> insert rows from temp table
> identity_insert off
> drop temp table.
> A bit long winded, but it works. (I've left out minor detail re.
constraints
> etc.)
> Thanks
> Michael
>|||Adam,
Sorry I don't understand.
What do you mean by order and why would this be important to me?
Thanks Michael
"Adam Machanic" wrote:
> Yes, that works.
> Keep in mind that you'll have to insert those rows one-by-one with a cursor
> or loop if you want to guarantee the same order.
> --
> Adam Machanic
> SQL Server MVP
> http://www.sqljunkies.com/weblog/amachanic
> --
>
> "Mic" <micspam@.jadegroup.co.uk> wrote in message
> news:419FB142-5006-4418-8C59-CCEAFBD440A8@.microsoft.com...
> > Adam,
> > Thanks, but it needs to be an identity column. And I can have it both
> ways!
> > I've just found a way to do what I want:
> > Save the data into a temp table
> > delete the data
> > drop the int identity column
> > recreate the identity column as smallint
> > identity_insert on
> > insert rows from temp table
> > identity_insert off
> > drop temp table.
> >
> > A bit long winded, but it works. (I've left out minor detail re.
> constraints
> > etc.)
> > Thanks
> > Michael
> >
>
>|||"Mic" <micspam@.jadegroup.co.uk> wrote in message
news:BE612CBA-EA74-492B-B157-30B17F4DB131@.microsoft.com...
> Adam,
> Sorry I don't understand.
> What do you mean by order and why would this be important to me?
Sorry, I was thinking of something else totally unrelated! Disregard.
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--

How to change data type of column from int to bigint in replication

Hi,
Currently, we have a table in sql server 2000 which has one column as
int data-type and which is part of transactional replication we
replicate this table to oracle. The oracle version is 9.2. Now we want
to change the data type of this column to bigint what would be the
best way to alter this table so that we don't have to drop the
replication?
Thanks
If this is the pk you will have to drop the subscribers and publications. If
it is a non-key column you should try to use sp_repladdcolumn and
sp_repldropcolumn.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
<kulkarni.ninad@.gmail.com> wrote in message
news:1171302064.597470.203130@.s48g2000cws.googlegr oups.com...
> Hi,
> Currently, we have a table in sql server 2000 which has one column as
> int data-type and which is part of transactional replication we
> replicate this table to oracle. The oracle version is 9.2. Now we want
> to change the data type of this column to bigint what would be the
> best way to alter this table so that we don't have to drop the
> replication?
> Thanks
>
|||Please take a look at http://www.replicationanswers.com/AddColumn.asp
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
sql

How to Change AutoNumber Not Start From 1

I have a table with structure below :
Table_A
{
PGradeID int IDENTITY(1,1),
PName varchar(20)
}

If I delete all records on Table_A, and then fill some records,
PGradeID will no longer start from 1 anymore, everybody knows that. How
to change it so PGradeID will start from 1 after all records was
deleted?

Thanks b4

ResantLookup the DBCC CHECKIDENT command. Also, if you TRUNCATE rather than DELETE
then the IDENTITY will be reset to seed.

Why do you care what the IDENTITY value is? It's usually fatal to attach any
significance to the value assigned by IDENTITY. If you care about the value
then IDENTITY isn't the right solution.

--
David Portas
SQL Server MVP
--|||Reset identity value will not cause problem in my case, but I
appreciate your warning.
Thanks a lot, it's works!

How to catch errors

I have an statement
SELECT * from lkSFSAHousing where id = @.answer
Problem is that id is int and some times @.answer is not int
how would I catch either the error or not even get into not calling
this statement if @.answer is not digit (1 or 2 or 1000)
Can anybody help?
Thanks
On Feb 8, 11:59 am, "Sehboo" <MasoodAd...@.gmail.com> wrote:
> I have an statement
> SELECT * from lkSFSAHousing where id = @.answer
> Problem is that id is int and some times @.answer is not int
> how would I catch either the error or not even get into not calling
> this statement if @.answer is not digit (1 or 2 or 1000)
> Can anybody help?
> Thanks
Ideally, you should have some validation in place at the point where
@.answer is assigned a value. Where/how is this being done?

How to catch errors

I have an statement
SELECT * from lkSFSAHousing where id = @.answer
Problem is that id is int and some times @.answer is not int
how would I catch either the error or not even get into not calling
this statement if @.answer is not digit (1 or 2 or 1000)
Can anybody help?
ThanksOn Feb 8, 11:59 am, "Sehboo" <MasoodAd...@.gmail.com> wrote:
> I have an statement
> SELECT * from lkSFSAHousing where id = @.answer
> Problem is that id is int and some times @.answer is not int
> how would I catch either the error or not even get into not calling
> this statement if @.answer is not digit (1 or 2 or 1000)
> Can anybody help?
> Thanks
Ideally, you should have some validation in place at the point where
@.answer is assigned a value. Where/how is this being done?

How to catch errors

I have an statement
SELECT * from lkSFSAHousing where id = @.answer
Problem is that id is int and some times @.answer is not int
how would I catch either the error or not even get into not calling
this statement if @.answer is not digit (1 or 2 or 1000)
Can anybody help?
ThanksOn Feb 8, 11:59 am, "Sehboo" <MasoodAd...@.gmail.com> wrote:
> I have an statement
> SELECT * from lkSFSAHousing where id = @.answer
> Problem is that id is int and some times @.answer is not int
> how would I catch either the error or not even get into not calling
> this statement if @.answer is not digit (1 or 2 or 1000)
> Can anybody help?
> Thanks
Ideally, you should have some validation in place at the point where
@.answer is assigned a value. Where/how is this being done?

how to call C/C++ DLL in stored Procedure?

How do I call a C/++ DLL in SQL Server stored procedure?
The C function has following prototype.
double function_name(char* x,int i)
ThanksRead about extended stored procedures...

How to calculate record size

Hi,
Can anyone help me to calculate record size for a table.
1. Lets say I have a table with 10 fields, each of them is type int. Now,
will record size be diferent if I save in each field value 0 versus if I
save value NULL?
2. What if table has all column as NOT NULL? Will then SQL Server still
create NULL bitmap for record:
Null Bitmap (Null_Bitmap) = 2 + (( Num_Cols + 7) / 8 )
3. BOL has different size calculations for fixed-length columns and variable
length column. Is NUMERIC(19,2) NOT NULL field considered to be fixed length
or variable length? Which data types are variable length? I do know that
varchar, varbinary, nvarchar is variable. But are there any other?
Thank you for helpI should add that I am using SQL SErver 2000.
Thanks
"NoSpam" <NoSpam@.NoSpam.com> wrote in message
news:uvsHEJMzEHA.2040@.tk2msftngp13.phx.gbl...
> Hi,
> Can anyone help me to calculate record size for a table.
> 1. Lets say I have a table with 10 fields, each of them is type int. Now,
> will record size be diferent if I save in each field value 0 versus if I
> save value NULL?
> 2. What if table has all column as NOT NULL? Will then SQL Server still
> create NULL bitmap for record:
> Null Bitmap (Null_Bitmap) = 2 + (( Num_Cols + 7) / 8 )
> 3. BOL has different size calculations for fixed-length columns and
> variable length column. Is NUMERIC(19,2) NOT NULL field considered to be
> fixed length or variable length? Which data types are variable length? I
> do know that varchar, varbinary, nvarchar is variable. But are there any
> other?
>
> Thank you for help
>
>|||Kalen Delaney's "Inside SQL server 2000" will give you the full story on
this but in summary:
1. NO, Size will always be the same.
2. If I remember right if there are NO nullable columns then the null bitmap
is not stored in the row.
3. Add text (and ntext) as effectively variable length. How much is stored
in the row is dependant on whether the text is to be stored within the row,
or a pointer to seperate text pages, and hence could vary from 16 bytes
upwards. Your specific example of numeric (19,2) is fixed length. BOL has
defined physical sizes for all the different data types.
Mike John
"NoSpam" <NoSpam@.NoSpam.com> wrote in message
news:e5fliKMzEHA.3184@.TK2MSFTNGP10.phx.gbl...
>I should add that I am using SQL SErver 2000.
> Thanks
> "NoSpam" <NoSpam@.NoSpam.com> wrote in message
> news:uvsHEJMzEHA.2040@.tk2msftngp13.phx.gbl...
>> Hi,
>> Can anyone help me to calculate record size for a table.
>> 1. Lets say I have a table with 10 fields, each of them is type int. Now,
>> will record size be diferent if I save in each field value 0 versus if I
>> save value NULL?
>> 2. What if table has all column as NOT NULL? Will then SQL Server still
>> create NULL bitmap for record:
>> Null Bitmap (Null_Bitmap) = 2 + (( Num_Cols + 7) / 8 )
>> 3. BOL has different size calculations for fixed-length columns and
>> variable length column. Is NUMERIC(19,2) NOT NULL field considered to be
>> fixed length or variable length? Which data types are variable length? I
>> do know that varchar, varbinary, nvarchar is variable. But are there any
>> other?
>>
>> Thank you for help
>>
>|||Mike,
Thanks for the information.
"Mike John" <Mike.John@.knowledgepool.com> wrote in message
news:e2BQoSPzEHA.1564@.TK2MSFTNGP09.phx.gbl...
> Kalen Delaney's "Inside SQL server 2000" will give you the full story on
> this but in summary:
> 1. NO, Size will always be the same.
> 2. If I remember right if there are NO nullable columns then the null
> bitmap is not stored in the row.
> 3. Add text (and ntext) as effectively variable length. How much is stored
> in the row is dependant on whether the text is to be stored within the
> row, or a pointer to seperate text pages, and hence could vary from 16
> bytes upwards. Your specific example of numeric (19,2) is fixed length.
> BOL has defined physical sizes for all the different data types.
> Mike John
> "NoSpam" <NoSpam@.NoSpam.com> wrote in message
> news:e5fliKMzEHA.3184@.TK2MSFTNGP10.phx.gbl...
>>I should add that I am using SQL SErver 2000.
>> Thanks
>> "NoSpam" <NoSpam@.NoSpam.com> wrote in message
>> news:uvsHEJMzEHA.2040@.tk2msftngp13.phx.gbl...
>> Hi,
>> Can anyone help me to calculate record size for a table.
>> 1. Lets say I have a table with 10 fields, each of them is type int.
>> Now, will record size be diferent if I save in each field value 0 versus
>> if I save value NULL?
>> 2. What if table has all column as NOT NULL? Will then SQL Server still
>> create NULL bitmap for record:
>> Null Bitmap (Null_Bitmap) = 2 + (( Num_Cols + 7) / 8 )
>> 3. BOL has different size calculations for fixed-length columns and
>> variable length column. Is NUMERIC(19,2) NOT NULL field considered to be
>> fixed length or variable length? Which data types are variable length? I
>> do know that varchar, varbinary, nvarchar is variable. But are there any
>> other?
>>
>> Thank you for help
>>
>>
>

How to calculate record size

Hi,
Can anyone help me to calculate record size for a table.
1. Lets say I have a table with 10 fields, each of them is type int. Now,
will record size be diferent if I save in each field value 0 versus if I
save value NULL?
2. What if table has all column as NOT NULL? Will then SQL Server still
create NULL bitmap for record:
Null Bitmap (Null_Bitmap) = 2 + (( Num_Cols + 7) / 8 )
3. BOL has different size calculations for fixed-length columns and variable
length column. Is NUMERIC(19,2) NOT NULL field considered to be fixed length
or variable length? Which data types are variable length? I do know that
varchar, varbinary, nvarchar is variable. But are there any other?
Thank you for helpI should add that I am using SQL SErver 2000.
Thanks
"NoSpam" <NoSpam@.NoSpam.com> wrote in message
news:uvsHEJMzEHA.2040@.tk2msftngp13.phx.gbl...
> Hi,
> Can anyone help me to calculate record size for a table.
> 1. Lets say I have a table with 10 fields, each of them is type int. Now,
> will record size be diferent if I save in each field value 0 versus if I
> save value NULL?
> 2. What if table has all column as NOT NULL? Will then SQL Server still
> create NULL bitmap for record:
> Null Bitmap (Null_Bitmap) = 2 + (( Num_Cols + 7) / 8 )
> 3. BOL has different size calculations for fixed-length columns and
> variable length column. Is NUMERIC(19,2) NOT NULL field considered to be
> fixed length or variable length? Which data types are variable length? I
> do know that varchar, varbinary, nvarchar is variable. But are there any
> other?
>
> Thank you for help
>
>|||Kalen Delaney's "Inside SQL server 2000" will give you the full story on
this but in summary:
1. NO, Size will always be the same.
2. If I remember right if there are NO nullable columns then the null bitmap
is not stored in the row.
3. Add text (and ntext) as effectively variable length. How much is stored
in the row is dependant on whether the text is to be stored within the row,
or a pointer to seperate text pages, and hence could vary from 16 bytes
upwards. Your specific example of numeric (19,2) is fixed length. BOL has
defined physical sizes for all the different data types.
Mike John
"NoSpam" <NoSpam@.NoSpam.com> wrote in message
news:e5fliKMzEHA.3184@.TK2MSFTNGP10.phx.gbl...
>I should add that I am using SQL SErver 2000.
> Thanks
> "NoSpam" <NoSpam@.NoSpam.com> wrote in message
> news:uvsHEJMzEHA.2040@.tk2msftngp13.phx.gbl...
>|||Mike,
Thanks for the information.
"Mike John" <Mike.John@.knowledgepool.com> wrote in message
news:e2BQoSPzEHA.1564@.TK2MSFTNGP09.phx.gbl...
> Kalen Delaney's "Inside SQL server 2000" will give you the full story on
> this but in summary:
> 1. NO, Size will always be the same.
> 2. If I remember right if there are NO nullable columns then the null
> bitmap is not stored in the row.
> 3. Add text (and ntext) as effectively variable length. How much is stored
> in the row is dependant on whether the text is to be stored within the
> row, or a pointer to seperate text pages, and hence could vary from 16
> bytes upwards. Your specific example of numeric (19,2) is fixed length.
> BOL has defined physical sizes for all the different data types.
> Mike John
> "NoSpam" <NoSpam@.NoSpam.com> wrote in message
> news:e5fliKMzEHA.3184@.TK2MSFTNGP10.phx.gbl...
>

How to calculate record size

Hi,
Can anyone help me to calculate record size for a table.
1. Lets say I have a table with 10 fields, each of them is type int. Now,
will record size be diferent if I save in each field value 0 versus if I
save value NULL?
2. What if table has all column as NOT NULL? Will then SQL Server still
create NULL bitmap for record:
Null Bitmap (Null_Bitmap) = 2 + (( Num_Cols + 7) / 8 )
3. BOL has different size calculations for fixed-length columns and variable
length column. Is NUMERIC(19,2) NOT NULL field considered to be fixed length
or variable length? Which data types are variable length? I do know that
varchar, varbinary, nvarchar is variable. But are there any other?
Thank you for help
I should add that I am using SQL SErver 2000.
Thanks
"NoSpam" <NoSpam@.NoSpam.com> wrote in message
news:uvsHEJMzEHA.2040@.tk2msftngp13.phx.gbl...
> Hi,
> Can anyone help me to calculate record size for a table.
> 1. Lets say I have a table with 10 fields, each of them is type int. Now,
> will record size be diferent if I save in each field value 0 versus if I
> save value NULL?
> 2. What if table has all column as NOT NULL? Will then SQL Server still
> create NULL bitmap for record:
> Null Bitmap (Null_Bitmap) = 2 + (( Num_Cols + 7) / 8 )
> 3. BOL has different size calculations for fixed-length columns and
> variable length column. Is NUMERIC(19,2) NOT NULL field considered to be
> fixed length or variable length? Which data types are variable length? I
> do know that varchar, varbinary, nvarchar is variable. But are there any
> other?
>
> Thank you for help
>
>
|||Kalen Delaney's "Inside SQL server 2000" will give you the full story on
this but in summary:
1. NO, Size will always be the same.
2. If I remember right if there are NO nullable columns then the null bitmap
is not stored in the row.
3. Add text (and ntext) as effectively variable length. How much is stored
in the row is dependant on whether the text is to be stored within the row,
or a pointer to seperate text pages, and hence could vary from 16 bytes
upwards. Your specific example of numeric (19,2) is fixed length. BOL has
defined physical sizes for all the different data types.
Mike John
"NoSpam" <NoSpam@.NoSpam.com> wrote in message
news:e5fliKMzEHA.3184@.TK2MSFTNGP10.phx.gbl...
>I should add that I am using SQL SErver 2000.
> Thanks
> "NoSpam" <NoSpam@.NoSpam.com> wrote in message
> news:uvsHEJMzEHA.2040@.tk2msftngp13.phx.gbl...
>
|||Mike,
Thanks for the information.
"Mike John" <Mike.John@.knowledgepool.com> wrote in message
news:e2BQoSPzEHA.1564@.TK2MSFTNGP09.phx.gbl...
> Kalen Delaney's "Inside SQL server 2000" will give you the full story on
> this but in summary:
> 1. NO, Size will always be the same.
> 2. If I remember right if there are NO nullable columns then the null
> bitmap is not stored in the row.
> 3. Add text (and ntext) as effectively variable length. How much is stored
> in the row is dependant on whether the text is to be stored within the
> row, or a pointer to seperate text pages, and hence could vary from 16
> bytes upwards. Your specific example of numeric (19,2) is fixed length.
> BOL has defined physical sizes for all the different data types.
> Mike John
> "NoSpam" <NoSpam@.NoSpam.com> wrote in message
> news:e5fliKMzEHA.3184@.TK2MSFTNGP10.phx.gbl...
>