New training from Microsoft

Microsoft made SQL Server 2008 Microsoft Certified Master (MCM) Readiness Videos available on their site. Here they are   Enjoy!


Creating useful statistics from _WA_Sys stats

    You probably well aware that keeping statistics up-to-date is essential for SQL Server performance. You may also well know that if statistics is missing  on the columns of the table SQL Server creates _WA_Sys* stats for you automatically to have something instead of nothing to help optimizer decide which plan to chose. You…


Moving large tables between environments

Hello readers! Couple of days ago I had to move large table (~43 mln rows) from production environment to a dev environment. Below is a table structure as follows: CREATE TABLE [mmm].[Music Band]( [BandID] [int] IDENTITY(1,1) NOT NULL, [BandTypeID] [tinyint] NOT NULL, [BandName] [nvarchar](255) NOT NULL, [IsAvailable] [bit] NOT NULL, [IsRemoved] [tinyint] NOT NULL, [CreatedDataSourceTypeID]…


SQL Server system information

I guess many of you are familiar with build in SQL server function @@version, which you can use to quickly tell what SKU of SQL Server and Windows OS you are using as well as build numbers. Perhaps you are also aware of  serverproperty(param) function which tells you similar things eg your SQL Server instance…


Embeding XML into XML using FOR XML option

Hello, everyone! Today I am going to chat briefly about the question a co-worker asked me about. The problem was with embedding one XML snippet generated by using FOR XML option of the query into another XML also generated using FOR XML option. Here is cleaned up version of the query : declare @t table(c1…


Maximum sizes and numbers for SQL Server

While working with SQL Server you’ve probably heard certain restrictions like ‘There can be at most 1,024 columns declared in the table’ or ‘There can be only 8,600 bytes per row’. Some are urban legends and travel by the word of mouth others are hard to keep in the head even for people who constantly work…


SQL Server 2005/2008 database tuning – picking low hanging fruits – part 2

Some time ago I wrote a post how SQL Server optimizer keep track of what indexes maybe beneficial and logs this info into sys.dm_db_missing_index_* DMVs. Brent Ozar kindly pointed out that one can script ‘CREATE INDEX’ statement to build missing indexes to achieve performance gains. However, what was missing in my past post is what SQL statement may benefit from…


TSQL coding patterns I

With this post I will start writing some small TSQL coding patterns and tricks which make daily life of SQL Server database developer easier and are used a LOT when dealing with large databases. Data developers frequently have to update rows in tables. If table is small, you might be able to update the entire…


Visited from

<a href=”” id=”clustrMapsLink”><img src=”” style=”border:0px;” alt=”Locations of visitors to this page” title=”Locations of visitors to this page” id=”clustrMapsImg” onerror=”this.onerror=null; this.src=’’; document.getElementById(‘clustrMapsLink’).href=’’;” /></a>


Automatically refreshing your Excel reports

Many organization have lots of Excel reports and pivot tables whose data comes from SQL Server. As data on the backend database changes you would need to repopulate your excel books with updated information. Doing it by hand can be very tedious manual process for the reporting analyst and there are ways to automate it….