SQL Create Table [Default]

Question

Can I specify DEFAULT to get the exact value of a column?

I am creating a Serial table, with range, some has From and To and some are just singular.

Therefore I have two columns: [Serial_Fr] & [Serial_To]

If I insert Serial_Fr only, I want to populate Serial_To = Serial_Fr.

How do I do that? Or should I use triggers and just set default = '', in doing this I think it will cause a strain when I have millions of serials.

***SQL*** is just the *Structured Query Language* - a language used by many database systems, but not a a database product... many things are vendor-specific - so we really need to know what **database system** (and which version) you're using.... — marc_s, Mar 06 '13 at 06:38
You'd have to use a trigger but you also could just leave it NULL and deal with it when querying the table. Something like `BETWEEN Serial_Fr AND ISNULL(Serial_To, Serial_Fr)` — Lieven Keersmaekers, Mar 06 '13 at 06:51
thats great! but isnt NULL a bad thing in Normalization? or am I interpreting normalization wrong.. — Sid, Mar 06 '13 at 06:53
@Sid - `NULL` definitely isn't a bad thing. Consider a table containing user information where it's perfectly possible to not know the age *(stretching it a bit here but hope you'll get the point:))*. Without `NULL`, you have to *come up with some value* to represent the unknown age. — Lieven Keersmaekers, Mar 06 '13 at 07:01

score 0 · Accepted Answer · edited May 23 '17 at 10:32

0

Create a View which is

CREATE VIEW MyView AS
SELECT 
  Serial_Fr,
  Serial_To =  COALESCE(Serial_To,Serial_Fr)
FROM MyTable

Or this could be done in a Stored Procedure

But what is wrong with leaving some of the entries in Serial_To as NULL ?

Edit

ISNULL is T-sql : COALESCE is 92 standard sql. There is a good discussion of the subtle differences between these functions HERE ON SO

Martin Smith also refers to an article which has this interesting example that illustrates a difference:

DECLARE @test VARCHAR(2)
DECLARE @first VARCHAR(4)
DECLARE @second VARCHAR(4)

SELECT @first = ISNULL(@test, 'test')
SELECT @second = COALESCE(@test, 'test')

PRINT @first
PRINT @second

edited May 23 '17 at 10:32

Community

1
1

answered Mar 06 '13 at 08:13

whytheq

34,466
65
172
267

I did not get this solution...sorry. Sometimes I populate Serial From then Serial To differently. not always the same – Sid Mar 06 '13 at 09:23
ok - just the View section of the solution will work - although I need to amend it slightly – whytheq Mar 06 '13 at 09:30
@Sid did you try creating a View? – whytheq Mar 08 '13 at 07:22
Yes I tried creating a View, but I skipped on this part as to we don't know yet if NULL is acceptable or not. thanks for this. COALESCE is the one I am looking for – Sid Mar 12 '13 at 02:19

SQL Create Table [Default]

1 Answers1