Float or Int

Does it make a difference if I use the Float or Int data type for a field such as ReceiptNumber or CheckNumber?

Thanks for any thoughts,It absolutely makes a difference -- use an int. Floating-point columns are used for scientific calculations and store floating-point numbers.

And actually, depending on how those columns are being generated and what their purpose is, I feel that varchar might be a better choice. To me, numeric data types are only appropriate for primary keys and for columns where some sort of math is going to happen.

Terri|||Required more memory too..

KP|||Is there a way that I can globally change the float to int in all of the tables in a DB? and is there an example of this some where?

Thank you,|||Well, you can find out where you have used the float data type in your database by issuing this:

SELECT * FROM information_schema.columns WHERE data_type = 'float'

You could write some script that selects this information into a cursor and then you could scroll through the cursor putting together dynamic SQL statements to execute that would do an ALTER TABLE and change the data type. Whether or not this would be worth the trouble is dependent upon how many occurrences you have.

Terri|||Terri,

Thank you very much!sql

FK "this or that" case best practice

If I have a table that can either have a FK to one table or another, what is
the best way to design it?
eg.
create table template
(
id int not null primary key,
description varchar(50) not null,
roleId int null references role(roleId),
userId int null references user(userId)
)
At the moment, because either a role or a user can own the template, I
haveset both columns to allow null, but I do not like this approach. Would
intermediate tables be better even though it is a 1:1 relationship?
create table template
(
id int not null primary key,
description varchar(50) not null,
)
create table templateuser
(
templateId int null references template(id) primary key,
userId int null references user(userId)
)
create table templaterole
(
templateId int null references template(id) primary key,
roleId int null references role(roleId)
)
Thanks
--== Posted via mcse.ms - Unlimited-Uncensored-Secure Usenet News==-
--
http://www.mcse.ms The #1 Newsgroup Service in the World! 120,000+ New
sgroups
--= East and West-Coast Server Farms - Total Privacy via Encryption =--David,
One solution could be enforcing the RI using triggers.
AMB
"David J Rose" wrote:

> If I have a table that can either have a FK to one table or another, what
is
> the best way to design it?
> eg.
> create table template
> (
> id int not null primary key,
> description varchar(50) not null,
> roleId int null references role(roleId),
> userId int null references user(userId)
> )
> At the moment, because either a role or a user can own the template, I
> haveset both columns to allow null, but I do not like this approach. Would
> intermediate tables be better even though it is a 1:1 relationship?
> create table template
> (
> id int not null primary key,
> description varchar(50) not null,
> )
> create table templateuser
> (
> templateId int null references template(id) primary key,
> userId int null references user(userId)
> )
> create table templaterole
> (
> templateId int null references template(id) primary key,
> roleId int null references role(roleId)
> )
> Thanks
>
> --== Posted via mcse.ms - Unlimited-Uncensored-Secure Usenet News=
=--
> http://www.mcse.ms The #1 Newsgroup Service in the World! 120,000+ N
ewsgroups
> --= East and West-Coast Server Farms - Total Privacy via Encryption =--
-
>|||I think it is easier to go with one table, because then you can enforce with
a check constraint that a template is related to exactly a user or a role
(and not both or none), something which you can't do as easy if you use the
2 extra tables. The check constraint would be:
CONSTRAINT CK_template__either_role_or_user
CHECK((roleId IS NULL AND userID IS NOT NULL) OR (roleId IS NOT NULL AND
userID IS NULL))
Jacco Schalkwijk
SQL Server MVP
"David J Rose" <david.rose@.newsgroup.reply.only.com> wrote in message
news:425d0d17$1_1@.127.0.0.1...
> If I have a table that can either have a FK to one table or another, what
> is the best way to design it?
> eg.
> create table template
> (
> id int not null primary key,
> description varchar(50) not null,
> roleId int null references role(roleId),
> userId int null references user(userId)
> )
> At the moment, because either a role or a user can own the template, I
> haveset both columns to allow null, but I do not like this approach. Would
> intermediate tables be better even though it is a 1:1 relationship?
> create table template
> (
> id int not null primary key,
> description varchar(50) not null,
> )
> create table templateuser
> (
> templateId int null references template(id) primary key,
> userId int null references user(userId)
> )
> create table templaterole
> (
> templateId int null references template(id) primary key,
> roleId int null references role(roleId)
> )
> Thanks
>
> --== Posted via mcse.ms - Unlimited-Uncensored-Secure Usenet
> News==--
> http://www.mcse.ms The #1 Newsgroup Service in the World! 120,000+
> Newsgroups
> --= East and West-Coast Server Farms - Total Privacy via Encryption
> =--|||What is wrong with having two tables, one for users, one for roles. You
don't HAVE to have a row in both user and role, so this design makes sense.
I would put a FK to both tables, and probably set them to DELETE CASCADE.
----
Louis Davidson - drsql@.hotmail.com
SQL Server MVP
Compass Technology Management - www.compass.net
Pro SQL Server 2000 Database Design -
http://www.apress.com/book/bookDisplay.html?bID=266
Blog - http://spaces.msn.com/members/drsql/
Note: Please reply to the newsgroups only unless you are interested in
consulting services. All other replies may be ignored :)
"David J Rose" <david.rose@.newsgroup.reply.only.com> wrote in message
news:425d0d17$1_1@.127.0.0.1...
> If I have a table that can either have a FK to one table or another, what
> is the best way to design it?
> eg.
> create table template
> (
> id int not null primary key,
> description varchar(50) not null,
> roleId int null references role(roleId),
> userId int null references user(userId)
> )
> At the moment, because either a role or a user can own the template, I
> haveset both columns to allow null, but I do not like this approach. Would
> intermediate tables be better even though it is a 1:1 relationship?
> create table template
> (
> id int not null primary key,
> description varchar(50) not null,
> )
> create table templateuser
> (
> templateId int null references template(id) primary key,
> userId int null references user(userId)
> )
> create table templaterole
> (
> templateId int null references template(id) primary key,
> roleId int null references role(roleId)
> )
> Thanks
>
> --== Posted via mcse.ms - Unlimited-Uncensored-Secure Usenet
> News==--
> http://www.mcse.ms The #1 Newsgroup Service in the World! 120,000+
> Newsgroups
> --= East and West-Coast Server Farms - Total Privacy via Encryption
> =--

first 7 rows

I have a table sal with the following structure
emp_no int,
dept_no int,
basic_salary money
Is it possible to extract 7 highly paid employed with one SQL statement?
any help will be highly appreciated.select TOP 7
emp_no,
dept_no,
basic_salary
from YourTable
order by basic_salary desc|||how will it calculate highest salary? I mean how system knows to display record based on salary field?|||"order by basic_salary desc"|||the top clause retrieves the rows that would exist at the "Top" of your result set so if your result set was
joe
steve
bill
rob
mary

and you selected the top 3
you would get
joe
steve
bill

the control factor with the top clause is the order by clause
the order by clause sorts the result set either in ascending (ASC) or descending (DESC) order. so if you sorted a salary column asc, the lowest salary would be at the top correct?. and if you selected the top 7 salaries in that example, you would have the 7 lowest salaries.
by sorting the salary column in desc order, you would get the top 7 salaries

please open and read this help file
Books Online{Limiting Result Sets Using TOP and PERCENT}|||how will it calculate highest salary? I mean how system knows to display record based on salary field?

What is the context of the salary table? Does it hold weekly salary data?

Everyone here thinks it's annual.|||Sheesh!

create table sal
(
emp_no int
,dept_no int
,basic_salary money
)

insert into sal values (1 ,10,1000.00)
insert into sal values (2 ,10,2000.00)
insert into sal values (3 ,10,1500.00)
insert into sal values (4 ,10,1200.00)
insert into sal values (5 ,10,1000.00)
insert into sal values (6 ,10,3000.00)
insert into sal values (7 ,10,2200.00)
insert into sal values (8 ,10,1250.00)
insert into sal values (9 ,10,1350.00)
insert into sal values (10 ,10,1000.00)

select * from sal

select top 7 emp_no, dept_no
,Basic_Salary as 'WeeklySalary' --by week
,BiWeeklySalary=(Basic_Salary*2) --BI week
,AnnualSalary=((basic_salary*2)*26) --Annual
from sal
order by AnnualSalary desc|||OK...so what if there's more than 1 salary row per employee?|||...an aggregate query to sum up the salary values.|||...an aggregate query to sum up the salary values.

Thanks you...my point...

Without the DDL of the table we'd be just shooting in the dark

Bang

Yo blind dude..did that hit you?|||Nyah nyah, ya missed me!

You forget that, as the Blindman, I shoot in the dark just fine! :cool:|||okay
since the poster hasnt said anything about it then this post is officially dead.

dont be a malingerer.|||"since the poster hasnt said anything about it then this post is officially dead"?

Like THAT has ever stopped us before...

and "malingerer"?

In the words of Inigo Montoya -
"You keep using that word. I do not think it means what you think it means."

http://dictionary.reference.com/search?q=malingerer|||you killed my father -- prepare to die!|||i know exactly what it means.
you are spending your time posting to a thread that is dead
so you are in effect acting crazy(sick) to get out of the real work here
and that is driving rdjabarov and pat phelan crazy.|||i know exactly what it means.
you are spending your time posting to a thread that is dead
so you are in effect acting crazy(sick) to get out of the real work here
and that is driving rdjabarov and pat phelan crazy.Huh? I seem to keep missing meetings around here!

-PatP|||you killed my father -- prepare to die!

Do you have 6 fingers on your left hand?

That is a great movie

What's with Scott? Are there time issues involved here?

What's officially Dead?|||You are!

Because you killed my father! Prepare to die!

...and Ruprect: Glad you looked up the definition! It pays to enrich your wordpower!|||okay
since the poster hasnt said anything about it then this post is officially dead.

dont be a malingerer.

Hey...he's not even suppose to be here...he's an...an...Oracle developer...

Yo Blind dude..are you feeling OK? (http://www.google.com/search?hl=en&lr=&oi=defmore&q=define:malingerer)|||I have a cough that has been malingering on for weeks now...|||I have a cough that has been malingering on for weeks now...I resemble that remark!

-PatP|||Hey...he's not even suppose to be here...he's an...an...Oracle developer...Its Ok... He can have his kids tell folks that he plays the piano in... Nevermind!

-PatP|||And this thread is only mostly dead...there a difference

To blathe

Have fun stormin the castle boys

I would like to stay and malinger...but I have to go

EDIT: Ya know, this is corral stuff|||Where the heck is a Yak when a guy needs one? I'm off (but you knew that)!

-PatP|||Mostly dead. Now THAT was funny. I wish I'd though of that one, Buttercup.

Can this thread possibly go on? Inconceivable!|||The discussion of this mostle dead thread has moved to the corral

http://www.dbforums.com/showthread.php?p=3978181#post3978181