I must create an SQL table with 90+ fields, the majority of them are bit fields like N01, N02, N03 ... N89, N90 is there a fast way of creating multiple fileds or is it possible to have one single field to contain an array of values true/false? I need a solution that can also easily be queried.
6
-
In the dbms world it's columns, not fields...jarlh– jarlh2015-08-18 12:39:54 +00:00Commented Aug 18, 2015 at 12:39
-
4Having multiple columns with the same name, appended with numbers, is generally a sign of a poor table design. You should consider a junction table instead.Gordon Linoff– Gordon Linoff2015-08-18 12:42:17 +00:00Commented Aug 18, 2015 at 12:42
-
In T-SQL it's quite simple to build a loop that would create a table Dynamically. Taking into account that all the names of the fields are like provided in the post.Ispirer SQLWays Migrations– Ispirer SQLWays Migrations2015-08-18 12:43:20 +00:00Commented Aug 18, 2015 at 12:43
-
2Dynamically creating tables is also often a flag for a problematic design, hopefully this is a one off ...Alex K.– Alex K.2015-08-18 12:43:28 +00:00Commented Aug 18, 2015 at 12:43
-
elnath78: A table is, by its very nature, an array. Combining 90 some fields into a single one creates more problems than it solves, not to mention the fact that it specifically negates one of the stated objects (i.e. "I need a solution that can also easily be queried"). Of course, it would greatly help if you shared the goal of the project and how you planned on using this particular structure, and why you think it will help achieve said goal.Solomon Rutzky– Solomon Rutzky2015-08-18 12:54:25 +00:00Commented Aug 18, 2015 at 12:54
|
Show 1 more comment
4 Answers
There is no easy way to do this and it will be very challenging to do queries against such a table. Create a table with three columns - item number, bit field number and a value field. Then you will be able to write 'good' succinct Tsql queries against the table.
1 Comment
Solomon Rutzky
Without any more info as to how this table will be used, this is the appropriate solution.
At least you can generate ALTER TABLE scripts for bit fields, and then run those scripts.
DECLARE @COUNTER INT = 1
WHILE @COUNTER < 10
BEGIN
PRINT 'ALTER TABLE table_name ADD N' + RIGHT('00' + CONVERT(NVARCHAR(4), @COUNTER), 2) + ' bit'
SET @COUNTER += 1
END
Comments
TLDR: Use binary arithmetic.
For a structure like this
==============
Table_Original
==============
Id | N01| N02 |...
I would recommend an alternate table structure like this
==============
Table_Alternate
==============
Id | One_Col
This One_Col is of varchar type which will have value set as
cast(n01 as nvarchar(1)) + cast(n02 as nvarchar(1))+ cast(n03 as nvarchar(1)) as One_Col
I however feel that you'd use C# or some other programming language to set value into column. You can also use bit and bit-shift operations.
Whenever you need to get a value, you can use SQL or C# syntax(treating as string) In sql query terms you can use a query like
SELECT SUBSTRING(one_col,@pos,1)
and @pos can be set like
DECLARE @Colname nvarchar(4)
SET @colname=N'N32'
-- ....
SET @pos= CAST(REPLACE(@colname,'N','') as INT)
Also you can use binary arithmetic too with ease in any programming language.
6 Comments
elnath78
using substring in query will not affect performance on tables with a lot of records? Table will have 25.000+ records.
DhruvJoshi
@elnath78 if you need to use all these flags in a programming language, i'd suggest you move all logic to programming language and outside DB. Use DB only for storage as single
varchar(max) column or varbinary(max) columnelnath78
as per my comment in the initial post, I need to store extractions for national lottery, so it has 5 values for 11 "types" and I have 3 sorts each week for 15 years. What I was worried about was using substring in my queries to find a given number and lookup all the sorts where it was extracted. So I supposed to use bit values to quickly filter and narrow the results on multiple numbers without using substrings. Can you confirm that substrings in queries will not slow it down on a large table? we are talking of 25.000+ records to search in.
DhruvJoshi
@elnath78 Compare to SQL
SELECT colname using SUBSTRING to get column data will be slower. How much slow, depends on your data and indexes.DhruvJoshi
@elnath78 Check out this post mssqltips.com/sqlservertip/1218/…
|
Use three columns.
Table
ID NUMBER,
FIELD_NAME VARCHAR2(10),
VALUE NUMBER(1)
Example
ID FIELD VALUE
1 N01 1
1 N02 0
.
1 N90 1
.
2 N01 0
2 N02 1
.
2 N90 1
.
You can also OR an entire column for a fieldname (or fieldnameS):
select DECODE(SUM(VALUE), 0, 0, 1) from table where field_name = 'N01';
And even perform an AND
select EXP(SUM(LN(VALUE))) from table where field_name = 'N01';
(see http://viralpatel.net/blogs/row-data-multiplication-in-oracle/)