SQLServerPedia

For you T-SQL experts out there, Adam Machanic just posted a challenge with a great prize. The best submission wins a full MSDN subscription valued at around $10,000.

Check it out!

For you T-SQL experts out there, Adam Machanic just posted a challenge with a great prize. The best submission wins a full MSDN subscription valued at around $10,000.

Check it out!

Ninja Fog

The cloud has been all the buzz this week. Paul makes a prediction, Denis talks about the challenges, Steve chimes in and the register reports that Microsoft plans to release a full featured SQL Server to cloud. I have talked about this before but I got nothing but ninja fog today.

This comic was adapted from OfficeOFFline.

OK so this isn’t really a lesser known new feature in SQL Server 2008 but it’s a cool one so I figured I’d churn out a working example as those seem to be more popular than the “theory” posts.  So the idea behind MERGE is that you can take 2 sources and you can insert rows that do not exist and update rows that do exist.  Pretty cool stuff that used to require alot more typing and well by now you should know my problem with typing more than what’s absolutely necessary :)  So let’s get right to it.

CREATE TABLE tblNewMembers
(ID INT NOT NULL,
FirstName NVARCHAR(50),
LastName NVARCHAR(50))
CREATE TABLE tblSQLServerPediaMembers
(ID INT NOT NULL,
FirstName NVARCHAR(50),
LastName NVARCHAR(50))
INSERT INTO tblSQLServerPediaMembers
VALUES
(1, 'Clark', 'Kent'),
(2, 'Lana', 'Lang'),
(3, 'Lex', 'Luthor'),
(4, 'Lois', 'Lane')

OK and just for fun let’s have Lana Lang and Lois Lane both marry Clark Kent and take on the Kent last name. We’ll also add a new member Jimmy Olsen.

INSERT INTO tblSQLServerPediaMembers_UPSERTS
VALUES
(2, 'Lana', 'Kent'),
(4, 'Lois', 'Kent'),
(5, 'Jimmy', 'Olsen')

OK so let’s take our “UPSERTS” and MERGE them into the master table (tblSQLServerPediaMembers)

MERGE tblSQLServerPediaMembers A
using (SELECT * FROM [tblSQLServerPediaMembers_UPSERTS]) AS B
ON A.ID = B.ID
WHEN MATCHED THEN 
   UPDATE SET 
   A.FirstName = B.FirstName,
   A.LastName = B.LastName
WHEN NOT MATCHED
THEN INSERT VALUES (B.ID, B.FirstName, B.LastName);

Results: (3 row(s) affected)

So let’s take a look at our master table and see what we get:

SELECT *

FROM tblSQLServerPediaMembers

1 Clark Kent

2 Lana Kent

3 Lex Luthor

4 Lois Kent

5 Jimmy Olsen

 

 

Results look good as Jimmy Olsen is in the table now and Clark Kent is one happy guy.

For all the inner workings of the MERGE statement you can visit the 2008 BOL

Enjoy!!

I've just spoken with my publisher after submitting the final manuscript for my book. In a celebration of sorts, they've decided to offer my blog readers 40 % off the retail price. From as little as $16.50, you can download the book today, and receive the hard copy when it goes into print in about 3 months from now.

Download here and use discount code sqlcrunch40

I’m keen for opinions on the front cover. Where is this guy from, and more importantly, what’s he smoking?

Now for a little information on the book itself. It’s essentially a best practices book for SQL Server DBAs (or developers that find themselves administering the database when they'd rather be doing something else). Whilst the main focus is on the new administration features in SQL Server 2008, there’s a lot of information and best practices which are version independent, therefore, over half of the book is suitable for SQL Server 2005, 2000 and earlier.

Given its best practices nature, the book does not spend a lot of time on step by step instructions. There are plenty of other resources for that, including Books Online. It’s main goal is to cover as many best practices as possible, whilst providing background information on why they are so important.  For example, in addition to recommending the use of SQLIO/SIM and track aligning partitions, it describes why these tasks are important, and includes an overview of how these tasks are performed.

Each chapter closes with a bullet point list of all best practice statements. The chapter list is as follows;

Part I: Planning and Installation

 1. The SQL Server Landscape

 2: Storage System Sizing

 3: Physical Server Design

 4: Installing & Upgrading SQL Server 2008

 5: Failover Clustering

Part II: Configuration

 6: Security

 7: Configuring SQL Server

 8: Policy Based Management

 9: Data Management

Part III: Operations

10: Backup & Recovery

11: High Availability with Database Mirroring

12: DBCC Validation

13: Index Design & Maintenance

14: Monitoring & Automating

15: Data Collector & MDW

16: Resource Governor

17: Waits & Queues: A Performance Tuning Methodology

Appendix A: Top 25 DBA Worst Practices

Appendix B: Suggested DBA Work Plan

Appendix C: Common Performance Monitor Counters

Appendix D: Top 10 Management Studio Enhancements

Appendix E: Date/Time Data Types in SQL Server 2008

Cheers!

Apparently, someone is concerned with Google searches:

http://news.cnet.com/8301-11128_3-10140142-54.html

Now, why are they focusing on Google? I mean, how many hits a day does Amazon.com get? Why pick on Google? What about MSN.com? (I would ask about Live Search but chances are not enough people bother using that for it to matter as far as power consumption.)

I recall a while back reading about how choosing the wrong datatypes can lead to a performance impact on your queries. I dug up this article by Greg Larsen: http://www.databasejournal.com/features/mssql/article.php/3718066/Disk-Space-Usage-and-SQL-Server-Performance.htm. It is worth more than one read and something every DBA should reference the next time a developer asks why you are being so anal about datatypes. Better yet, print it out, roll it up, and smack them in the back of the head with it and leave it on their desk. Maybe then they will get the message about database design. Far too often I am told “the database design work is already done” before our team is even involved. The end result? Tables that can be hundreds of GBs in size, mostly due to the difference in smalldatetime and regular datetime datatypes which seems to be an easily misunderstood concept.

But, hey, if performance is acceptable, should I care? Well, I care about Mother Earth! So perhaps I should start out meetings by asking “who here does not care about the environment?” Or, better yet “does your manager know you do not care about Mother Earth?” So, forget the normalization versus denormalization debate, instead we should focus on answering a simple question “What is the carbon footprint for this design?”

How do you measure the carbon footprint? I have no idea. Something about power. I honestly think it is something that is made up in order to scare people into being more conscious about the environment. Honestly, should I stop using Google to search for information? You might as well tell me to stop brushing my teeth, or taking showers, in order to conserve water. In fact, perhaps we should all move to a shack in the middle of Montana.

Now, where am I going with all of this? Good question. I never really know. But I think what I want to point out is that a poor database design can have effects in places that you would not expect. It could be performance, it could be storage, and apparently it also affects Mother Earth. It should be possible to measure the extra I/O, convert that to a measure of power supply, and calculate a carbon footprint (if anyone knows what that really means). Good luck and let me know what you find out.

But what I really think is happening here is the obfuscation of the issues at hand. Are we, as humans, using the resources of our planet at such a rate they will be completely gone? I suppose so, but I really don’t know. I do know that at one time there was a great debate over how big the population of the Earth could ever become. The argument was essentially “there are only enough resources for a certain number” versus “the more people, the more brainpower, the more efficient we will become at growing food, and we will be able to sustain more”. I could not find a link to the debate, which might be the result of it having been a bacon-induced dream while in philosophy class in college, but this link is close to the idea.

And what about costs in the Cloud? Would you really want your business to be so dependent upon the Cloud that your rates increased, even doubled, in one month simply because your cloud provider has decided to “go Green”? Or would you rather have your head in the sand and tell people you have no carbon footprint, but the truth of the matter is that your Cloud provider is bulldozing their way through the Amazon and you are oblivious to the effects. Can you imagine if the price of Cloud computing was just like gasoline, and fluctuated daily? Well, guess what, it very well could be the case one day.

So, enjoy the good times while they last, keep your head in the sand, and keep Googling for whatever you need.

Buck Woody put together a list of resources for the exam 70-432 TS: Microsoft SQL Server 2008, Implementation and Maintenance.  This is a good list of resources for those looking to take the exam and I do recommend that people do take the exam.  With the economy as it is, it’s good to build up your skills as much as possible.  Completing the exam doesn’t make you an expert, but getting all of the required studying done will make you familiar with the concepts and features the exam covers.

As always, register with Second Chance Exams.  If you don’t pass the first time,  you will get that second go around where you know where your skills gaps are.

Even though I’ve passed this test already, I am thinking that Buck’s list is good enough material that I may just run through it all as a refresher. 

Here is a list of some of other certification posts I’ve put up on this blog.

So in SQL 2008 there is an object explorer command to Select (or edit) top 1000 rows when you right click on a table or view. When you do this a new query window will open, a select (or update) statement is created with the top 1000 rows and the statement is executed. I personally like this alot as when I’m writing stored procedures, etc I find I’m constantly dragging the columns folder into my active window and writing my SELECT statement from that… this definitely helps in my goal to type the least amount possible to accomplish any given task .

The number of rows is actually configurable as well. If you go to:

Tools -> Options -> SQL Server Object Explorer -> Commands

you’ll see that you can change the value for both the SELECT and the UPDATE commands. Nice customization option.

Here is my SQL Server 2010 request: Give me checkboxes to do 2 things:

1. Generate the script in my active query window
2. Allow me to decide if I want the statement to be executed or not

OK in the whole big scheme of things both of these are pretty minor/nitpicky and I’m probably sounding like that high maintenance girlfriend you dated back in high school but in the use-case I listed above (writing a stored proc and wanting to quickly pump out a select statement) I think it would be pretty slick (if configured) to have it generate my select statement, put it into my active window where my cursor is currently residing, and NOT execute.

Hopefully this functionality isn’t already there and I’m just not looking in the right place LOL

What do you think useful? not? pretty high maintenance/lazy request?

Enjoy!!

UPDATE:  So a buddy of mine gave me a buzz and reminded me that if you right click on the table or view and choose Script Table As and then choose SELECT TO you can do basically what I was asking for (with a few more mouse clicks but who’s counting hehehe).  If you select To Clipboard you can paste it into your proc while you’re writing it and off you go.  I guess I’ll have to change my request to something along the lines of… Being able to customize which “quick options” get displayed when I right click on a particular object… sounds cool… it’s probably already there and I just missed it hehehe ;)  Thanks RK!! 

This is a continuation of the class at the NTSSUG.  These are being held once a month before the main user group meeting.

This class went much smoother than the first one.  I start out with my top 10 list of how powershell is better than your wife,

and I end with my top 10 list of how powershell is better than your husband.  And somewhere in the middle there, we get to some scripting.

Here's the vid.

This is a live class I gave at the NTSSUG (North Texas SQL Server User Group).

Let me just say now that it was a disaster from the start.  My laptop wouldn’t  work with the projector so

I had to use someone else’s box at the last minute so I hadn’t tested my demos on that box and to top things off,

the guy who was supposed to speak after me wasn’t ready so I had to go completely without a script for over an hour so I got into some things

I wasn’t prepared for.  That said, it’s still a decent class and will give you an idea of powershell.

But it was definitely one of those nights when I was initially thrown by problems in the beginning and never got back on track.

Here's the vid.