All SQL Server Tips


Access says they're #Deleted, but they're not

Drat! If you've ever used Access 2.0, 95, or 97 in conjunction with SQL Server (or other RDBMSs including the one from the big O) using an ODBC connection, you may have run into this frustrating message. If Access can't find records based on your SELECT (e.g. the table has NULL values in the index field, or has an index based on a float value, or has an text index that may be padded with blanks, or has a trigger that modifies the index value underneath it), Access figures the record has been deleted. Possible solutions: user education or "data cleansing."

SQL Server Magazine, sister publication of Windows NT Magazine


Do Named Pipes Matter in a TCP/IP Age?

Many people have heard the term "named pipe" in reference to a SQL Server Netlib (network library protocol), but few people seem to know what a local named pipe is. Local pipes are optimized versions of the "standard" named pipe and are designed for IPC (interprocess communications) within the same machine. Network named pipes send data down through NT's networking layers and out to the NIC (network interface card). The NIC grabs the data right back and sends it back on up through NT's network layers. The benefit of local pipes is that they completely bypass the network-which yields a significant performance boost when transferring large data sets. Normally SQL Sever handles requests from network clients, so a local pipe can't be used. But it can be very effective for bcp operations run from the physical SQL Server machine, and a local named pipe is also beneficial when IIS (Internet Information Server) and SQL Server are co-resident on the same box. To force a local pipe when running bcp, leave the server name blank and specify "local" as your server name from an ODBC connect string.

SQL Server Magazine


Encryption

No, SQL Server doesn't encrypt data it sends over the network because, for most scenarios, encryption is overkill. However, for some applications, like some financial service or Internet-based e-commerce applications, you may want your data to be encrypted over the wire. SQL Server's Multi-protocol Netllib offers this feature and can be run over any network protocol supported by Windows NT. Obviously, this would be TCP/IP if you're connecting over the Internet. Remember--encryption isn't enabled by default; you have to explicitly enable it via the SQL Server "setup" utility. Be prepared to pay a performance penalty of 10-20 percent. If you may need to configure the Multi-protocol Netlib to communicate to and from SQL Server through a secure firewall, check out Knowledge Base Article Q164667.

SQL Server Magazine


Not Enough Foreign Keys

SQL Server 6.5 limits you to about 31 foreign keys (FKs) to a table (the actual number varies depending on whether work tables are needed). The good news is that in SQL Server 7.0, the FK constraints are upped to 63. (Other changes are the increase in the number of parameters per stored procedure to 1024 and the maximum number of nested subqueries to 64.) Getting around the FK limitation in SQL Server 6.5 or earlier requires creativity and/or compromise. You can eliminate some of the foreign keys in less volatile "historical" tables. You can redesign your database. You can write code to mimic the referential integrity (RI) that FKs provide.

SQL Server Magazine


The "MAX ASYNCH I/O" is even better in SQL Server 7

Experienced SQL Server 6.5 users know that "max asynch I/O" is a powerful configuration option for controlling the I/O throughput capabilities of SQL Server. In SQL Server 6.5 this option sets the limit on how many asynchronous I/O operations can be issued at one time by the server. In SQL Server 7.0, however, this option controls how many asynchronous operations can be issued against a file. That's because SQL Server 7.0 has replaced device-based storage with file-based storage. Keep this change in mind as you tune this important option on your systems.

SQL Server Magazine


Using Transactions to Bullet Proof Your Database Interactions

As web pages begin to rely more and more on databases, it is important you write code that is aware of the successes or failure of SQL statements. By grouping connected database interactions into Transactions, you can ensure that if any statement fails, none of the statements in the Transaction will be processed. This is useful in situations where you are updating two tables, and don't want one to fail and the other to succeed.

The following code shows how to group two SQL statements into a transaction: 

<%
Set MyConn=Server.CreateObject("ADODB.Connection")
MyConn.Open "FILEDSN=C:\Program Files\Common Files\ODBC\Data
Sources\JupiterData.dsn"

MyConn.BeginTrans
MyConn.Execute("UPDATE YourAccount SET Balance=Balance+100 WHERE Account='1'")
MyConn.Execute("UPDATE MyAccount SET Balance=Balance-100 WHERE Account='1'")
MyConn.CommitTrans
MyConn.Close
%>

Active Server Developer's Journal


Where's the Full Text Indexing?

SQL Server 7.0 ships with a full text indexing engine, but it isn't installed by default in Beta 3. To install it, you have to re-run SQL Server 7.0 setup and opt for the Custom Installation, Server Components. Deselect all the options you already installed and click on full text indexing. (And move over, Oracle ConText and IBM DB2 Data Mining for Text).

SQL Server Magazine