Description |
Tip |
| Choosing a sort order | If you're just now installing SQL Server, you can make choices about how
you install your server. For example, you choose your server's sort order
when you install SQL Server. The sort order you choose has an impact on
your server's performance. You can choose from the following sort orders:
binary, dictionary order case-sensitive, dictionary order
case-insensitive, dictionary order case-insensitive uppercase preference,
and dictionary order case-insensitive accent-insensitive. If you want to get the highest possible performance out of your server, you should choose the binary sort order. The dictionary order case-sensitive, dictionary order case-insensitive, and dictionary order case-insensitive uppercase preference sort orders are 20 percent slower. The dictionary order case-insensitive accent-insensitive sort order is 35 percent slower. |
| Creating scripts for database objects | Here's a quick and easy technique you can use to generate scripts for
tables, views, procedures, rules, defaults, and user-defined data types in
SQL Server 7.0. Open SQL Server Enterprise Manager. In the right pane of
SQL Server Enterprise Manager, right-click on the object for which you
want to create a script, and choose Copy. Then, open a text editor like
Word or Notepad and choose Paste. There's your script! Submitted by: Thomas Peterson [Tom.Peterson@Rainier.com] |
| Diagramming your SQL databases | SQL Server 7.0 includes the ability to create a graphical diagram of
your database. You can use this diagram to view the structure of all
tables in the database, as well as links between the tables in the
database. SQL Server displays the primary key-foreign key link between
tables. To create a database diagram, open SQL Server Enterprise Manager. Expand the database on which you want to create the diagram. Right-click on the Diagrams object, then choose New Database Diagram. You can then select some or all of the database's tables to include in the diagram. You can save the diagram so that you can retrieve it at a later date. In addition, you can print the diagram to document the structure of your database. |
| Publishing SQL Server data on a Web page | One of the easiest ways you can generate a Web page based on your SQL
data is to use the Web Assistant Wizard. This wizard enables you to choose
the database from which you want to publish and the table you want to
include on the page--and the columns from this table. You can also
schedule a job to create the Web page: you can configure SQL Server to
generate the page immediately, on demand, at a specific point in time,
when the data changes, or at regularly scheduled intervals. If you choose to have SQL Server update the Web page when your database's data changes, you can even choose which columns you want SQL Server to monitor for changes. The Web Assistant Wizard will also let you add some formatting to the Web page, as well as links to other sites. Once you've generated your Web page, you can go to a more sophisticated tool for formatting the page such as Microsoft FrontPage 98. |
| Recovering from a crashed master database in SQL Server 7.0 | If you lose your master database, you must first rebuild it by using the
new "rebuildm" utility in SQL Server 7.0 before you restore your
backup. To start the utility, use Windows NT Explorer to access the
\MSSQL7\BINN folder, then double-click on rebuildm. Next, specify a path
to your SQL Server CD-ROM by clicking on the Browse button. Then click on
Settings to specify your server's character set, sort order, and unicode
collation configuration. Finally, click on Rebuild to rebuild your master
database. Once you've rebuilt the master database, you can restore your
backup of the master database by starting your server in single-user mode
(type sqlservr -m at a Command Prompt). Next, start SQL Server Query
Analyzer and run the following query: RESTORE DATABASE master FROM DISK = 'name_of_your_backup_device' WITH RECOVERY This will restore your backup of the master database and automatically shutdown your server. You should now be able to restart your server by using SQL Service Manager or the Services icon in Control Panel. (Note: When you rebuild the master database, SQL Server automatically rebuilds the msdb database. You should also restore your backup of the msdb database after rebuilding the master database.) |
| Server Status | Q. When I launch Enterprise Manager for SQL Server 7.0 and expand the
group of registered servers, a window is supposed to show the status of
the server (such as started or stopped) without connecting to the server
itself. Unfortunately, I haven't found a way to implement this
functionality. I'm using the registered servers collection and the SQL
Server collections that SQL Distributed Management Objects (SQL-DMO)
provides. The registered server class doesn't have a status property, and
the Status property of the SQL Server COM class appears only after I
connect to the server. How can I determine the status of the SQL Server
machine without connecting or using SQL-DMO? And why is there a delay
between the time Enterprise Manager displays the registered servers and
the time that it displays the servers' status? Are multiple threads at
work here? A. You must use the Win32 services APIs if you want to detect the status of SQL Server services without connecting to SQL Server. To answer your second question: Yes, more than one activity is going on here. SQL-DMO calls the Win32 API to check on the service status. You can find the API in the Win32 software development kit (SDK), or you can search the Microsoft Developer Network (MSDN) site. The delay is caused by SQL Server calling the Windows API and waiting for the API to respond. |
| Sharing server registration information in SQL Server Enterprise Manager | When you register servers in SQL Server Enterprise Manager, SQL Server
stores this information in the Registry. One problem you might run into,
though, is that if you log on to Windows NT as a different user, or you
move from computer to computer, you'll have to register your SQL servers
in SQL Server Enterprise Manager for each user (or when you move to a
different computer). SQL Server 7.0 enables you to share your registration
information on the server so that you can access it from anywhere. To create shared registration information, begin by configuring your server to not store the registration information for each user. Open SQL Server Enterprise Manager, then choose Tools, Options. In the Properties dialog box, uncheck the Store User Independent option, then click OK to save your change. When you close the Properties dialog box, you'll have to re-register your servers to create the shared registration information on your server. You can then access this shared registration information from any instance of SQL Server Enterprise Manager by choosing Tools | Options. In the Properties dialog box, select Read From Remote. In the Server Name text box, type the name of the SQL server on which you created the shared server registration information, then click OK. You should now be able to see all of the servers you've registered in SQL Server Enterprise Manager. |