Index optimization is always interesting subject to me. Every time I receive requests to help optimize query or query on any specific table. I always ask Jr.DBA to go over following list first before I take a look at it. Most of the time the Query Speed is optimized just following basic rules mentioned below. Once following checklist applied interesting optimization part begins which only experiment and experience can resolve.
SQL SERVER – 2005 TOP Improvements/Enhancements
SQL Server 2005 introduces two enhancements to the TOP clause. 1) User can specify an expression as an input to the TOP keyword. 2) User can use TOP in modification statements (INSERT, UPDATE, and DELETE). Explanation : User can specify an expression as an input to the TOP keyword. In…
Read MoreSQL SERVER – FIX : ERROR : Msg 3159, Level 16, State 1, Line 1 – Msg 3013, Level 16, State 1, Line 1
While moving some of the script from SQL SERVER 2000 to SQL SERVER 2005 our migration team faced following error. Msg 3159, Level 16, State 1, Line 1 The tail of the log for the database “AdventureWorks” has not been backed up. Use BACKUP LOG WITH NORECOVERY to backup the…
Read MoreSQL SERVER – Explanation SQL SERVER Merge Join
The Merge Join transformation provides an output that is generated by joining two sorted data sets using a FULL, LEFT, or INNER join. The Merge Join transformation requires that both inputs be sorted and that the joined columns have matching meta-data. User cannot join a column that has a numeric…
Read MoreSQL SERVER – Good, Better and Best Programming Techniques
A week ago, I was invited to meeting of programmers. Subject of meeting was “Good, Better and Best Programming Techniques”. I had made small note before I went to meeting, so if I have to talk about or discuss SQL Server it can come handy. Well, I did not get…
Read MoreSQL SERVER – SELECT vs. SET Performance Comparison
Usage: SELECT : Designed to return data. SET : Designed to assign values to local variables. While testing the performance of the following two scripts in query analyzer, interesting results are discovered. SET @foo1 = 1; SET @foo2 = 2; SET @foo3 = 3; SELECT @foo1 = 1, @foo2 =…
Read MoreSQL SERVER – 2005 Take Off Line or Detach Database
EXEC sp_dboption N'mydb', N'offline', N'true' OR ALTER DATABASE [mydb] SET OFFLINE WITH ROLLBACK AFTER 30 SECONDS OR ALTER DATABASE [mydb] SET OFFLINE WITH ROLLBACK IMMEDIATE Using the alter database statement (SQL Server 2k and beyond) is the preferred method. The rollback after statement will force currently executing statements to rollback…
Read More