Top 5 Reasons to Attend SQLSaturday #47

I am going to take Jose Chinchilla (Blog | Twitter) approach to help promote SQLSaturday #47. Though he listed the “Top 10 reasons to attend SQL Saturday #62 Tampa” I am only going to touch on the Top 5.

SQLSaturday #47

When: Saturday, Feb. 19, 2011 — Where: 2626 E Pecos Rd Chandler, AZ 85225

Reason-5: Free Training

SQLSaturday permits people in the industry to offer real world examples of some of the challenges they faced. It promotes a fun, laid back learning experience for persons with different professional backgrounds. If you’re a seasoned professional or someone looking to get into the database world SQLSaturday #47 is guaranteed to have a session for you. Sessions covering topics on Database Administration, Database Development, Business Intelligence, Troubleshooting, writing T-SQL and much more… so don’t delay and sign up today!

Reason-4: Perfect Weather

February is spectacular in Arizona. It provides the opportunity to escape the colder climates and get cozy with terrific weather. Arizona is a beautiful during this season and the greens are ideal this time of year. Perfect weather for outdoor networking, the facility is beautiful and only compliments the entire experience like that perfect bottle of wine to make the dinner experience awesome.

Reason-3: Rockin’ Sessions

We have a fantastic lineup. Excellent speakers delivering high quality content. A great mixture of experience working with small to VLDBs combined with great presentation skills is what you have to look forward to. Looking for an SSIS session… we got it. Looking for a powershell session… we got it. Looking for a clustering session… we got that too! I am telling you we got what you need!!!

Reason-2: Getting Involved

Getting involved with the community returns several benefits for everyone involved:

  1. you get to know different people whom you might have not known otherwise
  2. knowledge exchange — share experiences and war stories
  3. you build new friendships and expand your network
  4. you’re not alone… help (#sqlhelp) is there if and when you need it
  5. you can become an acronym and twitter hash tag junkie
  6. you can hang-out with the cool kids
  7. Reason-1: Networking

    Come out and meet and greet fellow database professionals. Learn something new while making a new friend. We are a community and SQLSaturday is about community involvement, in a nutshell peers helping peers. You never know… the people you meet at SQLSaturday #47 might lead to a new and exciting career. Why would you pass up a chance like that? You still have time to sign-up!

    http://sqlsaturday.com/47/eventhome.aspx

    Advertisements

Get row count from all tables

I was reviewing some of my older scripts and I came across one in particular that stood out. I wrote it quite a while ago and looking back does indicate where I came from in terms of logic. To give a little background my script was written to grab the row counts from all tables within the given database. The problem… it created a temp table then loaded the temp table from querying the INFORMATION_SCHEMA.TABLES. Then it looped through each of the table names loading them it into a variable and then passed them to an update statement which updated the temp table. As you can see this was a very inefficient way to go about it.

I was reading up on catalog views and it seemed that I could query sys.sysobjects and join sys.sysindexes. So I figured I would get this verified. I posed the question and following are the responses I received.

Yep. sys.sysindexes is depreciated in Denali. Information here: msdn.microsoft.com

So here is my script. I originally left out line 15 and when I did I saw a lot of duplication in the results set. So I surfed around and found a post titled, “Display Row count for all tables” by Shyam Skj that used the indid field from sysindexes to filter the records. The closes thing I found was the index_id from sys.partitions and lo and behold using similar syntax the results returned nicely.

/* Check if object already exists */
IF OBJECT_ID('usp_GetTableRowCounts') IS NOT NULL
/* If available drop object */
BEGIN
DROP PROCEDURE usp_GetTableRowCounts
END
GO
/* Create procedure */
CREATE PROCEDURE usp_GetTableRowCounts
AS
SELECT o.name 'TableName', p.rows 'Rows'
FROM sys.objects o
JOIN sys.partitions p ON o.object_id = p.object_id
WHERE (o.type = 'U')
AND (p.index_id IN (0,1))
ORDER BY p.rows DESC

AdventureWorks Results

Internals and Performance Class by SQLSkills

Before I just dive in and tell you why I want to win the free seat offer for the (5-day Internals and Performance class in Dallas) from the folks at SQLSkills I want to take the opportunity to provide a little history first to help paint a picture.

So here I go…

I was handed the responsibilities of managing and maintaining our CMMS and WorkOrder management systems which is how I started working with databases. Prior to that I had zero experience. In a nutshell I had no idea where to start or what to do, let alone understand the ways to ensure if it was even functioning properly.

Most of my education and mentoring came from working with our vendors. Our company really valued consultants for their technical abilities and knowledge. Being the novice that I was (for the most part still am) I took everything they said and did as gold.

I supported the databases on a regular basis, monitored replication using Replication Monitor, removed and subscribed the subscribers and extracted data from subscriptions that had expired. I even scheduled my backups, tested backups, granted permissions to other staff so they could use Replication Monitor and developed and published reports using Reporting Services. At this point I was felling pretty good. The data was available to my client base, I scheduled my updates to limit the down time and things were going smooth. I even transitioned the servers and subscribers (about 200+ subscribers) from SQL Server 2000 to 2005 which made an incredible difference in terms of initial snapshot delivery and synchronization.

New Opportunity

One day an opportunity came up with the vendor and I was offered the job. Now I was feeling great, confident and most importantly I felt like a DBA. I was catapolted into a started project and things were looking great. Project after project I am implementing SQL Server 2005, setting up replication, setting up my subscribers and developing business intelligence reports using SSRS.

Reality rears it’s ugly little head

Then I joined a few social networking sites (twitter being one of them). Took on the handle @sqlsamson. Then as I started to rub virtual elbows with people in the online community and I quickly realized that I had no right to assume the moniker @sqlsamson. I cannot place myself at the ranks of people such as: @MrDenny, @BrentO, @SQLRockstar (just to name a few) and literally several others. Through simple tweets and blog reading I found that I barely scratched the surface and quite frankly they opened my eyes to a world I have never been apart of.

Sounds of change

So I took a step back, decided to drop the SQL from the name and switched to @just_samson. I saw this as an opportunity to make things right. I have discovered so much in such a short period of time. It’s amazing, the more I know the better I will be. I want to be a SQL DBA and that’s my focus. I love what I do, but I owe it to my clients to ensure that I am exercising all avenues to keep the database running at its optimal performance. This means reviewing and revising our current database structure and most importantly revising our practices to make the necessary changes for the better going forward. The SQLSkills Immersion Event ties into this perfectly.

If I win… it will be a huge step into the right direction and would help propel me towards my goal of being a real SQL DBA.

Excel 2007 Conditional Row Formatting

It’s pretty easy to highlight every other row in Excel using a condition. In the matters of highlighting a row based on a value of single cell is another story. Not entirely complex but nonetheless requires some knowledge of writing formulas and knowledge of the INDIRECT function. Bastien blogged about Conditional row color based on a cell value which illustrates the process quite nicely. The steps I am about to cover mimic Bastien’s procedures with the exception of targeting Excel 2007 instead of Excel 2003.

Sample data set

Step 1: Highlight the rows

You will need to highlight the rows that are targeted in which you wish to apply the conditional formatting.

Step 2: Click on Conditional Formatting

Conditional Formatting is found under the Home tab of the Ribbon.

Step 3: Select Manage Rules

Step 4: Click New Rule

Step 5: Select Use a formula to determine which cells to format

Step 6: Enter the following formula which uses the INDIRECT function

=INDIRECT("C"&ROW())="M"

Then click the Format button to specify the formatting options

Step 7: Click the Fill tab

Step 8: Select the background color

Once you have selected a color click the OK button to proceed

Step 9: Click OK

Step 10: Click OK to apply formatting

Final results

To highlight the rows that have a Gender value of F in column C simply repeat steps 4 – 10 and switch the conditional value to F as in (Step 6)

Formula Syntax:

=INDIRECT("C"&ROW())="F"

T-SQL Tuesday: Lucky 7-Resolutions

Official TSQL2sday Logo

TSQL2sday

Every year I make a resolution list just like anyone else and often I am able to achieve them while other items are pushed aside for reasons beyond my control. This year however is slightly different now that I have completed my Bachelors of Science degree in Computer Information Systems with a focus on Database Management. This alone makes some of my resolutions much more attainable. With the added time that I have I can finally focus on certification studies among other things that pertain to personal and professional development. If I am able to juggle my time wisely I can achieve the ultimate ying-yang of personal time management.

So to get down to business here is my list of things I want to accomplish in 2011:

1. Health

Without no surprise in order to achieve my goals I need to be healthy. I dropped about 30 lbs during 2010 and have about 20 lbs to go to reach my ideal weight, which is 190. So before 2011 comes to a close I should be at or around 190 lbs. Chances are I will meet that goal before mid year. I am pretty confident it will be definitely before mid year.

2. Certifications

I currently hold three certifications, but it’s time to buckle down and concentrate on SQL Server centric certifications. Before the end of the first quarter I want to complete the MCITP Database Administrator certification for SQL Server 2008. Which means I started studying for exam 70-432 the beginning of this year. And in order to make things more realistic I am going to schedule for the test by this Friday (to be taken in the near future) which will add the much-needed pressure thus making accountability that more important.

3. ETL Stizzuf

There is just something about Business Intelligence that tickles my fancy. It’s hard to describe but I love reports, especially those I don’t have to hand write. 2011 will be the year I become a BI bad ass so-to-speak. To paint a picture I want to strive to become the west coast version of Brian Knight but in a smaller scale all while wearing Adidas kicks or sandals with socks on because I feel strange without socks… all other attire is optional. At one point or another within the year I want to actually spend a night in a warehouse while developing a data warehouse. I think that would be peachy! Icing on the cake if you will.

4. Get more involved

I touched the waters last year and started to participate where I could. To my surprise I found that people are actually willing to lend a helping hand and share the wealth of knowledge with one another. When I was handed the keys to manage the organization’s data I really didn’t know where to begin or who to consult in the event I ran into a brick wall. I picked up some books, started browsing blogs and joined 26 social networking sites. Then after learning more about social networking I found that twitter and facebook seem to have the highest concentration of SQL Socialites. I can’t say they accepted me, but I can say they have made an impact on my life professionally and personally. I won’t get into details but know that I am grateful.

5. MVP or runner up

This might be a long shot given the pool of MVPs out there. So the stakes are high and quite frankly I have no idea how to toss my hat into the ring. This is something I have only read about lightly and never really asked someone how they achieved MVP status. But nonetheless I need goals to keep me aiming high and moving in the right direction.

6. Organize a SQLSaturday

This might be cheating along with a bit of self promotion since we are slated to launch #sqlsat47 this February 19th, 2011 at Chandler-Gilbert Community College. This ties into list (Item 4) about getting more involved. I am a newbie in terms of event coordinating but I plan all with my co-organizer @coneybeer to deliver a successful SQLSaturday. There is still time to register for the event… I hope to see you there!!!!

7. Participate in the TSQL2sDay blog party

I have seen many of these pop up last year and I find myself very intrigued. I did some research about #TSQL2sDay (which was founded by Adam Machanic) and anyone can become a host provided they participate in at minimum two #TSQL2sDay events which are held monthly, keep an active blog followed up with an email top Adam Machanic. So with the list I have this year and the participation that I plan on doing I should be ready come next year to host a #TSQL2sDay event. Check out the