Re: Creating an Index on a Foreign Key - Database Discussion Boards

pmarfleet12-Dec-07 10:47

12-Dec-07 10:47

You need to write a stored procedure that performs the INSERT operation and then returns the latest IDENTITY value as an OUTPUT parameter. The latest IDENTITY value can be determined by using the SCOPE_IDENTITY[^] command.

Paul Marfleet

"No, his mind is not for rent
To any God or government"
Tom Sawyer - Rush

Updatable view

Goodway12-Dec-07 9:22

Goodway

12-Dec-07 9:22

hello everyone,
how can i create an updatable view in sql server 2005.

Thanks.

Re: Updatable view

pmarfleet12-Dec-07 10:44

pmarfleet

12-Dec-07 10:44

I suggest you read this article[^].

Paul Marfleet

"No, his mind is not for rent
To any God or government"
Tom Sawyer - Rush

compare 2 datasets

mfmaneef12-Dec-07 8:44

mfmaneef

12-Dec-07 8:44

Hello,

i have to develop an application to compare a dataset (with around 1000s of records)with a database table which has almost more than 4 million records.

and if the dataset has matches in the table i will mark them as processed. is there any way to achive this .

note: i already have tried comparing them with 2 loops iterating manually in each record...i want some other way

aneef

Re: compare 2 datasets

pmarfleet12-Dec-07 10:42

pmarfleet

12-Dec-07 10:42

Assuming you are using SQL Server, a possible solution would be:

1. Serialize the contents of your dataset as XML and submit the data to your database using a stored procedure and the OPENXML command.

2. In your stored procedure, load the serialized data into a table variable whose structure matches the dataset. Add an extra bit field called Processed that can be used to mark matched records.

3. Run an UPDATE query on your table variable, joining it to your master database table. Set the value of your Processed field in the matching records to 1.

4. From your stored procedure, return all records in the table variable where Processed = 1.

Hope this helps.

Paul Marfleet

"No, his mind is not for rent
To any God or government"
Tom Sawyer - Rush

Naming Convention for Indexes and Triggers and Constraints

Brendan Vogt12-Dec-07 3:00

Brendan Vogt

12-Dec-07 3:00

Hi,

I want to know what are the naming conventions for creating the above mentioned? I went through some of the adventure works tables and looking at the indexes they either start with IX or AK, for example:

IX_EmployeeDepartmentHistory_DepartmentID

Please advise.

Brendan

Creating an Index on a Foreign Key

Brendan Vogt12-Dec-07 2:54

Brendan Vogt

12-Dec-07 2:54

Hi,

I have 3 tables, namely:

CategoryGroup
CategoriesInCategoryGroup
Categorty

CategoriesInCategoryGroup has only 2 fields, which I made the primary key each, namely CategoryGroupId and CategortyId, and they each reference the corresponding table. So basically they are foreign keys.

I have a question when creating an index in the CategoriesInCategoryGroup table. Do I create 1 index, or should I create a separate index for CategoryGroupId and CategortyId in the CategoriesInCategoryGroup table?

Please advise.

Brendan

Re: Creating an Index on a Foreign Key

DoomedOne12-Dec-07 3:33

DoomedOne

12-Dec-07 3:33

Is always a good idea to create an index for each field (or group of fields) that forms a FK

Only in few cases you could not create such an index, i.e. when it is the first field in an already existing index or is equal to de PK.

Habetis bona deum

Re: Creating an Index on a Foreign Key

andyharman12-Dec-07 23:44

andyharman

12-Dec-07 23:44

Hi Brendan

I would expect CategoriesInCategoryGroup to have CategoryGroupId and CategortyId as a clustered primary key. You would then normally create a separate non-clustered index on the CategoryId column.

If you are not using SQL-Server then the second index should be combine CategortyId and CategoryGroupId.

Regards
Andy

Releasing lock on an Oracle package.

Mel Padden12-Dec-07 0:59

Mel Padden

12-Dec-07 0:59

OK, I realise this is generally a SQL server-oriented forum, but I'm a bit stumped so I'll post anyway.

I was debugging an Oracle package which does a massive number of updates (9,6000,000 approx.) and it hung. I tried to kill the process but that didn't work either, and I had to kill TOAD eventually. When I went back in, I found that Oracle had placed a lock on the package, so I can't compile, change, or even drop the object. Does anyone know where I need to go in Oracle to release this lock? I don't have a DBA on-site here, I'm pretty much on my own.

Smokie, this is not 'Nam. This is bowling. There are rules.

regarding Group by

praveenanand11-Dec-07 22:44

praveenanand

11-Dec-07 22:44

Hi,

if we are trying to add the several columns in select statement without using Aggregate functions and only one condition in group by.

how can we do that ??

Re: regarding Group by

Rohde11-Dec-07 23:19

Rohde

11-Dec-07 23:19

I'm not sure I understand you. Can you throw some SQL my way...

"When you have made evil the means of survival, do not expect men to remain good. Do not expect them to stay moral and lose their lives for the purpose of becoming the fodder of the immoral. Do not expect them to produce, when production is punished and looting rewarded. Do not ask, `Who is destroying the world?' You are."

-Atlas Shrugged, Ayn Rand

Re: regarding Group by

Colin Angus Mackay12-Dec-07 0:03

Colin Angus Mackay

12-Dec-07 0:03

praveenanand wrote:
if we are trying to add the several columns in select statement without using Aggregate functions and only one condition in group by.

You can't. Each column not specified in the GROUP BY clause must be part of an aggregate on the SELECT. If you think about what it is doing you will see why.

Upcoming FREE developer events:
* Developer Day Scotland

My website | blog

Is there a way to efficiently load the cities of US into the database

Rocky#11-Dec-07 22:35

Rocky#

11-Dec-07 22:35

Hello,

I'm working on a project and its deadline is comming really close. I have a State and cities Table to store the names of the states and cities of the US. I've populated the State Table manually but the cities is like quite a hectic task. Is there some way that I can load the names of teh cities programaticaly (thru a file or something) in to the db
I'm using SQL 2005 and .NET 2.0

Any suggestions, guidlines are welcome

Thanks in advance

Rocky

Success is a ladder which you can't climb with your hands in your pockets.

Re: Is there a way to efficiently load the cities of US into the database

Rohde11-Dec-07 23:17

Rohde

11-Dec-07 23:17

When I needed to load a list of all the countries in the world I used an XMLDataSource and found an XML file with all the countries in it. I think I found the file at the US Census Bureau web site if I'm not mistaken.

I'm sure you could find an XML list with all the cities and then you could load it and save it to the db.

And how do you manage to submit a post on the 13th of December? Pretty crazy.

Re: Is there a way to efficiently load the cities of US into the database

Rocky#11-Dec-07 23:24

Rocky#

11-Dec-07 23:24

ok that's gr8 I'll chk it out to see what I can find on google.

Rohde wrote:
And how do you manage to submit a post on the 13th of December? Pretty crazy.

That one was over the top Sir!! Big Grin | :-D

Didnt get it????

Rocky

Success is a ladder which you can't climb with your hands in your pockets.

Re: Is there a way to efficiently load the cities of US into the database

Rohde11-Dec-07 23:32

Rohde

11-Dec-07 23:32

Rocky# wrote:
That one was over the top Sir!! Didnt get it????

Heh

When I reply to your post the beloved CP displays:

Reply to Message header:
Forum: SQL / ADO / ADO.NET
Subject: Re: Is there a way to efficiently load the cities of US into the database
Sender: Rocky#
Date: Thursday, December 13, 2007 2:24:00 AM

Re: Is there a way to efficiently load the cities of US into the database

Rocky#11-Dec-07 23:39

Rocky#

11-Dec-07 23:39

oh wow! that mean ATTN CP web masters!! There's a little issue around here. I wonder where did that come from

Rocky

Success is a ladder which you can't climb with your hands in your pockets.

how select row ?

azumike11-Dec-07 22:34

azumike

11-Dec-07 22:34

I have a problem in selecting one row of a table
1. Could you help me to select only one row without querying by the data inside the table, please ?
2. How i can identify the row ?

thanks and regards,
azumike

Re: how select row ?

Rohde11-Dec-07 23:19

Rohde

11-Dec-07 23:19

You use a primary key.

Re: how select row ?

azumike11-Dec-07 23:32

azumike

11-Dec-07 23:32

thanks for your inquiry,I have it, but, i want to select row without key on the table,
i mean that i want to select 1st row, 2nd row etc.

example i have table:
table name is: "person"

No | property
----------------------
1 | house
1 | name
1 | age

i want to select 2nd row but i don't wanna use :
select * from person
where property='name'

any other way ?

regards,
azumike

Re: how select row ?

Rohde11-Dec-07 23:39

Rohde

11-Dec-07 23:39

You cannot select a row from a database table without a unique identifier, i.e. a key. How else should the database know what to give you?

Re: how select row ?

azumike12-Dec-07 23:25

azumike

12-Dec-07 23:25

ok thank you very much

CONCATENATION problem!!?? (SQL 2000)

anthoy11-Dec-07 22:04

anthoy

11-Dec-07 22:04

help anyone!! phaleezz!!

i just want to combine the returned values(both varchar types)of my two 'user defined function' as one field in my sql statement

my codes like this..

<br />
select cast (UDF_getCellNos  + '/' + UDF_GetLandLines as varchar(100)) as Contact_Info from tblCrewInfo<br />

if both my UDF's returns a value..

my column 'Contact_Info' returns '09179673815 / (02)9614337'

works out fine..

but if either of my UDF's does not return a value...

my column 'Contact_Info' returns 'null'

it's supposed to return the value of the other UDF..
and thats my problem D'Oh! | :doh:

..is there any other way i can get my UDF's returned values as one column, even if one doesn't have a return value?

any help will be appreciated...
thanks in advance

Re: CONCATENATION problem!!?? (SQL 2000)

pmarfleet11-Dec-07 22:15

pmarfleet

11-Dec-07 22:15

Concatenating a NULL value with a string will always return NULL. Use the ISNULL function to convert NULL values to empty strings to prevent this happening.

Paul Marfleet

"No, his mind is not for rent
To any God or government"
Tom Sawyer - Rush

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.