SQL Server Tips

From the Database Community Forum

June 2004

Ensure that SQL Server returns error information to ADO

What if you know an error is occurring in a stored procedure, but the Errors collections doesn't seem to be getting populated with the errors at all? This is a known issue (a behavior Microsoft describes as "by design") and it usually occurs because the stored procedure you're calling contains multiple SELECT, INSERT, UPDATE, or DELETE operations without a SET NOCOUNT ON statement. When NOCOUNT is off (the default setting), the Errors collection doesn't maintain errors across multiple database operations because of the informational messages SQL Server returns (e.g., "0 record(s) affected"). So, to ensure that the proper error information is maintained and passed to your calling program through ADO, always use SET NOCOUNT ON in your stored procedures and place it before any other SQL operations. This has an added performance benefit as well, since SQL Server no longer has to pass back these informational messages (which your program doesn't need anyway).

Identify non-unique column values within a table in SQL Server

Suppose you want to determine which values occur more than once within a table--for instance, imaging a table like the Customers table in the Northwind sample database. In that table, the CustomerID is a string of text, indented to be unique; however, anytime you design a Primary Key in this way, you run the risk of accidentally using an identifier over again. Of course, you could set a UNIQUE constraint on the column, but suppose you inherited an already populated table, one that may or may not contain duplicate identifiers. How do you find them? The key to solving this query problem is to group the records by this identify, then look for groupings with more than one member. In T-SQL code, here's how to do that:

SELECT CustomerID, COUNT(*) AS Occurences
FROM Customers
GROUP BY CustomerID
HAVING COUNT(*) > 1

Update multiple columns in one table with data from multiple columns in another table in T-SQL

Most of the time, when we execute UPDATE statements in T-SQL, we're isolating one or more records from a single table and changing the data in them to literal values, like this:

UPDATE Table1 SET Column1 = 'NewText1', Column2 = 'NewText2'

But there are occasions where you might want to take the contents of Column1 in Table2 and replace the contents of Column1 in Table1 with that data. The solution to this problem may not be readily apparent, but it's actually fairly straightforward. The answer is to use a JOIN operation between the tables. Here's an example which should set you on the right path:

-- Make the updates

UPDATE Table1 SET Table1.Column1 = Table2.Column1, Table1.Column2 = Table2.Column2 FROM Table2 WHERE Table1.ColumnPK = Table2.ColumnPK

-- Check out the results to make sure it worked!

SELECT * FROM Table1

Should I use TOP N or SET ROWCOUNT N to return the top N rows from a resultset?

For indexed data, both perform equally well, but for unindexed data, using TOP N should result in better performance. On the other hand, TOP N isn't compatible with ANSI SQL, so if you work in a multi-vendor database environment and use standardized SQL, you might still want to stick with SET ROWCOUNT N.

Back to SQL Server Tips | Back to ZDNet/Element K Tips

Alrak's Course Resources ©2002-2007 Karla Carter. All rights reserved. This material (including, but not limited to, Mini-lectures and Challenge Labs) may not be reproduced, displayed, modified or distributed without the express prior written permission of the copyright holder. For permission, contact Karla.