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.
End of Article
Prev. page
1
2
[3]
next page -->
