PrimeOutput : difference between 'Output' and 'output buffer'

When overriding the PrimeOutput method in a custom component, you get as parameters the outputIDs and the output buffers (of type PipelineBuffer). using the outputIDs you can get IDTSOutput90 outputs.

As I interpret things, each output is associated with one output buffer. When you want to output rows, you add rows to the buffer. But what do you use the output (IDTSOutput90) for?

Regards,
HenkGood question. I'm keen to know the answer to this too.

-Jamie|||If you have more than one output on your component then you can use the order of the IDs and buffers (they are in the same order) to determine which buffer goes with which output. Obviously, there are other ways to do this but this makes it simple.

HTH,
Matt|||That was clear to me Matt, but what do you need an output for?|||Henk,

Maybe you have already come up with a answer, since there was no proper answer in the Thread, I will mention what I think.

IDTSOutput90 is used to Specify the MetaData of that particular output, using that Interface its possible to give the output columns' {Field}Name , Description, DataType, Length , etc..... which are the metadata of that Output.

The metadata will have to be specified if you are adding new columns to the Output, or else the metadata will be retrived by the earlier component.

When adding to the output buffer, the values will have to adhere to the metadata in the IDTSOutput90.

Hope this is the answer to your Question

Nilushan,sql

PrimeOutput : difference between 'Output' and 'output buffer'

When overriding the PrimeOutput method in a custom component, you get as parameters the outputIDs and the output buffers (of type PipelineBuffer). using the outputIDs you can get IDTSOutput90 outputs.

As I interpret things, each output is associated with one output buffer. When you want to output rows, you add rows to the buffer. But what do you use the output (IDTSOutput90) for?

Regards,
HenkGood question. I'm keen to know the answer to this too.

-Jamie|||If you have more than one output on your component then you can use the order of the IDs and buffers (they are in the same order) to determine which buffer goes with which output. Obviously, there are other ways to do this but this makes it simple.

HTH,
Matt|||That was clear to me Matt, but what do you need an output for?|||Henk,

Maybe you have already come up with a answer, since there was no proper answer in the Thread, I will mention what I think.

IDTSOutput90 is used to Specify the MetaData of that particular output, using that Interface its possible to give the output columns' {Field}Name , Description, DataType, Length , etc..... which are the metadata of that Output.

The metadata will have to be specified if you are adding new columns to the Output, or else the metadata will be retrived by the earlier component.

When adding to the output buffer, the values will have to adhere to the metadata in the IDTSOutput90.

Hope this is the answer to your Question

Nilushan,

Primary Key falling short

If the identity value falls short you will get an arithmetic overflow error.
But then you could change your identity field to another data type. For
example, if your identity is using the int data type you can then change to
bigint that, according to SQL Server 2000 BOL, can hold integers from from
-2^63 (-9223372036854775808) through 2^63-1 (9223372036854775807).
Hope this helps,
Ben Nevarez, MCDBA, OCP
Database Administrator
"Ravi" wrote:

> Plz tell me what to do if Primary key fall short and execed the max size o
f
> the datatype like integer with Identity increment option.Plz tell me what to do if Primary key fall short and execed the max size of
the datatype like integer with Identity increment option.|||If the identity value falls short you will get an arithmetic overflow error.
But then you could change your identity field to another data type. For
example, if your identity is using the int data type you can then change to
bigint that, according to SQL Server 2000 BOL, can hold integers from from
-2^63 (-9223372036854775808) through 2^63-1 (9223372036854775807).
Hope this helps,
Ben Nevarez, MCDBA, OCP
Database Administrator
"Ravi" wrote:

> Plz tell me what to do if Primary key fall short and execed the max size o
f
> the datatype like integer with Identity increment option.|||you have to drop the primary key and change the column datatype.
e.g.
create table tb1(i tinyint identity(254,1),constraint pk primary key(i))
go
insert tb1 default values
insert tb1 default values
--overflow error here
insert tb1 default values
go
alter table tb1 drop constraint pk
go
alter table tb1 alter column i bigint
go
alter table tb1 add constraint pk primary key(i)
go
--okay now
insert tb1 default values
go
select * from tb1
go
drop table tb1
-oj
"Ravi" <Ravi@.discussions.microsoft.com> wrote in message
news:CEAF4CD0-8F20-4E8F-ADF2-6490A60857E5@.microsoft.com...
> Plz tell me what to do if Primary key fall short and execed the max size
> of
> the datatype like integer with Identity increment option.|||you have to drop the primary key and change the column datatype.
e.g.
create table tb1(i tinyint identity(254,1),constraint pk primary key(i))
go
insert tb1 default values
insert tb1 default values
--overflow error here
insert tb1 default values
go
alter table tb1 drop constraint pk
go
alter table tb1 alter column i bigint
go
alter table tb1 add constraint pk primary key(i)
go
--okay now
insert tb1 default values
go
select * from tb1
go
drop table tb1
-oj
"Ravi" <Ravi@.discussions.microsoft.com> wrote in message
news:CEAF4CD0-8F20-4E8F-ADF2-6490A60857E5@.microsoft.com...
> Plz tell me what to do if Primary key fall short and execed the max size
> of
> the datatype like integer with Identity increment option.

Primary Key Data type in Time Dimension??

I have to create a Time dimension with day grain in a Datawarehouse system
and I don’t know what is the best data type for the primary key...
For example
1) I could put Number(8) datatype, then the dates will be: 20050114,
20050115, 20050116... Then in the fact tables I put the Number(8) datatype
in the date fields... But in my reporting tools I have to put the conversion
function to show the dates in the right format.
2) Or I could put Date datatype, then the dates will be: 01/14/2005,
01/15/2005, 01/16/2005... Then in the fact tables I put the Date datatype in
the date fields...
It’s the Date primary key a bad datatype? (Very slow)
What is the best Primary Key Data type in Time Dimension?
Thanks!
In my opinion the key should be as small as you can get it.
Depending on how many days you want to store you could have
Smallint (4 bytes) == over 170 years
Int (8 bytes) == a very long time
"Marcelo" <Marcelo@.discussions.microsoft.com> wrote in message
news:Marcelo@.discussions.microsoft.com:
> I have to create a Time dimension with day grain in a Datawarehouse system
> and I don't know what is the best data type for the primary key...
> For example
> 1) I could put Number(8) datatype, then the dates will be: 20050114,
> 20050115, 20050116... Then in the fact tables I put the Number(8)
> datatype
> in the date fields... But in my reporting tools I have to put the
> conversion
> function to show the dates in the right format.
> 2) Or I could put Date datatype, then the dates will be: 01/14/2005,
> 01/15/2005, 01/16/2005... Then in the fact tables I put the Date datatype
> in
> the date fields...
>
> It's the Date primary key a bad datatype? (Very slow)
> What is the best Primary Key Data type in Time Dimension?
> Thanks!
|||I have my date dimension using an INT primary key like 20050202.
This has been running for about 6 months now, and it is really
feeling like a good decision.
As for your reporting tool issue, you can just put additional
columns on your time dimension like
calendar_date DATETIME,
month_name VARCHAR(10),
day_name VARCHAR(10),
day_of_week VARCHAR(10),
...etc...
So you can have nice pre-formatted things to use in your
reporting tools.
It also makes for nice roll-up groupings if you include things
like
fiscal_period,
week_of_year,
quarter_of_year,
...etc...
Good luck,
Steve
"=?Utf-8?B?TWFyY2Vsbw==?=" <Marcelo@.discussions.microsoft.com>
wrote in
news:62A63A74-D1CE-4519-AA48-737FE514EB28@.microsoft.com:

> I have to create a Time dimension with day grain in a
> Datawarehouse system and I don’t know what is the best data
> type for the primary key...
> For example
> 1) I could put Number(8) datatype, then the dates will be:
> 20050114, 20050115, 20050116... Then in the fact tables I put
> the Number(8) datatype in the date fields... But in my
> reporting tools I have to put the conversion function to show
> the dates in the right format. 2) Or I could put Date
> datatype, then the dates will be: 01/14/2005, 01/15/2005,
> 01/16/2005... Then in the fact tables I put the Date datatype
> in the date fields...
>
> It’s the Date primary key a bad datatype? (Very slow)
> What is the best Primary Key Data type in Time Dimension?
> Thanks!
>

Primary Key Data type in Time Dimension??

I have to create a Time dimension with day grain in a Datawarehouse system
and I don’t know what is the best data type for the primary key...
For example
1) I could put Number(8) datatype, then the dates will be: 20050114,
20050115, 20050116... Then in the fact tables I put the Number(8) datatype
in the date fields... But in my reporting tools I have to put the conversion
function to show the dates in the right format.
2) Or I could put Date datatype, then the dates will be: 01/14/2005,
01/15/2005, 01/16/2005... Then in the fact tables I put the Date datatype i
n
the date fields...
It’s the Date primary key a bad datatype? (Very slow)
What is the best Primary Key Data type in Time Dimension?
Thanks!In my opinion the key should be as small as you can get it.
Depending on how many days you want to store you could have
Smallint (4 bytes) == over 170 years
Int (8 bytes) == a very long time
"Marcelo" <Marcelo@.discussions.microsoft.com> wrote in message
news:Marcelo@.discussions.microsoft.com:
> I have to create a Time dimension with day grain in a Datawarehouse system
> and I don't know what is the best data type for the primary key...
> For example
> 1) I could put Number(8) datatype, then the dates will be: 20050114,
> 20050115, 20050116... Then in the fact tables I put the Number(8)
> datatype
> in the date fields... But in my reporting tools I have to put the
> conversion
> function to show the dates in the right format.
> 2) Or I could put Date datatype, then the dates will be: 01/14/2005,
> 01/15/2005, 01/16/2005... Then in the fact tables I put the Date datatype
> in
> the date fields...
>
> It's the Date primary key a bad datatype? (Very slow)
> What is the best Primary Key Data type in Time Dimension?
> Thanks!|||I have my date dimension using an INT primary key like 20050202.
This has been running for about 6 months now, and it is really
feeling like a good decision.
As for your reporting tool issue, you can just put additional
columns on your time dimension like
calendar_date DATETIME,
month_name VARCHAR(10),
day_name VARCHAR(10),
day_of_week VARCHAR(10),
...etc...
So you can have nice pre-formatted things to use in your
reporting tools.
It also makes for nice roll-up groupings if you include things
like
fiscal_period,
week_of_year,
quarter_of_year,
...etc...
Good luck,
Steve
"examnotes" <Marcelo@.discussions.microsoft.com>
wrote in
news:62A63A74-D1CE-4519-AA48-737FE514EB28@.microsoft.com:

> I have to create a Time dimension with day grain in a
> Datawarehouse system and I don’t know what is the best data
> type for the primary key...
> For example
> 1) I could put Number(8) datatype, then the dates will be:
> 20050114, 20050115, 20050116... Then in the fact tables I put
> the Number(8) datatype in the date fields... But in my
> reporting tools I have to put the conversion function to show
> the dates in the right format. 2) Or I could put Date
> datatype, then the dates will be: 01/14/2005, 01/15/2005,
> 01/16/2005... Then in the fact tables I put the Date datatype
> in the date fields...
>
> It’s the Date primary key a bad datatype? (Very slow)
> What is the best Primary Key Data type in Time Dimension?
> Thanks!
>

primary key data type

Where is the biggest difference in performance between a 'int' ID and
a 'nvarchar' ID on big tables?
On 7 Feb 2007 21:30:03 -0800, retima@.gmail.com wrote:

>Where is the biggest difference in performance between a 'int' ID and
>a 'nvarchar' ID on big tables?
The PK is used as a final pointer by all other indexes on the table,
so whatever the size difference between the int and average nvarchar
(plus length indicator), may be multiplied, and makes other indexes
somewhat less dense, which is in general a bad thing.
J.
|||On Feb 8, 7:39 am, JXStern <JXSternChange...@.gte.net> wrote:
> On 7 Feb 2007 21:30:03 -0800, ret...@.gmail.com wrote:
>
> The PK is used as a final pointer by all other indexes on the table,
> so whatever the size difference between the int and average nvarchar
> (plus length indicator), may be multiplied, and makes other indexes
> somewhat less dense, which is in general a bad thing.
> J.
thanks J.
Can you direct me to an example which is more specific?
(a certain query that would prove it with numbers)
|||Yaniv,shalom
http://www.sql-server-performance.com/datatypes.asp
<yaniv.harpaz@.gmail.com> wrote in message
news:1170919960.359095.315430@.p10g2000cwp.googlegr oups.com...
> On Feb 8, 7:39 am, JXStern <JXSternChange...@.gte.net> wrote:
> thanks J.
> Can you direct me to an example which is more specific?
> (a certain query that would prove it with numbers)
>
|||I think that you mixed up primary key and clustered index. None
clustered index are using the clustered index's keys as a pointer to
the data. Of course if the primary key is clustered then all the none
clustered index will use the PK as the pointer to the data, but many
times there is another clustered index (and sometimes there is no
clustered index at all).
Adi
On Feb 8, 7:39 am, JXStern <JXSternChange...@.gte.net> wrote:
> On 7 Feb 2007 21:30:03 -0800, ret...@.gmail.com wrote:
>
> The PK is used as a final pointer by all other indexes on the table,
> so whatever the size difference between the int and average nvarchar
> (plus length indicator), may be multiplied, and makes other indexes
> somewhat less dense, which is in general a bad thing.
> J.
|||An int occupies 4 bytes, while nvarchar occupies 2 bytes per character. An
int goes from -2billion to +2 billion (ish) so smallint might be more
appropriate (2 bytes and +/- 32k ish). See
http://www.databasejournal.com/features/mssql/article.phpr/2212141 for exact
sizes. The reason I'm talking about the space occupied, is that this is one
crucial factor in determining the datatype - fewer pages to search = faster
searches. What I find confusing though is that these datatypes are not
really comparable as they'll store different kinds of information, and
perhaps you are questioning whether to use a surrogate key or a key with
business meaning, which is a different discussion and one you'll find many
threads on in this and the programming newsgroup.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
|||On Feb 8, 10:22 am, "Paul Ibison" <Paul.Ibi...@.Pygmalion.Com> wrote:
> An int occupies 4 bytes, while nvarchar occupies 2 bytes per character. An
> int goes from -2billion to +2 billion (ish) so smallint might be more
> appropriate (2 bytes and +/- 32k ish). Seehttp://www.databasejournal.com/features/mssql/article.phpr/2212141for exact
> sizes. The reason I'm talking about the space occupied, is that this is one
> crucial factor in determining the datatype - fewer pages to search = faster
> searches. What I find confusing though is that these datatypes are not
> really comparable as they'll store different kinds of information, and
> perhaps you are questioning whether to use a surrogate key or a key with
> business meaning, which is a different discussion and one you'll find many
> threads on in this and the programming newsgroup.
> Cheers,
> Paul Ibison SQL Server MVP,www.replicationanswers.com
Hi Paul,
In one of the systems I'm reviewing, most of the tables have nvarchar
data types because it's "comfortable" for programming and reports. I
want to convince them in the best way, that this move has problems and
might be very expensive to fix in the future.
|||OK - then if the column doesn't contain any 'meaningful' business data and
is just used to identify the records, I'd recommend using the appropriate
whole-number datatype. As a programmer I find them just as easy to use as
nvarchars. A lot of DBAs use surrogate PK keys with identity columns which
has other benefits - no need to design an algorithm for separate values.
Cheers,
Paul Ibison SQL Server MVP,www.replicationanswers.com
|||> The PK is used as a final pointer by all other indexes on the table,
Not really - it is the clustered index key that is used for bookmark
lookups. The clustered index is not necessarily the primary key.
Hope this helps.
Dan Guzman
SQL Server MVP
"JXStern" <JXSternChangeX2R@.gte.net> wrote in message
news:4pdls2125tpm4oln46ijq7lil8ht9i611b@.4ax.com...
> On 7 Feb 2007 21:30:03 -0800, retima@.gmail.com wrote:
>
> The PK is used as a final pointer by all other indexes on the table,
> so whatever the size difference between the int and average nvarchar
> (plus length indicator), may be multiplied, and makes other indexes
> somewhat less dense, which is in general a bad thing.
> J.
>
|||On 8 Feb 2007 00:10:19 -0800, "Adi" <adicohn@.hotmail.com> wrote:

> I think that you mixed up primary key and clustered index.
Ooops, yes I did.
Sorry for any confusion.
To try the original question again, if the PK is not clustered, just
being a PK, then the only impact is as any less dense index is that
much less efficient, and I suppose its use (like any index's use) by
FK's, would also consume that many more bytes of space.
I'll happily use char(8) or even varchar(24) as PKs, heck, I'll use
compound PKs even longer, as required, unless it's some hugely active
system where you have to squeeze out the last bits of performance.
And of course, most databases I walk into, are already using clustered
identity ints (or bigints more recently) as PKs on most tables ...
hence my sloppy answer last night.
J.

> None
>clustered index are using the clustered index's keys as a pointer to
>the data. Of course if the primary key is clustered then all the none
>clustered index will use the PK as the pointer to the data, but many
>times there is another clustered index (and sometimes there is no
>clustered index at all).
>Adi
>On Feb 8, 7:39 am, JXStern <JXSternChange...@.gte.net> wrote:
>

primary key data type

Where is the biggest difference in performance between a 'int' ID and
a 'nvarchar' ID on big tables?On 7 Feb 2007 21:30:03 -0800, retima@.gmail.com wrote:

>Where is the biggest difference in performance between a 'int' ID and
>a 'nvarchar' ID on big tables?
The PK is used as a final pointer by all other indexes on the table,
so whatever the size difference between the int and average nvarchar
(plus length indicator), may be multiplied, and makes other indexes
somewhat less dense, which is in general a bad thing.
J.|||On Feb 8, 7:39 am, JXStern <JXSternChange...@.gte.net> wrote:
> On 7 Feb 2007 21:30:03 -0800, ret...@.gmail.com wrote:
>
> The PK is used as a final pointer by all other indexes on the table,
> so whatever the size difference between the int and average nvarchar
> (plus length indicator), may be multiplied, and makes other indexes
> somewhat less dense, which is in general a bad thing.
> J.
thanks J.
Can you direct me to an example which is more specific?
(a certain query that would prove it with numbers)|||Yaniv,shalom
http://www.sql-server-performance.com/datatypes.asp
<yaniv.harpaz@.gmail.com> wrote in message
news:1170919960.359095.315430@.p10g2000cwp.googlegroups.com...
> On Feb 8, 7:39 am, JXStern <JXSternChange...@.gte.net> wrote:
> thanks J.
> Can you direct me to an example which is more specific?
> (a certain query that would prove it with numbers)
>|||I think that you mixed up primary key and clustered index. None
clustered index are using the clustered index's keys as a pointer to
the data. Of course if the primary key is clustered then all the none
clustered index will use the PK as the pointer to the data, but many
times there is another clustered index (and sometimes there is no
clustered index at all).
Adi
On Feb 8, 7:39 am, JXStern <JXSternChange...@.gte.net> wrote:
> On 7 Feb 2007 21:30:03 -0800, ret...@.gmail.com wrote:
>
> The PK is used as a final pointer by all other indexes on the table,
> so whatever the size difference between the int and average nvarchar
> (plus length indicator), may be multiplied, and makes other indexes
> somewhat less dense, which is in general a bad thing.
> J.|||An int occupies 4 bytes, while nvarchar occupies 2 bytes per character. An
int goes from -2billion to +2 billion (ish) so smallint might be more
appropriate (2 bytes and +/- 32k ish). See
http://www.databasejournal.com/feat...le.phpr/2212141 for exact
sizes. The reason I'm talking about the space occupied, is that this is one
crucial factor in determining the datatype - fewer pages to search = faster
searches. What I find confusing though is that these datatypes are not
really comparable as they'll store different kinds of information, and
perhaps you are questioning whether to use a surrogate key or a key with
business meaning, which is a different discussion and one you'll find many
threads on in this and the programming newsgroup.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com|||On Feb 8, 10:22 am, "Paul Ibison" <Paul.Ibi...@.Pygmalion.Com> wrote:
> An int occupies 4 bytes, while nvarchar occupies 2 bytes per character. An
> int goes from -2billion to +2 billion (ish) so smallint might be more
> appropriate (2 bytes and +/- 32k ish). Seehttp://www.databasejournal.com/f
eatures/mssql/article.phpr/2212141for exact
> sizes. The reason I'm talking about the space occupied, is that this is on
e
> crucial factor in determining the datatype - fewer pages to search = faste
r
> searches. What I find confusing though is that these datatypes are not
> really comparable as they'll store different kinds of information, and
> perhaps you are questioning whether to use a surrogate key or a key with
> business meaning, which is a different discussion and one you'll find many
> threads on in this and the programming newsgroup.
> Cheers,
> Paul Ibison SQL Server MVP,www.replicationanswers.com
Hi Paul,
In one of the systems I'm reviewing, most of the tables have nvarchar
data types because it's "comfortable" for programming and reports. I
want to convince them in the best way, that this move has problems and
might be very expensive to fix in the future.|||OK - then if the column doesn't contain any 'meaningful' business data and
is just used to identify the records, I'd recommend using the appropriate
whole-number datatype. As a programmer I find them just as easy to use as
nvarchars. A lot of DBAs use surrogate PK keys with identity columns which
has other benefits - no need to design an algorithm for separate values.
Cheers,
Paul Ibison SQL Server MVP,www.replicationanswers.com|||> The PK is used as a final pointer by all other indexes on the table,
Not really - it is the clustered index key that is used for bookmark
lookups. The clustered index is not necessarily the primary key.
Hope this helps.
Dan Guzman
SQL Server MVP
"JXStern" <JXSternChangeX2R@.gte.net> wrote in message
news:4pdls2125tpm4oln46ijq7lil8ht9i611b@.
4ax.com...
> On 7 Feb 2007 21:30:03 -0800, retima@.gmail.com wrote:
>
> The PK is used as a final pointer by all other indexes on the table,
> so whatever the size difference between the int and average nvarchar
> (plus length indicator), may be multiplied, and makes other indexes
> somewhat less dense, which is in general a bad thing.
> J.
>|||On 8 Feb 2007 00:10:19 -0800, "Adi" <adicohn@.hotmail.com> wrote:

> I think that you mixed up primary key and clustered index.
Ooops, yes I did.
Sorry for any confusion.
To try the original question again, if the PK is not clustered, just
being a PK, then the only impact is as any less dense index is that
much less efficient, and I suppose its use (like any index's use) by
FK's, would also consume that many more bytes of space.
I'll happily use char(8) or even varchar(24) as PKs, heck, I'll use
compound PKs even longer, as required, unless it's some hugely active
system where you have to squeeze out the last bits of performance.
And of course, most databases I walk into, are already using clustered
identity ints (or bigints more recently) as PKs on most tables ...
hence my sloppy answer last night.
J.

> None
>clustered index are using the clustered index's keys as a pointer to
>the data. Of course if the primary key is clustered then all the none
>clustered index will use the PK as the pointer to the data, but many
>times there is another clustered index (and sometimes there is no
>clustered index at all).
>Adi
>On Feb 8, 7:39 am, JXStern <JXSternChange...@.gte.net> wrote:
>

primary key data type

Where is the biggest difference in performance between a 'int' ID and
a 'nvarchar' ID on big tables?On 7 Feb 2007 21:30:03 -0800, retima@.gmail.com wrote:
>Where is the biggest difference in performance between a 'int' ID and
>a 'nvarchar' ID on big tables?
The PK is used as a final pointer by all other indexes on the table,
so whatever the size difference between the int and average nvarchar
(plus length indicator), may be multiplied, and makes other indexes
somewhat less dense, which is in general a bad thing.
J.|||On Feb 8, 7:39 am, JXStern <JXSternChange...@.gte.net> wrote:
> On 7 Feb 2007 21:30:03 -0800, ret...@.gmail.com wrote:
> >Where is the biggest difference in performance between a 'int' ID and
> >a 'nvarchar' ID on big tables?
> The PK is used as a final pointer by all other indexes on the table,
> so whatever the size difference between the int and average nvarchar
> (plus length indicator), may be multiplied, and makes other indexes
> somewhat less dense, which is in general a bad thing.
> J.
thanks J.
Can you direct me to an example which is more specific?
(a certain query that would prove it with numbers)|||I think that you mixed up primary key and clustered index. None
clustered index are using the clustered index's keys as a pointer to
the data. Of course if the primary key is clustered then all the none
clustered index will use the PK as the pointer to the data, but many
times there is another clustered index (and sometimes there is no
clustered index at all).
Adi
On Feb 8, 7:39 am, JXStern <JXSternChange...@.gte.net> wrote:
> On 7 Feb 2007 21:30:03 -0800, ret...@.gmail.com wrote:
> >Where is the biggest difference in performance between a 'int' ID and
> >a 'nvarchar' ID on big tables?
> The PK is used as a final pointer by all other indexes on the table,
> so whatever the size difference between the int and average nvarchar
> (plus length indicator), may be multiplied, and makes other indexes
> somewhat less dense, which is in general a bad thing.
> J.|||Yaniv,shalom
http://www.sql-server-performance.com/datatypes.asp
<yaniv.harpaz@.gmail.com> wrote in message
news:1170919960.359095.315430@.p10g2000cwp.googlegroups.com...
> On Feb 8, 7:39 am, JXStern <JXSternChange...@.gte.net> wrote:
>> On 7 Feb 2007 21:30:03 -0800, ret...@.gmail.com wrote:
>> >Where is the biggest difference in performance between a 'int' ID and
>> >a 'nvarchar' ID on big tables?
>> The PK is used as a final pointer by all other indexes on the table,
>> so whatever the size difference between the int and average nvarchar
>> (plus length indicator), may be multiplied, and makes other indexes
>> somewhat less dense, which is in general a bad thing.
>> J.
> thanks J.
> Can you direct me to an example which is more specific?
> (a certain query that would prove it with numbers)
>|||An int occupies 4 bytes, while nvarchar occupies 2 bytes per character. An
int goes from -2billion to +2 billion (ish) so smallint might be more
appropriate (2 bytes and +/- 32k ish). See
http://www.databasejournal.com/features/mssql/article.phpr/2212141 for exact
sizes. The reason I'm talking about the space occupied, is that this is one
crucial factor in determining the datatype - fewer pages to search = faster
searches. What I find confusing though is that these datatypes are not
really comparable as they'll store different kinds of information, and
perhaps you are questioning whether to use a surrogate key or a key with
business meaning, which is a different discussion and one you'll find many
threads on in this and the programming newsgroup.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com|||On Feb 8, 10:22 am, "Paul Ibison" <Paul.Ibi...@.Pygmalion.Com> wrote:
> An int occupies 4 bytes, while nvarchar occupies 2 bytes per character. An
> int goes from -2billion to +2 billion (ish) so smallint might be more
> appropriate (2 bytes and +/- 32k ish). Seehttp://www.databasejournal.com/features/mssql/article.phpr/2212141for exact
> sizes. The reason I'm talking about the space occupied, is that this is one
> crucial factor in determining the datatype - fewer pages to search = faster
> searches. What I find confusing though is that these datatypes are not
> really comparable as they'll store different kinds of information, and
> perhaps you are questioning whether to use a surrogate key or a key with
> business meaning, which is a different discussion and one you'll find many
> threads on in this and the programming newsgroup.
> Cheers,
> Paul Ibison SQL Server MVP,www.replicationanswers.com
Hi Paul,
In one of the systems I'm reviewing, most of the tables have nvarchar
data types because it's "comfortable" for programming and reports. I
want to convince them in the best way, that this move has problems and
might be very expensive to fix in the future.|||OK - then if the column doesn't contain any 'meaningful' business data and
is just used to identify the records, I'd recommend using the appropriate
whole-number datatype. As a programmer I find them just as easy to use as
nvarchars. A lot of DBAs use surrogate PK keys with identity columns which
has other benefits - no need to design an algorithm for separate values.
Cheers,
Paul Ibison SQL Server MVP,www.replicationanswers.com|||> The PK is used as a final pointer by all other indexes on the table,
Not really - it is the clustered index key that is used for bookmark
lookups. The clustered index is not necessarily the primary key.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"JXStern" <JXSternChangeX2R@.gte.net> wrote in message
news:4pdls2125tpm4oln46ijq7lil8ht9i611b@.4ax.com...
> On 7 Feb 2007 21:30:03 -0800, retima@.gmail.com wrote:
>>Where is the biggest difference in performance between a 'int' ID and
>>a 'nvarchar' ID on big tables?
> The PK is used as a final pointer by all other indexes on the table,
> so whatever the size difference between the int and average nvarchar
> (plus length indicator), may be multiplied, and makes other indexes
> somewhat less dense, which is in general a bad thing.
> J.
>|||On 8 Feb 2007 00:10:19 -0800, "Adi" <adicohn@.hotmail.com> wrote:
> I think that you mixed up primary key and clustered index.
Ooops, yes I did.
Sorry for any confusion.
To try the original question again, if the PK is not clustered, just
being a PK, then the only impact is as any less dense index is that
much less efficient, and I suppose its use (like any index's use) by
FK's, would also consume that many more bytes of space.
I'll happily use char(8) or even varchar(24) as PKs, heck, I'll use
compound PKs even longer, as required, unless it's some hugely active
system where you have to squeeze out the last bits of performance.
And of course, most databases I walk into, are already using clustered
identity ints (or bigints more recently) as PKs on most tables ...
hence my sloppy answer last night.
J.
> None
>clustered index are using the clustered index's keys as a pointer to
>the data. Of course if the primary key is clustered then all the none
>clustered index will use the PK as the pointer to the data, but many
>times there is another clustered index (and sometimes there is no
>clustered index at all).
>Adi
>On Feb 8, 7:39 am, JXStern <JXSternChange...@.gte.net> wrote:
>> On 7 Feb 2007 21:30:03 -0800, ret...@.gmail.com wrote:
>> >Where is the biggest difference in performance between a 'int' ID and
>> >a 'nvarchar' ID on big tables?
>> The PK is used as a final pointer by all other indexes on the table,
>> so whatever the size difference between the int and average nvarchar
>> (plus length indicator), may be multiplied, and makes other indexes
>> somewhat less dense, which is in general a bad thing.
>> J.
>|||On 8 Feb 2007 01:35:13 -0800, retima@.gmail.com wrote:
>In one of the systems I'm reviewing, most of the tables have nvarchar
>data types because it's "comfortable" for programming and reports. I
>want to convince them in the best way, that this move has problems and
>might be very expensive to fix in the future.
I'm rather sympathetic to that "comfortable" approach, it's often just
a classic database design, too. And ask Celko about reengineering a
database just to get rid of natural PKs (chuckle). Out of curiosity,
do they cluster these PKs?
If they're less than, oh, nvarchar(20), and hopefully average rather
shorter than that, I probably wouldn't worry about it, unless the
databases are huge, the transaction rate very high, or the servers
maxed out on RAM and very short of performance.
With a modern, moderate server having four processors (two dual-core),
4gb of RAM, and SCSI-level IO throughput, a solidly designed database
system can deliver a *lot* of performance, even if the PKs are not
minimal.
And the programmer's comfort can be a major factor on some systems,
too. Though I suppose it's been years since I've used that particular
argument, I've just surrendered to the surrogates.
Josh