Geek asks and answers

SQL Server 2012 and various data types stored in one place

Well, I am not a database architect, so this question completely comes from the point of view of software developers. I'm currently working on getting a simple settings database and wondered what is the most efficient and effective way to store various types on an SQL server. For example, SettingA may be boolean, int, float, or string. What is the most efficient way to store this data and the most efficient to retrieve.

  • Store all types in one field, which is a string, and the application server will convert it to the correct value.

  • They have different fields for different types and the application server sees which field is not null and uses it (BooleanValue, StringValue, IntValue, FloatValue and has only one value, non-zero and with a value)

  • Normalize settings in different tables based on types and only add a value to the table if this type is used. (Four tables BooleanValues, StringValues, IntValues, FloatValues ​​with a foreign key in the settings table). Then SP will return any table that has a record.

Performance is definitely my No. 1 problem, but space will be the second second.

+5
source share
4 answers

Save the settings in the XML column.

XML, . , . .

, XML / XQuery.

SQLFiddle, , : http://sqlfiddle.com/#!6/49c34/1

, XML- , , XML-:

XML- XML . , , , SQL Server. , Microsoft Research SQL Server. XML , .

, , , , . , , , .

, XML .

:

  • . , , , , , .
  • , . , , .
  • , , . , .
+1

, .

, , - LOB ( -), , , , -, , NF, , NF .

[SittingA], 4 , , , , , t - additioinal, , , .

Varchar, , hash varchar, (-). - .

.

, , .

+1

№2:
- Isnull, . , , , .

/*
        Create  Table #test 
                (
                    tID         Int Identity, 
                    StringVal   Varchar(100), 
                    FloatVal    Float, 
                    BoolVal     Bit, 
                    IntVal      Int
                )
*/

Declare @bah Varchar(100)

Set     @bah = 'ValuesToTest'

If      IsNumeric(@bah) = 0
Begin
        If      @bah In ('true','false') --Went with this assumption otherwise you'd get confused between 1 bool and 1 int
        Begin
                Insert  #test (BoolVal)
                Select  Case
                        When    @bah = 'true' Then 1
                        Else    0
                        End
        End
        Else
        Begin
                Insert  #test (StringVal)
                Select  @bah
        End
End
Else    If Floor(Convert(Money,@bah)) <> Ceiling(Convert(Money,@bah)) --Compensate for IsNumeric interpretation of money
Begin
        Insert  #test (FloatVal)
        Select  Convert(Float,Convert(Money,@bah))
End
Else
Begin
        Insert  #test (IntVal)
        Select  Convert(Int,@bah)
End

1 , , 1 , 4 int, 4 float 2 varchar ( ), , , 15 .

int, (15 ) , , 4 , 25 , varchar , . , varchar, 12 ( 2 1 ). , 1 000 000 11,5 .

, , , , , int float varchar, , , , , , , Null, . , varchar, .

+1

, ( > 1 ) , 2. : , , , . ( , , 4 ) .

. (, .) INT , .

0

More articles:


All Articles