Deleting millions of records from a table without blowing the transaction log
Posted by merill | Filed under SQL
Over the weekend I realized that deleting 80% of the records on a table with 87 million rows is not quite as easy as issuing a delete statement with a where criteria.
The main reason is that the transaction log quickly grows as it needs to keep track of all the uncommitted rows that are being [...]
Viewing the disk space usage (size) of all the tables in a SQL Server 2005/2008 database
Posted by merill | Filed under SQL
I spent a couple of hours searching for a script that returns the size of all the tables in a database. Most of the ones that you find use sp_msforeachtable but that is just for one table and you then need to use temporary tables to hold the values.
This article on the Database Journal by [...]
SQL query optimization
Posted by merill | Filed under SQL
Wow. Two SQL posts on the same day.
The SQL query processing team blog is quite a gem. I wonder how I missed this.
The query processing team – query optimization & execution – providing tips, tricks, advice and answers to frequently-asked questions in a continued effort to make your queries run faster and smoother in SQL [...]
Publishing your SQL Server 2005 database?
Posted by merill | Filed under SQL
Ever wanted to MySQL feature that let’s you script out a database (including the data) when working with SQL Server?
Fear no more the Database Publishing Wizard is here to help.
The Database Publishing Wizard enables the deployment of SQL Server 2005 databases into a hosted environment on either a SQL Server 2000 or 2005 server. [...]
Database diagram problems on a database migrated from SQL Server 2005
Posted by merill | Filed under SQL
I ran into a wierd error message when trying to view the Diagrams on a database that was migrated to SQL Server 2005. Something about the user not being valid and to change the database owner.
A quick search brought up this quick fix:
Try running this command to change the compatibility level to SQL Server 2005.
EXEC sp_dbcmptlevel [...]
Use scope_identity() instead of @@identity
Posted by merill | Filed under SQL
TravisL explains why
we should use scope_identity instead of @@identity when we want to get the ID of the
last record that was added.
[...]
