Hi,
I need to alter a column in a database table from varchar(5) to varchar(11). However, the tables involved are having primary key-foreign key contraints. How do I alter the columns. There is one obvious solution to disable all constraints and then alter the column. But are any ALTER TABLE CASCADE options available in SQL Server 2005 so that the child columns automatically get altered when the parent field is alterd. Please also let me know...
qhjghz
Last Activity: Mar 27 '08, 07:58 AM
Joined: Aug 27 '07
Location:
-
Aler tables in CASCADE Mode
-
The problem is that the application isn't ready to wait for the BULK UPLOAD to finish. So, I wanted a transaction isolation level where dirty read won't occur, but the application should not hang at the same also... -
The performance will be very poor for this sql....Leave a comment:
-
Well, the scenario is a bit different. The application is up and running 24*7, but the batch loading is via Informatica. The loading is done in a single transaction. I have no access to this feed process. But, suppose, while the data is getting loaded, the application fires a query on the particular row which is uncommitted, then the application comes to a standstill. It would have been much better for me if dirty reads could have been avoided and...Leave a comment:
-
Yes .. it was exactly the test I performed. But it surprised me. I expected that the I should get the snapshot of the database before the transaction began. But the select statement comes to a standstill. This is not what I expected and not a favorable behaviour also (IMHO)...Leave a comment:
-
DELETE FROM table_name where column_name in
(select column_name from table_name GROUP BY column_name HAVING COUNT(*)>1)...Leave a comment:
-
This is the best thing to do from performance point of view. But, however, if you can afford for a table scan then you can use this method
select * from
table_name a
where
CASE WHEN (@variable is null) THEN 1 ELSE
CASE WHEN a.column_name = @variable THEN 1 ELSE 0 END
END =1...Leave a comment:
-
Transaction Isolation Level
Hi All,
The transaction isolation level for my stored procedure is READ COMMITTED. As far as I read, in this isolation level, "In effect, a SELECT query sees a snapshot of the database as of the instant that that query begins to run." But, I observed a deviation from this. In my session, I am explicitly starting a transaction and inserting a row into the Property table (say). This property belongs to the City Maine(say).... -
Sending Mail
Hi,
I want to send mails from my desktop machine using SQL Server 2005. The server is on my machine. I have found out from Internet the necessary stored procedure we would need to send the mail. But when I am executing the oprocedure, I am getting no error, but no mail is being sent. Are there any configuration tasks I have to perform to send a mail fromSQL Server apart from opening the Port Number 25? -
I won't suggest the queries you have given .. Just think what extra are you doing with the HAVING caluse which you could not have done with your WHERE clause. Nothing. Don't use HAVING in this regard. It affects performance. But see the query I posted. There was no alternative way to do it via WHERE clause because it was FILTERING BASED ON THE AGGREGATE(ie the count). The filtering was NOT ROW BY ROW in that query. REMEMBER THAT HAVING IS AN AGGREGATE...Leave a comment:
-
I won't suggest the queries you have given .. Just think what extra are you doing with the HAVING caluse which you could not have done with your WHERE clause. Nothing. Don't use HAVING in this regard. It affexts performance. But see the query I posted....Leave a comment:
-
Locking
I have two tables say A_prod & A_staging. Now, I need to insert those rows in A_prod which are present in A_staging but not in A_prod. We can do this in chiefly two ways :-
1. We can simply insert the rows from A_staging to A_prod in an insert into select statement (but that would need to compare the two tables for non-matching values while performing the insert, thereby locking the table)
2. We can find out those... -
Thankx for your reply...Leave a comment:
-
Thankx for your reply...Leave a comment:
-
Thanks for your answer again. Can you please answer this one also? I have already pasted this as a separate query
Need to know how does one find out the triggers present. I have executed this query to find it out
select * From sysobjects where xtype='TR'
It gives me the list, but I just want to know if there are any limitations of this query. Are there any better methods to find out the triggers?...Leave a comment:
-
This is the solution:
select a.empno, a.ename, a.deptno, a.sal,b.max_sal ,
dense_rank() over (partition by a.deptno order by a.sal) dept_rank
from emp a,(select deptno,max(sal) max_sal from emp group by deptno) b
where
a.deptno=b.dept no
ORDER BY 5 DESC...Leave a comment:
-
select empno,count(*) from emp group by empno having count(empno)>1
This query finds out the rows in emp with same empno...Leave a comment:
-
I think the best place to read about indexes is Tom Kite's Expert Oracle Database Architecture...Leave a comment:
-
Do you know beforehand what are the possible USER_IDs present, then you can do use the count & decode easily....Leave a comment:
-
Joins or Temporary tables
Hi,
Am new to SQL Server. I have heard from "them" that in SQL Server it is better to temporarily store the result of 2 or more joins in a #table and then use it to join it with the other tables. Being from Oracle, I don't find it very logical. Can someone please help and explain...
No activity results to display
Leave a comment: