|
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.
|
|
|
|
|
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 DummyResults in:
autoinc columnA
------- -----------
1 1
2 1
3 1
4 1
|
|
|
|
|
>> 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]
|
|
|
|
|
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]
|
|
|
|
|
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
|
|
|
|
|
Pete, thanks, this seems to be one of the options. Or I can recast the "SQL byte" to "DataSet Int16", set the autoincrement to True in the DataSet and then check programatically whether the autoincremented value in my Windows form is within the boundary of "byte".
Thanks,
Michal
|
|
|
|
|
Hi all,
Iam new to this forumand also to MySQL
I want pass a variable to mysql_query in C. How can I do it?
eg.,
char * sql_insert ;<br />
char * name = "codeproject";<br />
sprintf( sql_insert, "INSERT INTO t1 ( col1 ) VALUES (%s)", name );<br />
<br />
(mysql_query (conn, sql_insert);
If I do this I get segmentation fault?
or how should a variable be passed?
Can anyone help me, please?
Thanks in advance.
|
|
|
|
|
Where does the fault occur?
here -> "sprintf( sql_insert, "INSERT INTO t1 ..."
or
here -> "(mysql_query (conn, sql..."
To me I think the fault should happen at the sprintf call as there is no memory allocated for sprintf to put your string into.
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]
|
|
|
|
|
Thanks.
But how do I do?
How to allocate memory for sprintf?
|
|
|
|
|
We have a query containing some select statements.How the result is returned in DataSet when we call dataAdapter.Fill method.
queries :
----------------------------
SELECT * FROM Table1
SELECT * FROM Table2
SELECT * FROM Table3
----------------------------
|
|
|
|
|
Do all 3 tables have the same fields? If they don't, then it cannot be done.
Some people have a memory and an attention span, you should try them out one day. - Jeremy Falcon
|
|
|
|
|
Doesn't it make a seperate table for each select query. What I want is to have all three results in the dataset placed in a seperate table with just one roundtrip to the server. How can I do it ?
|
|
|
|
|
Is there any kind of relationship between the three tables? It would be helpful to provide some specifications of what the three tables are.
Some people have a memory and an attention span, you should try them out one day. - Jeremy Falcon
|
|
|
|
|
No relationship exists between them.
|
|
|
|
|
devboycpp wrote: No relationship exists between them.
You are stuck fetching one at a time then. You cannot intermix several different tables into one data set.
Some people have a memory and an attention span, you should try them out one day. - Jeremy Falcon
|
|
|
|
|
Hi,
Use a stored procedure. Once you call the stored procedure, you will get result of all three SQL statements, irrespective of any relation between the tables. Use a data adapter to fill a data set. This will create a seperate table for each sql query fired through the stored procedure.
It is as simple as it sounds to be. Hope i am clear enough.
"A good programmer is someone who looks both ways before crossing a one-way street." -- Doug Linder
coolestCoder
|
|
|
|
|
How can I access those tables. What will their names be ???
dataSet.Tables["Table1"];??? will this work ??
How can I merge the three tables in one table ,assuming they have columns of the same number and datatype but with different names ????
|
|
|
|
|
devboycpp wrote: dataSet.Tables["Table1"];??? will this work ??
You can use -
dataSet.Tables[0] for first table.
dataSet.Tables[1] for the second and so on....
devboycpp wrote: How can I merge the three tables in one table ,assuming they have columns of the same number and datatype but with different names ????
You can use a union clause in the stored procedure instead of merging them in the dataset. I think it would be a better choice !
"A good programmer is someone who looks both ways before crossing a one-way street." -- Doug Linder
coolestCoder
|
|
|
|
|
coolestCoder wrote: "A good programmer is someone who looks both ways before crossing a one-way street." -- Doug Linder
I have seen that one before and it is a good one. Nice to see it again 
|
|
|
|
|
PaulC1972 wrote: have seen that one before and it is a good one. Nice to see it again
Thanks !
"A good programmer is someone who looks both ways before crossing a one-way street." -- Doug Linder
coolestCoder
|
|
|
|
|
coolestCoder wrote: Use a stored procedure. Once you call the stored procedure, you will get result of all three SQL statements, irrespective of any relation between the tables. Use a data adapter to fill a data set. This will create a seperate table for each sql query fired through the stored procedure.
It is as simple as it sounds to be.
Cool. I learned something new today Always thought only one table could be in a data set. Makes sense 
Some people have a memory and an attention span, you should try them out one day. - Jeremy Falcon
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
