TRUNCATE TABLE vs DELETE FROM table (SQL Server)

DELETE FROM [table name] without using a WHERE clause and TRUNCATE TABLE [table name]  will have the same effect on the database, all the rows will be deleted from your table (assuming that the deletion of those rows does not violate any constraints). So which one should you use? The answer lies in the differences between those two statements:

• DELETE statement removes rows one at a time and records an entry in the transaction log for each deleted row. TRUNCATE TABLE removes the data by de-allocating the data pages used to store the table's data, and only the page de-allocations are recorded in the transaction log. 
• TRUNCATE resets the counter used by an identity for new rows to the seed for the column, whereas DELETE does not reset the counter.
• TRUNCATE TABLE cannot be used on a table referenced by a FOREIGN KEY constraint even if no violation of the constraint would occur, in fact a TRUNCATE on a table referenced by a FOREIGN KEY would fail even if the table is empty. The DELETE on the other side will succeed if deleting the rows does not violate the constraint.
• TRUNCATE does not activate the triggers whereas a DELETE will activate the trigger for every row that is deleted.
• TRUNCATE TABLE may not be used on tables participating in an indexed view.

Truncate table is a lot faster and it consumes significantly less resources than the Delete from table statement but you have to choose the right statement based on the particular requirements you have not simply based on which one runs faster.

If you read this article chances are that our SQL Data Compare tool would be very helpful to you - it is free for SQL Server Express with no limitations. Download your copy from here

Read More

The danger of subqueries on a T-SQL delete statement

How can a sub-query wreak havoc on your data? Consider this: you are doing some clean up on your database and need to delete from table [t1] all rows the id of which happens to refer to rows on a table [t2] that match a certain criteria - let's say all rows that were created before a given date. Without thinking twice you go ahead and write:

      delete from [t1]
        where id in (select id from [t2] where "some criteria")

You expect a few records will be deleted from table [t1] - you click on execute... you can't believe your eyes, SQL Server Management Studio is reporting that 200 thousand rows were deleted, oh no, the whole content of table [t1] was wiped out! How could this be? You are panicking... you realize you broke your own rules but first you want to understand how could this innocent query cause this. You examine it closer and you realize that there is no column named id on table [t2] - but shouldn't that have caused the sub-query, and consequently the whole query to fail?!

Well, it didn't, did it? Here is why: if you try running the sub-query by itself you will see that it will fail (assuming that there is no column named id on table [t2]), but when that sub-query is part of the bigger query things behave a bit differently. The reference id in the sub-query will be resolved against table [t1] in which case regardless of what the subquery criteria is, it will always return the id of the row being processed from table [t1] thus all rows from table [t1] will be deleted.

Here are a few simple rules that anyone working with data should follow religiously:

1. Always qualify the column names - had you done that your query would have failed and you would have been safe;
2. Never execute a delete statement without wrapping it in a transaction so that you can roll-back if you realize that you screwed up;
3. Write the query as a SELECT query first - execute it, see how many and which rows will be effected and only when you are certain that the query returns the rows you want then replace the "select * from" with "delete from"

Lastly, whenever you mess around with production data make sure your backups are good and keep SQL Server Data Compare handy as it will enable you to selectively restore only what you need without overwriting everything else.

Read More

SQL Server trigger security - granting privileges you are not authorized to

Both DML and DDL triggers execute under the context of the user that caused the trigger to fire – in other words, if for example we have a DML trigger that fires whenever a row is deleted from table T then the trigger will fire under the context of the user that executes the delete statement. Does this tell you anything about the potential inherent risk with triggers? Unlikely, until you consider this: a rogue developer writes a DDL trigger that looks something like this:

CREATE TRIGGER DDL_RogueDev
    ON DATABASE
      FOR ALTER_TABLE
      AS
        GRANT CONTROL SERVER TO RogueDev ;
 GO

Now, if the RogueDev tries to get the trigger fired so that he can get Server Control his attempt will fail since he does not have permission to grant himself server control, but, remember what we said above! What is going to happen when the DBA with full control goes and alters a table in the database? You guessed it – he inadvertently will be granting server control to RogueDev!  Ooops!

There are trigger security best practices that the DBA can follow like maintaining a strict inventory of DML and DDL triggers in the database and on the server instance, disabling triggers etc.  However, a proactive DBA can do more – he can use the very triggers to protect his servers / databases against people like RougeDev. How? Here is one simple example – a DBA could write a trigger that looks something like this:

CREATE TRIGGER no_grant_server
    ON ALL SERVER
      FOR GRANT_SERVER
      AS
          PRINT 'What do you think you are doing!'
          ROLLBACK;
GO

What does this do? Anytime the DDL_RogueDev fires and attempts to grant server control to RougeDev the no_grant_server trigger will fire and prevent that from happening no matter under what authority the DDL_RogueDev may be running. Instead of printing a silly message you could log the attempt, send notifications etc.

Another quick and easy measure would be to utilize a tool like SQL Compare for SQL Server to take regular snapshots of the schema and compare those snapshots with each other and with the live database to determine what database objects may have been added or altered.

Such security countermeasures are not hard to implement but not many DBAs do, until they have been burned.

Read More

One-click SQL Data Compare

Comparing and synchronizing data in two SQL Server databases is most of the time a fairly straight forward process: you select the databases you want to compare; xSQL Data Compare maps the tables and views automatically, identifies and selects the comparison keys and performs the comparison at the end of which it shows you the results on the screen. At that point you can click a button and generate the synchronization script which, if executed on the target will make the target the same as the source. So, the interaction required is minimal.

However, there are often times when the process is not very simple. Here are some possible complications:

• Two identical tables might be owned by different schemas in both databases so with the default settings xSQL Data Compare will not map those two tables with each other. So, you either have to change the mapping rules to ignore the schema or you have to manually map those two tables with each other;
• You might have tables that have no primary key defined on them and no unique constraints that can be used as comparison keys. In these cases you will need to drill down to those object pairs and manually select a comparison key that can be a combination of columns from this table;
• You might want to completely exclude some tables from the comparison;
• You might want to tweak the behavior of the comparison engine by adjusting the options to your needs;

For a relatively large database you might spend hours preparing the comparison. That is where the xSQL Data Compare’s “comparison session” saves the day – once you go through the comparison configuration process every "bit" of the configuration is stored in a session. Next time you launch xSQL Data Compare you will see a box on the main window for that session with a "Launch" action link – one click on that link and the comparison process will be done for you - many hours saved.

xSQL Data Compare supports SQL Server 2008 R2/2008/2005/2000 and it is free for SQL Server Express – download your copy from: http://www.xsql.com/download/package.aspx?packageid=10

Read More

xSQL Version Control discontinued

After a lot of pondering we decided to discontinue xSQL Version Control. Despite the majorly positive feedback we received from users every time we asked, the interest in the product was “lukewarm“. Therefore, instead of continuing the struggle to make the product financially viable we decided to “kill it”.

In theory, a decision like this should be easy to make but in reality those products we create are not simple, rational financial investments but a mix of financial and not so rational emotional investment. It is that emotional component of the investment the one that drives the outstanding support we provide so in that sense it is a crucial component in the success of the product. But, on the other hand it is exactly that component which “fogs” the reason and makes decisions like this very hard to make.

To our current users: we will continue to provide free email support for xSQL Version Control indefinitely but we will cease making any further changes to it.

Read More

xSQL Software gets a brand new look

Visitors to xSQL Software’s website today will be greeted by a brand new look – we have changed the logo and the whole website.

Why change the logo? We loved our logo from the day we approved it about 9 years ago and still love it just like one loves the toys he has grown up with. But, just like with the toys, there comes a time when you outgrow the logo and have to replace it. We felt that time for xSQL Software was now and that led to the creation and adaptation of this new beautiful logo that you see at the top of this blog.

Why a new web site? Well, again it is not that we did not like our old site, we think it looked great and it did its job very well.  However, we do realize that what was “trendy” 5 years ago, when we last revamped our site, looks a bit old and outdated today. We like clean, simple and to the point design and the new site has all three of those attributes. The time you spend on our site is an important parameter as far as search engine rankings go so the natural tendency would be to design a site in such a way that entices you to stay longer but we know that there are many more important things in your lives then reading about database tools. So, our goal was to give you the information you are looking for in the least amount of time possible and we believe we have achieved that with this new site.

New shorter url coming soon: when we first started about 9 years ago the xsql.com url was taken and we did not have the means to acquire it so we settled for x-sql.com. A couple of years later we decided that the dash in the url did not look very good so we ditched it and decided to go with the longer xsqlsoftware.com domain. Now, we have the name we have been longing for from the beginning and within the next month or so xsql.com will become the primary domain name for xSQL Software.  In fact as you will notice this blog is hosted under blog.xsql.com so we have began the transition.

Read More