Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: SQL database
Hi Team,
 
I have a Table, which is contain number of database field are Empty. I just want to be know about that-
1- Is it make any impact on database performance.
2- It it occupied any Spaces in data base.
Please guide me.
 
Thanks
Ravik
Posted 9-Dec-12 18:52pm
Comments
choudhary.sumit at 10-Dec-12 0:55am
   
if they are empty, what is the reason to be exist of them in table? elaborate your question.
ITSRavik at 10-Dec-12 8:13am
   
Empty field goes in table due to use same query syntax, Suppose I have a query like
Insert into MyTable (MyName, MyAddress, MyPhone)values ('ABC', 'xyz', '1234567890')
it's stored all data, but I have partial data (Name and Address)now I enter this data using same query like-
Empty field goes in table due to use same query syntax, Suppose I have a query like
Insert into MyTable (MyName, MyAddress, MyPhone)values ('ABC', 'xyz', '')
now what happen, Phone no. is saved as a Empty.
So My Question is that-
This empty field occupied any space or make any Impact in database performace.
Please help me.
 
Thanks
Ravik
pradiprenushe at 10-Dec-12 1:22am
   
Good question. check this link
http://stackoverflow.com/questions/3731172/how-much-size-null-value-takes-in-sql-server
http://stackoverflow.com/questions/632332/sql-server-performance-size-drawbacks-of-null-columns
http://www.sql-server-performance.com/2007/datatypes/
http://www.sqlmag.com/article/sql-server-2000/designing-for-performance-null-or-not-null-

1 solution

Rate this: bad
good
Please Sign up or sign in to vote.

Solution 2

While designing a database you must set the size of the each column to avoid any unnecessary allocation of memory and database size while inserting a record.
 
You said some of the columns may have empty values which you cannot avoid because you cannot design a table for each row value, rather you design column wise.
 
for eg: if you have set the MyPhone column with varchar size 30 no matter how much data you stored whether it's one char or 10 char or 30 the space allocation is 30. and if you try to store more than that it will crop your data to 30 char length.
 
so in this case you cannot do anything the better options must be limit the column size to the required length rather setting some arbitrary number. and Yes empty space increases your database size so care must be take while designing your database tables.
  Permalink  
Comments
Jörgen Andersson at 15-Dec-12 15:54pm
   
Var in varchar stands for 'variable', which means they take up as much space as they need up to their size limit. your description would be correct for a char field
jibesh at 15-Dec-12 16:09pm
   
Thanks Andersson...for clearing me I double checked ad what you said is right if one defined varchar the column size will be a compressed one unlike the char variable it reserves the all the space.
 
Updated mo solution!!

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
0 Sergey Alexandrovich Kryukov 420
1 _Amy 235
2 Andreas Gieriet 180
3 Peter Leow 175
4 Dave Kreskowiak 155
0 OriginalGriff 7,540
1 Sergey Alexandrovich Kryukov 6,597
2 Maciej Los 3,849
3 Peter Leow 3,653
4 CHill60 2,712


Advertise | Privacy | Mobile
Web04 | 2.8.140721.1 | Last Updated 14 Dec 2012
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100