Looking the best way to storing NOT NULL in table

Question

Post reply

Looking the best way to storing NOT NULL in table

miss.delinda

SSCommitted

Points: 1616
More actions
January 25, 2010 at 12:49 pm

#209458

Consider my table as follow,
declare @tUsrAcct table
(
TrnxID int identity(1,1) not null,
UsrID varchar(30) not null,
FNme varchar(50) not null,
LNme varchar(50) not null,
Remarks varchar(50) not null
)
--My 1st statement as follow,
insert into @tUsrAcct(UsrID,FNme,LNme,Remarks) values('wolverine1','James','Howlet','')
--My 2nd statement as follow,
insert into @tUsrAcct(UsrID,FNme,LNme,Remarks) values('wolverine2','James','Howlet','NA')
I need to know, which T-SQL is the best practice? 1st statement or 2nd statment? Hopefully somebody will give an answer with explanation.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

LutzM SSC Guru Points: 107049 More actions · Answer 1

I don't think there will be a "best practice" answer...

It's more like one for the "it depends" category... But I might be wrong.

I, personally would use the first option since it makes it a little easier to visually identify empty remarks. Using special characters might also force you to take care of several issues (for instance leading blanks, upper/lower/mixed case, "misuse" like SSMS OpenTable and delete a remark in table grid).

But if it's common practice for other tables/apps to identify empty rows with NA I'd use NA as well. So: It depends 🙂

You've already done the major step to not allow NULL values in the first place. But this might start a discussion whether this is best practice or not, as well. 😉

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

My honest opinion...

If a column is optional and it's valid and allowed for there not to be an entry in that column, make it nullable and let null stand for 'not there'

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

miss.delinda SSCommitted Points: 1616 More actions · Answer 3

My reason to make it Remarks is Not Null because it'll allow me to index this column

if me make it this Remarks allow Null, this column can't be indexing

correct me if im wrong

so, me assume 1st statement is ok,

insert into @tUsrAcct(UsrID,FNme,LNme,Remarks) values('wolverine1','James','Howlet','')

and 2nd statement is ok also.

insert into @tUsrAcct(UsrID,FNme,LNme,Remarks) values('wolverine2','James','Howlet','NA')

Alvin Ramard SSC-Forever Points: 41190 More actions · Answer 4

I agree with Gail, and, correct me if I'm wrong, but I do believe SQL 2005 can index nullable columns.

Alvin Ramard
Memphis PASS Chapter[/url]

All my SSC forum answers come with a money back guarantee. If you didn't like the answer then I'll gladly refund what you paid for it.

For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

miss.delinda (1/25/2010)
My reason to make it Remarks is Not Null because it'll allow me to index this column
if me make it this Remarks allow Null, this column can't be indexing

Create table #temp (

TrnxID int identity(1,1) not null,

UsrID varchar(30) not null,

FNme varchar(50) not null,

LNme varchar(50) not null,

Remarks varchar(50) null

)

Create Index idx_Temp_REmarks ON #Temp (Remarks)

Command(s) completed successfully.

I changed to a temp table, because table variables cannot be indexed at all, regardless of data type and nullability.

The only restrictions on an index key is that it cannot exceed 900 bytes or 16 columns and that it cannot contain the LOB data types (varchar(max), etc)

Of course, the usefulness of indexing a remarks column is another question entirely. How often do you search the table only by remarks.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

miss.delinda SSCommitted Points: 1616 More actions · Answer 6

miss.delinda

SSCommitted

Points: 1616

January 26, 2010 at 9:21 am

#1108760

tq for all the idea.