Tips

Tips

• How to restore from a transaction log in SQL Server

  A successful SQL Server restore often involves retrieving data from transaction log backups, which means restoring multiple files rather than one large full backup. Edgewood Solutions' Greg Robidoux walks you through the process of backing up and ...  Continue Reading

• SQL Server consolidation pros and cons

  You may want to consolidate SQL Servers to reap the reduced licensing, hardware and administrative costs, but there are various "gotchas" to consider before making such a move. Contributor Hilary Cotter outlines the pros and cons.  Continue Reading

• Stored procedure: Keep track of SQL Server Agent jobs

  Enterprise Manager's standard tools aren't much help in examining SQL Agent jobs. To get details about jobs currently running, try this sp_ListJobInformation routine instead.  Continue Reading

• How to store database backup media

  As a DBA, you may not personally be responsible for long-term storage of backup files, but you must always be ready to replace data quickly in the event of a SQL Server failure. Edgewood Solutions' Greg Robidoux offers some important best practices ...  Continue Reading

• How to back up and restore Analysis Services databases

  Backing up a Microsoft Analysis Services (MSAS) database looks easier than it is. Contributor Baya Pavliashvili explains how to back up and restore MSAS 2000 databases and identifies enhancements in MSAS 2005.  Continue Reading

• Stored procedure: Simplify comma-separated value (CSV) exports

  Comma-separated value (CSV) exports are often used to provide SQL Server data to Excel -- but there is no simple way to export SQL Server data within a stored procedure to a file. The sp_SaveDelimitedColumns routine makes CSV exports a breeze by ...  Continue Reading

• Maintaining a healthy SQL Server database

  Just like following a checklist, you must regularly address, strengthen and tune several SQL Server maintenance processes to increase database availability and lower total cost of ownership.  Continue Reading

• Stored procedure: Create a fixed-width file to simplify data exports

  Exporting SQL Server data to a file from within a stored procedure is a common task, but there are different ways to do this --none of which are very easy.  Continue Reading

• Hiding SQL Server

  If you're running SQL Server in an environment where you don't want computers to access it, you must hide the instance from network discovery. Contributor Serdar Yegulalp outlines two ways to hide your SQL Servers.  Continue Reading

• Top 10 new features in SQL Server 2005

  From programming to administrative capabilities, SQL Server 2005 enhances many existing SQL Server 2000 features and goes well beyond. Edgewood Solutions' Jeremy Kadlec shares his SQL Server 2005 top 10 list.  Continue Reading

• Debugging SQL Server DTS packages

  Debug SQL Server DTS packages in production environments or risk poor performance and lost data integrity. You should use Dynamic Properties to debug in a QA or test environment first and then promote to production. Contributor Hilary Cotter offers ...  Continue Reading

• Stored procedure: Execute T-SQL code from a file

  Need to execute T-SQL code in a file from within other T-SQL code? The sp_ExecuteSQLFromFile stored procedure addresses the shortcomings and adds new functionality.  Continue Reading

• Stored procedure: A simple way to perform crosstab operations

  A crosstab operation usually involves complicated T-SQL coding or pulling data into an application, but sp_Crosstab makes it simple.  Continue Reading

• SQL Server 2000 Query Analyzer: 10 tricks for simple querying

  Many improvements can be made to the raw performance of your SQL Server statements, as well as to your job performance as a DBA or developer, by simply using SQL Server 2000's Query Analyzer -- a tool that Microsoft is excluding from SQL Server 2005...  Continue Reading

• Database partitioning methods in SQL Server

  Partitioning allows you to improve SQL Server read/write performance by distributing a table over multiple databases or servers. Contributor Hilary Cotter describes when to use each of the two partitioning methods -- vertical and horizontal -- and ...  Continue Reading

• Automate SQL Server restores to refresh test environments

  Creating an automated task to restore your database as needed enables you to easily refresh a development or test environment. Edgewood Solutions' Greg Robidoux offers a list of steps and a sample script to help you automate restores in this tip.  Continue Reading

• SQL Server merge replication performance fired up

  If your Subscribers are frequently offline, it may not be possible to run merge agents regularly to keep replication running smooth. To help you solve performance dilemmas, contributor Hilary Cotter offers his tuning tips in the final part of this ...  Continue Reading

• Ten merge replication tips and tricks

  From schema and parameter changes to wrapping and limiting merge agents, SQL Server MVP Hilary Cotter offers various tricks to help you get the best performance from your merge replication efforts.  Continue Reading

• SQL Server normalization rules you must follow

  What does normalization have to do with SQL Server? Edgewood Solutions' Greg Robidoux says nothing; your RDBMS doesn’t care what normal forms you use for your data model. However, using normalization to establish a clean data model has its benefits.  Continue Reading

• How merge replication works in SQL Server

  When you meet up with an old friend, you immediately try to recall when you last met so you know how much you have to catch up on. Merge replication works in a similar way. Get up to speed on merge replication in this tip, the first in a three-part ...  Continue Reading

• Solving SQL Server 2000 index fragmentation

  Indexing saves you from having to scan an entire table for data search and retrieval, but indexes can easily become fragmented over time. Edgewood Solutions' Jeremy Kadlec explains how they become fragmented and how to maintain beneficial indexes to...  Continue Reading

• Log shipping a replicated database

  Log shipping allows you to automatically copy and restore transaction logs to another database on a standby server. Find out how and when to use it for a replicated database.  Continue Reading

• Maintenance checks for SQL Server

  SQL Server's maintenance plan wizard can automate all maintenance tasks for you -- but beware what's really going on behind the scenes.  Continue Reading

• Top 10 Performance Monitor counters

  Performance Monitor can help you determine how well SQL Server is performing relative to the performance baseline. In the second part of this three-part series, contributor Jeremy Kadlec offers his 10 recommended Performance Monitor counters to ...  Continue Reading

• 15 SQL Server replication tips in 15 minutes

  This list of tips and tricks will help you enhance your replication techniques. It is broken up into three groups: performance, monitoring and miscellaneous.  Continue Reading

• Stored procedures: Find columns and tables

  Continuing our stored procedure series, this simple routine is handy for finding columns by name and listing the table(s) in which they exist. The listing includes table name, column name, ordinal position, data type, data size, width/precision and ...  Continue Reading

• Faster SQL Server backups in 10 steps

  Faster SQL Server backups are just 10 steps away with this expert advice. These days, a 100 GB database is considered the norm, and many databases are beginning to surpass the terabyte range. Contributor Greg Robidoux looks at 10 ways to achieve ...  Continue Reading

• Navigate SQL Server hierarchies using recursive common table expressions

  Modeling a hierarchical chart for your company, such as an employee table, may seem simple -- but the simplest data structures are typically the most difficult to query. Learn how to use common table expressions to get around this problem.  Continue Reading

• How to set up an event forwarding server

  In the second installment of this series, contributor Kevin Kline explains how to set up a central server for event forwarding so you can catch database problems before users do.  Continue Reading

• Optimize SQL Server hardware performance

  You must set up SQL Server hardware properly and keep optimization best practices at hand throughout the application's lifecycle to achieve high performance. Find out how here.  Continue Reading

• Understanding replication methods: Snapshot, transactional and merge

  In this book excerpt, you will learn the process, the advantages and disadvantages, and tips on when to use three different replication methods: snapshot, transactional and merge.  Continue Reading

• Restoring a database from another SQL Server

  Restoring a database from another SQL Server is simple -- matching up the logins and users again is not. Get the steps you need to restore one database from another in this tip.  Continue Reading

• Stored procedure: Retrieve filtered file lists from a specified directory

  Two code listings create a system stored procedure named sp_ListFiles. The first routine returns a filtered list of files found in a specified directory. The second, a README file.  Continue Reading

• Using MAX data types in SQL Server

  Using MAX data types in SQL Server 2005 adds flexibility when adding large amounts of data.  Continue Reading

• Use DBCC CLEANTABLE to reclaim unused space in SQL Server tables

  This tip shows you how to use DBCC CLEANTABLE to reclaim unused space in your SQL Server tables.  Continue Reading

• SQL Server version control

  Developers and DBAs often talk about version control, but most don't do anything about it. In this article, Hilary Cotter discusses how to establish version control.  Continue Reading

• How to send e-mail notifications when new records are added to a table

  Serdar Yegulalp discusses how administrators can send e-mail notifications when there are updates for a particular table.  Continue Reading

• Stored procedure: List SQL Server database objects by selected type(s)

  Brian Walker presents a simple stored procedure to return a list of all the objects of selected types in the current database.  Continue Reading

• SQL Server OLTP vs. data warehouse performance tuning

  This article discusses two broad categories of SQL Server processing -- online transaction processing (OLTP) and data warehouses -- and outlines performance-tuning tips for each.  Continue Reading

• SQL Server 2000 Service Pack 4: An overview

  Serdar Yegulalp discusses the most important new features in SQL Server 2000 Service Pack 4.  Continue Reading

• User-defined functions to generate and apply a table of sequence numbers

  This tip contains user-defined functions that generate a table of sequence numbers and examples of how to apply such a table.  Continue Reading

• Terminate SQL Server connections before you perform maintenance

  If there are active connections open on your database, some types of maintenance may not run. Learn how to ensure that connections are closed before maintenance starts.  Continue Reading

• A workaround to SQL Server's 8,060-byte row length limit

  There are several possible workarounds to the 8,060-byte row length limit, and most of them involve a re-thinking of the table design. Serdar Yegulalp explains.  Continue Reading

• Sorting output with CASE expressions for custom sort orders

  Quick tip on using CASE statements with Microsoft SQL Server.  Continue Reading

• Have you been ODBCPing with SQL Server?

  Learn the syntax and what the command does in Microsoft SQL Server.  Continue Reading

• Performing incremental inserts/updates in SQL Server

  This article discusses performing incremental inserts/updates to reduce lock conditions in Microsoft SQL Server.  Continue Reading

• Understanding NULL values in SQL Server

  Learn what NULL values are good for in Microsoft SQL Server and how to use them.  Continue Reading

• Key differences between SQL CAST vs. CONVERT functions

  Learn how the MSSQL CAST vs. CONVERT functions operate -- and the best time to use each in Microsoft SQL Server.  Continue Reading

• Using the WITH RECOMPILE option in SQL Server

  What it the WITH RECOMPILE option is, when you should use it and how it relates to SQL Server stored procedures.  Continue Reading

• Horizontal partitioning of large databases

  Learn about the details and considerations of performing horizontal partitioning of large databases.  Continue Reading

• The ACID test for T-SQL batch statements

  What is the ACID test? It's a test to determine whether a T-SQL statement is valid in Microsoft SQL Server.  Continue Reading

• When to use data replication

  Data replication can be tricky, but there are some situations when using it can be beneficial. Get several reasons to use SQL Server replication.  Continue Reading

• Stored procedures vs. functions

  Differences and when to use which.  Continue Reading

• Using SQL Server application roles

  Learn how to use application roles in SQL Server and some things to consider ahead of time.  Continue Reading

• Vertical partitioning of large SQL Server databases

  Learn the pros and cons of vertical partitioning to improve performance of large SQL Server databases.  Continue Reading

• Creating faster cursors in SQL Server

  Using SQL Server cursors can be slow. However there is a way to speed them up.  Continue Reading

• Guidelines for creating indexes

  Some guidelines for index usage to maximize database performance.  Continue Reading

• Nested queries vs. temporary tables in SQL Server

  A look at when to use nested queries or temporary tables when performing large operations in Microsoft SQL Server.  Continue Reading

• Dynamically manipulating files using xp_cmdshell

  Dynamically build and execute OS commands within an SQL script.  Continue Reading

• Determine the number of rows in a table without a table scan

  SQL Server keeps the row count in sysindexes and it can be retrieved there. Here's how.  Continue Reading

• Display detailed index statistics

  A stored procedure that allows you to obtain detailed DBCC SHOW_STATISTICS information on all indexes for a given table.  Continue Reading

• Get status information for trace flag(s) currently turned on

  The command DBCC TRACESTATUS can be used to get the status information for a particular trace flag currently turned on.  Continue Reading

• How to resolve "user or role already exists" errors

  How to resolve this SQL Server error that results from restoring a database from another server without a master DB.  Continue Reading

• Get SQL Server table structure in XML format

  Here is a quick stored procedure in SQL Server 2000 that returns the table structure in XML format.  Continue Reading

• Mapping user and login security

  Having problems with database users that aren't mapped to logins? You aren't alone. Here is an overview of the problem and a quick solution.  Continue Reading

• Creating a SQL Server database

  A step-by-step tutorial that describes several methods for creating a SQL Server database.  Continue Reading

• Copying a table across linked servers

  This procedure will copy a table across linked servers, overwriting the destination table.  Continue Reading

• Connect an SQL Server from the iSeries

  Take the frustration out of connecting a SQL Server database from AS400/iSeries.  Continue Reading

• Comparing timestamps

  test  Continue Reading

• Changing the date and time in SQL Server using T-SQL

  A quick commmand that changes the date and time in SQL Server, complete with warnings!  Continue Reading

• Check the isolation level of any SPID

  Check the isolation level of any SPID on your Microsoft SQL Server 2000.  Continue Reading

• Avoid cursors by using TABLE variables

  You will find many ways to improve the performance of your T-SQL code if you make TABLE variables your friend.  Continue Reading

• Avoid blocking in Microsoft SQL Server administration

  Learn why you should avoid blocking in SQL Server, a classic database problem in which a transaction locks a first record and then a second record.  Continue Reading

• Automatically formatting your T-SQL code

  An easy method to properly format your code with capitalization, indents, spacing, etc. using T-SQL coding in Microsoft SQL Server.  Continue Reading

• Adding an ORDER BY clause to a view in SQL Server

  Microsoft says that you cannot put an ORDER BY clause in the view definition with SQL Server. True...unless you do the following trick!  Continue Reading

• Convert a string to the proper case using SQL language

  This user-defined function takes an input string and converts it to upper and lower case (or proper case) format.  Continue Reading

• Enabling and disabling all constraints on a table in SQL Server

  This SQL Server stored procedure will disable/enable all constraints for a given parametric table.  Continue Reading

• An introduction to coding SQL-DMO

  SQL-DMO basics help DBAs program administrative tasks for SQL Server.  Continue Reading

• Compare two SQL Server tables with stored procedures

  This SQL Server stored procedure compares the data in two tables and reports the differences.  Continue Reading

• Find when a stored procedure or function was last altered

  Find when a SQL Server stored procedure or function was last altered with this script.  Continue Reading

• Passing parameters using simple T-SQL XML parsing

  Describes an alternative way of passing a varying number of input parameters from one T-SQL stored procedure to another by using the built-in OPENXML T-SQL row set statement.  Continue Reading

• Write query results to Excel using a linked server and T-SQL

  A SQL Server procedure that can be used as a general tool for exporting data to an Excel spreadsheet since the bcp utility can only export to a text file.  Continue Reading

• Using AWE to increase performance

  Using "Address Windowing Extensions" to increase memory usage will increase the performance of SQL Server.  Continue Reading

• SQL Server injection: When firewalls offer no protection

  This chapter will detail the basics of SQL injection and best practices for defending SQL Server against such attacks.  Continue Reading

• Using split delimiter-separated lists in SQL Server

  This SQL Server function is useful to split a string of values delimited by a special character.  Continue Reading

• Reseeding identities in SQL Server

  Here are some tips to use and pitfalls to avoid when using IDENTITY columns as primary keys in SQL Server tables.  Continue Reading

• Split delimiter-separated lists with a T-SQL UDF

  Creating and parsing lists of items in a string is a frequent request. Here's a SQL Server UDF that does it.  Continue Reading

• SQL Server script grants permissions to users

  Here's a simple SQL Server script that can be customized to quickly grant permissions to various users on all tables or stored procedures.  Continue Reading

• ZIP files with a stored procedure

  A SQL Server stored procedure that zips files from a source and destination directory that you pass when calling the procedure.  Continue Reading

• Upgrading SQL Server, part I: Overview and project planning

  First of a series detailing the SQL Server upgrade process from the technical, logistical and business perspective.  Continue Reading

• SQL Server administration dos and don'ts

  Ignore these SQL Server admin tips at your peril!  Continue Reading

• SQL Server string parser stored procedure

  This stored procedure takes a delimited string, parses it and puts it into a table.  Continue Reading

• Decrypt encrypted stored procedures

  Here is a stored procedure used to decrypt compiled T-SQL stored procedures.  Continue Reading

• Stored procedure to export/import images to SQL Server

  This procedure serves as an interface to the TextCopy utility in order to export/import images or docs to SQL Server.  Continue Reading

• Replacing non-alphanumeric characters in strings using T-SQL

  Learn two important methods for replacing non-alphanumeric characters in strings using T-SQL with Microsoft SQL Server.  Continue Reading

• Quick way to find space used by each table in a database

  Use this stored procedure to find the disk space used by each table in a SQL Server database.  Continue Reading

• ODBC IP connection troubleshooting

  A tip on how to resolve MDAC and connection issues in SQL Server 7 & 2000.  Continue Reading

• A stored procedure to export/import data using BCP

  This is a SP that can be run from a master database to export/import a table to/from a given path+name from/to a given DB.  Continue Reading

• Troubleshooting locking conflicts in SQL Server

  Overview of potential locking problems, including what causes them, how DBAs can detect and resolve them.  Continue Reading

• Procedure to drop all user-created indexes of given table

  A simple stored procedure that allows you to drop all user created index of a given table.  Continue Reading