|
Hi,
Quick question, I run an update statement to update a huge table then I click the Stop/Cancel button.
What happens? Does it roll back all previous transactions or does it leave the updated rows as is?
Thanks.
Rafferty
|
|
|
|
|
Its leave the updated rows. If you want to rollback all previous transactions, use the following before query:
Begin Tran
|
|
|
|
|
First, I would say that it should be easy for you to test that yourself.
Second, I just performed that test, and if the update is done by a single statement and that statement is canceled, it appears to be rolled back.
Scott
|
|
|
|
|
Hi Scott,
I did test it, and like you said it appears to be rolled back. Part of me just couldn't believe it so I asked here for more "documentation"-like answers 
But it does appear that if it's a single statement, the query analyzer will treat all the changes as part of one transaction.
Thanks.
Rafferty
|
|
|
|
|
Sorry, I didn't realize you had already tested it. At least our test results seem consistent.
I did finally find this in the Books Online:
Errors During Transaction Processing
If a severe error prevents the successful completion of a transaction, SQL Server automatically rolls back the transaction and frees all resources held by the transaction. If the client's network connection to SQL Server is broken, any outstanding transactions for the connection are rolled back when the network notifies SQL Server of the break. If the client application fails or if the client computer goes down or is restarted, this also breaks the connection, and SQL Server rolls back any outstanding connections when the network notifies it of the break. If the client logs off the application, any outstanding transactions are rolled back.
Scott
|
|
|
|
|
Wow, there's the answer I'm looking for! Thanks Scott!
Rafferty
|
|
|
|
|
Kindly let me know how to make updateble views on a table and how to apply Indexes on them??? i will be very thankful if anyone can give me the exact syntax for whole..
Thanks in Advance..
|
|
|
|
|
Creating an index on a view uses the normal CREATE INDEX syntax. For considerations, you should look at: Creating an Indexed View[^]
Also note that this feature is in Enterprise Edition only.
|
|
|
|
|
advantage and disadvantage of it??
thanks in advance.
|
|
|
|
|
In short, worker thread is the actual thread serving a call to the database.
Aman786Singh wrote: advantage and disadvantage of it
There's no choice in this. This is how SQL Server works.
|
|
|
|
|
How to get which process is deadlocking the system??
how to know current connections establish??
Thanks in Advance.
|
|
|
|
|
What is it and benefits of it???
Thanks in Advance.
|
|
|
|
|
It's designed to decrease context switches in multi CPU environment, thus giving a little bit better performance in certain, very specific situations. Normally there's no need to use it (or at least lots of optimizations should be done before this is considered).
For more info: The Perils of Fiber Mode[^]
|
|
|
|
|
Goodness, am I just thick or why can't I seem to get my head around GROUP BY!?
Let's use the Northwind DB as an example and let's say I want to join the Orders and Customers tables and return the first row for each customer. In other words, what I want is the first order that every customer placed, but inner joined to the customer table so that the customer's details is also returned.
This sounds simple enough but I just can't seem to make it work. The fact that I want the first order for each customer suggested to me that I should use something like SELECT TOP 1 * and have the GROUP BY CustomerID clause in there somewhere but then the result set would be rife with columns that are not contained in aggregate functions or the GROUP BY clause.
Would anyone care to offer advice here? I'm just using the Northwind DB as an example because I'm hoping that most people would be familiar with it but what I'm actually trying to achieve is to extract the first observation plus some details about the bird from a birdwatching database in which, roughly speaking, the Observations table would be equivalent to the Orders table and BirdList table would be equivalent to the Customers table.
|
|
|
|
|
GROUP BY isn't necessarily the easiest way to do this (although it's possible). Since you want to join two tables and when joining retrieve only the first row from the other table, I would use something else than GROUP BY (basically because I'm not calclulating anything, sums , averages, counts etc, but just restricting rows)
First version could be something like:
SELECT ...
FROM BirdList bl,
INNER JOIN
Observations o
ON bl.BirdId = o.BirdId
WHERE NOT EXISTS (SELECT 1
FROM Observations o2
WHERE o2.ObservationDate < o.ObservationDate
AND 02.BirdId = o.BirdId)
So fetch rows using normal join but then use correlated subquery test to check that there are no earlier records.
Didn't know the actual names of your columns so they are propably nonsense.
|
|
|
|
|
Excellent suggestions, thanks! I really should have thought of this before I even started to think along the lines of GROUP BY.
|
|
|
|
|
You're welcome 
|
|
|
|
|
Hi, I am planning to host my owns websites. and all are in .net with ms SQL, I want to by MS SQL 2005 but all about license in microsoft is so confusin, Question
If I want to Use the ms SQL only for websites, what version I should get?
Standard?
thanks
|
|
|
|
|
The licensing is a bit complicated. Why not contact microsoft sales or local dealer directly?
|
|
|
|
|
|
how to search amharic unicode data from sql server?
|
|
|
|
|
Have you tried using N character before strings? For example:
WHERE Column = N'text to search'
|
|
|
|
|
Hello EveryOne!!!
I am using Sql server 2005.
I have one table containing one of the column of type "DateTime".
Can i insert data like "Dec-08" in it ?.
and how ?
Thanks in advance.
DaTtA. 
|
|
|
|
|
Datetime data type always contains both date and time portion.
Depending how you want to use it, you can make a decision to use for example only month and year and always set day to 1. For example you can use CONVERT function to convert literal month with year to datetime. One example is:
SELECT CONVERT( datetime, '01-DEC-08', 6)
For more info on usage see: CAST and CONVERT[^]
|
|
|
|
|
Thanks a lot
Nice answer but suppose i want it in the form
if i want to insert 'DEC-08-2009' into DateTime then what should i do ?
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
