I want some Moore

SSMS Tools PACK 1.1 - now with SQL Server Management Studio 2008 support

Mladen Prajdić — Mon, 06 Oct 2008 08:59:07 GMT

With this new version you can use it for SQL Server Management Studio 2008 and SQL Server Management Studio 2008 Express.

I've added a new feature to Search through the Database data. There are times when you'd like to find some value but can't remember in which table it is.

Also the SQL Query History Log Viewer has been remodeled. I'm open to suggestions on how to improve it further.

You can also have the SSMS Tools Pack installed for both SSMS 2005 and SSMS 2008 on the same machine, however they don't share the same settings.

I would also like to turn to you dear reader to help me find a good logo for your favorite tool right here.

Since this is a completely free product I'm not in a position to pay anything but I would be more than glad to splash your name with praise all over the front page. :) Thank you for any and all input.

In SSMS Tools Pack 1.1 you can find these features

- Query Execution History (Soft Source Control) and Current Window History:

Save all executed queries to file or database and easily find them.

Current window history is a dockable window that show queries executed in a currently active window. There is also a search box at the top that filters results as you type

- Search Database Data:

Search for a value in all non-binary columns in all tables in the database

- Uppercase/Lowercase keywords:

Set all keywords to uppercase or lowercase letters. Custom keywords can be added. Doesn't check comments, text and quoted text anymore.

- Run one script on multiple databases:

Run selected or full window text on selected databases on the currently connected server.

- Copy execution plan bitmaps to clipboard:

Copy selected or all execution plans to a bitmap that is saved on the clipboard.

- Search Results in Grid Mode and Execution Plans:

Find all occurrences of your search string in the execution plans or in the results in datagrid mode.

- Generate Insert statements for a single table, the whole database or current resultsets in grids:

Generate insert statement from your data.

- Text document Regions and Debug sections:

Add Regions and Debug section in your scripts to ease development experience.

- Running custom scripts from Object explorer's Context menu:

Speedy execution of custom scripts from Object Explorer's context menus.

- CRUD (Create, Read, Update, Delete) stored procedure generation:

Generate Customizable CRUD stored procedures for all tables in your database.

- New query template:

Create a template that is shown when creating a new query window.

Currently supported SQL Server Management Studio versions are:

SQL Server Management Studio 2008 and SQL Server Management Studio 2008 Express

SQL Server Management Studio 2005

SQL Server Management Studio 2005 Express

Hope you enjoy it!

And if you're feeling extra generous there's always PayPal. :)

Normalization for databases is like Dependency Injection for code

Mladen Prajdić — Wed, 17 Sep 2008 08:38:52 GMT

Let us start with a simple question:

What is the goal of software development, be it database or .Net (or any other language)?

The first answer would be: Customer satisfaction!

And you'd be right. However there's more to customer satisfaction then the immediate product delivery effect. We have to think about future change requests, maintenance periods, etc...

Almost every business application out there consists of 2 basic parts: database back end and some kind of front end that consumes the data. In our case the front end is anything with access to the database.

In regard to future code changes and addition of new features what is the best way to construct this code? I would say the best way is using modular or black box design which states that solutions to big problems are built from solutions to smaller problems. This means that we have small independent modules that are black boxes to the outside world and they perform some operation based on input parameters and optionally provide output for other modules to consume. So how does this apply to our back and front end systems?

Front end - object oriented code

Dependency injection (a form of Inversion of Control) is the leading principle that enables our goal of keeping code in modules that have no outer dependencies. The input (constructor) arguments are usually interfaces that provide knowledge about the other modules that our module (class) uses. With this setup we can easily test each module by itself using automated unit tests. Also changing a module does not break other modules unless we change it's interface. And this is what we want.

This also enables us to keep our code separated and non repeatable. The worst thing we can do is have code that does the same thing in more than once place. Think maintenance nightmare!

A new feature request came in? No problem! Build it, test it, use interfaces of existing modules to access their functionality if needed. Change of an existing feature? Again, no problem. Change the module holding the feature and write new tests to validate its working correctly. And we're done.

A module in object oriented code is a class that exposes its functionality through an interface.

A simple example just to demonstrate the Dependency injection (DI) using Star Trek theme for all the geeks out there :)

Move method implementations:

// ENTERPRISE                                                 // BORG CUBE                             
public void Move(Speed speed)                                 public void Move(Speed speed)            
{                                                             {                                        
    if (speed == Speed.Impulse)                                   if (speed == Speed.Impulse)          
        _IImpulseEngine.GoSlow();                                     _IImpulseEngine.GoSlow();        
    else if (speed == Speed.Warp)                                 else if (speed == Speed.Warp)        
        _IWarpEngine.GoFast();                                        _IWarpEngine.GoFast();           
    else                                                          else                                 
        throw new Exception("Can't go faster than warp!");            _ITransWarpEngine.GoReallyFast();
}                                                             }

Method implementations are irrelevant, since the point here is that our ships are built from modules. They know only about the modules interface and that's why the module itself can be changed with no problem. We could completely change the internals of ImpulseEngine without having to change a single thing in our ship as long as the drives interface stayed the same. And yes, we could refactor this simple example further on.

If you ask me Dependency Injection is every developers dream. Unfortunately that dream usually comes to a screeching halt when we start dealing with databases.

Back end - databases

The concept of DI is meant for object oriented logic which is excellent for code but totally fails when dealing with databases. A module in code is a class that implements an interface which exposes it's functionality. There is no such concept in databases.

So what is a module in a database? Table, row, stored procedure? Wrong.

A module in the database is DATA.

Remember: Data is the core of the business application. Bad data, lousy business. Doesn't matter if you have the most amazing application known to mankind.

Denormalized design

This is a classic example of a denormalized schema. The problem lies in the ShippingAddress column. Since it is in the Orders table it could contain duplicates if we're shipping different orders to the same address. Because of this we can and always will get data inconsistency. And that is bad for a number of reasons.

CREATE TABLE [Order] 
(
    Id INT, 
    OrderName VARCHAR(100), 
    ShippingAddress VARCHAR(100)
)

This below result set is the common problem with denormalized design. If we edit and save the address for order 4, we can change it independently of order 1 despite both orders having the same address. Now we have duplicated data and we don't know which one is correct. In a normalized design this isn't possible.

Normalized design

This is the upper table normalized into 2 tables: Order and Address. This way our data is only in one place and thus it can't get corrupted by bad input. Note that AddressValue column should be split further, but for shortness sake lets make it simple.

CREATE TABLE [Order] 
(
    Id INT, 
    OrderName VARCHAR(100), 
    AddressId INT
)

CREATE TABLE [Address] 
(
    Id INT, 
    AddressValue VARCHAR(100)
)

Data normalization is not just a whim of every developers arch nemesis - the DBA ;). It's a simple concept that is used to guarantee data consistency and validity. So when the time comes to change or extend our schema we don't have to change it in different places in our database. Just like Dependency Injection enables simple and testable change in code by modularizing objects so does Database Normalization by applying the same goal for data.

So don't fear normalization. Embrace it and make your life easier in the long run.

Denormalizing on purpose

The most heard and quite valid argument is that fully normalized database schema is slow. This is true since querying 4 normalized joined tables is slower than querying 1 denormalized table.

However this argument is usually used in the wrong context. People tend to just leave their data denormalized.

Always first normalize data and then denormalize it if the need arises.

With SQL Server this problem can easily be solved with indexed views. With them we can query the denormalized data but update the normalized data. And thus we can achieve the performance we need without having duplicated data.

Summary

Dependency Injection and Database Normalization are two entirely different concepts that strive to achieve the same thing. Ability to make quick and stable changes.

Keeping the area of change small enough helps us make any kind of change fast be it small or large. In the long run this is what we want. Let's face it, how many times did an important business application that supports some business process have a lifetime of six months or less without the slightest change? Almost never. And because of that it pays to design our business software properly. It really isn't that hard.

SQL Server 2005 Express Job Scheduling - Part 1

Mladen Prajdić — Thu, 28 Aug 2008 14:32:17 GMT

I've written an article here on SQL Team on how to schedule jobs in SQL Server 2005 Express

Intro

As we all know SQL Server 2005 Express is a very powerful free edition of SQL Server 2005. However it does not contain SQL Server Agent service. Because of this scheduling jobs is not possible. So if we want to do this we have to install a free or commercial 3rd party product. This usually isn't allowed due to the security policies of many hosting companies and thus presents a problem. Maybe we want to schedule daily backups, database reindexing, statistics updating, etc... This is why I wanted to have a solution based only on SQL Server 2005 Express and not dependant on the hosting company. And of course there is one based on our old friend the Service Broker.

Part 1 - Scheduling Jobs in SQL Server Express

Part 2 will be out in couple of weeks

SQL Server 2005 temporary tables bug, feature or expected behavior?

Mladen Prajdić — Thu, 21 Aug 2008 14:56:43 GMT

In my opinion these 2 batches should behave the same but they don't. the first fails and the second runs ok.

I've searched through Books Online for any clue but i haven't really found anything useful.

Does anyone have any clue about this?

Permissions and transaction isolation levels are not an issue here.

-- just to make sure it doesn't already exist
IF OBJECT_ID('tempdb..#tempTable') IS NOT NULL
BEGIN 
    SELECT 'DROP Temporary table'
    DROP TABLE #tempTable
END 
IF OBJECT_ID('normalTable') IS NOT NULL
BEGIN 
    SELECT 'DROP Normal table'
    DROP TABLE normalTable
END 

GO
-- THIS FAILS WITH ERROR MESSAGE: Msg 2714, Level 16, State 1, Line 7 There is already an object named '#tempTable' in the database.
SELECT 'Temporary table'

SELECT * INTO #tempTable FROM [master]..spt_values
SELECT * FROM #tempTable
DROP TABLE #tempTable

SELECT * INTO #tempTable FROM [master]..spt_values
SELECT * FROM #tempTable
DROP TABLE #tempTable

GO
-- THIS RUNS OK
SELECT 'Normal table'
SELECT * INTO normalTable FROM [master]..spt_values
SELECT * FROM normalTable
DROP TABLE normalTable

SELECT * INTO normalTable FROM [master]..spt_values
SELECT * FROM normalTable
DROP TABLE normalTable

I've also tried this on SQL Server 2000 and it behaves the same. Haven't tried it on SQL Server 2008 though.

Software Development Cycle truism

Mladen Prajdić — Tue, 12 Aug 2008 15:53:53 GMT

For all enthusiasts out there this is how software development cycle works no matter what the project is:

Programmer produces code he believes is bug-free.
Product is tested. 20 bugs are found.
Programmer fixes 10 of the bugs and explains to the testing department that the other 10 aren't really bugs.
Testing department finds that five of the fixes didn't work and discovers 15 new bugs.
Repeat three times steps 3 and 4.
Due to marketing pressure and an extremely premature product announcement based on overly-optimistic programming schedule, the product is released.
Users find 137 new bugs.
Original programmer, having cashed his royalty check, is nowhere to be found.
Newly-assembled programming team fixes almost all of the 137 bugs, but introduce 456 new ones.
Original programmer sends underpaid testing department a postcard from Fiji. Entire testing department quits.
Company is bought in a hostile takeover by competitor using profits from their latest release, which had 783 bugs.
New CEO is brought in by board of directors. He hires a programmer to redo program from scratch.
Programmer produces code he believes is bug-free...

More truisms like the one above can be found here.

Number one advice to recruiters from an IT guy

Mladen Prajdić — Thu, 24 Jul 2008 17:07:44 GMT

I can't stress this enough:

Never ever call me on my work phone as a form of the first contact!

Unless I know you and have given you permission to call me, don't. Send me an email instead.

I don't have anything against recruiters, I think their jobs are very important in the greater scheme of things.

I also never give my work phone number away, and if I have to I give my mobile phone number to people.

So it's beyond me how do recruiters think it's actually OK to call me at work. It shows disrespect to me and to the company I work for.

And that is NOT OK. Not even if you're offering me the most amazing job in the universe.

But for a minute lets say it's OK and I have no problem with the call. There are a number of things that can go wrong here:

- I'm in a really lousy mood for whatever reason

- my boss answers the phone (now I don't know about others but my current boss is very cool and has no problem with it but others may not be so lucky)

- you disrupt my coding zone. And I get REALY annoyed if I have to break out of my zone.

- I'm in a meeting or something similar

- other stuff I haven't thought of ...

If any of the above happens to be true you've just lost me as a potential candidate.

And lets get something straight: You need me more than I need you. And that translates into lost revenue for you.

So you see an email is a much better way to make first contact. Then we can talk about scheduling a call if it's needed.

Come on, be smart about it!

Immediate deadlock notifications without changing existing code

Mladen Prajdić — Fri, 18 Jul 2008 09:00:04 GMT

In my previous post about immediate deadlock notifications in SQL Server 2005 I've shown a way to use a try catch block to get the deadlock error.

The con of this method, although it uses best practice for error handling in SQL Server 2005, is that you have to change existing code and

it doesn't work for non stored procedure code. And that IS a pretty BIG con! As is customary in this blog there is a solution to this. :)

SQL Server 2005 Event notifications

Event notifications are a special kind of database object that send information about server and database events to a Service Broker service.

They execute in response to a variety of Transact-SQL data definition language (DDL) statements and SQL Trace events by sending information

about these events to a Service Broker service. There are three scopes for event notifications: Server, Database and Queue.

We of course want a Server wide deadlock notification so that we can be notified of all deadlocks on the entire server

I have to point out that event notification are an awesome use of Service Broker functionality.

Setup

For the purpose of this post I've used tempdb to hold our deadlock event info. Of course this should go into an administrative database if you have one.

Also an email is sent to notify the DBA that the deadlock happened. Thus the Immediate part :)

USE tempdb
GO

-- this procedure will write our event data into the table and send the notification email
CREATE PROCEDURE usp_ProcessNotification 
AS
    DECLARE @msgBody XML    
    DECLARE @dlgId uniqueidentifier

    -- you can change this to get all messages at once
    WHILE(1=1)
    BEGIN
        BEGIN TRANSACTION    
        BEGIN TRY        
            -- receive messages from the queue one by one            
            ;RECEIVE TOP(1) 
                    @msgBody    = message_body,
                    @dlgId        = conversation_handle
            FROM    dbo.DeadLockNotificationsQueue
            
            -- exit when the whole queue has been processed
            IF @@ROWCOUNT = 0
            BEGIN
                IF @@TRANCOUNT > 0
                BEGIN 
                    ROLLBACK;
                END  
                BREAK;
            END 

            -- insert event data into our table 
            INSERT INTO TestEventNotification(eventMsg)
            SELECT @msgBody
            
            DECLARE @MailBody NVARCHAR(MAX)    
            SELECT @MailBody = CAST(@msgBody AS NVARCHAR(MAX));

            -- send an email with the defined email profile. 
            -- since this is async it doesn't halt execution
            -- EXEC msdb.dbo.sp_send_dbmail
            --         @profile_name = 'your mail profile', -- your defined email profile 
            --         @recipients = 'dba@yourCompany.com', -- your email
            --         @subject = 'Deadlock occured notification',
            --         @body = @MailBody;
            
            IF @@TRANCOUNT > 0
            BEGIN 
                COMMIT;
            END
        END TRY
        BEGIN CATCH
            IF @@TRANCOUNT > 0
            BEGIN 
                ROLLBACK;
            END
            -- write any error in to the event log
            DECLARE @errorNumber BIGINT, @errorMessage nvarchar(2048), @dbName nvarchar(128)
            SELECT  @errorNumber = ERROR_NUMBER(), @errorMessage = ERROR_MESSAGE(), @dbName = DB_NAME()

            RAISERROR (N'Error WHILE receiving Service Broker message FROM queue DeadLockNotificationsQueue.
                        DATABASE Name: %s; Error number: %I64d; Error Message: %s', 
                        16, 1, @dbName, @errorNumber, @errorMessage) WITH LOG;
        END CATCH;
    END
GO

-- create the notification queue that will receive the event notification messages
-- add the activation stored procedure that will process the messages in the queue
-- as they arrive
CREATE QUEUE DeadLockNotificationsQueue
    WITH STATUS = ON,
    ACTIVATION (
        PROCEDURE_NAME = usp_ProcessNotification,
        MAX_QUEUE_READERS = 1,
        EXECUTE AS 'dbo' );
GO

-- crete the notofication service for our queue with the pre-defined message type
CREATE SERVICE DeadLockNotificationsService
    ON QUEUE DeadLockNotificationsQueue 
                ([http://schemas.microsoft.com/SQL/Notifications/PostEventNotification]);
GO

-- create the route for the service
CREATE ROUTE DeadLockNotificationsRoute
    WITH SERVICE_NAME = 'DeadLockNotificationsService',
    ADDRESS = 'LOCAL';
GO

-- create the event notification for the DEADLOCK_GRAPH event. 
-- other lock events can be added 
CREATE EVENT NOTIFICATION DeadLockNotificationEvent
ON SERVER 
FOR DEADLOCK_GRAPH -- , LOCK_DEADLOCK_CHAIN, LOCK_DEADLOCK, LOCK_ESCALATION -- ANY OF these can be SET
TO SERVICE 'DeadLockNotificationsService', 
           'current database' -- CASE sensitive string that specifies USE OF server broker IN CURRENT db
GO

-- check to see if our event notification has been created ok
SELECT * FROM sys.server_event_notifications WHERE name = 'DeadLockNotificationEvent';
GO

-- create the table that will hold our deadlock info
CREATE TABLE TestEventNotification(Id INT IDENTITY(1,1), EventMsg xml, EventDate datetime default(GETDATE()))
GO

-- clean up
/*
DROP TABLE TestEventNotification
DROP PROCEDURE usp_ProcessNotification
DROP EVENT NOTIFICATION DeadLockNotificationEvent ON SERVER 
DROP ROUTE DeadLockNotificationsRoute
DROP SERVICE DeadLockNotificationsService
DROP QUEUE DeadLockNotificationsQueue
*/

Testing

For testing you'll need to open 2 windows in SQL Server Management Studio

-- tun this first to create the test table 
USE AdventureWorks
IF object_id('DeadlockTest') IS NOT NULL 
    DROP TABLE DeadlockTest 
GO
CREATE TABLE DeadlockTest ( id INT)
INSERT INTO DeadlockTest
SELECT 1 UNION ALL
SELECT 2
GO
----------------------------------------------------------------
----------------------------------------------------------------
-- run this in query window 1
BEGIN TRAN

UPDATE DeadlockTest 
SET id = 12
WHERE id = 2

-- wait 5 secs to set up deadlock condition in other window
WAITFOR DELAY '00:00:05'

UPDATE DeadlockTest 
SET id = 11
WHERE id = 1

COMMIT

----------------------------------------------------------------
----------------------------------------------------------------
-- run this in query window 2 a second or two 
-- after you've run the script in query window 1
BEGIN TRAN

UPDATE DeadlockTest 
SET id = 11
WHERE id = 1

-- wait 5 secs to set up deadlock condition in other window
WAITFOR DELAY '00:00:05'

UPDATE DeadlockTest 
SET id = 12
WHERE id = 2

COMMIT
----------------------------------------------------------------
----------------------------------------------------------------

-- run this after the test to see that we have our deadlock event notification saved
USE tempdb
SELECT * FROM TestEventNotification
ORDER BY id

We can see that this setup works great. Because we have subscribed to the DEADLOCK_GRAPH event we can see the same information

as if we had traced it with the SQL Profiler. Of course this kind of setup can be used for any kind of event that is supported.