Datatype for Primary key fields ...

Hi,
As far as my understanding goes, normally PK would be set on fields whose
datatype is INT. But in one of the project I saw 99% of the tables they have
used Varchar datatype for PK fields.
This internally means that it would string comparisons. I was arguing that
SQL server isn't good at 'String comparisons'. Am I right? or Am i missing
something? Any pointers on this topic would be of great help to me.
Best Regards
Vadivel
http://vadivel.blogspot.comVadivel wrote:

> Hi,
> As far as my understanding goes, normally PK would be set on fields whose
> datatype is INT. But in one of the project I saw 99% of the tables they ha
ve
> used Varchar datatype for PK fields.
> This internally means that it would string comparisons. I was arguing that
> SQL server isn't good at 'String comparisons'. Am I right? or Am i missing
> something? Any pointers on this topic would be of great help to me.
> Best Regards
> Vadivel
> http://vadivel.blogspot.com
This is the wrong question because the datatype for keys is determined
by the data you need to model in the table. For example how are you
going to represent names using an INTEGER?
If you meant to ask "what should I use for an artiificial surrogate
key?" then you can search the archives of this group for many previous
discussions on that topic.
David Portas
SQL Server MVP
--|||"Vadivel" <Vadivel@.discussions.microsoft.com> wrote in message
news:F2B2A710-79AB-460F-8057-46B4A0166183@.microsoft.com...
> Hi,
> As far as my understanding goes, normally PK would be set on fields whose
> datatype is INT. But in one of the project I saw 99% of the tables they
> have
> used Varchar datatype for PK fields.
> This internally means that it would string comparisons. I was arguing that
> SQL server isn't good at 'String comparisons'. Am I right? or Am i missing
> something? Any pointers on this topic would be of great help to me.
> Best Regards
> Vadivel
> http://vadivel.blogspot.com
If you don't use surrogates, I'd say that most natural primary keys are
strings.
Even telephone numbers, license plate numbers, zip codes, serial codes,
credit card numbers, bar codes, invoice numbers, bank account numbers... are
in reality, strings.|||Are you speaking of a natural or surrogate primary key?
http://www.aspfaq.com/show.asp?id=2504
Natural keys can be a combination of most any data type. However, if the key
in it's basic form is numeric (such as SSN, CustomerNo, or PhoneNumber) then
do try to define it as integer, becuase it's storage will be smaller than
character, and it will thus result in less memory usage and fewer index
pages to traverse.
If you plan to use an identity column as a surrogate primary key, then I
don't see a reason to use anything but an integer.
http://www.windowsitpro.com/Article...ArticleID=23449
Data Type Performance Tuning Tips for Microsoft SQL Server
http://www.sql-server-performance.com/datatypes.asp
In general integer based comparisons are more efficient than Char or VarChar
comparisons, but I don't know of SQL Server specifically not being not good
at character comparisons relative to any other DBMS system such as Oracle or
DB2.
"Vadivel" <Vadivel@.discussions.microsoft.com> wrote in message
news:F2B2A710-79AB-460F-8057-46B4A0166183@.microsoft.com...
> Hi,
> As far as my understanding goes, normally PK would be set on fields whose
> datatype is INT. But in one of the project I saw 99% of the tables they
> have
> used Varchar datatype for PK fields.
> This internally means that it would string comparisons. I was arguing that
> SQL server isn't good at 'String comparisons'. Am I right? or Am i missing
> something? Any pointers on this topic would be of great help to me.
> Best Regards
> Vadivel
> http://vadivel.blogspot.com|||I know that telephone numbers, Credit card nos, Zipcode all would be varchar
fields only. As we won't be doing any mathematical calculation based on that
data there isn't a need for us to go for INT datatype. Even though u would
have those fields as Varchar in ur DB won't you have a ID field in that
table? Won't that ID field be of INT datatype?
Best Regards
Vadivel
http://vadivel.blogspot.com
"Raymond D'Anjou" wrote:

> "Vadivel" <Vadivel@.discussions.microsoft.com> wrote in message
> news:F2B2A710-79AB-460F-8057-46B4A0166183@.microsoft.com...
> If you don't use surrogates, I'd say that most natural primary keys are
> strings.
> Even telephone numbers, license plate numbers, zip codes, serial codes,
> credit card numbers, bar codes, invoice numbers, bank account numbers... a
re
> in reality, strings.
>
>|||"Vadivel" <Vadivel@.discussions.microsoft.com> wrote in message
news:1CCC4883-5193-4706-9F62-CBEECE8FBF4C@.microsoft.com...
>I know that telephone numbers, Credit card nos, Zipcode all would be
>varchar
> fields only. As we won't be doing any mathematical calculation based on
> that
> data there isn't a need for us to go for INT datatype. Even though u would
> have those fields as Varchar in ur DB won't you have a ID field in that
> table? Won't that ID field be of INT datatype?
> Best Regards
> Vadivel
Read JTs response including the links.
Your ID field (column) is a surrogate.
The use of surrogates (including Identity) has been discussed in this
newsgroup "ad nauseum".
Even though you can use surrogates as keys, there should always exist a
"natural" primary key in your tables.|||"Raymond D'Anjou" <rdanjou@.canatradeNOSPAM.com> wrote in message
news:u8lYzCMAGHA.3936@.TK2MSFTNGP12.phx.gbl...
> "Vadivel" <Vadivel@.discussions.microsoft.com> wrote in message
> news:1CCC4883-5193-4706-9F62-CBEECE8FBF4C@.microsoft.com...
> Read JTs response including the links.
> Your ID field (column) is a surrogate.
> The use of surrogates (including Identity) has been discussed in this
> newsgroup "ad nauseum".
> Even though you can use surrogates as keys, there should always exist a
> "natural" primary key in your tables.
One other thing:
Concerning your "performance hit" using INTs versus VARCHARs.
From what I've read, especially in today's databases, if there is one it's
negligable.|||Thanks for the links JT.
Lets assume that I have a table with just two fields,
CityID Varchar(20) -- PK
CityName Varchar(50)
Now I would be using this field CityID in some other table as a FK field. Is
that ok? Or is it advisable / mandatory to have another column with INT
datatype and use it in other tbls as FK?
Best Regards
Vadivel
http://vadivel.blogspot.com
"JT" wrote:

> Are you speaking of a natural or surrogate primary key?
> http://www.aspfaq.com/show.asp?id=2504
> Natural keys can be a combination of most any data type. However, if the k
ey
> in it's basic form is numeric (such as SSN, CustomerNo, or PhoneNumber) th
en
> do try to define it as integer, becuase it's storage will be smaller than
> character, and it will thus result in less memory usage and fewer index
> pages to traverse.
> If you plan to use an identity column as a surrogate primary key, then I
> don't see a reason to use anything but an integer.
> http://www.windowsitpro.com/Article...ArticleID=23449
> Data Type Performance Tuning Tips for Microsoft SQL Server
> http://www.sql-server-performance.com/datatypes.asp
> In general integer based comparisons are more efficient than Char or VarCh
ar
> comparisons, but I don't know of SQL Server specifically not being not goo
d
> at character comparisons relative to any other DBMS system such as Oracle
or
> DB2.
>
> "Vadivel" <Vadivel@.discussions.microsoft.com> wrote in message
> news:F2B2A710-79AB-460F-8057-46B4A0166183@.microsoft.com...
>
>|||If CityID is the primary key, then that is the column you want to use as the
foreign key when joining referencing tables. However, I don't understand why
CityID would be 20 characters long. Is this something like an ISO code
assigned to every city on the planet? If CityID contains embedded attributes
like geographic coordinates or nation, state, county codes, then split those
attributes out as seperate columns.
Provide more details about what CityID means and how it's values are
assigned.
"Vadivel" <Vadivel@.discussions.microsoft.com> wrote in message
news:67DAA04A-A277-49AB-8745-521699D2F06F@.microsoft.com...
> Thanks for the links JT.
> Lets assume that I have a table with just two fields,
> CityID Varchar(20) -- PK
> CityName Varchar(50)
> Now I would be using this field CityID in some other table as a FK field.
> Is
> that ok? Or is it advisable / mandatory to have another column with INT
> datatype and use it in other tbls as FK?
> Best Regards
> Vadivel
> http://vadivel.blogspot.com
>
> "JT" wrote:
>

DataSizer for 2000

Hi,
I'm trying to estimate the amount of space that will be required for several
import tables.
I've run the DataSizer tool and it calculates the following for a table with
3 INT fields and one REAL field (prec - 24).
Heap Table Data Sizer
Rows in table 137,242,307
Data Row Fixed Len Col Size 16
Number of Columns in data row 4
Number of Variable Length Columns in data row 0
Max Size of Variable Length Data in data row 0
Index Key Fixed Len Col Size 0
Number of Columns in Index Key 0
Number of Variable Length Columns in index key 0
Max Size of Variable Length Data in index key 0
Page size (not configurable) 8192
Page Hdr Size (not configurable) 96
Data Row Size Calculation
Data Row Header 4
Null Bitmap 3
Variable Length Columns Total Size 0
Minimum Row Length 16
Total Row Size 23
Data rows per page 324
Data pages 423588
Table Size 3,470,032,896
This example happens to be an existing table (heap) that reports the
following information via sp_spaceused
rows = 137242307
space used = 3569632 KB
There is about a 100MB difference between the two values returned (table
size est and table size actual). Is the DataSizer tool accurate for SQL
2000? Am I performing the calculation incorrectly?
Thanks
Jerry
Sp_spaceused might use stale information (see the @.updateusage parameter to sp_spaceused and also
DBCC UPDATEUSAGE). Also, you might have less than 100% full pages (see DBCC SHOWCONTIG).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:%23yVzzudoFHA.1444@.TK2MSFTNGP10.phx.gbl...
> Hi,
> I'm trying to estimate the amount of space that will be required for several import tables.
> I've run the DataSizer tool and it calculates the following for a table with 3 INT fields and one
> REAL field (prec - 24).
> Heap Table Data Sizer
> Rows in table 137,242,307
> Data Row Fixed Len Col Size 16
> Number of Columns in data row 4
> Number of Variable Length Columns in data row 0
> Max Size of Variable Length Data in data row 0
> Index Key Fixed Len Col Size 0
> Number of Columns in Index Key 0
> Number of Variable Length Columns in index key 0
> Max Size of Variable Length Data in index key 0
> Page size (not configurable) 8192
> Page Hdr Size (not configurable) 96
> Data Row Size Calculation
> Data Row Header 4
> Null Bitmap 3
> Variable Length Columns Total Size 0
> Minimum Row Length 16
> Total Row Size 23
> Data rows per page 324
> Data pages 423588
> Table Size 3,470,032,896
>
> This example happens to be an existing table (heap) that reports the following information via
> sp_spaceused
>
> rows = 137242307
> space used = 3569632 KB
> There is about a 100MB difference between the two values returned (table size est and table size
> actual). Is the DataSizer tool accurate for SQL 2000? Am I performing the calculation
> incorrectly?
> Thanks
> Jerry
>
|||Hey Tibor.
I did use the updateusage param of sp_spaceused.
Yea I thought it might be due to fragmentation so I created a clustered
index on the table then dropped the clustered index to recompact the heap
pages. The 137 million rows now consume 3,399,200 KB (apx 70 MB less than
the estimated and about 170 MB less than before with the fragmentation).
Interesting ;-)
Thanks again for the reply.
Jerry
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:eTrz2aeoFHA.3448@.TK2MSFTNGP12.phx.gbl...
> Sp_spaceused might use stale information (see the @.updateusage parameter
> to sp_spaceused and also DBCC UPDATEUSAGE). Also, you might have less than
> 100% full pages (see DBCC SHOWCONTIG).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
> news:%23yVzzudoFHA.1444@.TK2MSFTNGP10.phx.gbl...
>
|||Hi Jerry,
If I take the numbers from your calculation (3,470,032,896 bytes) and compare to the numbers from
sp_spaceused (I assume) (3,399,200 KB) and convert properly to MB etc, I get:
SELECT KB, KB/1024 AS MB, KB/(1024*1024) AS GB
FROM
(
SELECT 3470032896/1024 AS KB
UNION
SELECT 3399200 AS KB
) AS i
KB MB GB
-- -- --
3388704.000000 3309.28125000000 3.23171997070312500
3399200.000000 3319.53125000000 3.24172973632812500
I.e. a difference of 10 MB, which for a 3.2GB table is pretty close, IMO :-).
Btw, I always select from sysindexes (after updateusage) when I want to get these values. I never
remember whether sp_spaceused uses reserved, dpages or the used column. To be honest, I don't
remember the difference between dpages and used by heart either, so I always look up sysindexes in
BOL.
I should also say that I didn't actually look at your formula/calculation. I was initially focused
on the stale information in sysindexes.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:%23CmI1geoFHA.2472@.TK2MSFTNGP15.phx.gbl...
> Hey Tibor.
> I did use the updateusage param of sp_spaceused.
> Yea I thought it might be due to fragmentation so I created a clustered index on the table then
> dropped the clustered index to recompact the heap pages. The 137 million rows now consume
> 3,399,200 KB (apx 70 MB less than the estimated and about 170 MB less than before with the
> fragmentation).
> Interesting ;-)
> Thanks again for the reply.
> Jerry
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
> news:eTrz2aeoFHA.3448@.TK2MSFTNGP12.phx.gbl...
>

DataSizer for 2000

Hi,
I'm trying to estimate the amount of space that will be required for several
import tables.
I've run the DataSizer tool and it calculates the following for a table with
3 INT fields and one REAL field (prec - 24).
Heap Table Data Sizer
Rows in table 137,242,307
Data Row Fixed Len Col Size 16
Number of Columns in data row 4
Number of Variable Length Columns in data row 0
Max Size of Variable Length Data in data row 0
Index Key Fixed Len Col Size 0
Number of Columns in Index Key 0
Number of Variable Length Columns in index key 0
Max Size of Variable Length Data in index key 0
Page size (not configurable) 8192
Page Hdr Size (not configurable) 96
Data Row Size Calculation
Data Row Header 4
Null Bitmap 3
Variable Length Columns Total Size 0
Minimum Row Length 16
Total Row Size 23
Data rows per page 324
Data pages 423588
Table Size 3,470,032,896
This example happens to be an existing table (heap) that reports the
following information via sp_spaceused
rows = 137242307
space used = 3569632 KB
There is about a 100MB difference between the two values returned (table
size est and table size actual). Is the DataSizer tool accurate for SQL
2000? Am I performing the calculation incorrectly?
Thanks
JerrySp_spaceused might use stale information (see the @.updateusage parameter to
sp_spaceused and also
DBCC UPDATEUSAGE). Also, you might have less than 100% full pages (see DBCC
SHOWCONTIG).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:%23yVzzudoFHA.1444@.TK2MSFTNGP10.phx.gbl...
> Hi,
> I'm trying to estimate the amount of space that will be required for sever
al import tables.
> I've run the DataSizer tool and it calculates the following for a table wi
th 3 INT fields and one
> REAL field (prec - 24).
> Heap Table Data Sizer
> Rows in table 137,242,307
> Data Row Fixed Len Col Size 16
> Number of Columns in data row 4
> Number of Variable Length Columns in data row 0
> Max Size of Variable Length Data in data row 0
> Index Key Fixed Len Col Size 0
> Number of Columns in Index Key 0
> Number of Variable Length Columns in index key 0
> Max Size of Variable Length Data in index key 0
> Page size (not configurable) 8192
> Page Hdr Size (not configurable) 96
> Data Row Size Calculation
> Data Row Header 4
> Null Bitmap 3
> Variable Length Columns Total Size 0
> Minimum Row Length 16
> Total Row Size 23
> Data rows per page 324
> Data pages 423588
> Table Size 3,470,032,896
>
> This example happens to be an existing table (heap) that reports the follo
wing information via
> sp_spaceused
>
> rows = 137242307
> space used = 3569632 KB
> There is about a 100MB difference between the two values returned (table s
ize est and table size
> actual). Is the DataSizer tool accurate for SQL 2000? Am I performing the
calculation
> incorrectly?
> Thanks
> Jerry
>|||Hey Tibor.
I did use the updateusage param of sp_spaceused.
Yea I thought it might be due to fragmentation so I created a clustered
index on the table then dropped the clustered index to recompact the heap
pages. The 137 million rows now consume 3,399,200 KB (apx 70 MB less than
the estimated and about 170 MB less than before with the fragmentation).
Interesting ;-)
Thanks again for the reply.
Jerry
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:eTrz2aeoFHA.3448@.TK2MSFTNGP12.phx.gbl...
> Sp_spaceused might use stale information (see the @.updateusage parameter
> to sp_spaceused and also DBCC UPDATEUSAGE). Also, you might have less than
> 100% full pages (see DBCC SHOWCONTIG).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
> news:%23yVzzudoFHA.1444@.TK2MSFTNGP10.phx.gbl...
>|||Hi Jerry,
If I take the numbers from your calculation (3,470,032,896 bytes) and compar
e to the numbers from
sp_spaceused (I assume) (3,399,200 KB) and convert properly to MB etc, I get
:
SELECT KB, KB/1024 AS MB, KB/(1024*1024) AS GB
FROM
(
SELECT 3470032896/1024 AS KB
UNION
SELECT 3399200 AS KB
) AS i
KB MB GB
-- -- --
3388704.000000 3309.28125000000 3.23171997070312500
3399200.000000 3319.53125000000 3.24172973632812500
I.e. a difference of 10 MB, which for a 3.2GB table is pretty close, IMO :-)
.
Btw, I always select from sysindexes (after updateusage) when I want to get
these values. I never
remember whether sp_spaceused uses reserved, dpages or the used column. To b
e honest, I don't
remember the difference between dpages and used by heart either, so I always
look up sysindexes in
BOL.
I should also say that I didn't actually look at your formula/calculation. I
was initially focused
on the stale information in sysindexes.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:%23CmI1geoFHA.2472@.TK2MSFTNGP15.phx.gbl...
> Hey Tibor.
> I did use the updateusage param of sp_spaceused.
> Yea I thought it might be due to fragmentation so I created a clustered in
dex on the table then
> dropped the clustered index to recompact the heap pages. The 137 million
rows now consume
> 3,399,200 KB (apx 70 MB less than the estimated and about 170 MB less than
before with the
> fragmentation).
> Interesting ;-)
> Thanks again for the reply.
> Jerry
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote i
n message
> news:eTrz2aeoFHA.3448@.TK2MSFTNGP12.phx.gbl...
>

DataSizer for 2000

Hi,
I'm trying to estimate the amount of space that will be required for several
import tables.
I've run the DataSizer tool and it calculates the following for a table with
3 INT fields and one REAL field (prec - 24).
Heap Table Data Sizer
Rows in table 137,242,307
Data Row Fixed Len Col Size 16
Number of Columns in data row 4
Number of Variable Length Columns in data row 0
Max Size of Variable Length Data in data row 0
Index Key Fixed Len Col Size 0
Number of Columns in Index Key 0
Number of Variable Length Columns in index key 0
Max Size of Variable Length Data in index key 0
Page size (not configurable) 8192
Page Hdr Size (not configurable) 96
Data Row Size Calculation
Data Row Header 4
Null Bitmap 3
Variable Length Columns Total Size 0
Minimum Row Length 16
Total Row Size 23
Data rows per page 324
Data pages 423588
Table Size 3,470,032,896
This example happens to be an existing table (heap) that reports the
following information via sp_spaceused
rows = 137242307
space used = 3569632 KB
There is about a 100MB difference between the two values returned (table
size est and table size actual). Is the DataSizer tool accurate for SQL
2000? Am I performing the calculation incorrectly?
Thanks
JerrySp_spaceused might use stale information (see the @.updateusage parameter to sp_spaceused and also
DBCC UPDATEUSAGE). Also, you might have less than 100% full pages (see DBCC SHOWCONTIG).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:%23yVzzudoFHA.1444@.TK2MSFTNGP10.phx.gbl...
> Hi,
> I'm trying to estimate the amount of space that will be required for several import tables.
> I've run the DataSizer tool and it calculates the following for a table with 3 INT fields and one
> REAL field (prec - 24).
> Heap Table Data Sizer
> Rows in table 137,242,307
> Data Row Fixed Len Col Size 16
> Number of Columns in data row 4
> Number of Variable Length Columns in data row 0
> Max Size of Variable Length Data in data row 0
> Index Key Fixed Len Col Size 0
> Number of Columns in Index Key 0
> Number of Variable Length Columns in index key 0
> Max Size of Variable Length Data in index key 0
> Page size (not configurable) 8192
> Page Hdr Size (not configurable) 96
> Data Row Size Calculation
> Data Row Header 4
> Null Bitmap 3
> Variable Length Columns Total Size 0
> Minimum Row Length 16
> Total Row Size 23
> Data rows per page 324
> Data pages 423588
> Table Size 3,470,032,896
>
> This example happens to be an existing table (heap) that reports the following information via
> sp_spaceused
>
> rows = 137242307
> space used = 3569632 KB
> There is about a 100MB difference between the two values returned (table size est and table size
> actual). Is the DataSizer tool accurate for SQL 2000? Am I performing the calculation
> incorrectly?
> Thanks
> Jerry
>|||Hey Tibor.
I did use the updateusage param of sp_spaceused.
Yea I thought it might be due to fragmentation so I created a clustered
index on the table then dropped the clustered index to recompact the heap
pages. The 137 million rows now consume 3,399,200 KB (apx 70 MB less than
the estimated and about 170 MB less than before with the fragmentation).
Interesting ;-)
Thanks again for the reply.
Jerry
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:eTrz2aeoFHA.3448@.TK2MSFTNGP12.phx.gbl...
> Sp_spaceused might use stale information (see the @.updateusage parameter
> to sp_spaceused and also DBCC UPDATEUSAGE). Also, you might have less than
> 100% full pages (see DBCC SHOWCONTIG).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
> news:%23yVzzudoFHA.1444@.TK2MSFTNGP10.phx.gbl...
>> Hi,
>> I'm trying to estimate the amount of space that will be required for
>> several import tables.
>> I've run the DataSizer tool and it calculates the following for a table
>> with 3 INT fields and one REAL field (prec - 24).
>> Heap Table Data Sizer
>> Rows in table 137,242,307
>> Data Row Fixed Len Col Size 16
>> Number of Columns in data row 4
>> Number of Variable Length Columns in data row 0
>> Max Size of Variable Length Data in data row 0
>> Index Key Fixed Len Col Size 0
>> Number of Columns in Index Key 0
>> Number of Variable Length Columns in index key 0
>> Max Size of Variable Length Data in index key 0
>> Page size (not configurable) 8192
>> Page Hdr Size (not configurable) 96
>> Data Row Size Calculation
>> Data Row Header 4
>> Null Bitmap 3
>> Variable Length Columns Total Size 0
>> Minimum Row Length 16
>> Total Row Size 23
>> Data rows per page 324
>> Data pages 423588
>> Table Size 3,470,032,896
>>
>> This example happens to be an existing table (heap) that reports the
>> following information via sp_spaceused
>>
>> rows = 137242307
>> space used = 3569632 KB
>> There is about a 100MB difference between the two values returned (table
>> size est and table size actual). Is the DataSizer tool accurate for SQL
>> 2000? Am I performing the calculation incorrectly?
>> Thanks
>> Jerry
>|||Hi Jerry,
If I take the numbers from your calculation (3,470,032,896 bytes) and compare to the numbers from
sp_spaceused (I assume) (3,399,200 KB) and convert properly to MB etc, I get:
SELECT KB, KB/1024 AS MB, KB/(1024*1024) AS GB
FROM
(
SELECT 3470032896/1024 AS KB
UNION
SELECT 3399200 AS KB
) AS i
KB MB GB
-- -- --
3388704.000000 3309.28125000000 3.23171997070312500
3399200.000000 3319.53125000000 3.24172973632812500
I.e. a difference of 10 MB, which for a 3.2GB table is pretty close, IMO :-).
Btw, I always select from sysindexes (after updateusage) when I want to get these values. I never
remember whether sp_spaceused uses reserved, dpages or the used column. To be honest, I don't
remember the difference between dpages and used by heart either, so I always look up sysindexes in
BOL.
I should also say that I didn't actually look at your formula/calculation. I was initially focused
on the stale information in sysindexes.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:%23CmI1geoFHA.2472@.TK2MSFTNGP15.phx.gbl...
> Hey Tibor.
> I did use the updateusage param of sp_spaceused.
> Yea I thought it might be due to fragmentation so I created a clustered index on the table then
> dropped the clustered index to recompact the heap pages. The 137 million rows now consume
> 3,399,200 KB (apx 70 MB less than the estimated and about 170 MB less than before with the
> fragmentation).
> Interesting ;-)
> Thanks again for the reply.
> Jerry
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
> news:eTrz2aeoFHA.3448@.TK2MSFTNGP12.phx.gbl...
>> Sp_spaceused might use stale information (see the @.updateusage parameter to sp_spaceused and also
>> DBCC UPDATEUSAGE). Also, you might have less than 100% full pages (see DBCC SHOWCONTIG).
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>> Blog: http://solidqualitylearning.com/blogs/tibor/
>>
>> "Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
>> news:%23yVzzudoFHA.1444@.TK2MSFTNGP10.phx.gbl...
>> Hi,
>> I'm trying to estimate the amount of space that will be required for several import tables.
>> I've run the DataSizer tool and it calculates the following for a table with 3 INT fields and
>> one REAL field (prec - 24).
>> Heap Table Data Sizer
>> Rows in table 137,242,307
>> Data Row Fixed Len Col Size 16
>> Number of Columns in data row 4
>> Number of Variable Length Columns in data row 0
>> Max Size of Variable Length Data in data row 0
>> Index Key Fixed Len Col Size 0
>> Number of Columns in Index Key 0
>> Number of Variable Length Columns in index key 0
>> Max Size of Variable Length Data in index key 0
>> Page size (not configurable) 8192
>> Page Hdr Size (not configurable) 96
>> Data Row Size Calculation
>> Data Row Header 4
>> Null Bitmap 3
>> Variable Length Columns Total Size 0
>> Minimum Row Length 16
>> Total Row Size 23
>> Data rows per page 324
>> Data pages 423588
>> Table Size 3,470,032,896
>>
>> This example happens to be an existing table (heap) that reports the following information via
>> sp_spaceused
>>
>> rows = 137242307
>> space used = 3569632 KB
>> There is about a 100MB difference between the two values returned (table size est and table size
>> actual). Is the DataSizer tool accurate for SQL 2000? Am I performing the calculation
>> incorrectly?
>> Thanks
>> Jerry
>>
>

Datasets with multiple tables..

hey guys, quick question that i hope someone can help steer me in the correct direction.

i've got a failry large report that gets its data from and XML web service.. the dataset that the webservice returns has 6 tables in it (i'm interested in data from 5 of them).. in order to get my fields to show up correctly in my dataset viewer i've created 5 datasets and use the ElementPath in my query to single out the table i want for each dataset.. this works fine about 50% of the time.. the other 50% of the time it craps out on my because some of the data manipulation that the webservice does causes primary key violations.. is the report making 5 calls to the webservice? if it is, is it sending them in multiple threads.. its the only thing i can think of that would cause my key violations..

my sproc that is dying looks similar to this:

delete from dbo.Charges where contractid = @.contract

insert into dbo.charges (columns.....) values (values)

i can see that it would violate the key if both threads were doing the insert simultaneously(sp)..

now.. is there a way to only make one trip to the webservice and use those results in the report, while still having the fields available in the dataset viewer?

thanks

mike

Yes, RS will execute each DataSet in its own thread if the DataSource is not marked to use a transaction. If your RS DataSource is set to execute in a transaction each DataSet will be executed in order in the same thread. In report designer, you can mark a DataSource to use a transaction by checking the "Use single transaction" box in the Data Source dialog.

However, even if all your DataSets are executed in the same thread, each DataSet will still make a seperate call to the web service to retrieve the data.

John

|||

thanks John.. it works as expected now.. is there anyway that i could prevent all the calls to the webservice? ie have a main dataset with all the tables, then pass that dataset to a subreport for each section that uses a specific table?

thanks again for the quick reply

mike

Dataset with multiple tables is not getting populated

Hi,

I have a stored proc which returns multiple result sets. These results sets I am capturing using a strongly typed dataset which in turn I am using to display in the code. My dataset will have 5 tables. However when I run the code only 3 tables get populated and the remaining 2 gets no data.

I have seen the problem earlier and could not resolved it. Please let me know if any one can help.

Thanks in advance

Rohit

From your description, i think it really has no records.

because you can really see five tables without any error.

it means the SP does populate five tables and pass them to Dataset.

|||

Hi

Thanks for your reply.

There is data in the database and when I run the sproc directly through the execute command it returns me the results that I want i.e. all the 5 tables with the rows. However when I use the data adapter method using the fill dataset it just populates fhe first 3 tables and the next 2 are blank. Between the tables in the strongly typed dataset I have no relations set either.

Thanks in advance.

|||Try to useSqlDataAdapter to fill a DataSet, instead of using strongly typed dataset.sql

dataset problem

hi,

i am using dataset for passing value to crystal report.
when the stored procedure contains 2 tables then how to create the dataset1.xsd for two table.
query with join works fine in QA.
i tried by giving two tables in dataset schema but how to give two tables with selected fields as per the query.
which table i should mention in fill method.

please tell me a procedure how to do this.

i tried an alternative method also.
by creating dataset at runtime using adapter.
but without filteration as per query all data appears in the report.

thanksafter setting dataset using adapter, aand setting it to crystal use record selection formula.|||iam using crystal report.net in vb.net
can you send a sample code for this.
your help will be appreciated.|||Hi u can code something like this.

Dim srcCr As Object
Dim rptDoc As New ReportDocument

srcCr = rptDoc
srcCr.SetDataSource(dsObj) --dsobj is ur dataset
rptDoc.Load("\reports\abc.rpt")

srcCr.RecordSelectionFormula = "{command.AccID}=124"

hope it helps you

Dataset Data is not the same as Database Data

Hi,

Thanks in advance. I have a problem in my dataset. Today morning we updated some of the descriptions to our parameter tables. But these are not getting updated to our reports. I checked the dataset. Even that shows the same values. How do I go about this? Please help. Thank You.

Regards,

Das.

You might have turned on caching on the reports. Reports execution can be cached, which is mostly done in environments where data retrieval would take a tremendous time comparing to the benefit of having realtime / cached data. Look in the properties of the report. Caching of the report should be disabled, otherwise the reportserver will take a copy from the Report Server Temp database to render the report.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de
|||

Hi,

Thanks for the reply. I tried to find how to disable the cache in report and dataset properties. But I couldn't find any option. Could you please tell me where can I find the option? Thanks for your help. Bye.

Regards,

Das.

dataset and identity of new record inserted

Hi,

I have 2 tables in my databasePrescriptionHeader and PrescriptionDetails.

My PrescriptionHeader table has the following fields:

PrescriptionID -identity field

PatientID

PatientfName

Patientlname

PrescriptionDetails table has the following fields:

PrescriptionDetailID -identity

PrescriptionID -from PrescriptionHeader table

MedicineDosage

The functionInsertPrescription inserts values into the tablePrescriptionHeader. I want the same function to then insert the value ofMedicineDosage intoPrescriptionDetails with the same PrescriptionID inserted into PrescriptionHeader. How do I tell the function to insert the PrescriptionID that was automatically inserted into PrescriptionHeader also into table PrescriptionDetails . How do I return the identity before proceeding to insert into PrescriptionDetails table?

Thanks

Function InsertPrescription(ByVal PatientIDAsString, _

ByVal PatientFnameAsString, _

ByVal PatientlnameAsString, ByValMedicineDosage as String)

Dim DBAdapterAs SqlDataAdapter

Dim DBDataSetAs DataSet

Dim SQLStringAsString

Dim DBCommandBuilderAs SqlCommandBuilder

SQLString ="SELECT * FROM PrescriptionHeader WHERE PrescriptionId = ''"

DBAdapter =New SqlDataAdapter(SQLString, DBConnection)

DBDataSet =New DataSet

DBAdapter.Fill(DBDataSet)

Dim AddedRowAs DataRow = DBDataSet.Tables(0).NewRow()

AddedRow("PatientID") = PatientID

AddedRow("PatientfName") = PatientFname

AddedRow("Patientlname") = Patientlname

DBDataSet.Tables(0).Rows.Add(AddedRow)

DBCommandBuilder =New SqlCommandBuilder(DBAdapter)

DBAdapter.Update(DBDataSet)

EndFunction

The following article is very helpful in your case

Inserting relational data using DataSet and DataAdapter

HTH
Regards

DataReader is blocking my tables

Hello Newsgroup,

I have written a very short program to get information from a whole
table out of a database. the problem is that other people couldn't
work on this table during the process. It seems that my program locks
the whole table.
I used the DataReader from the .NET Framework. Can you please take a
look at the code and give me any solution? Thank very much, Nils

Dim strSQL As String = "SELECT * FROM TESTTABLE"
Dim Conn As System.Data.SqlClient.SqlConnection = New
System.Data.SqlClient.SqlConnection("Data Source=1.1.1.1;
User ID=sa;Password=secret;Persist Security Info=True;
Initial Catalog=TestDB")
Conn.Open()
Dim SqlCmd As SqlCommand = New SqlCommand(strSQL,Conn)
Dim DR As System.Data.SqlClient.SqlDataReader
Try
DR = SqlCmd.ExecuteReader
Do While DR.Read()
<only reading with DR.item("columnname")>
Loop
Catch ex As Exception
errorhandler(ex.ToString)
Finally
If DR.IsClosed = False Then DR.Close()
SqlCmd.Dispose()
End TryNils Pommerien (fishinet@.gmx.de) writes:
> I have written a very short program to get information from a whole
> table out of a database. the problem is that other people couldn't
> work on this table during the process. It seems that my program locks
> the whole table.
> I used the DataReader from the .NET Framework. Can you please take a
> look at the code and give me any solution? Thank very much, Nils
> Dim strSQL As String = "SELECT * FROM TESTTABLE"

Well, a SELECT * from a table without any WHERE condition will require
the entire table to be locked while you get the data. Other people
should still be able to read from the table, but updates will not
be possible.

If the table is small, this is not much of an issue, because unless
you go do some huge processing for each row. But if the table is big,
you will held the locks for quite some time. In such case I would
question the wise in getting so much data to the client.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Erland,

Would a "SELECT * FROM TABLE WITH NOLOCK" work in his case? Assuming he
does not care if the data changes.

Oscar...

"Erland Sommarskog" <sommar@.algonet.se> wrote in message
news:Xns93ED17B437BFYazorman@.127.0.0.1...
> Nils Pommerien (fishinet@.gmx.de) writes:
> > I have written a very short program to get information from a whole
> > table out of a database. the problem is that other people couldn't
> > work on this table during the process. It seems that my program locks
> > the whole table.
> > I used the DataReader from the .NET Framework. Can you please take a
> > look at the code and give me any solution? Thank very much, Nils
> > Dim strSQL As String = "SELECT * FROM TESTTABLE"
> Well, a SELECT * from a table without any WHERE condition will require
> the entire table to be locked while you get the data. Other people
> should still be able to read from the table, but updates will not
> be possible.
> If the table is small, this is not much of an issue, because unless
> you go do some huge processing for each row. But if the table is big,
> you will held the locks for quite some time. In such case I would
> question the wise in getting so much data to the client.|||Oscar Santiesteban Jr. (oscarsantiesteban@.worldnet.att.net) writes:
> Erland,
> Would a "SELECT * FROM TABLE WITH NOLOCK" work in his case? Assuming he
> does not care if the data changes.

No that would not work:

Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 'nolock'.

But:

SELECT * FROM tbl WITH (NOLOCK)

would of course the remove the locking problems. I didn't mention this
possibility, because I had a feeling that he his real problem one of:

1) He's getting far more rows than he has use for.
2) He's doing something long-winding between the retrieval of each row.

So the NOLOCK would only be a band-aid on a poor design.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Erland, a question for you, I've noticed that the NOLOCK statement
generates more logical IO than selecting from the entire table, do you
know the cause?

Ray Higdon MCSE, MCDBA, CCNA

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||Ray Higdon (rayhigdon@.higdonconsulting.com) writes:
> Erland, a question for you, I've noticed that the NOLOCK statement
> generates more logical IO than selecting from the entire table, do you
> know the cause?

Eh, could you provide a repro?

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

datamodelling

Hi,
I'm designing a database where i have some tables about contacts. What i
would like to know is what the best design is for creating contacts.
I have a table relations where i store persons name and company names. Now
to determine the relation where who is related to who, e.g. a company and
their contacts or a family with their family members, i need some sort of a
recursive table/query.
Can anyone help me with this or is what i want not the right way?
First of all you have to know what are your entities an their atributes,
after that need to know what kind of relation you want, then you determine
the cardinality of it, and only at the end of work you will see if is
necessary some other indexes or special constraints and can evaluate the
performance.
Ex:
The entitys and atributes are:
Family (familyid, familyname) and Family members (memberid, membername)
If you want a family have various members and a member is on only one
family, then it is a 1 to N relation.
Then you will model your tables (2 tables and 1 relation) like:
Family (familyid as PK, familyname) <- Family members (memberid as PK,
familyid as PK and FK, membername)
If you want a family have various members and a member is on more than one
family, then it is a N to N relation.
Then you will model your tables (3 tables and 2 relations) like:
Family (familyid as PK, familyname) <- Familymembers (memberid as PK and FK,
familyid as PK and FK)
Members (memberid as PK, membername) <- Familymembers (memberid as PK and
FK, familyid as PK and FK)
It seems you need to study a little about normalization. ; )
Regards
BMartins (Brazil)
"Jason" <jlewis@.homail.com> escreveu na mensagem
news:OiGcCjJNFHA.2680@.TK2MSFTNGP09.phx.gbl...
> Hi,
> I'm designing a database where i have some tables about contacts. What i
> would like to know is what the best design is for creating contacts.
> I have a table relations where i store persons name and company names. Now
> to determine the relation where who is related to who, e.g. a company and
> their contacts or a family with their family members, i need some sort of
a
> recursive table/query.
> Can anyone help me with this or is what i want not the right way?
>

datamodelling

Hi,
I'm designing a database where i have some tables about contacts. What i
would like to know is what the best design is for creating contacts.
I have a table relations where i store persons name and company names. Now
to determine the relation where who is related to who, e.g. a company and
their contacts or a family with their family members, i need some sort of a
recursive table/query.
Can anyone help me with this or is what i want not the right way?First of all you have to know what are your entities an their atributes,
after that need to know what kind of relation you want, then you determine
the cardinality of it, and only at the end of work you will see if is
necessary some other indexes or special constraints and can evaluate the
performance.
Ex:
The entitys and atributes are:
Family (familyid, familyname) and Family members (memberid, membername)
If you want a family have various members and a member is on only one
family, then it is a 1 to N relation.
Then you will model your tables (2 tables and 1 relation) like:
Family (familyid as PK, familyname) <- Family members (memberid as PK,
familyid as PK and FK, membername)
If you want a family have various members and a member is on more than one
family, then it is a N to N relation.
Then you will model your tables (3 tables and 2 relations) like:
Family (familyid as PK, familyname) <- Familymembers (memberid as PK and FK,
familyid as PK and FK)
Members (memberid as PK, membername) <- Familymembers (memberid as PK and
FK, familyid as PK and FK)
It seems you need to study a little about normalization. ; )
--
Regards
BMartins (Brazil)
"Jason" <jlewis@.homail.com> escreveu na mensagem
news:OiGcCjJNFHA.2680@.TK2MSFTNGP09.phx.gbl...
> Hi,
> I'm designing a database where i have some tables about contacts. What i
> would like to know is what the best design is for creating contacts.
> I have a table relations where i store persons name and company names. Now
> to determine the relation where who is related to who, e.g. a company and
> their contacts or a family with their family members, i need some sort of
a
> recursive table/query.
> Can anyone help me with this or is what i want not the right way?
>

datamodelling

Hi,
I'm designing a database where i have some tables about contacts. What i
would like to know is what the best design is for creating contacts.
I have a table relations where i store persons name and company names. Now
to determine the relation where who is related to who, e.g. a company and
their contacts or a family with their family members, i need some sort of a
recursive table/query.
Can anyone help me with this or is what i want not the right way?First of all you have to know what are your entities an their atributes,
after that need to know what kind of relation you want, then you determine
the cardinality of it, and only at the end of work you will see if is
necessary some other indexes or special constraints and can evaluate the
performance.
Ex:
The entitys and atributes are:
Family (familyid, familyname) and Family members (memberid, membername)
If you want a family have various members and a member is on only one
family, then it is a 1 to N relation.
Then you will model your tables (2 tables and 1 relation) like:
Family (familyid as PK, familyname) <- Family members (memberid as PK,
familyid as PK and FK, membername)
If you want a family have various members and a member is on more than one
family, then it is a N to N relation.
Then you will model your tables (3 tables and 2 relations) like:
Family (familyid as PK, familyname) <- Familymembers (memberid as PK and FK,
familyid as PK and FK)
Members (memberid as PK, membername) <- Familymembers (memberid as PK and
FK, familyid as PK and FK)
It seems you need to study a little about normalization. ; )
Regards
BMartins (Brazil)
"Jason" <jlewis@.homail.com> escreveu na mensagem
news:OiGcCjJNFHA.2680@.TK2MSFTNGP09.phx.gbl...
> Hi,
> I'm designing a database where i have some tables about contacts. What i
> would like to know is what the best design is for creating contacts.
> I have a table relations where i store persons name and company names. Now
> to determine the relation where who is related to who, e.g. a company and
> their contacts or a family with their family members, i need some sort of
a
> recursive table/query.
> Can anyone help me with this or is what i want not the right way?
>

Datamodel behind System Tables

L.S.,
How can I find out what the datamodel is behind the System Tables
(SysColumns, SysObjects, SysDatabases, etc.). I once came across a question
about finding out what the default value for a certain column in a certain
table was. There was talk about doing some heavy parsing of the result set
after using stored procedure sp_help(text). And even then it was not sure
that the desired result would be achieved, they said, leaving one to the
choice of looking up the actual SQL code.
However, after looking up some documentation on the System Tables and
guessing from there on where I might find the desired information, I found
out that one may find the default value for a certain column in a certain
table (or any other object for that matter) in the SysComments table. It
would have been a lot easier to find this out if I had had a datamodel of
those System Tables. Now, before using ER Studio, I was wondering if this
datamodel exists and if so, if it could be shared with the community at large.
Many thanks in advance,
Wilfred Damhuis
P.S.: replies may be send to wdyttg@.rubycon.demon.nl
Does this help you:
http://www.microsoft.com/sql/techinf.../systables.asp
"Rubycon" <Rubycon@.discussions.microsoft.com> wrote in message
news:0B3B2E98-6C5E-45CD-BB2E-7E844B1D197F@.microsoft.com...
> L.S.,
> How can I find out what the datamodel is behind the System Tables
> (SysColumns, SysObjects, SysDatabases, etc.). I once came across a
question
> about finding out what the default value for a certain column in a certain
> table was. There was talk about doing some heavy parsing of the result set
> after using stored procedure sp_help(text). And even then it was not sure
> that the desired result would be achieved, they said, leaving one to the
> choice of looking up the actual SQL code.
> However, after looking up some documentation on the System Tables and
> guessing from there on where I might find the desired information, I found
> out that one may find the default value for a certain column in a certain
> table (or any other object for that matter) in the SysComments table. It
> would have been a lot easier to find this out if I had had a datamodel of
> those System Tables. Now, before using ER Studio, I was wondering if this
> datamodel exists and if so, if it could be shared with the community at
large.
> Many thanks in advance,
> Wilfred Damhuis
> P.S.: replies may be send to wdyttg@.rubycon.demon.nl
|||Adam,
thanks, not only for your swift response, but also for the indeed very
helpfull info. But is it just me or is this little known?
Regards,
Wilfred Dmahuis
"Adam Machanic" wrote:

> Does this help you:
> http://www.microsoft.com/sql/techinf.../systables.asp
>
> "Rubycon" <Rubycon@.discussions.microsoft.com> wrote in message
> news:0B3B2E98-6C5E-45CD-BB2E-7E844B1D197F@.microsoft.com...
> question
> large.
>
>
|||"Rubycon" <Rubycon@.discussions.microsoft.com> wrote in message
news:EE0D57A7-EF61-4856-99B1-66EFEA8CFD0B@.microsoft.com...
> helpfull info. But is it just me or is this little known?
I have no idea how well known it is (according to the text in the link,
it's "very popular") but I only discovered it last week

Datamodel behind System Tables

L.S.,
How can I find out what the datamodel is behind the System Tables
(SysColumns, SysObjects, SysDatabases, etc.). I once came across a question
about finding out what the default value for a certain column in a certain
table was. There was talk about doing some heavy parsing of the result set
after using stored procedure sp_help(text). And even then it was not sure
that the desired result would be achieved, they said, leaving one to the
choice of looking up the actual SQL code.
However, after looking up some documentation on the System Tables and
guessing from there on where I might find the desired information, I found
out that one may find the default value for a certain column in a certain
table (or any other object for that matter) in the SysComments table. It
would have been a lot easier to find this out if I had had a datamodel of
those System Tables. Now, before using ER Studio, I was wondering if this
datamodel exists and if so, if it could be shared with the community at large.
Many thanks in advance,
Wilfred Damhuis
P.S.: replies may be send to wdyttg@.rubycon.demon.nlDoes this help you:
http://www.microsoft.com/sql/techinfo/productdoc/2000/systables.asp
"Rubycon" <Rubycon@.discussions.microsoft.com> wrote in message
news:0B3B2E98-6C5E-45CD-BB2E-7E844B1D197F@.microsoft.com...
> L.S.,
> How can I find out what the datamodel is behind the System Tables
> (SysColumns, SysObjects, SysDatabases, etc.). I once came across a
question
> about finding out what the default value for a certain column in a certain
> table was. There was talk about doing some heavy parsing of the result set
> after using stored procedure sp_help(text). And even then it was not sure
> that the desired result would be achieved, they said, leaving one to the
> choice of looking up the actual SQL code.
> However, after looking up some documentation on the System Tables and
> guessing from there on where I might find the desired information, I found
> out that one may find the default value for a certain column in a certain
> table (or any other object for that matter) in the SysComments table. It
> would have been a lot easier to find this out if I had had a datamodel of
> those System Tables. Now, before using ER Studio, I was wondering if this
> datamodel exists and if so, if it could be shared with the community at
large.
> Many thanks in advance,
> Wilfred Damhuis
> P.S.: replies may be send to wdyttg@.rubycon.demon.nl|||Adam,
thanks, not only for your swift response, but also for the indeed very
helpfull info. But is it just me or is this little known?
Regards,
Wilfred Dmahuis
"Adam Machanic" wrote:
> Does this help you:
> http://www.microsoft.com/sql/techinfo/productdoc/2000/systables.asp
>
> "Rubycon" <Rubycon@.discussions.microsoft.com> wrote in message
> news:0B3B2E98-6C5E-45CD-BB2E-7E844B1D197F@.microsoft.com...
> > L.S.,
> >
> > How can I find out what the datamodel is behind the System Tables
> > (SysColumns, SysObjects, SysDatabases, etc.). I once came across a
> question
> > about finding out what the default value for a certain column in a certain
> > table was. There was talk about doing some heavy parsing of the result set
> > after using stored procedure sp_help(text). And even then it was not sure
> > that the desired result would be achieved, they said, leaving one to the
> > choice of looking up the actual SQL code.
> >
> > However, after looking up some documentation on the System Tables and
> > guessing from there on where I might find the desired information, I found
> > out that one may find the default value for a certain column in a certain
> > table (or any other object for that matter) in the SysComments table. It
> > would have been a lot easier to find this out if I had had a datamodel of
> > those System Tables. Now, before using ER Studio, I was wondering if this
> > datamodel exists and if so, if it could be shared with the community at
> large.
> >
> > Many thanks in advance,
> >
> > Wilfred Damhuis
> >
> > P.S.: replies may be send to wdyttg@.rubycon.demon.nl
>
>|||"Rubycon" <Rubycon@.discussions.microsoft.com> wrote in message
news:EE0D57A7-EF61-4856-99B1-66EFEA8CFD0B@.microsoft.com...
> helpfull info. But is it just me or is this little known?
I have no idea how well known it is (according to the text in the link,
it's "very popular") but I only discovered it last week :)

Dataflow Tab:There is no ODBC Source option in the Toolbox

I need to extract data from tables in a database that I can only access via ODBC.

I have successfully created a connection in Connection Manager (ConnectionManagerType = ODBC) for this database.

However I’m unable to add this connection as a Data Flow Source. There is no ODBC Source option in the Toolbox.

This is a major because we have been using the system dsn Microsoft Visual Foxpro Driver to access free table directory .dbf files under ODBC with DTS for years. To install a new Microsoft OLEDB driver for foxpro is out of the question on a production system as it would cost many thousands of dollars to go through our BAT testing process

How do I extract data from tables in a database via ODBC?

Thanks in advance

Dave

There is too, though it's not marked as such. Use the Data Reader Source.|||

You can use the script component as source.

|||

Thanks for that the data reader souce can actually use a ODBC source. I have it working fine.

I do think there is a peformance overhead and its slower than the ODBC connector in DTS.

|||

well that is interesting How can it connect to a foxpro file .dbf and matching .fpt file the .fpt files are used for memo text fieldsthe foxprpro ODBC driver does this for you behind the scenes

|||

Vijay Thirugnanam wrote:

You can use the script component as source.

True, though it shouldn't be faster than using the prepackaged source connectors.

DataConversion Problem

SQL Server 2000, Win 2000 Access 97
I have an Access FrontEnd and BackEnd
The backend tables have been imported (Data Transformation Services) into ab SQL Server Database.
Much of the data was not of the type required - e.g. nVarChar needed to be VarChar
These transformations were carried out using SQL EnterpriseManager > DesignTable
The Access Front End worked Fine.
PROBLEM
For security reasons one tables, Companies, was dropped.
Now after re-importing the table (Data Transformation Services)
and running a script (SQL Query Analyser)
[ example line of script > ALTER TABLE dbo.Companies Alter Column CompanyName VarChar(50) ]
This reports success.
The datatypes are now reported to be as required (SQL EnterpriseManager > Design Table)
Inspecting the data in the ACCESS 97 front end is fine
BUT - attempts to update the data give an ERROR
The import of the error is that the ACCESS front end is trying to update a text field BUT the server is still telling the access Front end that the datatype is nVarChar [NOT VarChar - as currently reported by SQL Enterprise Manager]
NOTE - the Access Front End first deletes ALL the tableDefs then recreates them with an ODBC connection to the Server
All the other tables (apart from Companies) are still updateable as they were before.
Jim Bunton
Jim
Do you have a last Service Pack installed on the SQL Server?
Looks strange. I just did some testing and it works just fine
Try to create a table first and then run DTS to update the table.
"Jim Bunton" <jBunton@.BlueYonder.co.uk> wrote in message news:KWCif.17904$8G6.12386@.fe1.news.blueyonder.co. uk...
SQL Server 2000, Win 2000 Access 97
I have an Access FrontEnd and BackEnd
The backend tables have been imported (Data Transformation Services) into ab SQL Server Database.
Much of the data was not of the type required - e.g. nVarChar needed to be VarChar
These transformations were carried out using SQL EnterpriseManager > DesignTable
The Access Front End worked Fine.
PROBLEM
For security reasons one tables, Companies, was dropped.
Now after re-importing the table (Data Transformation Services)
and running a script (SQL Query Analyser)
[ example line of script > ALTER TABLE dbo.Companies Alter Column CompanyName VarChar(50) ]
This reports success.
The datatypes are now reported to be as required (SQL EnterpriseManager > Design Table)
Inspecting the data in the ACCESS 97 front end is fine
BUT - attempts to update the data give an ERROR
The import of the error is that the ACCESS front end is trying to update a text field BUT the server is still telling the access Front end that the datatype is nVarChar [NOT VarChar - as currently reported by SQL Enterprise Manager]
NOTE - the Access Front End first deletes ALL the tableDefs then recreates them with an ODBC connection to the Server
All the other tables (apart from Companies) are still updateable as they were before.
Jim Bunton
|||Thanks for the reply Uri
DownLoaded Service Pack 3
Installed - works
DownLoaded Service Pack 3a (sql2kasp3.exe)
Setup.exe > begins to run - stops on ERROR 145 an error occurred in the move data process
? Help ? !! Have done a reboot. still no joy
"Uri Dimant" <urid@.iscar.co.il> wrote in message news:eeBjvvB9FHA.1032@.TK2MSFTNGP11.phx.gbl...
Jim
Do you have a last Service Pack installed on the SQL Server?
Looks strange. I just did some testing and it works just fine
Try to create a table first and then run DTS to update the table.
"Jim Bunton" <jBunton@.BlueYonder.co.uk> wrote in message news:KWCif.17904$8G6.12386@.fe1.news.blueyonder.co. uk...
SQL Server 2000, Win 2000 Access 97
I have an Access FrontEnd and BackEnd
The backend tables have been imported (Data Transformation Services) into ab SQL Server Database.
Much of the data was not of the type required - e.g. nVarChar needed to be VarChar
These transformations were carried out using SQL EnterpriseManager > DesignTable
The Access Front End worked Fine.
PROBLEM
For security reasons one tables, Companies, was dropped.
Now after re-importing the table (Data Transformation Services)
and running a script (SQL Query Analyser)
[ example line of script > ALTER TABLE dbo.Companies Alter Column CompanyName VarChar(50) ]
This reports success.
The datatypes are now reported to be as required (SQL EnterpriseManager > Design Table)
Inspecting the data in the ACCESS 97 front end is fine
BUT - attempts to update the data give an ERROR
The import of the error is that the ACCESS front end is trying to update a text field BUT the server is still telling the access Front end that the datatype is nVarChar [NOT VarChar - as currently reported by SQL Enterprise Manager]
NOTE - the Access Front End first deletes ALL the tableDefs then recreates them with an ODBC connection to the Server
All the other tables (apart from Companies) are still updateable as they were before.
Jim Bunton
|||Creating table first then loading data works fine - ca now edit data
[update to SP 4 - to SP 3 Ok but
SP 3a setup.exe > will not instal - stops wth err 145 'an error occurred in the move data process']
"Uri Dimant" <urid@.iscar.co.il> wrote in message news:eeBjvvB9FHA.1032@.TK2MSFTNGP11.phx.gbl...
Jim
Do you have a last Service Pack installed on the SQL Server?
Looks strange. I just did some testing and it works just fine
Try to create a table first and then run DTS to update the table.
"Jim Bunton" <jBunton@.BlueYonder.co.uk> wrote in message news:KWCif.17904$8G6.12386@.fe1.news.blueyonder.co. uk...
SQL Server 2000, Win 2000 Access 97
I have an Access FrontEnd and BackEnd
The backend tables have been imported (Data Transformation Services) into ab SQL Server Database.
Much of the data was not of the type required - e.g. nVarChar needed to be VarChar
These transformations were carried out using SQL EnterpriseManager > DesignTable
The Access Front End worked Fine.
PROBLEM
For security reasons one tables, Companies, was dropped.
Now after re-importing the table (Data Transformation Services)
and running a script (SQL Query Analyser)
[ example line of script > ALTER TABLE dbo.Companies Alter Column CompanyName VarChar(50) ]
This reports success.
The datatypes are now reported to be as required (SQL EnterpriseManager > Design Table)
Inspecting the data in the ACCESS 97 front end is fine
BUT - attempts to update the data give an ERROR
The import of the error is that the ACCESS front end is trying to update a text field BUT the server is still telling the access Front end that the datatype is nVarChar [NOT VarChar - as currently reported by SQL Enterprise Manager]
NOTE - the Access Front End first deletes ALL the tableDefs then recreates them with an ODBC connection to the Server
All the other tables (apart from Companies) are still updateable as they were before.
Jim Bunton

DataConversion Problem

SQL Server 2000, Win 2000 Access 97
I have an Access FrontEnd and BackEnd
The backend tables have been imported (Data Transformation Services) into ab
SQL Server Database.
Much of the data was not of the type required - e.g. nVarChar needed to be V
arChar
These transformations were carried out using SQL EnterpriseManager > DesignT
able
The Access Front End worked Fine.
PROBLEM
--
For security reasons one tables, Companies, was dropped.
Now after re-importing the table (Data Transformation Services)
and running a script (SQL Query Analyser)
[ example line of script > ALTER TABLE dbo.Companies Alter Column Compa
nyName VarChar(50) ]
This reports success.
The datatypes are now reported to be as required (SQL EnterpriseManager > De
sign Table)
Inspecting the data in the ACCESS 97 front end is fine
BUT - attempts to update the data give an ERROR
The import of the error is that the ACCESS front end is trying to update a t
ext field BUT the server is still telling the access Front end that the data
type is nVarChar [NOT VarChar - as currently reported by SQL Enterprise
Manager]
NOTE - the Access Front End first deletes ALL the tableDefs then recreates t
hem with an ODBC connection to the Server
All the other tables (apart from Companies) are still updateable as they wer
e before.
Jim BuntonJim
Do you have a last Service Pack installed on the SQL Server?
Looks strange. I just did some testing and it works just fine
Try to create a table first and then run DTS to update the table.
"Jim Bunton" <jBunton@.BlueYonder.co.uk> wrote in message news:KWCif.17904$8G
6.12386@.fe1.news.blueyonder.co.uk...
SQL Server 2000, Win 2000 Access 97
I have an Access FrontEnd and BackEnd
The backend tables have been imported (Data Transformation Services) into ab
SQL Server Database.
Much of the data was not of the type required - e.g. nVarChar needed to be V
arChar
These transformations were carried out using SQL EnterpriseManager > DesignT
able
The Access Front End worked Fine.
PROBLEM
--
For security reasons one tables, Companies, was dropped.
Now after re-importing the table (Data Transformation Services)
and running a script (SQL Query Analyser)
[ example line of script > ALTER TABLE dbo.Companies Alter Column Compa
nyName VarChar(50) ]
This reports success.
The datatypes are now reported to be as required (SQL EnterpriseManager > De
sign Table)
Inspecting the data in the ACCESS 97 front end is fine
BUT - attempts to update the data give an ERROR
The import of the error is that the ACCESS front end is trying to update a t
ext field BUT the server is still telling the access Front end that the data
type is nVarChar [NOT VarChar - as currently reported by SQL Enterprise
Manager]
NOTE - the Access Front End first deletes ALL the tableDefs then recreates t
hem with an ODBC connection to the Server
All the other tables (apart from Companies) are still updateable as they wer
e before.
Jim Bunton|||Thanks for the reply Uri
DownLoaded Service Pack 3
Installed - works
DownLoaded Service Pack 3a (sql2kasp3.exe)
Setup.exe > begins to run - stops on ERROR 145 an error occurred in the move
data process
? Help ? !! Have done a reboot. still no joy
"Uri Dimant" <urid@.iscar.co.il> wrote in message news:eeBjvvB9FHA.1032@.TK2MS
FTNGP11.phx.gbl...
Jim
Do you have a last Service Pack installed on the SQL Server?
Looks strange. I just did some testing and it works just fine
Try to create a table first and then run DTS to update the table.
"Jim Bunton" <jBunton@.BlueYonder.co.uk> wrote in message news:KWCif.17904$8G
6.12386@.fe1.news.blueyonder.co.uk...
SQL Server 2000, Win 2000 Access 97
I have an Access FrontEnd and BackEnd
The backend tables have been imported (Data Transformation Services) into ab
SQL Server Database.
Much of the data was not of the type required - e.g. nVarChar needed to be V
arChar
These transformations were carried out using SQL EnterpriseManager > DesignT
able
The Access Front End worked Fine.
PROBLEM
--
For security reasons one tables, Companies, was dropped.
Now after re-importing the table (Data Transformation Services)
and running a script (SQL Query Analyser)
[ example line of script > ALTER TABLE dbo.Companies Alter Column Compa
nyName VarChar(50) ]
This reports success.
The datatypes are now reported to be as required (SQL EnterpriseManager > De
sign Table)
Inspecting the data in the ACCESS 97 front end is fine
BUT - attempts to update the data give an ERROR
The import of the error is that the ACCESS front end is trying to update a t
ext field BUT the server is still telling the access Front end that the data
type is nVarChar [NOT VarChar - as currently reported by SQL Enterprise
Manager]
NOTE - the Access Front End first deletes ALL the tableDefs then recreates t
hem with an ODBC connection to the Server
All the other tables (apart from Companies) are still updateable as they wer
e before.
Jim Bunton|||Creating table first then loading data works fine - ca now edit data
[update to SP 4 - to SP 3 Ok but
SP 3a setup.exe > will not instal - stops wth err 145 'an error occurred in
the move data process']
"Uri Dimant" <urid@.iscar.co.il> wrote in message news:eeBjvvB9FHA.1032@.TK2MS
FTNGP11.phx.gbl...
Jim
Do you have a last Service Pack installed on the SQL Server?
Looks strange. I just did some testing and it works just fine
Try to create a table first and then run DTS to update the table.
"Jim Bunton" <jBunton@.BlueYonder.co.uk> wrote in message news:KWCif.17904$8G
6.12386@.fe1.news.blueyonder.co.uk...
SQL Server 2000, Win 2000 Access 97
I have an Access FrontEnd and BackEnd
The backend tables have been imported (Data Transformation Services) into ab
SQL Server Database.
Much of the data was not of the type required - e.g. nVarChar needed to be V
arChar
These transformations were carried out using SQL EnterpriseManager > DesignT
able
The Access Front End worked Fine.
PROBLEM
--
For security reasons one tables, Companies, was dropped.
Now after re-importing the table (Data Transformation Services)
and running a script (SQL Query Analyser)
[ example line of script > ALTER TABLE dbo.Companies Alter Column Compa
nyName VarChar(50) ]
This reports success.
The datatypes are now reported to be as required (SQL EnterpriseManager > De
sign Table)
Inspecting the data in the ACCESS 97 front end is fine
BUT - attempts to update the data give an ERROR
The import of the error is that the ACCESS front end is trying to update a t
ext field BUT the server is still telling the access Front end that the data
type is nVarChar [NOT VarChar - as currently reported by SQL Enterprise
Manager]
NOTE - the Access Front End first deletes ALL the tableDefs then recreates t
hem with an ODBC connection to the Server
All the other tables (apart from Companies) are still updateable as they wer
e before.
Jim Bunton

DataBindings

Hello

I need to link two tables,

one has a primary key (Membership) and one doesn't (results),

results doesn't have any primary key values, I have added a membership number column can I bind the results table column membership number to the values in the membership table?

If so how?

C

You need a FOREIGN KEY constraint between the 2 tables. Just set a FOREIGN KEY constraint on the membership number column of 'results' talbe which references the Primary Key column of Membership table. You can use such T-SQL statement:

ALTER TABLE Membership ADD CONSTRAINT
FK_MemShip_Num FOREIGN KEY
(
[membership number]
) REFERENCES dbo.titlex
(
[membership number]
)

For more information, please refers to following article:

http://msdn.microsoft.com/library/en-us/createdb/cm_8_des_04_8ypg.asp?frame=true

Databases

Hi i am trying to connect two databases together in sql server 2000, how would i go about doing this, as i would like two tables form different databases reading information to carry outs statements

Cheers if anyone can help

Quote:

Originally Posted by Taftheman

Hi i am trying to connect two databases together in sql server 2000, how would i go about doing this, as i would like two tables form different databases reading information to carry outs statements

Cheers if anyone can help

I would imagine you would need to use a JOIN. Is this for a query, stored procedure or is a scripting language (ASP) used for web?|||

Quote:

Originally Posted by Taftheman

Hi i am trying to connect two databases together in sql server 2000, how would i go about doing this, as i would like two tables form different databases reading information to carry outs statements

Cheers if anyone can help

Ycan refer to the full name of the table using the database name to start:

From [databasename].[dbo].[tablename]