All geek questions in one place

N / A storage on Sql server

We have a form that has several fields of various data types, including string, date and time.

Our client wants to be able to write N / A (not applicable) in any of these fields, as well as leave it blank.

Does anyone have an idea on how to create a Sql Server table so that it can support NULL or N / A?

We are using Sql Server 2008.

+4
source share
6 answers

If N / A and empty are really different values ​​that you need to support, I would say to create the field as a field with a zero value (in this case, NULL will represent an empty value) and then turn on the second bit field for [FieldName] NotAvailable or [FieldName].

Of course, you would use two separate fields to represent a single logical concept, trying to make it all in one field, to force you to keep two concepts in the same field (the field is interpreted in one direction, unless there is any magic value, then this means something else). Thus, with a separate field, it is much more clear what the expected behavior of the fields is.

+3
source

If they want only one state, you can, as other people point out, simply translate null:

SELECT COALESCE(thecolumn1,'N/A') AS mycolumnalias

IF it is found that they really want to have a “three-state” in the column, you can create a VARCHAR column for the companion.

 SELECT COALESCE(thecolumn1, companioncolumn1,'N/A') AS mycolumnalias

Benefits:

  • If they change their minds again, you can change the value of companioncolumn1.
  • If you use both columns in COALESCE, it automatically gets the first column when it is not null, gets the value of the companion object if it is set, and the first is null, and has a backup of "N / A" specified in select.
  • You can manage it at the database level.
  • You save the data type of the source column

Disadvantages:

  • Has an additional column in the database for management.
  • Somewhat more complex SELECT statements
  • You need to manage this at the database level.
  • Listing and type validation will be more complex, but you already have this problem.
+1
source

The only type I could think of was varchar.

You will need to have code to interpret the data based on the field (i.e. if the field name is InvoiceDate, this is the date).

EDIT: after reading your last comment, you can get the “IsNA” field (bit) for each field. So, if the user selects "NA" for InvoiceDate, set "IsInvoiceDateNA" to true and false (otherwise).

The screen view will be controlled by field, but for each input field.

0
source

I would save the answer in a single varchar (or nvarchar ) field.

Application code can check and interpret input as a number or date, etc. Field meta information tells you how to interpret it.

In this case, you can have NULL , or empty, or N / A, or a real value ...

0
source

As mentioned, varchar or nvarchar is your best solution.

0
source

Regardless of the data type of the column, a default null value can be used. When you return the results, you can use

 Select Isnull(colX,'N/A')

or Select Coalesce(collX,'N/A')

0
source

Source: https://habr.com/ru/post/1301861/

More articles:


All Articles