Aggregate Functions MIN and MAX

I was tasked to develop a performance report using SQL Server Reporting Services 2005. One of the requirements for this report was to get the MAX and MIN date values and then perform a DATEDIFF to get the difference of days between the two values. One of the hurdles I faced was the fact that table contained multiple row for a single woid so to shed some clarity I put together some sample data to illustrate my scenario. There is probably multiple ways to accomplish the same end results, but this was my route.

Create a temp table

For this example we will be using only three fields. I added the ID field for my own selfish reasons… actually come to think of it… I really won’t need it. Any who here is the skinny.

CREATE TABLE #tmpTbl
(
	ID INT IDENTITY(1,1)
	,WOID INT
	,CreateDate datetime
)

Insert records

This is pretty straightforward as it is a simple insert statement that will insert 100 rows.

INSERT INTO #tmpTbl (WOID,CreateDate) VALUES (1,'2008-12-11 01:33:55.997')
INSERT INTO #tmpTbl (WOID,CreateDate) VALUES (1,'2008-12-11 01:33:56.010')
INSERT INTO #tmpTbl (WOID,CreateDate) VALUES (2,'2008-12-11 06:02:56.983')
INSERT INTO #tmpTbl (WOID,CreateDate) VALUES (2,'2008-12-11 06:04:12.640')
INSERT INTO #tmpTbl (WOID,CreateDate) VALUES (2,'2008-12-11 06:04:17.907')
INSERT INTO #tmpTbl (WOID,CreateDate) VALUES (3,'2008-12-11 07:49:53.810')
INSERT INTO #tmpTbl (WOID,CreateDate) VALUES (3,'2008-12-11 07:49:53.810')
INSERT INTO #tmpTbl (WOID,CreateDate) VALUES (3,'2008-12-11 07:55:40.567')
INSERT INTO #tmpTbl (WOID,CreateDate) VALUES (4,'2008-12-11 08:00:50.477')
INSERT INTO #tmpTbl (WOID,CreateDate) VALUES (4,'2008-12-11 08:00:50.477')
INSERT INTO #tmpTbl (WOID,CreateDate) VALUES (4,'2008-12-11 08:03:27.480')
INSERT INTO #tmpTbl (WOID,CreateDate) VALUES (5,'2008-12-11 08:12:07.927')
INSERT INTO #tmpTbl (WOID,CreateDate) VALUES (5,'2008-12-11 08:12:07.927')
INSERT INTO #tmpTbl (WOID,CreateDate) VALUES (5,'2008-12-11 08:18:42.327')
INSERT INTO #tmpTbl (WOID,CreateDate) VALUES (6,'2008-12-11 08:59:49.217')
INSERT INTO #tmpTbl (WOID,CreateDate) VALUES (6,'2008-12-11 09:07:24.443')
INSERT INTO #tmpTbl (WOID,CreateDate) VALUES (6,'2008-12-11 09:09:30.883')
INSERT INTO #tmpTbl (WOID,CreateDate) VALUES (6,'2008-12-11 09:29:05.030')
INSERT INTO #tmpTbl (WOID,CreateDate) VALUES (6,'2008-12-11 09:29:23.610')
INSERT INTO #tmpTbl (WOID,CreateDate) VALUES (7,'2008-12-11 09:55:24.327')
INSERT INTO #tmpTbl (WOID,CreateDate) VALUES (7,'2008-12-11 09:55:24.340')
INSERT INTO #tmpTbl (WOID,CreateDate) VALUES (8,'2008-12-11 09:58:10.970')
INSERT INTO #tmpTbl (WOID,CreateDate) VALUES (8,'2008-12-11 09:58:10.970')
INSERT INTO #tmpTbl (WOID,CreateDate) VALUES (8,'2008-12-11 09:58:23.170')
INSERT INTO #tmpTbl (WOID,CreateDate) VALUES (9,'2008-12-11 10:00:32.207')
INSERT INTO #tmpTbl (WOID,CreateDate) VALUES (9,'2008-12-11 10:00:32.207')
INSERT INTO #tmpTbl (WOID,CreateDate) VALUES (10,'2008-12-11 10:26:03.173')
INSERT INTO #tmpTbl (WOID,CreateDate) VALUES (10,'2008-12-11 10:28:43.940')
INSERT INTO #tmpTbl (WOID,CreateDate) VALUES (10,'2008-12-11 12:27:09.417')
INSERT INTO #tmpTbl (WOID,CreateDate) VALUES (10,'2008-12-11 12:28:07.600')
INSERT INTO #tmpTbl (WOID,CreateDate) VALUES (11,'2008-12-11 12:48:33.023')
INSERT INTO #tmpTbl (WOID,CreateDate) VALUES (11,'2008-12-11 12:48:33.023')
INSERT INTO #tmpTbl (WOID,CreateDate) VALUES (12,'2008-12-11 13:26:49.440')
INSERT INTO #tmpTbl (WOID,CreateDate) VALUES (12,'2008-12-11 13:26:49.470')
INSERT INTO #tmpTbl (WOID,CreateDate) VALUES (13,'2008-12-11 13:28:56.300')
INSERT INTO #tmpTbl (WOID,CreateDate) VALUES (13,'2008-12-11 13:28:56.300')
INSERT INTO #tmpTbl (WOID,CreateDate) VALUES (14,'2008-12-11 13:31:01.223')
INSERT INTO #tmpTbl (WOID,CreateDate) VALUES (14,'2008-12-11 13:31:01.223')
INSERT INTO #tmpTbl (WOID,CreateDate) VALUES (15,'2008-12-11 14:05:41.173')
INSERT INTO #tmpTbl (WOID,CreateDate) VALUES (15,'2008-12-11 14:05:41.207')
INSERT INTO #tmpTbl (WOID,CreateDate) VALUES (16,'2008-12-11 15:09:38.990')
INSERT INTO #tmpTbl (WOID,CreateDate) VALUES (16,'2008-12-11 15:09:38.990')
INSERT INTO #tmpTbl (WOID,CreateDate) VALUES (5,'2008-12-11 15:15:44.560')
INSERT INTO #tmpTbl (WOID,CreateDate) VALUES (15,'2008-12-11 15:18:54.733')
INSERT INTO #tmpTbl (WOID,CreateDate) VALUES (16,'2008-12-11 15:25:39.223')
INSERT INTO #tmpTbl (WOID,CreateDate) VALUES (13,'2008-12-11 15:27:13.160')
INSERT INTO #tmpTbl (WOID,CreateDate) VALUES (12,'2008-12-11 15:27:58.490')
INSERT INTO #tmpTbl (WOID,CreateDate) VALUES (11,'2008-12-11 15:29:50.600')
INSERT INTO #tmpTbl (WOID,CreateDate) VALUES (11,'2008-12-11 15:29:53.663')
INSERT INTO #tmpTbl (WOID,CreateDate) VALUES (16,'2008-12-11 15:31:30.210')
INSERT INTO #tmpTbl (WOID,CreateDate) VALUES (16,'2008-12-11 17:09:22.057')
INSERT INTO #tmpTbl (WOID,CreateDate) VALUES (11,'2008-12-11 17:09:54.043')
INSERT INTO #tmpTbl (WOID,CreateDate) VALUES (6,'2008-12-11 17:11:10.353')
INSERT INTO #tmpTbl (WOID,CreateDate) VALUES (17,'2008-12-12 07:06:27.550')
INSERT INTO #tmpTbl (WOID,CreateDate) VALUES (17,'2008-12-12 07:06:27.550')
INSERT INTO #tmpTbl (WOID,CreateDate) VALUES (3,'2008-12-12 07:07:23.393')
INSERT INTO #tmpTbl (WOID,CreateDate) VALUES (3,'2008-12-12 07:10:41.897')
INSERT INTO #tmpTbl (WOID,CreateDate) VALUES (10,'2008-12-12 07:18:08.377')
INSERT INTO #tmpTbl (WOID,CreateDate) VALUES (13,'2008-12-12 07:20:14.627')
INSERT INTO #tmpTbl (WOID,CreateDate) VALUES (7,'2008-12-12 07:46:29.563')
INSERT INTO #tmpTbl (WOID,CreateDate) VALUES (9,'2008-12-12 08:08:37.073')
INSERT INTO #tmpTbl (WOID,CreateDate) VALUES (16,'2008-12-12 08:14:06.267')
INSERT INTO #tmpTbl (WOID,CreateDate) VALUES (16,'2008-12-12 08:30:03.270')
INSERT INTO #tmpTbl (WOID,CreateDate) VALUES (16,'2008-12-12 08:30:25.350')
INSERT INTO #tmpTbl (WOID,CreateDate) VALUES (16,'2008-12-12 08:34:11.557')
INSERT INTO #tmpTbl (WOID,CreateDate) VALUES (16,'2008-12-12 08:42:04.197')
INSERT INTO #tmpTbl (WOID,CreateDate) VALUES (18,'2008-12-12 08:43:36.873')
INSERT INTO #tmpTbl (WOID,CreateDate) VALUES (19,'2008-12-12 08:48:38.390')
INSERT INTO #tmpTbl (WOID,CreateDate) VALUES (19,'2008-12-12 08:48:38.390')
INSERT INTO #tmpTbl (WOID,CreateDate) VALUES (16,'2008-12-12 08:56:07.633')
INSERT INTO #tmpTbl (WOID,CreateDate) VALUES (18,'2008-12-12 08:56:39.760')
INSERT INTO #tmpTbl (WOID,CreateDate) VALUES (19,'2008-12-12 08:57:03.603')
INSERT INTO #tmpTbl (WOID,CreateDate) VALUES (19,'2008-12-12 08:57:26.807')
INSERT INTO #tmpTbl (WOID,CreateDate) VALUES (20,'2008-12-12 09:00:13.077')
INSERT INTO #tmpTbl (WOID,CreateDate) VALUES (20,'2008-12-12 09:00:13.077')
INSERT INTO #tmpTbl (WOID,CreateDate) VALUES (20,'2008-12-12 09:00:38.153')
INSERT INTO #tmpTbl (WOID,CreateDate) VALUES (20,'2008-12-12 09:00:46.437')
INSERT INTO #tmpTbl (WOID,CreateDate) VALUES (21,'2008-12-12 09:13:14.637')
INSERT INTO #tmpTbl (WOID,CreateDate) VALUES (21,'2008-12-12 09:20:57.927')
INSERT INTO #tmpTbl (WOID,CreateDate) VALUES (21,'2008-12-12 09:22:33.040')
INSERT INTO #tmpTbl (WOID,CreateDate) VALUES (21,'2008-12-12 09:23:29.383')
INSERT INTO #tmpTbl (WOID,CreateDate) VALUES (18,'2008-12-12 09:28:24.827')
INSERT INTO #tmpTbl (WOID,CreateDate) VALUES (16,'2008-12-12 09:34:04.410')
INSERT INTO #tmpTbl (WOID,CreateDate) VALUES (9,'2008-12-12 09:50:24.820')
INSERT INTO #tmpTbl (WOID,CreateDate) VALUES (9,'2008-12-12 09:54:03.217')
INSERT INTO #tmpTbl (WOID,CreateDate) VALUES (10,'2008-12-12 09:54:29.327')
INSERT INTO #tmpTbl (WOID,CreateDate) VALUES (10,'2008-12-12 09:54:35.937')
INSERT INTO #tmpTbl (WOID,CreateDate) VALUES (22,'2008-12-12 10:25:15.753')
INSERT INTO #tmpTbl (WOID,CreateDate) VALUES (22,'2008-12-12 10:25:15.753')
INSERT INTO #tmpTbl (WOID,CreateDate) VALUES (23,'2008-12-12 10:30:18.803')
INSERT INTO #tmpTbl (WOID,CreateDate) VALUES (23,'2008-12-12 10:30:18.803')
INSERT INTO #tmpTbl (WOID,CreateDate) VALUES (9,'2008-12-12 10:33:03.073')
INSERT INTO #tmpTbl (WOID,CreateDate) VALUES (9,'2008-12-12 10:37:01.220')
INSERT INTO #tmpTbl (WOID,CreateDate) VALUES (9,'2008-12-12 10:37:15.580')
INSERT INTO #tmpTbl (WOID,CreateDate) VALUES (11,'2008-12-12 10:38:25.737')
INSERT INTO #tmpTbl (WOID,CreateDate) VALUES (8,'2008-12-12 10:40:53.490')
INSERT INTO #tmpTbl (WOID,CreateDate) VALUES (8,'2008-12-12 10:47:17.497')
INSERT INTO #tmpTbl (WOID,CreateDate) VALUES (8,'2008-12-12 10:47:46.090')
INSERT INTO #tmpTbl (WOID,CreateDate) VALUES (8,'2008-12-12 10:48:23.310')
INSERT INTO #tmpTbl (WOID,CreateDate) VALUES (18,'2008-12-12 11:00:24.733')

Let’s select the raw data

As you can see most records have more than one CreateDate associated to a woid.

SELECT * FROM #tmpTbl ORDER BY WOID

Let’s get some counts

So let’s select the data to get date counts and then group by woid to identify how many dates per woid. The results imply that there is no count that is below 2.

SELECT WOID, COUNT(CreateDate) 'Total' 
FROM #tmpTbl
GROUP BY WOID
ORDER BY Total

Getting MIN and MAX values

I figured we better run through this or touch base on this briefly. The first query will return the minimum date value per record set, meaning only only record per set will be returned. The same goes for the second query following; however, it returns the maximum date value per record set. For more information about aggregate functions please visit: MSDN: AGGREGATE FUNCTIONS

SELECT	WOID
		,MIN(CreateDate) 'MinDate'
FROM #tmpTbl
GROUP BY WOID

SELECT	WOID
		,MAX(CreateDate) 'MaxDate'
FROM #tmpTbl
GROUP BY WOID

Returning both aggregates

In the following query I am returning both the MIN and MAX date value for a given woid. I join a second instance of the #tmpTbl to itself on woid and then group by the woid to be in accordance with the rules of aggregation.

SELECT	t1.WOID
		,MIN(t1.CreateDate) 'MinDate'
		,MAX(t2.CreateDate) 'MaxDate'
FROM #tmpTbl t1
JOIN #tmpTbl t2 ON t1.WOID = t2.WOID
GROUP BY t1.WOID

Verify

Just to make sure the values returned are correct let’s select a record to verify against to make sure the results are as expected. It’s always a good idea to verify by the way!

SELECT	WOID
		,CreateDate
FROM #tmpTbl
WHERE (WOID = 2)
ORDER BY CreateDate

Going further

Now that we have our date aggregates in place it would be a good idea to get the difference in days between the two values by using DATEDIFF. In the following query I am using the D interval which represents Days. For more information about DateDiff and it’s intervals please visit: MSDN: DATEDIFF

SELECT	t1.WOID
		,MIN(t1.CreateDate) 'MinDate'
		,MAX(t2.CreateDate) 'MaxDate'
		,DATEDIFF(d,MIN(t1.CreateDate),MAX(t2.CreateDate)) 'Days'
FROM #tmpTbl t1
JOIN #tmpTbl t2 ON t1.WOID = t2.WOID
GROUP BY t1.WOID
DROP TABLE #tmpTbl

This works well in this case, but I am dealing with a few more requirements which adds a little more complexity to the equation. I have to filter by additional statuses which are returning more then one instance of the record per woid due to a one-to-many relationship. Then I need to join in two additional tables which also pose a one-to-many situation. This is affecting the MIN and MAX aggregate dates severely. As soon as I douse the fire I will add to this post with further details.

Amended

Turns out my query was correct in design, but the data had some anomalies that caused records to display multiple times due to a couple of things. Some of the records did not have a completed status which were filtered out in my WHERE clause. Then some of the records had duplicate CreateDates with a different associated GrpID but with the same StatID which is an oddity. This caused some of the records to display twice. I’ll need to figure out a way to select only one. I am using a SELECT TOP 1 which passes the CreateDate but since the dataset that I am passing it has duplicate records I still get the duplicates returned. S I might need to pass the data to a temp table then do a filter and then join back to the main query via UNION ALL.

Advertisements

Analysis Tool Pak Plug-in for Excel 2007

In the event you have Office 2007 and need to install the Analysis Tool Pak plug-in here is the step-by-step.

Installing the Add-in

1) Click the Office button

2) Select Excel Options

3) Select Add-ins

4) Select Analysis Tool Pak

5) Click OK

Validate the Install

1) Select the Data Tab

2) Analysis Group is Missing

Load the group

1) Click the Office button

2) Select Excel Options

3) Manage Add-Ins

4) Select Analysis Tool Pak

5) Click OK

Validate the Group

1) Select the Data Tab

Configuring Database Mail

SQL Server Database Mail plays a nice role in my administration. I have set up a few SQL Server Agent Jobs that calls upon stored procedures that I put together to help monitor issues that arise with the data. Sometimes its specific for tracking GIS Schema changes or incoming dirty data from interfacing systems. I like to be in the know right away as it helps me address problems quickly.

First thing to understand is the fact that in order to use DB Mail you need the SQL Server Agent running. Secondly in order for you to execute the system stored procedure sp_send_dbmail you need to be a member of the DatabaseMailUserRole which resides in MSDB.

Step 1: Show Advanced Options

use [master]
go
sp_configure 'show advanced options',1
go
reconfigure
go
sp_configure 'Database Mail XPs',1
go
reconfigure
go

Side note: some options require a restart of the database engine however some can be circumvented by supplying the argument “with override” to the reconfigure command. For more information please visit the following MSDN posts: Setting Server Configuration Options and RECONFIGURE (Transact-SQL).

Create the Mailer Profile

EXEC msdb.dbo.sysmail_add_profile_sp
@profile_name = 'DB Admin', -- Can be whatever you specify
@description = 'Profile used for database mail'

Create the Mailing Account

EXEC msdb.dbo.sysmail_add_account_sp
@account_name = 'Notifier', -- Can be whatever you specify
@description = 'Notification Account of Database Changes',
@email_address = 'No-Reply@sqlsam7.com', -- can be fictitious
@display_name = 'No-Reply',
@mailserver_name = 'smtp.gmail.com',
@port = 587,
@username = 'YourAccount@gmail.com',
@password = 'your password',
--@use_default_credentials =, 
@enable_ssl = 1

Associate the Mailer Profile to the Mailing Account

EXEC msdb.dbo.sysmail_add_profileaccount_sp
@profile_name = 'DB Admin', -- Must be the same as above
@account_name = 'Notifier', -- Must be the same as above
@sequence_number = 1

Test

USE [msdb]
EXEC sp_send_dbmail
@profile_name = 'DB Admin', -- Can be whatever you specify
@recipients = 'samson@sqlsam7.com',
/* @copy_recipients = '[Email address protected]
@blind_copy_recipients = '[Email address protected] */
@subject = 'Test Email from SQL Server dbMail',
@importance = 'High',
@body = 'This is a test of the SQL Server dbMail.',
@body_format = 'Text' -- Can be HTML too.

Verify Mail Log and Event Log

SELECT * FROM msdb.dbo.sysmail_allitems
ORDER BY mailitem_id DESC
SELECT * FROM msdb.dbo.sysmail_event_log 
ORDER BY log_id DESC

MCTS: Final Decision

After some consideration I decided to pursue the 70-432 Exam: Microsoft SQL Server 2008, Implementation and Maintenance instead of the 2005 version. Though a large part of me thinks that getting certified in both versions is a good idea. As I mentioned before I am late in the SQL Certification arena so I might as well go with the most current version. The first thing I did was to visit the Microsoft Learning site and reviewed the “skills measured” section to see the areas I needed to focus on. There are eight sections that cover installation to high availability. So naturally I will hit the installation portion first. I do feel like I know the material well enough in terms of installing SQL Server 2008, but I don’t want to sell myself short so I will follow along and check off each item listed one-by-one. I am guessing I could spend about 8 weeks or so covering every section with every bit of spare time I have on a day-to-day basis in order to knock this out within two months.

I have some course material that I will use but I will also incorporate the use of Books-Online, Microsoft Learning, SQL Server centric sites and blog posts via the SQL Community. I definitely believe this is a plausible goal, but I will really need to focus on juggling my time management between work, school and family life. I do have some experience behind me with installation, configuration, replication, upgrades and such so tomorrow marks the start of my actual self-instructed training. I will run full steam ahead on this and plug away as much as I can. I will do my best to blog about my study experience as much as possible along with any and all materials I end up using.

Policy Based Management (PBM)

I merely ready about policy based management (PBM) through short snippets here and there, but never really got that in-depth with it as I normally do with other subjects. I think the major factor was due to the name Policy. Mistakenly I automatically associated it with Active Directory. PBM was introduced with SQL Server 2008 and offers great benefits with administering SQL Server. I was conducting some research on the subject and found a post by Ashish Kumar Mehta titled: “Identify Databases Not in Full Recovery Model Using Policy Based Management” which steps you through the process of creating a simple policy and runs you through the evaluation process. Ashish covers the steps rather well so be sure to visit the post for more details. If you want to get deeper into the terminology, scheduling and such then visit a post by Ray Barley titled: “Using Policy-Based Management in SQL Server 2008

After walking through Ashish’s post I wanted to make one similar but I wanted to return all Windows Accounts and such, though his use for the Recovery Model is more practical. So fire up your SSMS 2008 and let’s get started. This is just a quick run down of how to create one. This does not get into the deep details that others like have. More of a pre-primer primer if you will.

Open SQL Server Management Studio and double-click or expand Management.

Right click on Policies

Select New Policy…

Give your policy a name

Click the Check Condition and Select New Condition…

Give your Condition a name… I know it requires a lot of naming

Select the Facet drop down and Select Login

In the Expression section click on the Field column and select @LoginType

Select the Operator (=)

Select WindowsUser for the Value and hit Enter

Click Ok to create the Condition and the Policy

Policies should be expanded now and you should see your newly created policy. Expand Condition and you should see the condition you just finalized.

Now let’s test the Policy. Right click on the newly created Policy and select Evaluate.

And this brings us to the end results

The green marks are the Windows Accounts and the Red marked rows are not.

In the event you are working on a server that may or may not have policies in place Ken Simmons provided me with the following select statement that will return information about all the existing policies.

SELECT * FROM msdb.dbo.syspolicy_policies

In the event you incorrectly spelled a policy here is a method that will allow you to rename it. Again thanks to Ken Simmons with the #sqlhelp tweet.

SELECT * FROM msdb.dbo.sp_syspolicy_rename_policy @name = 'OldName', @new_name = 'NewName

Ken Simmons (website | @KenSimmons) is also a published author on this very subject so check out his book titled: (Pro SQL Server 2008 Policy-Based Management) with was co-authored by Jorge Segarra (website | @SQLChicken) and Colin Stasiuk (website | @benchmarkIT)

MSDN Ultimate Subscription Winner!

Today I received an email from Ken Simmons (website | twitter) indicating that I was a winner of his MSDN Ultimate subscription contest. Ken Simmons held a contest that provided three methods of entry so I took advantage of two of them via twitter and proof I was a PASS member by emailing an article title that only registered members can view.

This is truly fantastic. I have been wanting to purchase an MSDN subscription for my personal development, but my budget didn’t permit it. Especially at an Ultimate level which is extremely out of my budget range. We do have some where I work, but they are not accessible to me so I try to make do with the time limited trial editions. To be honest 120 days come and go so quickly. Especially if your schedule seems to last throughout the day which makes the trial editions end before you can fully explorer all the functionality.

I have never owned an MSDN subscription before so I feel like a kid in a candy… giddy as can be. Anxious to play and just tinker away. I have so many scenarios that I have archived in my mind that I will be able to finally put into action. I am a bit bias to SQL Server, but I also have a hidden desire to develop skills in the application development arena. I have a few concepts that I believe would be beneficial on a mid-level scale so that would be exciting to see it finally moved into action.

At this point all I can say is Thank you Ken for holding this contest… I deeply appreciate it!

Personal Investments

I am a little late in the certification arena but I have a good excuse… my dog ate my ambition! Actually I have been plugging away at my Computer Information Systems (CIS) degree with a concentration of Database Management and it boils down to a few remaining classes. Seems like I have been working towards this degree for a lifetime. I would have been finished long ago, but I had to decide if I would: 1) either run full steam ahead with my education while working full-time in a travelling required position and let my grades suffer or 2) attend part-time and maintain a 3.5 or higher GPA. I choose the latter. Even though some have told me that a (D) will still get you a degree, that is just not my character. I believe if you are going to commit, then you need to commit 100%. Especially when the investment comes with a hefty price tag. Not to mention “half-ass-ism” can be habit-forming and that quite frankly is something I am not looking to catch.

During these next few classes I have decided to pursue DBA specific certifications. I mean I am certified but the certifications that I currently hold are desktop support centric and since my role has changed it is only natural that I change my focus. To be honest when I first started in the IT field I really wanted nothing to do with databases. I was set on being a Server or Network Admin and thought databases were boring. Looking back I realize I was foolish because now I am consumed in it and wish to do nothing more but DBA work. I literally started working with SQL Server 2000 by an initiative that was put into action by my previous employer. The overall goal was to eliminate the use of paperwork for field staff. So the consultants were brought in and shortly after careful observation a project was born. Instead of equipping the field staff with paperwork to shuffle they were issued laptops with a custom developed application and a local copy of sql server (MSDE) back then. The fact we implemented Merge Replication somewhat enticed me to refer to the field staff as subscribers rather than field staff because I was trying to immerse myself with SQL Server terminology as much as I could. Not to mention I felt cool just speaking it out loud during meetings since this was a new concept to our organization.

I have heard and read about the pros and cons about certifications, but I see it as another item to prove that I can take my own initiatives along with my willingness to continue and further my knowledge and skill-set. Another available avenue for continual education. I have also heard certifications combined with a degree tends to hold more weight over those with just a certification but that is debatable. In the end I suppose it is all up to you and how you feel about it. Some say it boosts personal self-confidence and I am all for that. Why not?

The first exam I might tackle is (70-431: Microsoft SQL Server 2005 Implementation and Maintenance). I know it is a SQL Server 2005 exam, but I am more familiar with 2005 at the present time over 2008. I picked up the Microsoft Press Self-Paced book and will use that as my primary study material. I have heard and read many good things about this title. I also thought about (70-445: Business Intelligence Development and Maintenance). I cannot decide actually because I have a love for both. I see a need for Administration and Business Intelligence development. Either way I figured I would set study time to one hour a day at minimum with weekends being optional. If I can or cannot cover multiple chapters in one hour then so be it. I need to be strict with a schedule in order to stick with it. Luckily I have a license for VMware Workstation 7 which allows me to build a VM study system that I can literally destroy and revert when needed. This will provide me an adequate study platform during my progress. One thing I need to do is schedule myself for the actual test as it will make the process more official since I will be actually setting a realistic and attainable goal. Not to mention it will keep me focused since the added penalty of wasting money will add fire to my priorities and will align my studies.

I am interested in knowing what you would pursue first? SQL Server Administration or Business Intelligence?