Problem with Querry - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Problem with Querry

Author

Topic

samitkumbhani
Starting Member

15 Posts

Posted - 2004-11-10 : 14:37:53

Hi forum,

I have been trying to write a querry wherein I want to order the result by Academic Year like :

2004 - 2005
....
....
2003 - 2004
...
...

But what I am puzzled is what should be type of the field Academic Year in the database because I certainly cant use Text as it does not allow ordering by Text field and the above format is not a valid Date format.

Can someone please help me.

Thanks.

Samit

amachanic
SQL Server MVP

169 Posts

Posted - 2004-11-10 : 15:51:04

Samit,

I would highly recommend against using TEXT for such a short string. TEXT columns take up a lot more overhead than VARCHAR, as each row has a 16-byte pointer to an off-row area, and the off-row area has around 100 bytes of extra storage. So your final size for such a small string will be quite a bit larger than the actual string.

You can store the values directly in your table using a VARCHAR; but it actually might make more sense for you to store the valid ranges in another table with a surrogate key:

CREATE TABLE AcademicYears
( AcademicYearId INT NOT NULL PRIMARY KEY,
AcademicYearValue VARCHAR(20) NOT NULL
)

INSERT AcademicYears VALUES (1, '2002 - 2003')
INSERT AcademicYears VALUES (2, '2003 - 2004')
INSERT AcademicYears VALUES (3, '2004 - 2005')

Then you'll store just the ID in your main table. You can then join to get the value and order by it.

tuenty
Constraint Violating Yak Guru

278 Posts

Posted - 2004-11-10 : 15:55:15

I would only store the first year in an int field and order by that field
In the display you can manipulate it to show your desired format
But if you want to have the academic year in a text field then define it as a char(11) field

*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*

A candle loses nothing by lighting another candle

samitkumbhani
Starting Member

15 Posts

Posted - 2004-11-10 : 15:57:09

Hi,

Thanks for the reply.

I found out another way. Please analyze it and let me know.

I inserted it as text and then used Left function in the querry and retrieved the first 4 value ie ( out of 2004-05 i got 2004) and displayed in the form sorted based on that value.

Thanks,

Samit

amachanic
SQL Server MVP

169 Posts

Posted - 2004-11-10 : 15:59:04

Samit,

Why are you insisting on using the TEXT datatype? Do you understand the differences between CHAR, VARCHAR, and TEXT?

samitkumbhani
Starting Member

15 Posts

Posted - 2004-11-10 : 16:01:28

HI,

sorry i mis represented my self.

Actually that field is of the type Varchar.

Sorry about that.
Should I use Int or Varchar would be fine.

samit

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-11-10 : 16:04:54

Tuenty actually has the best idea, if the hard and fast rule is you always want to "add one" to the year to indicate the range. Or, you might want to have two columns -- startYear, endYear, both ints. As many have learned the hard way here, never store different pieces of data in 1 column -- always break them up as much as possible into multiple columns, you can always put them together and present them any way you want in any given query.

it really depends on what data you are storing and what you are using these values for.

- Jeff

samitkumbhani
Starting Member

15 Posts

Posted - 2004-11-10 : 16:07:49

HI,

Thank You all.

Its working fine now.

Samit

tuenty
Constraint Violating Yak Guru

278 Posts

Posted - 2004-11-10 : 16:16:44

quote:
Originally posted by amachanic

Samit,

...So your final size for such a small string will be quite a bit larger than the actual string.

though amachanic statement is true in general (for example when setting a student's name field),

unless you add month or something else to your academic year format

it's size won't increase in about

7995 years. So I think 11 is just the right size in this particular case if you

insist on having the academic year in a string

*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*

A candle loses nothing by lighting another candle

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-11-10 : 16:17:17

i worked on an app that had a varchar(9) type in format '2004/2005' and it was even a PK.
there was no problem with joins or anything else... and it was named - you wouldn't believe it: SchoolYear

Go with the flow & have fun! Else fight the flow

amachanic
SQL Server MVP

169 Posts

Posted - 2004-11-10 : 16:28:35

Tuenty,

I'm not sure where you get that figure; here's an entry from my blog in which I prove that TEXT columns have gigantic overhead:

http://sqljunkies.com/WebLog/amachanic/archive/2004/10/22/4743.aspx

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-11-10 : 16:32:23

Tuenty -- I think you missed the point or misunderstood the comparison of text versus varchar ....

- Jeff

tuenty
Constraint Violating Yak Guru

278 Posts

Posted - 2004-11-10 : 17:16:54

I agreed with amachanic in the the comparison of text versus varchar

maybe my syntax or grammar was incorrect
what I ment is that since he formated the calendar year as 'yyyy - yyyy' and we are in
year '2004 - 2005' he won't need more than 11 characters until year '9999 - 10000' (12 chars) and
year '10000 - 10001' (13 chars) (if he decides to keep it in nvarchar or char --not text)

or are saying that even if he is going to use only 11 is better to set the size to nvarchar(20)?

I have read my post twice and still don't see why you guys got the idea that I was
contradicting amachanic in the the comparison of text versus varchar. Can you point it out?
(remember English is not my first language)

*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*

A candle loses nothing by lighting another candle

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2004-11-10 : 19:52:47

i agree on jeff's suggestion to have two fields for this.
1. sorting will be a lot easier
2. indexes can be used properly
3. no need for parsing the string to get the start and end year
4. joins are more manageable if you want to get the start or end year

--------------------
keeping it simple...

tuenty
Constraint Violating Yak Guru

278 Posts

Posted - 2004-11-11 : 08:07:33

quote:
Originally posted by amachanic

Tuenty,

I'm not sure where you get that figure; here's an entry from my blog in which I prove that TEXT columns have gigantic overhead:

http://sqljunkies.com/WebLog/amachanic/archive/2004/10/22/4743.aspx

what figure??

7995 years???

10000 next year with 5 digits
-2005 current end of academic year
-----
 7995 difference in years

I did not posted explicitly but my point was that I don't think any of today's application will
still be running for the next 7995 years so I don't see the need of setting the field size bigger
than 11

*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*

A candle loses nothing by lighting another candle

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources