Auto incremented primary key only for Int16/32/64?
-
Hello, in Visual Studio 2005, I designed an SQL database for storing large amount of rows (several hundred million rows). It's intended for storing market tick data. With this huge amount of rows, I wanted to make the row design as small as possible datawise, naturally. So I employed several TinyInt (byte) columns instead of int (Int32) or SmallInt (Int16). But the problem is now that VisualStudio doesn't allow the primary keys with the TinyInt data type to be set as "AutoIncrement" in the DataSet. Why this is so I can't tell. How can I work around this? Thank you very much for any help, Michal Kreslik
-
Hello, in Visual Studio 2005, I designed an SQL database for storing large amount of rows (several hundred million rows). It's intended for storing market tick data. With this huge amount of rows, I wanted to make the row design as small as possible datawise, naturally. So I employed several TinyInt (byte) columns instead of int (Int32) or SmallInt (Int16). But the problem is now that VisualStudio doesn't allow the primary keys with the TinyInt data type to be set as "AutoIncrement" in the DataSet. Why this is so I can't tell. How can I work around this? Thank you very much for any help, Michal Kreslik
Hoahh boy. Well, think about how big a TinyInt is, and then think about how big your Autoincrement will get. To work around this (for a DB the size that you have indicated), you can either use a uniqueuidentifier field (set to default with NewID()) or use a long (Int64).
Arthur Dent - "That would explain it. All my life I've had this strange feeling that there's something big and sinister going on in the world." Slartibartfast - "No. That's perfectly normal paranoia. Everybody in the universe gets that." Deja View - the feeling that you've seen this post before.
-
Hoahh boy. Well, think about how big a TinyInt is, and then think about how big your Autoincrement will get. To work around this (for a DB the size that you have indicated), you can either use a uniqueuidentifier field (set to default with NewID()) or use a long (Int64).
Arthur Dent - "That would explain it. All my life I've had this strange feeling that there's something big and sinister going on in the world." Slartibartfast - "No. That's perfectly normal paranoia. Everybody in the universe gets that." Deja View - the feeling that you've seen this post before.
Hello, Pete, seems like you didn't get my question :) Of course, the main table that will store those hundreds of millions of rows does have its own ID primary key column. This ID column is set to Int32 type which gives us an unsigned range of 0 to 4,294,967,295 (2^32 - 1). But I was not talking about indexing those rows. I was talking about indexing other tables that will be linked to the main table. Some of those tables will only have up to 256 unique values in their IDs, so there's really no need for indexing them with Int16/32/64. Instead, I used "byte" to save the space in the main table. But VisualStudio refuses to automatically increment those "byte" indexes in the auxiliary tables. So the only way would be to edit those byte-keyed tables directly within SQL. Which is not a very good idea. Any help would be greatly appreciated. Thanks, Michal Kreslik
-
Hello, Pete, seems like you didn't get my question :) Of course, the main table that will store those hundreds of millions of rows does have its own ID primary key column. This ID column is set to Int32 type which gives us an unsigned range of 0 to 4,294,967,295 (2^32 - 1). But I was not talking about indexing those rows. I was talking about indexing other tables that will be linked to the main table. Some of those tables will only have up to 256 unique values in their IDs, so there's really no need for indexing them with Int16/32/64. Instead, I used "byte" to save the space in the main table. But VisualStudio refuses to automatically increment those "byte" indexes in the auxiliary tables. So the only way would be to edit those byte-keyed tables directly within SQL. Which is not a very good idea. Any help would be greatly appreciated. Thanks, Michal Kreslik
michal.kreslik wrote:
This ID column is set to Int32 type which gives us an unsigned range of 0 to 4,294,967,295 (2^32 - 1).
No, it gives you a signed range of -2^31 to (+2^31)-1
michal.kreslik wrote:
But VisualStudio refuses to automatically increment those "byte" indexes in the auxiliary tables
What does visual studio have to do with it. This is a function of SQL Server and tinyint columns work perfectly well:
CREATE TABLE Dummy
(
autoinc tinyint IDENTITY(1,1) NOT NULL,
columnA int NOT NULL
);
GO
INSERT Dummy(columnA) VALUES(1);
INSERT Dummy(columnA) VALUES(1);
INSERT Dummy(columnA) VALUES(1);
INSERT Dummy(columnA) VALUES(1);SELECT * FROM Dummy
Results in:
autoinc columnA
1 1
2 1
3 1
4 1
*** Developer Day 4 in Reading, England on 2nd December 2006 - Registration Now Open *** Upcoming Scottish Developers events: * Developer Day Scotland: are you interested in speaking or attending? My: Website | Blog | Photos
-
michal.kreslik wrote:
This ID column is set to Int32 type which gives us an unsigned range of 0 to 4,294,967,295 (2^32 - 1).
No, it gives you a signed range of -2^31 to (+2^31)-1
michal.kreslik wrote:
But VisualStudio refuses to automatically increment those "byte" indexes in the auxiliary tables
What does visual studio have to do with it. This is a function of SQL Server and tinyint columns work perfectly well:
CREATE TABLE Dummy
(
autoinc tinyint IDENTITY(1,1) NOT NULL,
columnA int NOT NULL
);
GO
INSERT Dummy(columnA) VALUES(1);
INSERT Dummy(columnA) VALUES(1);
INSERT Dummy(columnA) VALUES(1);
INSERT Dummy(columnA) VALUES(1);SELECT * FROM Dummy
Results in:
autoinc columnA
1 1
2 1
3 1
4 1
*** Developer Day 4 in Reading, England on 2nd December 2006 - Registration Now Open *** Upcoming Scottish Developers events: * Developer Day Scotland: are you interested in speaking or attending? My: Website | Blog | Photos
>> michal.kreslik wrote: >> This ID column is set to Int32 type which gives us an unsigned range of 0 to >> 4,294,967,295 (2^32 - 1). > No, it gives you a signed range of -2^31 to (+2^31)-1 Sure, I forgot "U": UInt32 >> michal.kreslik wrote: >> But VisualStudio refuses to automatically increment those "byte" indexes in the >> auxiliary tables > What does visual studio have to do with it. This is a function of SQL Server and > tinyint columns work perfectly well: I know this works well in SQL itself :) What Visual Studio has to do with it: VisualStudio DataSet doesn't support this functionality! Since I want to work with the tables that have byte data type as their primary key, I need to create a DataSet and use this DataSet with my Windows form. The Visual Studio DataSet doesn't support automatical incrementing of the byte data type primary key. Michal
-
>> michal.kreslik wrote: >> This ID column is set to Int32 type which gives us an unsigned range of 0 to >> 4,294,967,295 (2^32 - 1). > No, it gives you a signed range of -2^31 to (+2^31)-1 Sure, I forgot "U": UInt32 >> michal.kreslik wrote: >> But VisualStudio refuses to automatically increment those "byte" indexes in the >> auxiliary tables > What does visual studio have to do with it. This is a function of SQL Server and > tinyint columns work perfectly well: I know this works well in SQL itself :) What Visual Studio has to do with it: VisualStudio DataSet doesn't support this functionality! Since I want to work with the tables that have byte data type as their primary key, I need to create a DataSet and use this DataSet with my Windows form. The Visual Studio DataSet doesn't support automatical incrementing of the byte data type primary key. Michal
I'm missing something. Since the functionality is there in SQL Server at the level of a table definition, you don't need to have the functionality in Visual Studio.
michal.kreslik wrote:
Visual Studio DataSet doesn't support automatical incrementing of the byte data type primary key
This isn't making sense to me. The database will do the auto incrementing you require. Is there some error message you can provide that may help to understand this better.
Chris Meech I am Canadian. [heard in a local bar] I agree with you that my argument is useless. [Red Stateler] Hey, I am part of a special bread, we are called smart people [Captain See Sharp] The zen of the soapbox is hard to attain...[Jörgen Sigvardsson] I wish I could remember what it was like to only have a short term memory.[David Kentley]
-
I'm missing something. Since the functionality is there in SQL Server at the level of a table definition, you don't need to have the functionality in Visual Studio.
michal.kreslik wrote:
Visual Studio DataSet doesn't support automatical incrementing of the byte data type primary key
This isn't making sense to me. The database will do the auto incrementing you require. Is there some error message you can provide that may help to understand this better.
Chris Meech I am Canadian. [heard in a local bar] I agree with you that my argument is useless. [Red Stateler] Hey, I am part of a special bread, we are called smart people [Captain See Sharp] The zen of the soapbox is hard to attain...[Jörgen Sigvardsson] I wish I could remember what it was like to only have a short term memory.[David Kentley]
Obviously, the functionality is in the the SQL Server. It would be beneficial to have this functionality in Visual Studio, too. There's no error message. The Visual Studio simply turns the auto-incrementing functionality off. If you attempt to add a new row in your DataSet-aware Windows form, it simply doesn't update the ID column. Have a look at the screenshot: http://kreslik.com/forums/viewtopic.php?t=357 Michal
-
Obviously, the functionality is in the the SQL Server. It would be beneficial to have this functionality in Visual Studio, too. There's no error message. The Visual Studio simply turns the auto-incrementing functionality off. If you attempt to add a new row in your DataSet-aware Windows form, it simply doesn't update the ID column. Have a look at the screenshot: http://kreslik.com/forums/viewtopic.php?t=357 Michal
Just so I'm clear, does the "DataSet-aware Windows form" update the ID field when the field is of Int16/32/64?
Chris Meech I am Canadian. [heard in a local bar] I agree with you that my argument is useless. [Red Stateler] Hey, I am part of a special bread, we are called smart people [Captain See Sharp] The zen of the soapbox is hard to attain...[Jörgen Sigvardsson] I wish I could remember what it was like to only have a short term memory.[David Kentley]
-
Just so I'm clear, does the "DataSet-aware Windows form" update the ID field when the field is of Int16/32/64?
Chris Meech I am Canadian. [heard in a local bar] I agree with you that my argument is useless. [Red Stateler] Hey, I am part of a special bread, we are called smart people [Captain See Sharp] The zen of the soapbox is hard to attain...[Jörgen Sigvardsson] I wish I could remember what it was like to only have a short term memory.[David Kentley]
Yes. With Int16/32/64 the field is updated automatically. With byte, it's not. The DataSet doesn't even allow the byte field to be set to "AutoIncrement = true". Michal