Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: SQLdatabase
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
Your Filters
Interested
Ignored
     
0 Sergey Alexandrovich Kryukov 437
1 sanket saxena 355
2 thatraja 240
3 Abhinav S 216
4 CPallini 215
0 Sergey Alexandrovich Kryukov 8,809
1 OriginalGriff 5,025
2 Peter Leow 3,839
3 Maciej Los 3,535
4 Abhinav S 3,118


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