|
|
could someone please suggest to me a query i can run on the query analyzer to retrieve logical file name (physical file location) of a database?
|
|
|
|
|
SELECT filename FROM sysfiles
the last thing I want to see is some pasty-faced geek with skin so pale that it's almost translucent trying to bump parts with a partner - John Simmons / outlaw programmer
Deja View - the feeling that you've seen this post before.
|
|
|
|
|
can we give more than one fields as primary key in a table.if we can how we can give in sql server2000 by design view and how we can give unique key by design view
|
|
|
|
|
A table can have only one primary key. However, if you want to define primary key for multiple columns, you can select those columns using CTRL key and select "Set Primary Key" option.
|
|
|
|
|
Dear all coder,
How to reset the Identity of some column (PK), and the foreign key (FK) will be updated too as the new PK (identity column) with SQL Sript?
Thanks in advance.
Jati Indrayanto.
Everything is possible.
|
|
|
|
|
How do I reset the identity column?
--------------------------------------------------------------------------------
Answer:
You can use the DBCC CHECKIDENT statement, if you want to reset or reseed the identity column. For example, if you need to force the current identity value in the jobs table to a value of 100, you can use the following:
USE pubs
GO
DBCC CHECKIDENT (jobs, RESEED, 100)
GO
See DBCC CHECKIDENT in SQL Server Books Online for more details.
Regards,
Sylvester G
Senior Software Engineer
Xoriant Solutions
|
|
|
|
|
Thanks for your comment, Sir Sylvester.
If I use
DBCC CHECKIDENT(jobs, RESEED, 100)
It will affect right after when we insert new row right?
Conversely, I wish it will change the current values of PK'.
For example I have 2 tables
Parent Child
------ --------
pk (int identity) fk (int)
name (varchar) descr(varchar)
Suppose the values of PK are 1,9,10 (all has reference in Child)
Then, I wanna change the value of PK to (1,2,3) and cascade the value of FK in Child.
Is it possible to do that with built-in SP or function in SQL 2k5?
Or do I have to make manually?
Thanks In Advance.
Jati Indrayanto.
Everything is possible.
|
|
|
|
|
After I researched for 2 days, I assume that there's no way to reset the identity of a column (PK) and affect for each FK on other table at the same time. We have to do it manually...here's the example.
Scenario: I want to update the value of parent.pk (1,2,9,10 to become 1,2,3,4) and will reflect to
FK' values. But the problem arise is; I couldn't update ordinarily coz it's Identity.
So, here i'm trying to share the solution of my question was.
Parent
------------
pk (int, identity)
name (varchar)
Child
------------
child_pk(int)
parent_fk(int)
descr (varchar)
==========
9 STEPS
==========
--1. drop constraint of FK to PK (identity)
alter table child
drop constraint FK_child_parent
--2. drop PK of identity column
alter table parent
drop constraint pk_parent
--3. Add another column with same DT as Identity column to parent. and allow null
alter table parent
add new_pk int null
--4. update the new column with the values of the identity column
update parent
set new_pk = pk
--5. if the new column permit null, alter the column to not null
alter table parent
alter column new_pk int not null
--6. drop the identity column [IMPORTANT]
alter table parent
drop column pk
select identity(int, 1,1)
as pk, * into parent2
from parent
-- update the child table. update the FK value to the new value of PK in parent table
update child
set parent_fk = parent2.pk
from child, parent2
where child.parent_fk = parent2.new_pk
--drop the obsolete column
alter table parent2
drop column new_pk
-- drop the obsolete table
drop table parent
--7. Rename the table to the dropped table's name
exec sp_rename 'parent2', 'parent'
--8. if a primary key exists on the new column, recreate the key. In this case you recreate the
-- primary key on parent
alter table parent
add constraint pk_parent primary key (pk)
--9. On other tables. recreate any foreign keys that originally pointed to the old Identity col
-- and point them to the new column. here we recreate the foreign key on child
alter table child with nocheck
add constraint FK_child_parent
foreign key(parent_fk)
references parent(pk)
Jati Indrayanto.
Everything is possible.
|
|
|
|
|
Hi all,
I have a question about the performance of FIND function compared to the SELECT statement. I am developing a VC++ program that queries data from 60+ tables. Before today, I had used only 300 recordsets in the main table and didn't see any problem. Today I added 7000 entries to it, and the a dialog that queries 10 tables (including the main table) took almost 2 seconds to show the result! I use FindFirst/FindNext on all the queries.
Will converting Find to SELECT/WHERE statement help a lot here?
My spec: SQL Express 2005, Pentium4D, 1GB RAM.
Any comments will be appreciated here. Thanks in advance.
|
|
|
|
|
Changing would probably help, but since you are under two seconds the difference would be negligible IMO.
_________________________________________________________________
Dick Cheney looks like a high school calculus teacher with persistent heartburn.
|
|
|
|
|
But this is under 2secs for entries<10K. I am expecting this to grow close to 1 million, wouldn't the delay grow even more then?
|
|
|
|
|
Ofcourse it will grow. Just use the Select/Where method. But you should have the right indexes! This way you can query a table with bilions of records with a 2 second response time.
But I do not know what kind of application you are talking about. Maybey it is wise to combine the two techniques.
Wout Louwers
|
|
|
|
|
yes, indexing and normalizing your tables will really help.
_________________________________________________________________
Dick Cheney looks like a high school calculus teacher with persistent heartburn.
|
|
|
|
|
My data is normalized, that's why i have 60+ table for a relatively simple inventory management database.
And if by indexing you mean having Primary Keys, then I have those on the tables that need them.
|
|
|
|
|
Indexing does not mean having Primary Keys. Indexing means creating the right indexes for the queries you perform on the tables.
Wout Louwers
|
|
|
|
|
Hi
Ive got a program that connects and converts an excel file into a plain text file
The problem is that this program works fine on certain pc’s
But on other pc’s it give the following message
Object invalid or no longer set
The strange thing is that this source code has been working fine for years
Pls help
im a junior developer at a company called securemail in south africa
|
|
|
|
|
You need to move this question to the VB message board. Please also include your source code.
_________________________________________________________________
Dick Cheney looks like a high school calculus teacher with persistent heartburn.
|
|
|
|
|
Hi,
Programatically, I want to creat table space on remote oracle server, but don't know which path should i provide for data file, is there any one can help?
Warm Regards,
Mushq
|
|
|
|
|
Hi Friends,
I want to know is there any way i can use SQL profiler to MONITOR all queries RUN on a PARTICULAR table. I see that there is a way to tracks all queries run a particular DATABASE, but i couldn't find a way to trace queries run on a PARTICULAR table in a database.
Even though there is no direct way a Work around suggestion would be great.
Thanks a lot for ur help..
Regards,
Leela Krishna
|
|
|
|
|
Set a filter on the TextData to be Like %table%.
the last thing I want to see is some pasty-faced geek with skin so pale that it's almost translucent trying to bump parts with a partner - John Simmons / outlaw programmer
Deja View - the feeling that you've seen this post before.
|
|
|
|
|
Hi Pete,
Thanks a lot.. Will try that..
Regards,
Leela Krishna
|
|
|
|
|
Hi all,
What is the query to get the details of an employee whose sal is the second highest salary among all the epmloyees from Employee table by using SQL Server and Oracle. Thank you.
Regards,
S/W Engineer
Akebono Soft Technologies
aleem_abdul@akebonosoft.com.
|
|
|
|
|
This is the type of query that you should be looking at.
SELECT TOP 1 * FROM Employee a WHERE a.Salary IN (
SELECT TOP 2 b.Salary FROM Employee b
ORDER By b.Salary DESC )
ORDER BY a.Salary ASC
the last thing I want to see is some pasty-faced geek with skin so pale that it's almost translucent trying to bump parts with a partner - John Simmons / outlaw programmer
Deja View - the feeling that you've seen this post before.
|
|
|
|
|
Hey man this will work for both sql server and oracle
This is 100% pure SQL
select max(salary) from employee
where salary<(select max(salary) from employee)
Regards,
Sylvester G
Senior Software Engineer
Xoriant Solutions
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
![Rose | [Rose]](https://codeproject.freetls.fastly.net/script/Forums/Images/rose.gif)
