Metadata Services: feature

Showing posts with label feature. Show all posts

Tuesday, March 20, 2012

no "UNDO" feature in SQL? So what do people do to undo mistakes?

Given that there are is no "UNDO" feature in SQL, says my textbook,
what are the best practices in SQL to avoid making a irreversible
mistake? For example, backup your database prior to modifying it with
an UPDATE command, or, make a copy of a table with SELECT * prior to
modifying a table?
RL"raylopez99" <raylopez99@.yahoo.com> wrote in message
news:1185342099.040833.177280@.d55g2000hsg.googlegroups.com...
> Given that there are is no "UNDO" feature in SQL, says my textbook,
> what are the best practices in SQL to avoid making a irreversible
> mistake? For example, backup your database prior to modifying it with
> an UPDATE command, or, make a copy of a table with SELECT * prior to
> modifying a table?
> RL
>
The answer depends on the context. If you are performing some one-off
maintenance task then do it in a transaction and verify the results
immediately. Only commit if the test is good. Of course you would always
have a backup anyway wouldn't you?
In an application context you might decide to preserve a history of changes
or just rely on FULL RECOVERY and point-in-time restore.
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||On Jul 24, 11:10 pm, "David Portas"
<REMOVE_BEFORE_REPLYING_dpor...@.acm.org> wrote:
> The answer depends on the context. If you are performing some one-off
> maintenance task then do it in a transaction and verify the results
> immediately. Only commit if the test is good. Of course you would always
> have a backup anyway wouldn't you?
> In an application context you might decide to preserve a history of changes
> or just rely on FULL RECOVERY and point-in-time restore.
>
Thanks. Never heard of FULL RECOVERY and I'll do more research on it;
I expect MS Visual Studio and MS SQL Server 2005 to support it.
In any event, SQL the Dinosaur Macro Language needs an "undo" feature
IMO (as well as a feature telling you how exactly your query was
performed--for example Indexes are not even implemented up front but
'behind the scenes', but that's a rant for another day.
RL
You must not confuse the recovery model with functionailty and what it
will allow
you to. The full recovery model is exactly what it says it is. It's to
do with
your disaster recovery planning. If you run your database in full
recovery mode
then you will log as much data as SQL will let you to the transaction
logs.
However, you should only do this when you are maintaining an unbroken
sequence of
transaction log backups. There would be no point in logging everything
to the
transaction log is anything else could truncate that log file.
Restarting SQL will
therefore not truncate your logs. BOL does explain this pretty well I
believe,
although I think it remains ambiguous as to whether the full backup
would truncate
it. It does not. Only a Log Backup will clear down the inactive
portion of the log
in Full mode. Full recovery does not restrict the commands you can
run, it's
simply for disaster planning.
Cheers
Nick Hall Senior Database Administrator|||> Thanks. Never heard of FULL RECOVERY and I'll do more research on it;
> I expect MS Visual Studio and MS SQL Server 2005 to support it.
Yes, they do. Read up on BACKUP DATABASE, BACKUP LOG, RESTORE DATABASE and RESTORE LOG.
> In any event, SQL the Dinosaur Macro Language needs an "undo" feature
There already is. BEGIN, COMMIT and ROLLBACK TRANSACTION. There are also various SET settings to
influence behavior, like SET IMPLICIT TRANSACTION and SET XACT_ABORT.
> (as well as a feature telling you how exactly your query was
> performed--for example Indexes are not even implemented up front but
> 'behind the scenes', but that's a rant for another day.
I'm not sure what you mean by the "behind the scenes" comment, but there are many features and
option to investigate the execution plan and query cost.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"raylopez99" <raylopez99@.yahoo.com> wrote in message
news:1185346012.395305.5730@.q75g2000hsh.googlegroups.com...
> On Jul 24, 11:10 pm, "David Portas"
> <REMOVE_BEFORE_REPLYING_dpor...@.acm.org> wrote:
>> The answer depends on the context. If you are performing some one-off
>> maintenance task then do it in a transaction and verify the results
>> immediately. Only commit if the test is good. Of course you would always
>> have a backup anyway wouldn't you?
>> In an application context you might decide to preserve a history of changes
>> or just rely on FULL RECOVERY and point-in-time restore.
> Thanks. Never heard of FULL RECOVERY and I'll do more research on it;
> I expect MS Visual Studio and MS SQL Server 2005 to support it.
> In any event, SQL the Dinosaur Macro Language needs an "undo" feature
> IMO (as well as a feature telling you how exactly your query was
> performed--for example Indexes are not even implemented up front but
> 'behind the scenes', but that's a rant for another day.
>
> RL
>
> You must not confuse the recovery model with functionailty and what it
> will allow
> you to. The full recovery model is exactly what it says it is. It's to
> do with
> your disaster recovery planning. If you run your database in full
> recovery mode
> then you will log as much data as SQL will let you to the transaction
> logs.
> However, you should only do this when you are maintaining an unbroken
> sequence of
> transaction log backups. There would be no point in logging everything
> to the
> transaction log is anything else could truncate that log file.
> Restarting SQL will
> therefore not truncate your logs. BOL does explain this pretty well I
> believe,
> although I think it remains ambiguous as to whether the full backup
> would truncate
> it. It does not. Only a Log Backup will clear down the inactive
> portion of the log
> in Full mode. Full recovery does not restrict the commands you can
> run, it's
> simply for disaster planning.
> Cheers
> Nick Hall Senior Database Administrator
>|||On Jul 25, 6:30 am, "Tibor Karaszi"
<tibor_please.no.email_kara...@.hotmail.nomail.com> wrote:
> > Thanks. Never heard of FULL RECOVERY and I'll do more research on it;
> > I expect MS Visual Studio and MS SQL Server 2005 to support it.
> Yes, they do. Read up on BACKUP DATABASE, BACKUP LOG, RESTORE DATABASE and RESTORE LOG.
> > In any event, SQL the Dinosaur Macro Language needs an "undo" feature
> There already is. BEGIN, COMMIT and ROLLBACK TRANSACTION. There are also various SET settings to
> influence behavior, like SET IMPLICIT TRANSACTION and SET XACT_ABORT.
> > (as well as a feature telling you how exactly your query was
> > performed--for example Indexes are not even implemented up front but
> > 'behind the scenes', but that's a rant for another day.
> I'm not sure what you mean by the "behind the scenes" comment, but there are many features and
> option to investigate the execution plan and query cost.
> --
> Tibor Karaszi, SQL Server MVPhttp://www.karaszi.com/sqlserver/default.asphttp://sqlblog.com/blogs/tibor_karaszi
> "raylopez99" <raylope...@.yahoo.com> wrote in message
> news:1185346012.395305.5730@.q75g2000hsh.googlegroups.com...
> > On Jul 24, 11:10 pm, "David Portas"
> > <REMOVE_BEFORE_REPLYING_dpor...@.acm.org> wrote:
> >> The answer depends on the context. If you are performing some one-off
> >> maintenance task then do it in a transaction and verify the results
> >> immediately. Only commit if the test is good. Of course you would always
> >> have a backup anyway wouldn't you?
> >> In an application context you might decide to preserve a history of changes
> >> or just rely on FULL RECOVERY and point-in-time restore.
> > Thanks. Never heard of FULL RECOVERY and I'll do more research on it;
> > I expect MS Visual Studio and MS SQL Server 2005 to support it.
> > In any event, SQL the Dinosaur Macro Language needs an "undo" feature
> > IMO (as well as a feature telling you how exactly your query was
> > performed--for example Indexes are not even implemented up front but
> > 'behind the scenes', but that's a rant for another day.
> > RL
> > You must not confuse the recovery model with functionailty and what it
> > will allow
> > you to. The full recovery model is exactly what it says it is. It's to
> > do with
> > your disaster recovery planning. If you run your database in full
> > recovery mode
> > then you will log as much data as SQL will let you to the transaction
> > logs.
> > However, you should only do this when you are maintaining an unbroken
> > sequence of
> > transaction log backups. There would be no point in logging everything
> > to the
> > transaction log is anything else could truncate that log file.
> > Restarting SQL will
> > therefore not truncate your logs. BOL does explain this pretty well I
> > believe,
> > although I think it remains ambiguous as to whether the full backup
> > would truncate
> > it. It does not. Only a Log Backup will clear down the inactive
> > portion of the log
> > in Full mode. Full recovery does not restrict the commands you can
> > run, it's
> > simply for disaster planning.
> > Cheers
> > Nick Hall Senior Database Administrator
I'm glad someone mentioned this. Yes, as good as SQL is at doing it's
own transactions you CAN tell it when to BEGIN, COMMIT, and ROLLLBACK
transactions. Rolling back a trasaction will take you to the point
before the begin tansaction was started which, in effect, is the
"undo" you're asking about.

no "UNDO" feature in SQL? So what do people do to undo mistakes?

Monday, March 19, 2012

Nice to have feature

If you ask ANY developer that has worked with both MySQL and MS SQL, every single one of them will say why hasn't microsoft given MS SQL the capability to do LIMITS as such in MySQL. I'm not impressed by any of the stupid work arounds either... they run heavy and crapout under a large record load.

Of course there are several other attributes that MS SQL has that MySQL can't offer, but again, MySQL is FREE and PAID tools should have all that a free competitor has plus some.

Is there any planned action to remedy this absurd oversight?

Could you please post a suggestion for this on http://connect.microsoft.com/sqlserver? Suggestions (and defect reports) filed on the Connect site go directly into our internal issue tracking system so we don't lose them. Suggestions from customers carry extra weight with the development team when we are prioritizing future work.

I'm afraid I haven't used MySQL, so I have no idea what "LIMIT" does. Is "LIMIT" like the ANSI syntax "SELECT TOP n ..."? If you could explain what you're looking for in terms of functionality, I might be able to help you out.

Thanks,
Steve

|||

Thank you for the url to post this fix suggestion, i will do that.

A limit is also known as a 'Range Result'. A 'must have' for pagination in any sort of program/database development to decrease the need to process, handle and bandwidth requirements to handle unnecessary large datasets. (As produced by 'TOP n' queries) Especially when dealing with hundreds of thousands of records.

Definition: Limit is used to limit your MySQL query results to those that fall within a specified range. You can use it to show the first X number of results, or to show a range from X - Y results. It is phrased as Limit X, Y and included at the end of your query. X is the starting point (remember the first record is 0) and Y is the duration (how many records to display).

SELECT * FROM `your_table` LIMIT 0, 10

This will display the first 10 results from the database.

SELECT * FROM `your_table` LIMIT 5, 5

This will show records 6, 7, 8, 9, and 10

I highly recommend that everyone trying out other SQL servers to not get stuck in the MS paradigm.

Let me know if you have any other questions regarding LIMITS.

|||

you have to roll your own sproc logic to do that in SQL Server.

That would be a very nice to have feature for future releases.

MySQL definately has some nice advantages.

Cheers,

|||

Have you looked at the new ROW_NUMBER syntax in SQL Server 2005?

USE AdventureWorks;
GO
WITH OrderedOrders AS
(
SELECT SalesOrderID, OrderDate,
ROW_NUMBER() OVER (ORDER BY OrderDate) AS 'RowNumber'
FROM Sales.SalesOrderHeader
)
SELECT *
FROM OrderedOrders
WHERE RowNumber BETWEEN 50 AND 60;

http://msdn2.microsoft.com/en-us/library/ms186734.aspx

Paul A. Mestemaker II
Program Manager
Microsoft SQL Server Manageability
http://blogs.msdn.com/sqlrem/

|||true... that is a work around for a STORED PROC. however, i'm not super interested in stored procs and again, it's a work around.|||Its a workaround until you get a straight solution

|||

Actually, no... this isn't a stored proc. All of that code can be performed inline as ad-hoc SQL. I believe the term for this is a "Common Table Expression". You can do other cool stuff with it like recursive queries.

WITH common_table_expression (Transact-SQL):
http://msdn2.microsoft.com/en-us/library/ms175972.aspx

Paul A. Mestemaker II
Program Manager
Microsoft SQL Server Manageability
http://blogs.msdn.com/sqlrem/

Nice feature for next SQL server version

Wouldn't it be nice if SQL build statistics which automatically would help
the system create indexes at runtime?
It defenitely would help lazy programmers :)I would add some statistics to help us know whether indexes are used or not (so we can drop the
indexes not being used). Sounds like a case for sqlwish@.microsoft.com. :-)
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Egbert Nierop (MVP for IIS)" <egbert_nierop@.nospam.com> wrote in message
news:%23$i%23RbCtDHA.1884@.TK2MSFTNGP10.phx.gbl...
> Wouldn't it be nice if SQL build statistics which automatically would help
> the system create indexes at runtime?
> It defenitely would help lazy programmers :)
>|||I take it you're aware of the Index Tuning Wizard which semi-automates the
process of identifying useful indexes and creating them.
--
David Portas
--
Please reply only to the newsgroup
--
"Egbert Nierop (MVP for IIS)" <egbert_nierop@.nospam.com> wrote in message
news:%23$i%23RbCtDHA.1884@.TK2MSFTNGP10.phx.gbl...
> Wouldn't it be nice if SQL build statistics which automatically would help
> the system create indexes at runtime?
> It defenitely would help lazy programmers :)
>|||How often have you actually gotten something useful out of the Index
Tuning Wizard? I try it from time to time when I'm stumped and 99% of the
time it ends up either not being able to do anything with the query or
recommends a gigantic composite index that slows performance... Very
annoying.
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:88WdnexFd47lWFmiRVn-sQ@.giganews.com...
> I take it you're aware of the Index Tuning Wizard which semi-automates the
> process of identifying useful indexes and creating them.
> --
> David Portas
> --
> Please reply only to the newsgroup|||> How often have you actually gotten something useful out of the Index
> Tuning Wizard?
Errr.. never. I've hardly ever used it. I expect it can give useful results
if supplied with a suitable Profiler trace but I expect most full-time
administrators and designers prefer to do this stuff for themselves based on
business requirements and their own research.
I don't think generating indexes at "runtime" could offer anything better.
Creating indexes without the opportunity for review by the
administrator/designer sounds pretty dangerous to me - if that's what the OP
was suggesting.
--
David Portas
--
Please reply only to the newsgroup
--|||Yeah! Something like a usage counter for each index would be great.
--
Linchi Shea
linchi_shea@.NOSPAMml.com
"Tibor Karaszi" <tibor.please_reply_to_public_forum.karaszi@.cornerstone.se>
wrote in message news:%23gTl40CtDHA.684@.TK2MSFTNGP09.phx.gbl...
> I would add some statistics to help us know whether indexes are used or
not (so we can drop the
> indexes not being used). Sounds like a case for sqlwish@.microsoft.com. :-)
> --
> Tibor Karaszi, SQL Server MVP
> Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
>
> "Egbert Nierop (MVP for IIS)" <egbert_nierop@.nospam.com> wrote in message
> news:%23$i%23RbCtDHA.1884@.TK2MSFTNGP10.phx.gbl...
> > Wouldn't it be nice if SQL build statistics which automatically would
help
> > the system create indexes at runtime?
> > It defenitely would help lazy programmers :)
> >
>|||"Egbert Nierop (MVP for IIS)" <egbert_nierop@.nospam.com> wrote in message
news:%23$i%23RbCtDHA.1884@.TK2MSFTNGP10.phx.gbl...
> Wouldn't it be nice if SQL build statistics which automatically would help
> the system create indexes at runtime?
I'd absolutely hate it. Well building a table of suggested indexes might be
nice, but making the change automagically would introduce more problems than
it was worth IMO.
> It defenitely would help lazy programmers :)
Niall Litchfield
Oracle DBA
Audit Commission UK|||"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:88WdnexFd47lWFmiRVn-sQ@.giganews.com...
> I take it you're aware of the Index Tuning Wizard which semi-automates the
> process of identifying useful indexes and creating them.
I do not agree quite.
I'd use it personally on projects, but the problem is I DO NOT manage the
SQL server, so I should instruct the DBADMIN to optimize or start an
optimization session. Of course, I include indexes at design but I might
forget some of them and more, I have some sort of SQL stored procedures,
which can sort and filter on ANY column. So if the -client- uses a lot of
queries of a specific type, SQL Server should decide at a certain defined
point, to add the index.
It would be unwise of me to add indexes for all possible combinations on
deployment.
> --
> David Portas
> --
> Please reply only to the newsgroup
> --
> "Egbert Nierop (MVP for IIS)" <egbert_nierop@.nospam.com> wrote in message
> news:%23$i%23RbCtDHA.1884@.TK2MSFTNGP10.phx.gbl...
> > Wouldn't it be nice if SQL build statistics which automatically would
help
> > the system create indexes at runtime?
> > It defenitely would help lazy programmers :)
> >
>|||"Niall Litchfield" <n-litchfield@.audit-commission.gov.uk> wrote in message
news:3fc5ee5a$0$9383$ed9e5944@.reading.news.pipex.net...
> "Egbert Nierop (MVP for IIS)" <egbert_nierop@.nospam.com> wrote in message
> news:%23$i%23RbCtDHA.1884@.TK2MSFTNGP10.phx.gbl...
> > Wouldn't it be nice if SQL build statistics which automatically would
help
> > the system create indexes at runtime?
> I'd absolutely hate it. Well building a table of suggested indexes might
be
> nice, but making the change automagically would introduce more problems
than
> it was worth IMO.
Allrighty,
in case we can define withhold parameters for auto-indexing and such it
would be no problem. The designer still has to have control over it but the
designer should not be too dependend on the dbadmin for helping or
co-operating on optimizing (and vice-versa)|||"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:tPGdne5sW-tVTVmiRVn-tA@.giganews.com...
> > How often have you actually gotten something useful out of the Index
> > Tuning Wizard?
> Errr.. never. I've hardly ever used it. I expect it can give useful
results
> if supplied with a suitable Profiler trace but I expect most full-time
> administrators and designers prefer to do this stuff for themselves based
on
> business requirements and their own research.
> I don't think generating indexes at "runtime" could offer anything better.
> Creating indexes without the opportunity for review by the
> administrator/designer sounds pretty dangerous to me - if that's what the
OP
> was suggesting.
Again, I do not suppose that SQL blindly will do that, I suggest a feature
that can be enabled per table, so if a table consists of 20 columns,
potentially, it could create 20 or more (if more columns are combined)
indexes SQL should decide at some points whether or not to create one.
> --
> David Portas
> --
> Please reply only to the newsgroup
> --
>|||"Egbert Nierop (MVP for IIS)" <egbert_nierop@.nospam.com> wrote in message
news:eOIchwxvDHA.2180@.TK2MSFTNGP09.phx.gbl...
> "Niall Litchfield" <n-litchfield@.audit-commission.gov.uk> wrote in message
> news:3fc5ee5a$0$9383$ed9e5944@.reading.news.pipex.net...
> > "Egbert Nierop (MVP for IIS)" <egbert_nierop@.nospam.com> wrote in
message
> > news:%23$i%23RbCtDHA.1884@.TK2MSFTNGP10.phx.gbl...
> > > Wouldn't it be nice if SQL build statistics which automatically would
> help
> > > the system create indexes at runtime?
> >
> > I'd absolutely hate it. Well building a table of suggested indexes might
> be
> > nice, but making the change automagically would introduce more problems
> than
> > it was worth IMO.
> Allrighty,
> in case we can define withhold parameters for auto-indexing and such it
> would be no problem. The designer still has to have control over it but
the
> designer should not be too dependend on the dbadmin for helping or
> co-operating on optimizing (and vice-versa)
You seem to be suggesting that the fewer times the dba and the system
designer have to speak the better. I'd have though the polar opposite were
true. design and admin are tasks that are inextricably linked.
Niall Litchfield
Oracle DBA
Audit Commission UK|||"Niall Litchfield" <n-litchfield@.audit-commission.gov.uk> wrote in message
news:3fd87eb4$0$9385$ed9e5944@.reading.news.pipex.net...
> "Egbert Nierop (MVP for IIS)" <egbert_nierop@.nospam.com> wrote in message
> news:eOIchwxvDHA.2180@.TK2MSFTNGP09.phx.gbl...
> > in case we can define withhold parameters for auto-indexing and such it
> > would be no problem. The designer still has to have control over it but
> the
> > designer should not be too dependend on the dbadmin for helping or
> > co-operating on optimizing (and vice-versa)
> You seem to be suggesting that the fewer times the dba and the system
> designer have to speak the better. I'd have though the polar opposite were
> true. design and admin are tasks that are inextricably linked.
I absolutely do not say that there is a **one fits all* way of working. In
some cases you are right. In other cases, you don't want to have long talks
with the dbadmin that you even don't know, you give him a list of options
(db size, backup needs, etc) but you don't want to beg to get him or her
optimize *your app* if he has no budget for it and you have no priviliges to
start a tracelog.
> --
> Niall Litchfield
> Oracle DBA
> Audit Commission UK
>|||"Niall Litchfield" <n-litchfield@.audit-commission.gov.uk> wrote in message
news:3fd87eb4$0$9385$ed9e5944@.reading.news.pipex.net...
> "Egbert Nierop (MVP for IIS)" <egbert_nierop@.nospam.com> wrote in message
> news:eOIchwxvDHA.2180@.TK2MSFTNGP09.phx.gbl...
> You seem to be suggesting that the fewer times the dba and the system
> designer have to speak the better. I'd have though the polar opposite were
> true. design and admin are tasks that are inextricably linked.
ps; I'd like to stress again my original point. I have an app (CRM like)
where you *cannot* optimize everything on forehand! The app can only be
optimized in combination of user statistics, so if the user likes to search
on postalcode only or on his own favorite fields, SQL server should decide
for that environment, to add a new index. My app is that flexible that the
user can sort and search on any column. You don't want to add indexes for
any column do you? :)

Tuesday, March 20, 2012

no "UNDO" feature in SQL? So what do people do to undo mistakes?

no "UNDO" feature in SQL? So what do people do to undo mistakes?

no "UNDO" feature in SQL? So what do people do to undo mistakes?

Monday, March 19, 2012

Nice to have feature

Nice feature for next SQL server version

Metadata Services

Blog Archive

About Me