| Downloads |
|---|
| 23273.zip |
When you use SQL Server Enterprise Manager in Microsoft SQL Server 2000 to create a table, the bottom half of the screen lists several properties of the selected column: Description, Default Value, Precision, Scale, Identity, Identity Seed, Identity Increment, Is RowGuid, Formula, and Collation. How can I use a SELECT statement or function to return the Description property for a particular column?
Enterprise Manager creates and stores the Description property as an extended property. You can use extended properties to store application- or site-specific information about the database and the following database objects: tables, views, procedures, functions, defaults, rules, columns, parameters, indexes, constraints, and triggers.
You use three system-stored procedures and a function to create and manage extended properties:
- sp_addextendedproperty
- sp_updateextendedproperty
- sp_dropextendedproperty
- fn_listextendedproperty()
Enterprise Manager uses these commands for creating, managing, and retrieving the description property. Figure 1 shows how to add a description for the au_id column of the authors table in the Pubs database. The following query shows how you can use fn_listextendedproperty() to retrieve the extended property information you just added:
SELECT * FROM ::fn_listextendedproperty (NULL, 'user', 'dbo', 'table', 'authors', 'column', default)
This code produces a table that describes the extended property named MS_Description. This naming taxonomy is consistent with objects that you name through Enterprise Manager. For more information about extended properties, see Bill Vaughn, "Managing Extended Properties," http://www.sqlmag.com, InstantDoc ID 20886, and the "Property Management" topic in SQL Server Books Online (BOL).
I understand how to use the TOP clause within a SELECT statement to retrieve the first n rows of a resultset, but I have a business need to return the second set of n rows. For example, rather than returning the first 10 rows, I want to return rows 11 through 20. What's a good way to retrieve a second set of rows?
SQL is a set-based language, which means that it doesn't support working with specific rows within a resultset well. Using a server-side T-SQL cursor to solve the problem of get rows 1120 is easy, but I don't recommend that solution. Most SQL experts agree that server-side T-SQL cursors are horribly inefficient. Although T-SQL cursors aren't a problem when you're working with a query that you run infrequently and that doesn't operate on many rows, I recommend that you avoid server-side T-SQL cursors in favor of set-based solutions whenever possible.
A set-based solution is a solution that you can express by using single T-SQL commands that operate on a range of rows to return one resultset. With this solution, you don't need to rely on the use of a T-SQL cursor.
Sometimes, taking a problem that lends itself to a row-by-row, iterative cursor-based solution and creating a set-based T-SQL solution is difficult. However, you can usually find a creative way to solve the problem. The T-SQL code in Listing 1 shows one example of how you can return rows numbered 1 through 10 from a resultset without using a T-SQL cursor.
The query in Listing 2 shows the 10 orders with the lowest total order amounts from a specific vendor. This query might be a bit complex to follow if you're a SQL novice, so let's walk through it quickly. You reuse the query from Listing 1 twice in Listing 2. You use it the first time to generate a list of the 10 order IDs with the lowest total order amounts, which you then use as the subquery that the NOT IN clause operates on. The outer query in Listing 2 is essentially the same query as in Listing 1 except that you filter out the order IDs that you've already counted within the subquery.
You can choose from a variety of set-based operations for accomplishing this same task. For this answer, I didn't worry about query optimization, and the NOT IN clause isn't always the most efficient query construction. However, this construction makes the logic and query easier to follow than some alternative query formulations; it also makes demonstrating the primary point of this answer—avoiding row-by-row T-SQL cursor operations—easier.
I need to programmatically drop SQL Server logins associated with Windows authentication. I have a login named PACIFIC\VNE1B that uses Windows NT security authentication. When I supply the sp_droplogin procedure with PACIFIC\VNE1B or VNE1B, I get an error message that says the login doesn't exist. What's wrong?
I love the simple questions! You should spend some time in SQL Server Books Online (BOL) reading about the differences between Windows-authenticated and SQL Serverauthenticated logins. The simple answer is that sp_droplogin is intended for managing SQL Serverauthenticated logins only. Sp_revokelogin is the correct command to use when you want to drop logins that use Windows authentication.
Under the covers, different sets of T-SQL commands control Windows-authenticated logins and SQL Serverauthenticated logins. But don't worry—it's easy to get confused if you primarily work through SQL Server Enterprise Manager's GUI tools, which mask many low-level details.
I work on a payroll-management database and suggested to the development team that I use a datetime column as a table's primary key. Team members told me not to do this, but when I pressed for a reason, no one could provide a good answer. Why shouldn't I use a datetime column as a primary key?
I agree with your development team. In general, you should avoid using datetime as a primary key for a couple of reasons.
First, datetime is an 8-byte data type, and narrow keys tend to be more efficient and faster than wider keys. If your table is going to be very large, a smaller integer-based data type, such as the 4-byte int or the 2-byte smallint, might be a better fit.
Second, and much more important, datetime is accurate only to one three-hundredth of a second, or 3.33 milliseconds (ms). Table 1, which I copied from SQL Server Books Online (BOL), shows how SQL Server rounds different datetime values. By definition, primary key columns must be unique, and you can't ensure that you'll have unique values in a datetime column. Your business rules might say that entering multiple records within 3.33ms of one another is impossible, but I think that making that assumption is dangerous. Business rules and technical assumptions can always change.
I need to capture the SQL trace of a process that I scheduled to start daily at 2:00 a.m. I don't want to dial in at 2:00 a.m., but I can't figure out how to set up a trace programmatically by using the SQL trace system stored procedures. Can I schedule a trace through the SQL Server Profiler GUI?
I have bad news and good news. The bad news is that SQL Server has no interface for directly scheduling a trace to run from the Profiler GUI. The good news is that you don't have to get up in the middle of the night, and you don't have to struggle through SQL Server Books Online's (BOL)'s poor trace procedure documentation.
The Profiler GUI provides a convenient mechanism for scripting the definition of an existing trace into a SQL file that makes the proper calls to the SQL trace procedures. Simply create a trace that includes the events, data columns, and filters that you need. From the File menu, select the Script Trace option, then select SQL Server 2000 or SQL Server 7.0. Trace procedures have changed dramatically between SQL Server 7.0 and SQL Server 2000, but the new Profiler GUI knows how to create the proper trace calls for both versions. Now your trace file includes standard SQL statements that you can use as a starting point to create a job that you can schedule to run through SQL Server Agent or another scheduling tool.
If you want the script to be a general-purpose task that you can run at the same time every night, you need to take one final step. You have to edit the script that Profiler generates to ensure that the script stops when you want it to.
I have an instance of SQL Server 7.0 that was installed using a sort order different from the SQL Server 7.0 default. Now the instance is different from every other SQL Server instance we're running, and the difference has been causing some annoying problems. How can I change the SQL Server sort order?
Unfortunately, I don't have an easy answer if you're using SQL Server 7.0. Under SQL Server 7.0, the sort order you choose during installation is set for the entire server. You can't change the sort order for an individual database, and you can't change the order after SQL Server has been installed without rebuilding the Master database and choosing a new sort order, unless you simply start over with a fresh installation. Both approaches require you to export your data, perhaps to flat files, and reimport the data into new databases after you rebuild the server or reinstall it with a new sort order.
Alternatively, you could upgrade to SQL Server 2000, which supports multiple collations (a fancy word for sort orders) within the same server. In fact, SQL Server 2000 lets different columns within the same table have different collations.
SQL Server 2000 doesn't provide a command that lets you globally change the collations of a database's existing columns, but you can change collations on a column-by-column basis by using the ALTER TABLE command. You'll find plenty of information about this topic if you type the word collations into the Index tab of the SQL Server Books Online (BOL) search mechanism.
Can you detach a database from SQL Server 7.0 and attach it to SQL Server 2000?
Yes, but be careful. SQL Server 2000 makes slight changes to a SQL Server 7.0 database file before attaching it to SQL Server 2000. These changes don't cause data loss, but they'll keep you from subsequently reattaching the database to SQL Server 7.0. Just to be safe, make a file-level backup of the detached database files before you attach them to SQL Server 2000, in case you need to reattach them to SQL Server 7.0.
I don't understand how SQL Server Enterprise Manager reports a table's column sizes. For example, I created a table called HowBigAmI with a single column of type nvarchar(10), then issued sp_help HowBigAmI to see what SQL Server would report as the column size. The result said the column had a length of 20. But I defined the column as varchar(10), and Enterprise Manager tells me the length is 10. Why is SQL Server reporting different lengths?
Run the script
CREATE TABLE HowBigAmI (Col1 nvarchar(10)) GO sp_help HowBigAmI GO
and you'll see that sp_help indeed shows the column size as 20, but Enterprise Manager's design view for the table shows the size as 10. SQL Server Books Online (BOL) says Enterprise Manager shows column size as the number of bytes, but that isn't the case. Enterprise Manager shows the number of characters that can fit in the column. The script defines the Col1 column as Unicode (that's what the n in front of varchar means), which is a two-byte character set. So, Col1 can accept 10 Unicode characters, but each character consumes 2 bytes. Thus, you have Enterprise Manager reporting the column size as 10 and sp_help reporting the column size as 20. I agree that this behavior is confusing. But in reality, DBAs need both pieces of information. DBAs need to know that they're limited to only 10 characters when storing data in the column and that the column consumes 20 bytes.
