Some general questions - 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)

Some general questions

Author

Topic

SQLError
Yak Posting Veteran

63 Posts

Posted - 2004-07-11 : 10:44:44

Hi, can some one let me know:

1)
The difference between char and varchar.
I know that varchar does not pad spaces. Does that mean byte wise
is takes up less space on disk?

2)
Is there another decimal data type with size < float = 8 bytes?
I tried changing the default byte size but I couldnt in
enterprise manager

3)
What is the importance of setting up relations?
When I do a query I can join my tables within the query,
why do I need to explicitly set up releations?

Thank you very much.

Kristen
Test

22859 Posts

Posted - 2004-07-11 : 12:44:59

1) Yes, varchar only uses the amount of disk space necessary to hold the data, char will pad the data with spaces to the full width

2) REAL is 4 bytes, FLOAT is 8 bytes, or DECIMAL(n, m) is between 5 and 17 bytes

3) Relations will ensure that you cannot create "orphan" data - so you will not be able to insert an Invoice Item if an Invoice Header does not exist, also you will not be able to Delete an Invoice Header unless all the Invoice Items have already been deleted.

You can also set up relations so that changes "cascade" - so changing the Invoice Number on the Invoice Header and cascade so that all the invoice numbers on the correspnding Invoice Item records are also updated. A Cascade can also be configured to delete all the Invoice Items when the Invoice Header is deleted.

And Enterprise Manager will draw you a pretty diagram with links between the tables if you have relations set up.

Kristen

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-07-11 : 13:55:53

Just another note on 2). You really shouldn't be using float at all unless you need it for a scientific calculation requiring float. Float is an approximation and does not have an exact decimal placement. You should stick to int or decimal datatypes unless you have a good reason to do otherwise.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

Kristen
Test

22859 Posts

Posted - 2004-07-11 : 14:53:57

Does MONEY do "proper" maths? Or have I got to use DECIMAL for that?

Hmmm ... OK I'll go test it before I post ...


DECLARE	@MyMoney1 money, @MyMoney2 money
SELECT	@myMoney1 = CONVERT(money, 1.00)/CONVERT(money, 3.00),
	@myMoney2 = @myMoney1 * 2
SELECT	@myMoney1, @myMoney2, @myMoney1+@myMoney2

DECLARE	@MyFloat1 float, @MyFloat2 float
SELECT	@MyFloat1 = CONVERT(float, 1.00)/CONVERT(float, 3.00),
	@MyFloat2 = @MyFloat1 * 2
SELECT	@MyFloat1, @MyFloat2, @MyFloat1+@MyFloat2

DECLARE	@MyDecimal1 decimal(10,4), @MyDecimal2 decimal(10,4)
SELECT	@MyDecimal1 = CONVERT(decimal(10,4), 1.00)/CONVERT(decimal(10,4), 3.00),
	@MyDecimal2 = @MyDecimal1 * 2
SELECT	@MyDecimal1, @MyDecimal2, @MyDecimal1+@MyDecimal2

Yup! Seems to be OK (as in I DO get different answers!)

Kristen

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-07-11 : 17:16:30

Money is actually a decimal(19,4).

Smallmoney is a decimal(10,4).

That should help answer your question. :)

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

SQLError
Yak Posting Veteran

63 Posts

Posted - 2004-07-11 : 18:00:14

Hi, so if I am storing money, I should use the money data type instead of float. What would be the difference?

kselvia
Aged Yak Warrior

526 Posts

Posted - 2004-07-11 : 19:49:33

I'm not sure what other differences there are, but some values can be assigned to money that can't be assigned to decimal(19,4)

select convert(varchar, cast ('$123,000.00' as money),1)

------------------------------
123,000.00

select convert(varchar, cast ('$123,000.00' as decimal(19,4)),1)

Server: Msg 8114, Level 16, State 5, Line 3
Error converting data type varchar to numeric.

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-07-11 : 23:20:29

Money will accept the dollar or currency variable of most countries. I believe if you look up the money datatype in Books Online it has a list of all the symbols you can use. Also, it's going to be correct for accounting if you just use the money datatype. Better to stick to the datatype most applicable then be sorry later.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

Kristen
Test

22859 Posts

Posted - 2004-07-12 : 13:50:11

> Hi, so if I am storing money, I should use the money data type instead of float. What would be the difference?

I don't think you will get rounding errors (which you will with float)

Kristen

lazerath
Constraint Violating Yak Guru

343 Posts

Posted - 2004-07-12 : 15:22:33

Just a note on #1: I believe that the SQL Server engine performs faster operations when CHAR is used instead of VARCHAR because of the way varchar is stored on disk.

I think the difference is very small, however, if the data in the field is always going to be a certain length or the application doesn't care about the additional spaces, you should use CHAR. I've seen a lot of developers that use VARCHAR 100%, even on such blatantly CHARable fields like State Code.

If anyone knows more details about why CHAR is faster, under what conditions, and to what extent, I'd be interested in learning about it.

kselvia
Aged Yak Warrior

526 Posts

Posted - 2004-07-12 : 15:41:53

Some of the old-timers may remember pre-SQL 2000 had two different update modes: Direct and Defferred. Direct update meant the data pages were updated directly where they were on disk, allowing part of a row to be updated without updateing the entire row. This was possible if the new column was not bigger than the original column, and no columns in the row were nullable before the column being updated. Otherwise the entire row had to be read, reconstructed as a new row, written to disk, pointers were updated to point to the new row, and the original row was deleted. I have tried to find out how SQL 2000 manages this, and weather there is advantage to creating char colums for speed for this reason but I have not been able to find out anything about 2000 internals that are similar to SQL 6.5 and before.

--Ken
Your Kung-Fu is not strong. -- 'The Core'

Kristen
Test

22859 Posts

Posted - 2004-07-13 : 01:35:07

The biggest issue I see of using CHAR instead of VARCHAR is that he "bigger" rows that are stored will mean that fewer will be stored per page, and thus more pages will be processed.

I'm talking of varchar(big number) storing 'short string', rather than varchar(1) v. char(1) !

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources