Do i need primary key?

Here is the scenario
I have a table (let's call it tblTest) with 3 fields, UserID, Note,
NoteEnterDate (simple one to many relationship. user can have many notes)
Query would be to join it with user table and get the first and last name of
the user (based on UserID) and show the Note and NoteEnterDate (at this
point, I cannot think of any other query that uses this tblTest other than
this).
As far as index is concerned, I think I just need to create clustered index
on UserID.
I know it is good to declare a primary key for every table. Is there a need
for primary key for tblTest perhaps by adding another field called NoteID?
Thanks"Justin" <jus820@.hotmail.com> wrote in message
news:e4OGN5TbGHA.3812@.TK2MSFTNGP04.phx.gbl...
> Here is the scenario
> I have a table (let's call it tblTest) with 3 fields, UserID, Note,
> NoteEnterDate (simple one to many relationship. user can have many notes)
> Query would be to join it with user table and get the first and last name
> of the user (based on UserID) and show the Note and NoteEnterDate (at this
> point, I cannot think of any other query that uses this tblTest other than
> this).
> As far as index is concerned, I think I just need to create clustered
> index on UserID.
> I know it is good to declare a primary key for every table. Is there a
> need for primary key for tblTest perhaps by adding another field called
> NoteID?
>
Would you allow the same (UserID,Note,NoteEnterDate) to be entered twice?
If so, add a synthetic NoteID to tell them apart. If not, create the PK on
(UserID, NoteEnterDate,Note).
David|||you don 't need to waste the space on another column, if the noteid is not
having significance. and if you are going to use an identity for it then its
of no use at all.
u can use user ID and NoteEnterDate as the primary key.
Hope this helps.
--
"Justin" wrote:

> Here is the scenario
> I have a table (let's call it tblTest) with 3 fields, UserID, Note,
> NoteEnterDate (simple one to many relationship. user can have many notes)
> Query would be to join it with user table and get the first and last name
of
> the user (based on UserID) and show the Note and NoteEnterDate (at this
> point, I cannot think of any other query that uses this tblTest other than
> this).
> As far as index is concerned, I think I just need to create clustered inde
x
> on UserID.
> I know it is good to declare a primary key for every table. Is there a ne
ed
> for primary key for tblTest perhaps by adding another field called NoteID?
> Thanks
>
>|||NoteEnterDate is smalldatetime datatype (since we don't basically care about
the seconds). User can type more than one note in a minute. If I create
primary key on UserID and NoteEnterDate, this is not allowed.
Even even if this was not allowed (creating more than one notes in a
minute), what would be the point of creating primary key on UserID,
NoteEnterDate?
Thanks
"Omnibuzz" <Omnibuzz@.discussions.microsoft.com> wrote in message
news:0F05A821-5FFD-4A20-97E2-301565FFA187@.microsoft.com...
> you don 't need to waste the space on another column, if the noteid is not
> having significance. and if you are going to use an identity for it then
> its
> of no use at all.
> u can use user ID and NoteEnterDate as the primary key.
> Hope this helps.
> --
>
>
> "Justin" wrote:
>|||> Even even if this was not allowed (creating more than one notes in a
> minute), what would be the point of creating primary key on UserID,
> NoteEnterDate?
Maybe to prevent someone hitting refresh on your web page, 80 times in a
minute, and populating your table with redundant data.
A|||> you don 't need to waste the space on another column, if the noteid is not
> having significance. and if you are going to use an identity for it then
> its
> of no use at all.
I don't think I particularly agree. What harm does an IDENTITY column do?
I don't think it's as horrible as you make it out to be. The OP doesn't
know all future requirements now, so we can't really gauge its significance,
but let's say you wanted to track notes for some reason (e.g. show who has
viewed them and when). You might create another table called:
CREATE TABLE dbo.NoteTracking
(
? FOREIGN KEY REFERENCES dbo.tblTest(?),
ViewDate SMALLDATETIME
)
Surely you don't suggest it would be better to make up this primary key
UserID,NoteEnterDate and use that as your reference in the secondary table?
Like the OP, I'm not clear on what business sense a primary key on
UserID,NoteEnterDate would make, other than to fulfill the mantra "every
table must have a primary key." If it really is possible (and even
desirable) for a single user to enter two notes in one minute, then we're
back at square one.
A|||All tables must have at least one set of columns that can invariably
identify every row in the table. And there are obvious practical reasons to
explicitly declare one of those sets of columns as the primary key.
Anithsql

Do I need a Primary Key?

I am programming a site in ASP. I am used to using Access which forces you
to have a Primary Key. However, I am learning to use SQL which does not
seem to force you to have a Primary Key. Do I really need one? Please let
me know and why. Thanks!!Techniclly speaking you don't need one, but I would consider any table
without a primary key poor design.
The main reason you need one is that the primary key guarantees you'll have
a column in your table that you can use to uniquely identify each record.
Without a primary key you could potentialy end up with multiple identical
records in your table which you're not able to identify individualy using a
select, update or delete statement.
HTH
Karl Gram
http://www.gramonline.com
"michaaal" <res0gyio@.verizon.net> wrote in message
news:O6$6#0iDEHA.3784@.TK2MSFTNGP10.phx.gbl...
> I am programming a site in ASP. I am used to using Access which forces
you
> to have a Primary Key. However, I am learning to use SQL which does not
> seem to force you to have a Primary Key. Do I really need one? Please
let
> me know and why. Thanks!!
>|||Hi,
To add on to old post,
1. Enforce uniqueness for values entered in specified columns
2. Will not allow nulls.
3. If you define a primary key for a table in your database, you can relate
that table to other tables, thus reducing the need for redundant data.
This will allow you to have Parent child relation ship with out writing
code.
4. This will allow you to do Cascading (Refer boks inline)
Always for a better database modelling we should enforce Primary key /
Foregn key concept.
Thanks
Hari
MCDBA
"Karl Gram" <NOSPAMkarl@.gramonline.nl> wrote in message
news:#FrwxMkDEHA.2600@.TK2MSFTNGP09.phx.gbl...
> Techniclly speaking you don't need one, but I would consider any table
> without a primary key poor design.
> The main reason you need one is that the primary key guarantees you'll
have
> a column in your table that you can use to uniquely identify each record.
> Without a primary key you could potentialy end up with multiple identical
> records in your table which you're not able to identify individualy using
a
> select, update or delete statement.
> --
> HTH
> Karl Gram
> http://www.gramonline.com
> "michaaal" <res0gyio@.verizon.net> wrote in message
> news:O6$6#0iDEHA.3784@.TK2MSFTNGP10.phx.gbl...
> you
> let
>|||> 3. If you define a primary key for a table in your database, you can
relate
> that table to other tables, thus reducing the need for redundant data.
> This will allow you to have Parent child relation ship with out
writing
> code.
The above statement brings on another question I had... Do I really
WANT to do this type of thing on the SQL server level? Or do I
want to do this type of thing in my code. My first inclination is to do
it in the code, however, I have not really sat down and researched the
possible speed differences. Any comments on this? Thank you!|||"michaaal" <res0gyio@.verizon.net> wrote in message
news:eRB$WFlDEHA.3980@.TK2MSFTNGP09.phx.gbl...
> relate
> writing
> The above statement brings on another question I had... Do I really
> WANT to do this type of thing on the SQL server level? Or do I
> want to do this type of thing in my code. My first inclination is to do
> it in the code, however, I have not really sat down and researched the
> possible speed differences. Any comments on this? Thank you!
Enforcing constraints in the code means that they will only be enforced in
your code. If someone uses Access or similar to access your database
directly they can by-pass all your constraints and wreak havoc.
It is also (IMHO) easier to document and troubleshoot. The constraints are
there as part of your table definition. All your data-centric info is in
one place.
As for speed, if you have the contsraints in SQL Server the optimizer and
can make informed decisions on how best to optimize the queries. Otherwise
in the code you will have to decide how to join the data, which is either
going to be very complicated, or not the best method in every circumstance.
Finally, and this is more a judgement on my programming skills than yours,
constraints work pretty much the way it says on the box. If you are
hand-coding all this, then bugs can creep in, you may not foresee every
eventuality, etc.
So my vote is for data-centric rules to be in the data tier.
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.614 / Virus Database: 393 - Release Date: 05/03/2004|||No, you should always enforce constraints at the DATA level. Enforcing them
in the code means that your data can become corrupt by someone simply
bypassing your application (e.g. running an insert/update/delete from Query
Analyzer).
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"michaaal" <res0gyio@.verizon.net> wrote in message
news:eRB$WFlDEHA.3980@.TK2MSFTNGP09.phx.gbl...
> relate
> writing
> The above statement brings on another question I had... Do I really
> WANT to do this type of thing on the SQL server level? Or do I
> want to do this type of thing in my code. My first inclination is to do
> it in the code, however, I have not really sat down and researched the
> possible speed differences. Any comments on this? Thank you!
>
>|||RE/
>No, you should always enforce constraints at the DATA level. Enforcing the
m
>in the code means that your data can become corrupt by someone simply
>bypassing your application (e.g. running an insert/update/delete from Query
>Analyzer).
Do you prefer to enforce RI via triggers or the other way?
"Other way" because I don't know enough go spell it out...Converted a few MS
Access DBs and wound up with triggers - so that's all I know. MSDN Univers
al
coming soon - so I guess I'll get the option to go either way via MS Visio's
DB
design tool...
--
PeteCresswell|||No, triggers can be pretty poor for performance, depending on other
circumstances. I prefer traditional primary/foreign key relationships, then
violations are stopped in their tracks rather than after the fact.
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"(Pete Cresswell)" <x@.y.z> wrote in message
news:k2op50heohj4o6j7tkgp05o7gk61hf1eue@.
4ax.com...
> RE/
> Do you prefer to enforce RI via triggers or the other way?
> "Other way" because I don't know enough go spell it out...Converted a few
> MS
> Access DBs and wound up with triggers - so that's all I know. MSDN
> Universal
> coming soon - so I guess I'll get the option to go either way via MS
> Visio's DB
> design tool...
> --
> PeteCresswell

Do foreign keys generate implicit indexes?

If i create a simple table with a foreign key constraint, does it
create an implicit index on that given ID? I've been told this is
done in some databases, but i need to know for sure if SQL Server does
it. Has anyone heard of this before, on any other databses perhaps?

Heres an example of how the foreign key constraint is being added:

ALTER TABLE [dbo].[administrators] WITH CHECK ADD CONSTRAINT
[FPSLUFSUOXZGAJOJ] FOREIGN KEY([AdministratorRoleID])
REFERENCES [dbo].[administratorroles] ([AdministratorRoleID])

My initial testing seems to indicate adding an index on the foreign
key column helps, but i need to know for sure. Any insight would be
greatly appreciated!

Bobbobdurie@.gmail.com (bobdurie@.gmail.com) writes:

Quote:

Originally Posted by

If i create a simple table with a foreign key constraint, does it
create an implicit index on that given ID?

In SQL Server, no.

Quote:

Originally Posted by

I've been told this is done in some databases, but i need to know for
sure if SQL Server does it. Has anyone heard of this before, on any
other databses perhaps?

I seem to recall having heard this about Sybase Anywhere.

Quote:

Originally Posted by

Heres an example of how the foreign key constraint is being added:
>
ALTER TABLE [dbo].[administrators] WITH CHECK ADD CONSTRAINT
[FPSLUFSUOXZGAJOJ] FOREIGN KEY([AdministratorRoleID])
REFERENCES [dbo].[administratorroles] ([AdministratorRoleID])
>
My initial testing seems to indicate adding an index on the foreign
key column helps, but i need to know for sure. Any insight would be
greatly appreciated!

Indeed, it is often a good idea to add indexes on foreign keys, as it
can speed up deletions considerably. And it is not uncommon to search
for data in a table on a foreign key. However, as always, you should
think twice, and not add indexes blindly. For instance, if you have a
country-code column in a address table, there is little reason to add
an index on that column, since you don't delete countries very often.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||>I've been told this is done in some databases, .. <<

Yes, but better. Sybase SQL Anywhere (nee Watcom SQL) builds links
from all the FK references to the single PRIMARY KET/UNIQUE occurence
in the referenced table. Saves space, pre-joins tables for speed and
makes DRI actions both easy and fast.

SQL Server is still thinking in terms of "table = file" instead of
"table is part of a whole schema" and that "record =row" instead of
"row is made up of columns". Stonebreaker had a recent blog on column-
oriented design over contigous storage model.|||On Fri, 21 Sep 2007 19:51:14 -0000, "bobdurie@.gmail.com"
<bobdurie@.gmail.comwrote:

Microsoft Access does this, when you create a relationship between two
tables.
Check with sysindexes to see if SQL Server does this too.

-Tom.

Quote:

Originally Posted by

>If i create a simple table with a foreign key constraint, does it
>create an implicit index on that given ID? I've been told this is
>done in some databases, but i need to know for sure if SQL Server does
>it. Has anyone heard of this before, on any other databses perhaps?
>
>Heres an example of how the foreign key constraint is being added:
>
>ALTER TABLE [dbo].[administrators] WITH CHECK ADD CONSTRAINT
>[FPSLUFSUOXZGAJOJ] FOREIGN KEY([AdministratorRoleID])
>REFERENCES [dbo].[administratorroles] ([AdministratorRoleID])
>
>My initial testing seems to indicate adding an index on the foreign
>key column helps, but i need to know for sure. Any insight would be
>greatly appreciated!
>
>Bob

|||Check with sysindexes to see if SQL Server does this too.

As Erland mentioned, SQL Server does not automatically index foreign key
columns. That task is left to the discretion of the DBA, who might choose
not to index the foreign column(s) due to low cardinality and static data.

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Tom van Stiphout" <no.spam.tom7744@.cox.netwrote in message
news:8qqbf3190hk4cis52502s9th2ebsjjfpd5@.4ax.com...

Quote:

Originally Posted by

On Fri, 21 Sep 2007 19:51:14 -0000, "bobdurie@.gmail.com"
<bobdurie@.gmail.comwrote:
>
Microsoft Access does this, when you create a relationship between two
tables.
Check with sysindexes to see if SQL Server does this too.
>
-Tom.
>
>

Quote:

Originally Posted by

>>If i create a simple table with a foreign key constraint, does it
>>create an implicit index on that given ID? I've been told this is
>>done in some databases, but i need to know for sure if SQL Server does
>>it. Has anyone heard of this before, on any other databses perhaps?
>>
>>Heres an example of how the foreign key constraint is being added:
>>
>>ALTER TABLE [dbo].[administrators] WITH CHECK ADD CONSTRAINT
>>[FPSLUFSUOXZGAJOJ] FOREIGN KEY([AdministratorRoleID])
>>REFERENCES [dbo].[administratorroles] ([AdministratorRoleID])
>>
>>My initial testing seems to indicate adding an index on the foreign
>>key column helps, but i need to know for sure. Any insight would be
>>greatly appreciated!
>>
>>Bob

|||Also, analyse your query requirements then apply an Indexing Strategy

--

Jack Vamvas
___________________________________
Need an IT job? http://www.ITjobfeed.com/SQL
<bobdurie@.gmail.comwrote in message
news:1190404274.471197.197240@.n39g2000hsh.googlegr oups.com...

Quote:

Originally Posted by

If i create a simple table with a foreign key constraint, does it
create an implicit index on that given ID? I've been told this is
done in some databases, but i need to know for sure if SQL Server does
it. Has anyone heard of this before, on any other databses perhaps?
>
Heres an example of how the foreign key constraint is being added:
>
ALTER TABLE [dbo].[administrators] WITH CHECK ADD CONSTRAINT
[FPSLUFSUOXZGAJOJ] FOREIGN KEY([AdministratorRoleID])
REFERENCES [dbo].[administratorroles] ([AdministratorRoleID])
>
My initial testing seems to indicate adding an index on the foreign
key column helps, but i need to know for sure. Any insight would be
greatly appreciated!
>
Bob
>

|||On Sep 24, 3:25 am, "Jack Vamvas" <DEL_TO_RE...@.del.comwrote:

Quote:

Originally Posted by

Also, analyse your query requirements then apply an Indexing Strategy
>
--
>
Jack Vamvas
___________________________________
Need an IT job? http://www.ITjobfeed.com/SQL
>
<bobdu...@.gmail.comwrote in message
>
news:1190404274.471197.197240@.n39g2000hsh.googlegr oups.com...
>

Quote:

Originally Posted by

If i create a simple table with a foreign key constraint, does it
create an implicit index on that given ID? I've been told this is
done in some databases, but i need to know for sure if SQL Server does
it. Has anyone heard of this before, on any other databses perhaps?

>

Quote:

Originally Posted by

Heres an example of how the foreign key constraint is being added:

>

Quote:

Originally Posted by

ALTER TABLE [dbo].[administrators] WITH CHECK ADD CONSTRAINT
[FPSLUFSUOXZGAJOJ] FOREIGN KEY([AdministratorRoleID])
REFERENCES [dbo].[administratorroles] ([AdministratorRoleID])

>

Quote:

Originally Posted by

My initial testing seems to indicate adding an index on the foreign
key column helps, but i need to know for sure. Any insight would be
greatly appreciated!

>

Quote:

Originally Posted by

Bob

Thanks for all the responses on this, its much appreciated!!! I also
found this article which makes me realize other people have had the
same misconceptions as me :)
http://www.sqlskills.com/blogs/kimb...yColu mns.aspx

Do Foreign Key Boost Performance?

Hello,
can Foreign Keys boost performance resp. Select or Where Statement in combination with a join?
Silasnope. but indexes do and I index all of my FKs. FKs are all about maintaining data integrity and they are very important.|||Do brakes boost your car's performance?|||In a very few incredibly specific circumstances foreign keys can (in an MS SQL Server DB) boost performance. I never consider them in terms of a performance boost. I would advise you don't either - there are an incalcable number of things to consider first.|||gotta link?|||One caveat to my previous post. I have read that using cascading deletes in conjunction with foreign keys requires less overhead than maintaining the data integrity on your own. But as far as I know plain vanilla foreign keys on their own do not impact performance. If I am wrong, please let me know.|||Yeah - a bit like Sean I'd like to stress I only addressed a small part of the performance aspect of your question. Their purpose in maintaining integrity is far more important than any performance overhead.gotta link?Yuppers:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=74552&SearchTerms=foreign,key,pootle,kristen|||does youse guys ever link anyplace other than sqlteam?

buncha wannabe (http://en.wiktionary.org/wiki/wannabe)s

:p|||does youse guys ever link anyplace other than sqlteam?

buncha wannabe (http://en.wiktionary.org/wiki/wannabe)s

:p

whenever my boss introduces me as the resident guru I do feel like a wannabe and a fraud.

but back to my point here, I went to the msdn link and am I right that FK's on their own without setting db options or setting cascading deletes to on do not improve performance in a perceptible way?|||"setting cascading deletes to on" is a bit of misnomer

as soon as you define the foreign key, it gets an ON DELETE action

if you specify nothing, it defaults to NO ACTION

SQL Server 2000 offers only NO ACTION and DELETE as options

SQL Server 2005 offers NO ACTION, CASCADE, SET NULL, and SET DEFAULT

it's safe to say that all of these actions are more efficiently done by the database than by application code

but as far as SELECT queries are concerned, no, foreign keys don't speed those up (except insofar as the columns are indexed, which they usually are when someone defines foreign keys)|||Do brakes boost your car's performance?

Yes....in terms of stopping performance! ;)|||Yes....in terms of stopping performance! ;)If you are at least marginally sane, you won't operate a car that you can't stop safely. A car without brakes is crippled, its top performance is negligable because I can walk faster than I'll try to drive it.

Give me a 1970 Volvo that is rusted out, with one bad carburator, and good brakes and it will easily reach the speed limit... It might be smelly, ugly, and loud, but it will go as fast as the law allows.

Give me a 2007 Lamborghini, fresh off the sales floor with bad brakes, and while the engine is fantastic and the transmission is good enough, the amount of torque that can reach the pavement is limited (by law) to what will get me to ten kph.

Since the Volvo with brakes can travel at more than ten times the top legal speed of the Lamborghini, I feel pretty comfortable with the assertion that good brakes will significantly increase performance!

-PatP|||For those of you that are slow studies, the above analogy applies just as well to databases as it applies to vehicles... Foreign keys are the "safety gear" that is needed to keep a database from becoming corrupt.

Getting wrong answers fast is the fetish of amatuer programmers and DBAs. Wrong answers are still wrong, it doesn't matter how fast you get them.

Having foreign key constraints that you don't need will not hurt you (or query performance). Having them may help reduce elapsed time for queries because of how the engine enforces the foreign key and runs queries, but it will not hurt queries. There is some (time) cost to loading data with foreign key constraints, but that is a tiny cost compared to either a wrong answer that it caught and corrected by the business and trivial compared to the cost of a wrong answer that is actually used by the business!

-PatP|||oops, you posted first, and a nice lesson there about FKs

i wanted to mention that good performance without reliable brakes is a huge risk

last year i drove my car (a '69 beetle) 20 miles to the shop using only the handbrake, as the brake cable had snapped off, and i can tell you, i drove real careful (note to the pedants: yes, i am aware i used an adjective where i should've used an adverb, but that's just my style, innit :))

this year the clutch pedal rusted half off, and i had to drive it to the same shop without being able to get it into first gear, and let me tell you, that was no picnic, you just cannot let yourself come to a complete stop because then you can't get it into second, either, and in third you'll just stall when you try to go (luckily i only had to push it while in traffic once)

anyhow, my point to databases was that speed is irrelevant if something else is wrong

then i was going to quote that familiar "to err is human, but to really screw things up, let your programmers put apps into production without data reliability"|||THAT is why I love old vehicles... Especially Beetles :D|||The question was: Do foreign keys boost performance?
I'm the only one that answered the question and provided evidence (from the horses mouth too - and I don't mean the wannabees).
ergo:
Poots - 1
The rest - 0

Besides - Silas gave up on us ages ago.|||Isn't that always the score? ;)|||Isn't that always the score? ;)Spoken like a true protege ;)|||...I feel pretty comfortable with the assertion that good brakes will significantly increase performance!

-PatP

When I wrote 'stopping performance' I meant the ability to be able to stop, not reducing the ability to go. There is a difference I believe!|||Guys, can we just drop this topic now and move on to evaluating the importance of regularly scheduled oil changes?

dmx query probablity ?

CREATE MINING MODEL mortgage
(
[id] long key,
Edu_Status long DISCRETE,
Work_Status long DISCRETE,
age long CONTINUOUS,
asset_value long CONTINUOUS,
Net_income long CONTINUOUS,
paid_Status long DISCRETE PREDICT
)USING MICROSOFT_DECISION_TREES

i have a mining model above and i am designing a web cross ablication to see probablity of the a costumers' paid status.can you write for me dmx command for selecting paid status that have same criteria(ex;age=30 net income=2000,Edu_status=college) and when i write dxm window(select * from [paid_Status] ) it only shows "4" one row one column why ? as you see i dont know

dmx and datamining well..:)

string dxmcommand = "";

private AdomdCommand ascommand = new AdomdCommand();

private AdomdConnection asconnection=new AdomdConnection();

private AdomdDataReader asreader=new AdomdDataReader();

ascommand.CommandText = dmxcommand;

if I understand your question correctly, then the query would be something like:

SELECT Paid_Status, PredictProbability(Paid_Status)

FROM mortgage NATURAL PREDICTION JOIN

(SELECT 30 AS Age, 2000 as Net_Income ) AS T

This query will return the predicted value for Paid_Status as well as the proability for that prediction. Note that I did not include "College" as Edu_status. The reason is that the Edu_status column of the mining model is defined as "LONG DISCRETE" and "College" is a string, so it cannot be mapped to a long. You will have to convert "College" to the numeric code which I suppose it is used in describing this string, then add "<Numeric_Code_For_College> AS Edu_Status" to the query

Also, in C#, the sequence of operations should be along this line:

AdomdConnection cn = new AdomdConnection();

cn.ConnectionString = "Data Source=localhost; Initial Catalog=<Your Database>";

cn.Open(); // open the connection

AdomdCommand cmd = new AdomdCommand();

cmd.Connection = cn; // associate the command with the current connection

cmd.CommandText = dmxcommand;

AdomdDataReader rdr = cmd.ExecuteReader(); // obtain the reader from the command execution instead of creating it with new

Hope this helps

Dividing in a view: 1 or 0

Hello,
I am having, what seems to me as the oddest problem in SQL.
I have created a view based on two tables, the two key columns in these
tables are integers (quantity) what I am trying to do in this table is divide
one by the other to get the weight which I want as a decimal however SQL
Server is giving me a 1 or a 0 which I dont want!
Any ideas?!
Take a look at this
declare @.i1 int, @.i2 int
select @.i1 =10,@.i2 =3
select @.i1/@.i2,@.i1/(@.i2*1.0),1.0*@.i1/@.i2
http://sqlservercode.blogspot.com/
|||Thanks for your response however perhaps I wasnt clear,
The weight value will always be between 1 and 0 in the same way as a
percentage is between 1 and 0!
My problem is that when I have 34 / 823 I get 0 instead of 0.041312272
Thanks in Advance
Chris
"SQL" wrote:

> Take a look at this
> declare @.i1 int, @.i2 int
> select @.i1 =10,@.i2 =3
> select @.i1/@.i2,@.i1/(@.i2*1.0),1.0*@.i1/@.i2
> http://sqlservercode.blogspot.com/
>
|||If you do this
select (1.0*34 / 823 ) you will get .041312
and with this
select round(convert(decimal(9,5),34)/ convert(decimal(9,5),823),9) you
will get .041312272000000
http://sqlservercode.blogspot.com/

Dividing in a view: 1 or 0

Hello,
I am having, what seems to me as the oddest problem in SQL.
I have created a view based on two tables, the two key columns in these
tables are integers (quantity) what I am trying to do in this table is divid
e
one by the other to get the weight which I want as a decimal however SQL
Server is giving me a 1 or a 0 which I dont want!
Any ideas?!Take a look at this
declare @.i1 int, @.i2 int
select @.i1 =10,@.i2 =3
select @.i1/@.i2,@.i1/(@.i2*1.0),1.0*@.i1/@.i2
http://sqlservercode.blogspot.com/|||Thanks for your response however perhaps I wasnt clear,
The weight value will always be between 1 and 0 in the same way as a
percentage is between 1 and 0!
My problem is that when I have 34 / 823 I get 0 instead of 0.041312272
Thanks in Advance
Chris
"SQL" wrote:

> Take a look at this
> declare @.i1 int, @.i2 int
> select @.i1 =10,@.i2 =3
> select @.i1/@.i2,@.i1/(@.i2*1.0),1.0*@.i1/@.i2
> http://sqlservercode.blogspot.com/
>|||If you do this
select (1.0*34 / 823 ) you will get .041312
and with this
select round(convert(decimal(9,5),34)/ convert(decimal(9,5),823),9) you
will get .041312272000000
http://sqlservercode.blogspot.com/

Dividing in a view: 1 or 0

Hello,
I am having, what seems to me as the oddest problem in SQL.
I have created a view based on two tables, the two key columns in these
tables are integers (quantity) what I am trying to do in this table is divide
one by the other to get the weight which I want as a decimal however SQL
Server is giving me a 1 or a 0 which I dont want!
Any ideas?!Take a look at this
declare @.i1 int, @.i2 int
select @.i1 =10,@.i2 =3
select @.i1/@.i2,@.i1/(@.i2*1.0),1.0*@.i1/@.i2
http://sqlservercode.blogspot.com/|||Thanks for your response however perhaps I wasnt clear,
The weight value will always be between 1 and 0 in the same way as a
percentage is between 1 and 0!
My problem is that when I have 34 / 823 I get 0 instead of 0.041312272
Thanks in Advance
Chris
"SQL" wrote:
> Take a look at this
> declare @.i1 int, @.i2 int
> select @.i1 =10,@.i2 =3
> select @.i1/@.i2,@.i1/(@.i2*1.0),1.0*@.i1/@.i2
> http://sqlservercode.blogspot.com/
>|||If you do this
select (1.0*34 / 823 ) you will get .041312
and with this
select round(convert(decimal(9,5),34)/ convert(decimal(9,5),823),9) you
will get .041312272000000
http://sqlservercode.blogspot.com/