Beginning SQL Server 2000 Administration Part 2
This is the second part in the Beginners series and a short one since I'm a little buried. However, it's a common topic that I think many of you will run into. It's something I've been asked more than a few times and answered questions for, but something that most people new to SQL Server might not think of right away.
And spend a bunch of time typing to solve.
Limited Rows
Has someone ever asked you to get them a certain number of rows? Asked you to remove some duplicate rows? Delete all but a few rows?
This article looks at a very basic T-SQL function of how you can limit the number of rows that are returned in a query. I'm sure you will be asked to look at a table, at the data, etc. and there will be lots of times where you don't want to get all the data back. Like when it's a 2 million row table.
Before we learn about limiting the rows, remember from part 1 that the server holds data in tables, which are basically a bunch of spreadsheets. Instead of A, B, C at the top as in Excel, we have column names, which are whatever the person that built them named them. A "query" is the way you "ask" the server to send you the data back, or the rows from the spreadsheet.
Just as when you open an Excel spreadsheet with 50,000 rows and it's slow, returning 50,000 rows from SQL Server will take time. And annoy you.
So without spending too much time on what a query is, what a table is, etc, let's look at a couple ways you can return the data.
Enterprise Manager - Top
I hate seeing admins or developers with Enterprise Manager because I feel it gets them into trouble. But it is easy, so we'll start there. Open your Enterprise Manager client and expand the plus (+) signs until you see either your server name or "(local)". You should see something like Figure 1.
Figure 1
Now expand the server, and expand the "databases" item. Since not every server has the sample databases installed, we'll take a look at master. Don't worry, we're just looking and we won't change anything, so never fear.
Once you expand the "master" database (see Figure 2), you should see a list of items or objects that are contained in this database. There are tables, views, stored procedures, etc. We will focus on tables for now.
Figure 2
Select tables and a list of tables appears in the right hand pane. In this example, we will look at the sysobjects table since I know that will have a decent number of rows in it. If you right click the sysobjects table, you'll get a menu with a number of items. Select All Tasks. This expands to give you the options we are interested in (Figure 3). They are:
- Return All Rows
- Return Top
- Query
Figure 3
Since we're concerned about a large table (this isn't large, but pretend it is), let's select the "Return Top" option. From here we get a dialog box. Let's return the top 10 rows, so type "10" in the box (Figure 4) and hit "enter". You should get something similar to Figure 5. It may vary a little since you may or may not have different objects in your master database.
Figure 4
Figure 5
I hate to say it, but that's it for Enterprise Manager. If you want to query a table and return only a few rows, this is the easy way to do this in Enterprise Manager. Typically you might do this to get an idea of what rows are in the table, or what data is in the table. However you cannot control which 10 rows you get. To do that, you need to use the method below.
Enterprise Manager - Query
One of the other options in Enterprise Manager is the query option. Selecting that will give you a screen similar to that seen in Access and shown in Figure 6. This contains the table you are querying at the top, the filters in the next space, the SQL in the next space, and the results at the bottom.
Figure 6
Since we're looking to limit the number of rows, and determine which ones we want, let's try a couple of queries. Sticking with the sysobjects table, let's return 15 rows this time, starting with the newest ones at the top. Place a checkmark next to the "crdate" column and then drag and drop it into the first cell of the second section (see Figure 7). Next under the "sort type" column, select "descending".
Figure 7
I've also made a couple other changes, in the third section, the one with the SQL code, I've added a "TOP 15" after the "SELECT" keyword. This will limit me to 15 rows. Once you've done this, click the exclamation point in the tool bar (as I've done) and you'll see results, similar to those in Figure 7.
Now all of my objects were created at the server install time, so you don't see any new ones, but if you've added any third party objects or created anything in master, you'll see the most recent ones first.
Now let's limit the data further by specifying which types of rows we want to return. This would be like a filter in Excel. In SQL Server, we use a "WHERE" clause or addition to our SQL. Drag the "xtype" column into the 2nd cell in the middle section. The Utype is the type of object, which is really just a description of what data is stored in this column. In the Northwind database, the Orders table, the OrderDate column is the date on which an order was placed. Here we want to limit our results to a utype of "U", which is a user defined table. In Figure 8 you'll see I've typed a "U" in the criteria column.
Figure 8
Once you click elsewhere or type the "tab" key, this will change to a "= 'U'" in this cell and the SQL section will show the addition of "WHERE (xtype = 'U')" in it. Clicking the exclamation point again will run this query and you'll see 15 new rows.
Figure 9
Note that all of the rows have an xtype of "U". You can change this to any value and see what is returned. Or try it on other tables with the first technique, see what values are returned, and then try to limit your results.
Conclusions
These are fairly simple methods of querying a database, but if you're new to SQL Server, these are basic ways you can get data if someone asks you and you don't want to write code. Specifically, the 2nd method will be handy when you are trying to track down what is new in a database. The 3rd one is effective if you have a better idea of what "type" of data you are looking for.
Next time I'll take a look at security in SQL Server from the new system administrator perspective.
Steve Jones
©dkRanch.net July 2004