|
CTE is one possibility. Curious to know what makes the example horrible? Note that the insert in the example isn't using a persistent table but a table-type which is quite different.
|
|
|
|
|
Quick and dirty trial. Not particularly scientific but gives you some idea. On my local instance of SQL Server, this script (note the long time frame to bump the execution time to something measurable):
DECLARE @start DATETIME = '1950-01-01'
DECLARE @end DATETIME = '2011-03-31'
;WITH dateRange (date)
AS
(
SELECT
@start Date
UNION ALL
SELECT
DATEADD(day, 1, Date) Date
FROM
dateRange
WHERE
Date < @end
)
SELECT * FROM dateRange WHERE DATEPART(WEEKDAY,date) = 1
OPTION (MAXRECURSION 32767);
returns immediately (<1sec)
Whereas this script:
DECLARE @start DATETIME = '1950-01-01'
DECLARE @end DATETIME = '2011-03-31'
DECLARE @dates TABLE (date DATETIME)
WHILE (@start <= @end)
BEGIN
INSERT INTO @dates VALUES (@start);
SET @start = DATEADD(day, 1, @start);
END;
SELECT * FROM @dates WHERE DATEPART(WEEKDAY,date) = 1
is still running 1min25seconds later after typing this message (Edit: Final execution time: 7m4s).
Check the actual execution plans for each to see why one is "horrible".
modified on Thursday, March 24, 2011 6:01 AM
|
|
|
|
|
Thanks for the info. Wondering why your test takes so long. I ran the same statements and both execution times were <1 sec... Also the plan seemed quite good...
|
|
|
|
|
|
One possible reason could be if you have either statistics io or statistics time on in Management Studio. This would cause a flood in the messages tab since every insert is reported separately to Studio. This, of course, won't happen with CTE which is handled as a single statement thus reporting only one statistics.
|
|
|
|
|
On SQL 2008 you can use a Common Table Expression to generate a recursive list. You can then use the DATEPART function to select only sundays from this list:
DECLARE @start DATETIME = '2011-01-01'
DECLARE @end DATETIME = '2011-03-31'
;WITH dateRange (date)
AS
(
SELECT
@start Date
UNION ALL
SELECT
DATEADD(day, 1, Date) Date
FROM
dateRange
WHERE
Date < @end
)
SELECT * FROM dateRange
WHERE DATEPART(WEEKDAY,date) = 1 -- 1 is Sunday
|
|
|
|
|
Hi, I have two int columns in a table that have a sample data as below
and I need to update these columns with
1).If ColA has a non negative number then update ColB with 0 (zero).
2).If ColA has a negative number then update ColB with the same value
that ColA has.
Can some please help me with sql?
Thanks in advance.
L
-------------------------
ColA | ColB
-----------|------------
-1 -1
-1 -1
-3 -1
4 -1
-1 -1
7 -1
3 -1
73 -1
-3 -1
39 -1
32 -1
35 -1
3 -1
31 -1
3 -1
397 -1
400 -1
403 -1
406 -1
-1 -1
412 -1
436 -1
421 -1
421 -1
424 -1
-1 -1
430 -1
433 -1
-1 -1
415 -1
442 -1
445 -1
-1 -1
-3 -1
487 -1
523 -1
568 -1
472 -1
475 -1
478 -1
-3 -1
490 -1
493 -1
496 -1
502 -1
505 -1
511 -1
514 -1
517 -1
520 -1
-1 -1
526 -1
538 -1
541 -1
544 -1
556 -1
559 -1
550 -1
457 -1
574 -1
-1 -1
580 -1
583 -1
586 -1
589 -1
595 -1
625 -1
604 -1
625 -1
610 -1
-1 -1
622 -1
625 -1
-1 -1
-1 -1
637 -1
-1 -1
649 -1
652 -1
655 -1
658 -1
661 -1
664 -1
667 -1
670 -1
673 -1
-1 -1
685 -1
688 -1
691 -1
697 -1
700 -1
703 -1
709 -1
712 -1
715 -1
718 -1
721 -1
-1 -1
-3 -1
-1 -1
-1 -1
-1 -1
-1 -1
|
|
|
|
|
I would use two Update statements.
Begin Transaction
update MyTable
set ColB = 0
where ColA > 0
update MyTable
set ColB = ColA
where ColA < 0
By using a transaction you can review the number of rows affected and decide whether you want to
Commit or Rollback the transaction.
><david><
|
|
|
|
|
What if ColA == 0?
I may or may not be responsible for my own actions
|
|
|
|
|
One version is:
UPDATE TableName
SET ColB = CASE
WHEN ColA < 0 THEN 0
ELSE ColA
END;
Correction based on the good feedback by musefan:
UPDATE TableName
SET ColB = CASE
WHEN ColA >= 0 THEN 0
ELSE ColA
END
The need to optimize rises from a bad design.My articles[^]
modified on Wednesday, March 23, 2011 12:16 PM
|
|
|
|
|
I think you misread the requirement (even thou you got an accepted answer), ColB should be 0 for NON-negative numbers
I may or may not be responsible for my own actions
|
|
|
|
|
You're right for non-negative it's vice versa. I think the OP still got the idea.
|
|
|
|
|
UPDATE table SET colB = DECODE( SIGN( colA ), -1, colA, 0 )
|
|
|
|
|
Reading excel file with OPENROWSET of sql server.
Working fine and getting values.
Now, if the file which i am reading is in use (opened), it gives error.
How should i capture this error, I tried
Declare @error varchar(50)
SELECT *
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=\\webex\cis\CIS overview.xls',
'SELECT * FROM [CIS Overview$]')
IF(@@error <> 0 )
BEGIN
Set @error = 'Error reading file'
RETURN
END
But its not even reaching to IF clause,
terminate giving message
OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0' IDBInitialize::Initialize returned 0x80004005: The provider did not give any information about the error.].
Msg 7399, Level 16, State 1, Line 3
OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error. The provider did not give any information about the error.
How can i capture this error ?
reagards
|
|
|
|
|
|
Any other Light ?
As i am using sql 2000
|
|
|
|
|
Okay, and you're sure the IF is not hit at all... One thing you could try is to call this procedure from another and check if you can handle the error properly in the calling procedure.
|
|
|
|
|
Mika Wendelius wrote: you're sure the IF is not hit at all...
Well instead of if you can use PRINT, if control reaches to print it should print.
But execution terminates on OPENROWSET itself.
Mika Wendelius wrote: call this procedure from another and check if you can handle the error properly in the calling procedure.
No luck !
in a new query window i tried like
EXEC [spName]
IF(@@error <> 0)
BEGIN
Print 'No error'
END
ELSE
BEGIN
Print 'error'
END
here also not going to if clause !
Any other suggestion?
|
|
|
|
|
Hmm, what happens if you try to execute the statement dynamically. Put it into a string and then use exec to run the statement. Unfortunately I don't have SQL Server 2000 to test this issue...
|
|
|
|
|
Hi,
I am trying to implement a dialog for updating some data in a DB. The OK button should actually save the data to the DB, while the Cancel button should discard the changes. The problem is, I need to be able to read the changed but not yet committed data within the dialog. I have read much information about transactions, isolation levels etc. and if I understand it correctly, the only way to achieve this is to wrap all the changes in a single transaction and use the READUNCOMMITED isolation level for it. Then in the OK button handler I'd just commit the transaction and in the Cancel button handler I'd call rollback.
However, since I am using the SQLite.NET library, I am only able to use READCOMMITTED or SERIALIZABLE levels. What's more, in some other piece of software I have which uses MS Access database and solves exactly the same problem the transaction is started with the READCOMMITED isolation level and everything works as intended. That really puzzles me.
Do you have any suggestions/advice for this kind of problem?
|
|
|
|
|
Perhaps I misunderstand your question, but the whole idea of using a dialog is to let the user determine the variables.
Once this is done, it's easy enough to validate the input in your code, before even creating a query.
My advice is free, and you may get what you paid for.
|
|
|
|
|
The dialog doesn't just show some variables. It lists (complex) objects from the database and should allow adding/deleting/editing them. When he adds a new object he might decide to edit it before leaving the dialog, so I need to be able to store its properties somewhere. Of course I could hold the references to the new objects in my code, but I thought the most elegant way would be to store them in the DB immediately and access them in a unified way. Of course I still need the option to roll back all the changes when the user hits Cancel.
|
|
|
|
|
Well, the basic choice is to keep all the data in client memory, or server memory, or write the whole thing to disk, right?
If you have lots of clients sending transactions to a database server at the same time, I'd try and do all the work on the client.
If the user might use a lot of time performing a transaction, a simple and robust solution could be to store the whole transaction in a temporary table (or structure of tables). That way you can perform some of the validation directly on input in your app, and then when the user clicks ok, you can still run some validation first between transactions from different users, and then commit the transaction to the live tables after that. If the user clicks cancel just delete the object from the temporary table.
An added advantage of this method, is that you can think about offering the user "template" or "favorite" transactions.
My advice is free, and you may get what you paid for.
|
|
|
|
|
Sure, it's always an option (albeit a cumbersome one) to store all the changes in the client code until the user hits OK or Cancel. I just wanted to know whether it is possible to set a "restore point" and later roll back all the changes up to that point. The database is single-user, so there won't be any read/write conflicts.
|
|
|
|
|
The first rule of transactions is: Never have a conversation with the user while a transaction is active!
So what you should do is to ask all the necessary questions before you start the db operations, perhaps show what's in the database now and how it's going to be changed etc. After the last yes/no question, then do all the operations at once and if everything goes fine, commit otherwise rollback.
In my opinion (I know many disagree) Read uncommitted should be banned for good. In a well designed system there's no need for RU isolation level and as you mentioned many of the database products don't even support it (inlcude Oracle in your list).
Having UI conversations etc while you have placed locks in the database typically causes prolonged transactions, slows the system down and decreases parallelism because of locking issues.
|
|
|
|