SQL Server with Mr. Denny


March 10, 2008  10:00 AM

Back To Basics: The SELECT Statement

Denny Cherry Denny Cherry Profile: Denny Cherry

There are four basic commands in databases.  They are SELECT, INSERT, UPDATE and DELETE.  Probably the most important of these is the SELECT command.  The SELECT command is how the data in the database is retrieved and displayed.

(All these code samples can be run on all versions of Microsoft SQL Server from 7.0 up.)

Like a regular sentence there are a few basic parts of the SELECT statement.  First there is a list of columns that you want to see.  Then there is the FROM portion of the statement.  This is the table or tables which you will be getting the data from.  Then is the WHERE portion of the statement which is the data filter.

When you put all this together the SELECT statement will look something like this.

SELECT id, name
FROM sysobjects
WHERE type = 'U'

If you were going to read this as a normal sentence it would read something like:
I want to see the "id" and "name" columns, from the "sysobjects" table, where the "type" column has a value of "U".

Now if we need to look at data from two different tables which is combined together we add a JOIN statement between the FROM and WHERE portions of the SELECT statement.

SELECT sysobjects.name, syscolumns.name
FROM sysobjects
JOIN syscolumns on sysobjects.id = syscolumns.id
WHERE sysobjects.type = 'U'

You can see from the above statement that we are getting the table names and column names for all the user tables. When you look at the JOIN command you see that we are matching up the id column from both of the tables. Now in this example the column name is the same in both tables. This isn’t always the case. If you don’t know how the tables relate to each other, you can usually look at the foreign key constraints to see how the tables relate to each other.

If you want to get counts of data you can use the GROUP BY clause along with the COUNT() function. In this next statement we will count the number of columns in each table.

SELECT sysobjects.name, count(*)
FROM sysobjects
JOIN syscolumns on sysobjects.id = syscolumns.id
WHERE sysobjects.type = 'U'
GROUP BY sysobjects.name

When ever you use a mathematical function such as MIN(), MAX(), AVG(), COUNT(), etc you have to add the GROUP BY clause to the SELECT statement so that SQL Server knows how to roll up the data.

Now as a last part of the SELECT statement to look at today, we are going to filter our GROUP BY query further, by showing all the tables that have over 5 columns. To do this we use the HAVING clause. Without the HAVING clause we would have to do a very complex query as a sub-query in the WHERE clause which would be very inefficient.

SELECT sysobjects.name, count(*)
FROM sysobjects
JOIN syscolumns on sysobjects.id = syscolumns.id
WHERE sysobjects.type = 'U'
GROUP BY sysobjects.name
HAVING count(*) > 5

The HAVING phrase is very simple in it’s syntax. You put the math function that you want to use (in this case COUNT(*)) then <, >, <> or = and what ever you want to compare it to.

I hope that you find this post useful. I’ve barely scratched the surface of the SELECT statement, and what it can do. I encourage everyone to open up Books OnLine and read through the information on the SELECT statement. It includes more examples, and some of the other options which are available to you.

Denny

March 6, 2008  3:12 AM

I’ll be speaking at the San Diego SQL Server Users Group

Denny Cherry Denny Cherry Profile: Denny Cherry

The good folks of the San Diego SQL Server User Group has invited me to come speak to them on March 20, 2008.  The meeting starts at 6PM.

 If you are in the area I urge you to come and check it out.  The topic of my talk will be Query Tuning as well as a bit of Query Governor.  If you attended the SoCal Code Camp in January and weren’t able to catch my session then, now is your chance.

The slide deck will be a little different so I’ll be adjusting it a little bit.  I’ll try and get the slide deck posted before the meeting, but no promises.

More information about the San Diego SQL Server User Group can be found on thier web site http://www.sdsqlug.org.  See you there.

Denny


March 6, 2008  2:26 AM

New Paper Published: Bullet-proof Change Management – Don’t just Cross your Fingers and Hope

Denny Cherry Denny Cherry Profile: Denny Cherry

I’ve written a technical brief on how my company, Awareness Technologies, uses the Quest Software Change Director for SQL Server product to monitor for and track unauthorised changes to our environments.

Denny


March 3, 2008  10:00 AM

T/SQL Back To Basics

Denny Cherry Denny Cherry Profile: Denny Cherry

I’ve seen a lot of posts online over the last few weeks with people asking about basic query syntax.  So over the next few posts I’m going to show some basic T/SQL queries as well as some more “advanced” features and syntaxes.

Hopefully you’ll find these syntaxes useful.

All the code syntaxes will cover SQL 2000 and SQL 2005.  Where code only works in one version or another it will be noted.

Denny


March 2, 2008  11:45 AM

New Article: Configuring SQL Server memory settings

Denny Cherry Denny Cherry Profile: Denny Cherry

I’ve recently published a new tip on SearchSQLServer.com called “Configuring SQL Server memory settings“.

Denny


March 2, 2008  4:43 AM

New Article: Create an upgrade plan for your move to SQL Server 2005

Denny Cherry Denny Cherry Profile: Denny Cherry

I’ve published a new tip over on SearchSQLServer.com entitled “Create an upgrade plan for your move to SQL Server 2005“.

While it’s based on upgrading to SQL Server 2005 it can be used to upgrade to SQL Server 2008 as well.

Denny


February 29, 2008  1:28 AM

What should I speak on at PASS this year?

Denny Cherry Denny Cherry Profile: Denny Cherry

Well the call for speakers for the PASS Summit 2008 should be coming up shortly, and I’m trying to decide what to submit to speak about.  And I decided that I’d ask the community here; If you were going to go to the PASS Summit this year what sessions would you like to attend?  Please post your ideas to the comments below.  Nothing is to crazy (as long as it’s got something to do with Microsoft SQL Server).

 I’ve got a few ideas which I plan on submitting already, but I want to make sure that what I submit is what people want to actually here.

 For those who haven’t attended PASS in prior years it is a great place to network and learn a ton about Microsoft SQL Server.  There isn’t a whole lot of info posted yet, but the dates and basic info is posted on the PASS web site.

Denny


February 29, 2008  12:01 AM

Webcast: Recover the Data, the Whole Data and Nothing but the Data You Need

Denny Cherry Denny Cherry Profile: Denny Cherry

Coming on the heals of the extremely successful webcast “Under The Hood of SQL Server – Checking Out Internals”, Quest Software has asked me to co-present another webcast.  This one is entitled “Recover the Data, the Whole Data and Nothing but the Data You Need” and will be on March 6, 2008 at 8am Pacific (11am Eastern).  I’ve been told that so far the sign up rate has been incredible so far.

If you are interested in the product or doing object level restores (restoring one table or stored procedure, etc instead of restoring the entire database) then I highly recommend the webcast; granted I am a little biased.

See you at the webcast.

Denny


February 28, 2008  8:00 AM

SQL 2008 Launch Event was Yesterday

Denny Cherry Denny Cherry Profile: Denny Cherry

Well I just got back from the Launch event.  The event was excellent.  Microsoft was giving out a lot of really excellent information about all the products.  The turnout was just amazing.  I heard that about 5000 people registered, and that based on prior events about 3500 were expected to actually show up.  Somewhere around 6500 people actually attended so the show was very busy.

Tom Brokaw and Steve Ballmer both gave excellent speeches at the keynote speakers.  Because of the large number of people in attendance the check in process seamed to take quite a while for a lot of people.  The demos which were done were very interesting.  There was only one occasion of the “cooking show magic” where you go from the before to after without showing the changes with SQL Server Reporting Services Report Designer when adding a column.  Everyone seamed to know why we weren’t shown the work of adding a column to a report because it’s not as easy as they wanted it to seam.  We all got the gist of what they were getting at though.  Many of the sessions were packed so full that there were more people then could actually get into the session rooms.

Because of the extra people in attendance the lunch situation wasn’t the greatest, but it seamed that they were able to deal with it.  I didn’t see anyone going without lunch so I guess they were able to deal with it pretty quickly.

The only real complaints that I have are that the Microsoft pavilion was in a separate room from the main pavilion room so it was a little hard to find.  I didn’t even realise that there was a Microsoft pavilion until half way through the day, and by then I didn’t have time to go and check it out.

Because I was one of the first 1000 people to register for the event I was invited to the AMD / Microsoft party after the launch event.  The party was awesome to say the least.  The Barenaked Ladies performed a full set, and it was a great show.  Now I admit, I’m not the biggest fan of the Barenaked Ladies, but they put on a great show.  They appeared to be having a great time, and the music was great.  It was a top notch way to end a great day of learning.

Be sure to check out the event when it comes to a town near you.

I tried to take some photos, but my camera just didn’t cut it.  Some folks that I know took some pictures with me.  As soon as I get them, I’ll post them up on the site for all to see.

Denny


February 26, 2008  8:00 AM

SQL 2008 Launch Event is Tomorrow

Denny Cherry Denny Cherry Profile: Denny Cherry

Well, tomorrow is the official launch event for Microsoft SQL Server 2008 (as well as Windows Server 2008 and Visual Studio 2008 but really cares much about them).

If you can’t make it to the launch event I’ll be posting to my blog as often as I can through out the day to everyone updated on the information which is released.

I highly recommend that everyone check out the launch event in thier area.

Denny


Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to: