About the same type of large amount of data stored in more than one form questions.

Question

I want to save more is the current line of data, I know that a table column can store 1024 document (s).

   

When the data is stored more than 1024 lines, the database will own the expansion of the same type of table to save it?

  

But when my data is stored more than 1024 row, I have to own to create a form?

Or the database will automatically extend another form to help us out, we do not have to manually add form?

  

This is the first problem. 

  

Another problem is that when I query the same data column.

   

When the storage of more than 1024 columns, you need to go to another table to continue the same line of data storage.

  

I estimate that such a large amount of summary data stored 30000, probably 30 to form a deposit.

  

When I do a query using a stored procedure, I was not trying to use CASE or IF this T-SQL scripts.

  

In 30 I want to check out the search form information.

  

Answer 1

Hi,

Unfortunately your questions are not very clear.

If your need to store more than 1024 columns and your columns are mainly nullable - you can use Sparse columns (http://msdn.microsoft.com/en-us/library/ff427239.aspx). This is SQL Server 2008 feature.

If you need to search by any of those columns (or as alternative design) you'd better to "unpivot" the table and do something like:

create table dbo.MyData
(
	DocId int not null,
	ColId int not null,
	
	Value sql_variant,
	
	constraint PK_MyData
	primary key clustered(DocId, ColId)
)
go

create index IDX_MyData_Value
on dbo.MyData(Value)
go

This design has a few issues - the table would grow very fast, index requires intensive maintenance and last but not least you need to be careful with different data types stored in variant column. And of course you need to avoid joins when you "pivot" the data and perhaps do it on the client level..

---

Thank you!

My blog: http://aboutsqlserver.com

Answer 2

Hi

Sorry, I re-issue in my description clearer.

I now want to pretend that the amount of data stored in 30,000, while a form is only 1024.

  

This is clearly not enough, I need at least 30 forms to complete this work.

  

When I first table of this column is set to grow automatically, the database is up to the row

Storage capacity increased to 1024 only it.

  

I now doubt that the database will automatically grow out of the second to the third ten tables to help me save the job?

  

I want to own or to pay attention to the table column is full of 1024, I have to go their own to create a new table into the data.

  

Answer 3

Ok, we need to define the terminology. By "form" - do you mean "table"? SQL Server has physical limitation on the # of columns - (1024 or 30000 sparse columns) as well as physical limitation on the fixed part of the data row size (8060 bytes). So technically you can reach the limit with smaller number of columns than 1024.

No, SQL Server does not automatically create new tables when you exceed 1024 column. Nothing is done automatically.Yes, you can create tables in runtime from (and by) the application assuming you have enough security rights.

In your case I'd suggest you to think about different design. Do you really need to keep 30,000 attributes(columns) as the single row/record? Are there master/detail relations in the data (e.g. one to many relations - like order header and order line items)? Can you use XML to store some of the attributes(columns)? Can you "unpivot" the data?

Each approach has own pros and cons depend on the nature of the system.

---

Thank you!

My blog: http://aboutsqlserver.com

Answer 4

hi,

 

I will talk about my application.

 

I used to make artificial to design auto-reply system.

 

It's basically a design framework is <What did you say?> <I reply to what?>.

 

So I need to create a sample sentence to mass storage to store the table <What did you say?>

 

with <I reply to what?>.

  

So that I basically just added a table of three columns <number of sentences> <What did you say?> <I reply to what?>

 

I study  sparse columns and column sets.

 

Sparse columns can give a table 30000 columns.

 

However, after the addition of sparse columns can not use the index.

 

This does not do the search query will reduce its speed.

  

Feel this is not the best solution.

  

I reply in the design of this artificial system must automatically store a large number of sample sentences.

 

I do not know that you have better suggestions?

Answer 5

Additional point.

 

I have added some of the index is <number of sentences> <What did you say?> these two columns.

 

Sparse columns that if I added <I reply to what?>.

 

This will enable the table has three stores to 30,000 capacity?

 

Answer 6

Terminology again. So you created the table with 3 columns only and asking if this table can store more than 30000 rows/records? Yes, for sure. Only limitation on # of rows you'll have would be with SQL Express edition that limits database size (10Gb with SQL08 R2; 4Gb with previous versions). What exactly do you mean by capacity?

As for sparse columns - those are different. Those are for the case if you have a table with very large (>1024) number of columns AND those columns mainly have NULLs inside.

Your design (3 columns, 1 record/row per sentence) would work. Just don't make them sparse. If you search by <What did you say> create the index on this column and don't include <number of sentences> as the first column

---

Thank you!

My blog: http://aboutsqlserver.com

Answer 7

Thak you! I understand.