Jump to content


Check out our Community Blogs

Register and join over 40,000 other developers!


Recent Status Updates

View All Updates

Photo
- - - - -

Mssql Datatypes

mssql

  • Please log in to reply
15 replies to this topic

#1 Tonchi

Tonchi

    Helping the world with programming

  • Expert Member
  • PipPipPipPipPipPipPip
  • 1249 posts
  • Location:Zagreb
  • Programming Language:C#, Others
  • Learning:C, C++, Python, JavaScript, Transact-SQL, Assembly

Posted 21 June 2012 - 05:19 PM

I'm asking MSSql questions here since I'm using C# for my project. There's a lot of datatypes for some string but I'm interested only in few of them. What datatypes and their length I should use for Name, Surname, E-mail and Comments on something. I was googling it but every site gave me different answer, so I am confused.
  • 0

Microsoft Student Partner, Microsoft Certified Professional


#2 kernelcoder

kernelcoder

    CC Devotee

  • Expert Member
  • PipPipPipPipPipPip
  • 990 posts
  • Location:Dhaka
  • Programming Language:C, Java, C++, C#, Visual Basic .NET
  • Learning:Objective-C, PHP, Python, Delphi/Object Pascal

Posted 21 June 2012 - 07:06 PM

I think you should have posted this topic in database programming section.

If you are sure about or if you will give restriction to your users to input a maximum number of characters, you can use nvarchar(n) where n is the maximum number of characters you want to your users be able to input. Same for comments.

But if you don't want to apply restrictions, you can use nvarchar(max) type, however it has also an storage limitation of 2 GB!

Note, nvarchar is to support unicode. If you are sure you are not going to support unicode or your user will not input unicode, you can use varchar type instead of nvarchar.

For comments column, you can also think about text/ntext.
  • 0

#3 Tonchi

Tonchi

    Helping the world with programming

  • Expert Member
  • PipPipPipPipPipPipPip
  • 1249 posts
  • Location:Zagreb
  • Programming Language:C#, Others
  • Learning:C, C++, Python, JavaScript, Transact-SQL, Assembly

Posted 22 June 2012 - 06:52 AM

Yes, unicode support is what I need because of Croatian alphabet. But what I want to know is from your experience what should be maximum length of nvarchar for name, surname and e-mail?
  • 0

Microsoft Student Partner, Microsoft Certified Professional


#4 kernelcoder

kernelcoder

    CC Devotee

  • Expert Member
  • PipPipPipPipPipPip
  • 990 posts
  • Location:Dhaka
  • Programming Language:C, Java, C++, C#, Visual Basic .NET
  • Learning:Objective-C, PHP, Python, Delphi/Object Pascal

Posted 22 June 2012 - 07:25 AM

Sorry to say that I'm not an expert with MSSQL. But I as I said, you can set a constraints about the maximum length of name/surname/email on the UI where user will input those fields and you can set that maximum size to the database too. This post may come in help for you. Take a look into these two too -- post1 and post2.
  • 1

#5 Tonchi

Tonchi

    Helping the world with programming

  • Expert Member
  • PipPipPipPipPipPipPip
  • 1249 posts
  • Location:Zagreb
  • Programming Language:C#, Others
  • Learning:C, C++, Python, JavaScript, Transact-SQL, Assembly

Posted 22 June 2012 - 11:45 AM

ok you helped me for e-mail, but what about name field and surname field?
  • 0

Microsoft Student Partner, Microsoft Certified Professional


#6 papabear

papabear

    CC Devotee

  • Senior Member
  • PipPipPipPipPipPip
  • 472 posts
  • Location:DarkSide

Posted 22 June 2012 - 02:23 PM

ok you helped me for e-mail, but what about name field and surname field?


the name and surname field depends on you, in my case I'm putting a 30 or 50 length into a name and a 30-50 length also in surname because there are some names that is so long... but it's good to limit the characters allowed in a given field to save some database memory
  • 0
Life has no CTRL+Z
Never Forget To HIT "LIKE" If I Helped

#7 Tonchi

Tonchi

    Helping the world with programming

  • Expert Member
  • PipPipPipPipPipPipPip
  • 1249 posts
  • Location:Zagreb
  • Programming Language:C#, Others
  • Learning:C, C++, Python, JavaScript, Transact-SQL, Assembly

Posted 22 June 2012 - 03:18 PM

ok thank you very much
  • 0

Microsoft Student Partner, Microsoft Certified Professional


#8 Luthfi

Luthfi

    CC Leader

  • Expert Member
  • PipPipPipPipPipPipPip
  • 1320 posts
  • Programming Language:PHP, Delphi/Object Pascal, Pascal, Transact-SQL
  • Learning:C, Java, PHP

Posted 23 June 2012 - 12:12 AM

For variable length of chars, actually there is not much difference of performance or space saving between max length of 20 (for example) with 50 (the usual default max length). Unless there is another business rule you have to follow in other area, you can safely use 50 for max chars length for names.
  • 0

#9 Tonchi

Tonchi

    Helping the world with programming

  • Expert Member
  • PipPipPipPipPipPipPip
  • 1249 posts
  • Location:Zagreb
  • Programming Language:C#, Others
  • Learning:C, C++, Python, JavaScript, Transact-SQL, Assembly

Posted 23 June 2012 - 08:22 AM

just another theory question. I'm working with MDF file that I have created in Visual Studio, not in MSSql Server 2008. I'm wondering, if I want to store older datas from one Data Table, what should I do? I mean, there can be a 2 records that contain the same Name, but new record contains different values in the same Data Table, how can I show all datas from that person and how can I show the newest data from that person? Can I do it from primary key from that table or there is other method?
  • 0

Microsoft Student Partner, Microsoft Certified Professional


#10 kernelcoder

kernelcoder

    CC Devotee

  • Expert Member
  • PipPipPipPipPipPip
  • 990 posts
  • Location:Dhaka
  • Programming Language:C, Java, C++, C#, Visual Basic .NET
  • Learning:Objective-C, PHP, Python, Delphi/Object Pascal

Posted 23 June 2012 - 08:38 AM

Tonchi, I'm not sure about your question. Can you please explain a bit with example?
  • 0

#11 Tonchi

Tonchi

    Helping the world with programming

  • Expert Member
  • PipPipPipPipPipPipPip
  • 1249 posts
  • Location:Zagreb
  • Programming Language:C#, Others
  • Learning:C, C++, Python, JavaScript, Transact-SQL, Assembly

Posted 23 June 2012 - 08:46 AM

I was thinking that I didn't explain it well. So...Imagine that you are working in the school as a teacher. Insteaed of stupid grade books you have a small application for managing the grades of each student. Now, during the school year you are entering the grades for students. After the year has ended, you want to see every single grade from every / one student. I have indetical problem, but not for a students :)
Now if I'm using update statement in my database, my previous records would be deleted. Correct me if I am wrong. I don't want of those records to be deleted, I want to see them all in one table in application.
I hope that I have clear that :)
  • 0

Microsoft Student Partner, Microsoft Certified Professional


#12 kernelcoder

kernelcoder

    CC Devotee

  • Expert Member
  • PipPipPipPipPipPip
  • 990 posts
  • Location:Dhaka
  • Programming Language:C, Java, C++, C#, Visual Basic .NET
  • Learning:Objective-C, PHP, Python, Delphi/Object Pascal

Posted 23 June 2012 - 08:59 AM

I was thinking that I didn't explain it well. So...Imagine that you are working in the school as a teacher. Insteaed of stupid grade books you have a small application for managing the grades of each student. Now, during the school year you are entering the grades for students.

I think I understand this part correctly. Now,

After the year has ended, you want to see every single grade from every / one student. I have indetical problem, but not for a students :)

Well, after that year ended I can see the grades of every student by the query 'SELECT * FROM students;' where students is the table name.


Now if I'm using update statement in my database, my previous records would be deleted. Correct me if I am wrong. I don't want of those records to be deleted, I want to see them all in one table in application.

WIth the SQL UPDATE query, you are about to update the records that you want to intend to with the conditions in the UPDATE query (please see example here). What do you mean by the previous records will be deleted?


Sorry, I'm still not sure about your goal.
  • 0





Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download