How to Preview a Destructive SQL Query

How do I preview a destructive SQL query?

I would use the OUTPUT clause present in SQL SERVER 2008 onwards...

OUTPUT Clause (Transact-SQL)

Something like...

BEGIN TRANSACTION

DELETE [table] OUTPUT deleted.* WHERE [woof] 

ROLLBACK TRANSACTION

INSERTs and UPDATEs can use the 'inserted' table too. The MSDN article covers it all.

EDIT:

This is just like other suggestions of SELECT then DELETE inside a transaction, except that it actually does both together. So you open a transaction, delete/insert/update with an OUTPUT clause, and the changes are made while ALSO outputting what was done. Then you can choose to rollback or commit.

How do I test a sql query that modifies data, i.e. see the output before committing in sql server 2008?

Yes this is possible. You can either use the session option SET IMPLICIT_TRANSACTIONS ON or create an explicit transaction as below.

BEGIN TRAN

UPDATE YourTable 
SET foo=1
 /*To Review Changes can use OUTPUT clause here...*/
OUTPUT INSERTED.*, DELETED.*
WHERE bar=2

/*... or a SELECT against the table*/
SELECT *
FROM YourTable
WHERE bar=2

-- Execute the COMMIT or ROLLBACK commands when ready

However you should be aware that your open transaction will hold locks until the transaction completes which may block other transactions so this should be used with caution in any multi user environment.

Preview activerecord queries in rails console?

You can call .to_sql on an ActiveRecord::Relation to see the SQL that would be executed.

User.where(:id => 4).to_sql
 => "SELECT \"users\".* FROM \"users\"  WHERE \"users\".\"id\" = 4" 

Also, the console will only automatically execute the relation (and instantiate the objects) if it's the last command on the line, so you can do this:

relation = User.where(:id => 4); 1
=> 1

and thus set a variable to the relation without running it.

I'm not actually sure which of these two you wanted to do, but they're both handy tricks.

detectecting destructive SQL queries with C#

You can do:

if (badSqlList.Any(r => sqltext.IndexOf(r, StringComparison.InvariantCultureIgnoreCase) >= 0))
{
    //bad SQL found
}

IndexOf with StringComparison enum value will ensure case insensitive comparison.

Another approach could be:

return sqltext.Split()
        .Intersect(badSqlList,StringComparer.InvariantCultureIgnoreCase)
        .Any()

Split your Sql on white space and then compare each word with your white list array. This could save you in cases where your legal table name has keyword like INESRTEDStudents

---

Not really sure about your requirements, but, generally, a better option would be to use Parameterized queries in the first place. You can't be 100% sure with your white list and there still would be ways to bypass it.

Is there a command to test an SQL query without executing it? ( MySQL or ANSI SQL )

I realise this is a bit of an old question but for completeness...

If the intention is to find the query processing time without returning any rows (I need this quite often, I want to know how long a piece of code I am using will take without having it return a couple of million rows I am not interested in seeing) then the BLACKHOLE engine can be very useful:

https://dev.mysql.com/doc/refman/8.0/en/blackhole-storage-engine.html

For instance say I have 2 tables, t1 & t2, with millions of rows, that I am joining together. I want to check how long this is likely to take, in a GUI (SQLYog or mysql workbench or somesuch) without returning millions of rows that will eat up memory and presumably take time for the GUI to process and display. I use the blackhole engine to 'dump' the rows to nowhere.
EG:

CREATE TABLE tBH (a TINYINT) ENGINE = BLACKHOLE;
SELECT NOW(); -- Show start time
INSERT tBH 
SELECT 1 FROM t1
LEFT JOIN t2 ON t1.key1 = t2.key1;
SELECT NOW(); -- Show end time

Note that as I am just looking for execution time I do not bother returning all the columns (IE with "*") but just a placeholder ("1" in this case).

Performant techniques for finding similar values in SQL?

If you are using SQL Server, you might look into using the SOUNDEX() function as in:

...
where
   SOUNDEX("searchterm") = SOUNDEX(searchvaluefield)

it is supposed to do Phonetic matching on the strings ...

Some odd examples ... so it seems you could catch plurals by always appending the plural text to both sides, since multiple 's's sound the same ... :-)

select soundex('Canine'), soundex('Canines')
go

----- ----- 
C550  C552  

1 Row(s) affected

select soundex('Canine'), soundex('Caynyn')
go

----- ----- 
C550  C550  

1 Row(s) affected

select soundex('Canines'), soundex('Caniness')
go

----- ----- 
C552  C552  

1 Row(s) affected

RapidSQL Shortcuts

They're available in the RapidSQL user guide starting on page 53;

Keyboard Shortcuts

Rapid SQL provides a number of keyboard shortcuts to help you expedite your tasks.

The table below lists the taxes and related shortcuts:

Delete one character to the left BACKSPACE

Delete one character to the right DELETE

...and so on...

---

Related Topics