MS SQL Server 2000/2005/2008/2012 Tips & Tricks

Location: BlogsComments    
Posted by: Dale Thursday, January 10, 2008
A place to keep bits and pieces of useful information relating to MS SQL Server including SQL Country Table Script.

SET ANSI_NULLS OFF Performance Issues: We recently found that some stored procedures in an database that had been around for awhile had ANSI_NULLS set to OFF. Changing it to ON brought about an immediate, massive increase in performance. We guess this is because with ANSI_NULLS OFF the logic for matching null is far more complex than when ANSI_NULLS is ON.

Country Table: Every site I build I need a country table and every time I borrow a country list from a respectable site such as amazon. However here is a script for generating an ISO country table. The script is for MySQL but is easily modified for MS SQL Server.

Data Import: I just spent hours importing some data from an SQL Server 2000 database into an SQL Server 2005 Express database only to find that the identity fields are not preserved! There does not appear to be a standard solution at this point, but this tool may help: SQL Server Simple Bulk Copy Tool.

Determining Disk Space Used: Frequently I find a database has grown large for no obvious reason - well actually usually it is obvious - usually its a log table of some sort. Howeer its really nice to be able to find the offending table and the following SP does exactly that: exec sp_spaceused 'Tablename'.

Scripting Data: The SQL Data Exporter is a simple app that does the job.

Permalink |  Trackback

Your name:
Title:
Comment:
Add Comment   Cancel