Tips

Tips

• Using Surface Area Configuration to lock down SQL Server

  The SQL Server 2005 Surface Area Configuration tool allows you to manually enable only the services you absolutely need, minimizing the attack surface.  Continue Reading

• Stored procedure: Determine last database backup

  You have dozens or hundreds of SQL Servers to administer and you just want to know when the last backup was taken for each database on each instance. This stored procedure will help.  Continue Reading

• Undocumented DBCC: Command to track memory usage

  This DBCC gives you the top 20 cached items in SQL Server at any given time to help you analyze which objects are spending the most time in or out of the cache.  Continue Reading

• SQL Server cursors pros and cons

  SQL Server cursors can be useful despite their reputation as poor performers, resource hogs and an overused tool by junior developers. This tip explains how cursors work and their pros and cons.  Continue Reading

• Discover and lock down vulnerable SQL Server services

  Be sure to protect those "naked" SQL Servers -- databases unnecessarily exposed to internal and external hackers. Contributor Kevin Beaver explains how to test for vulnerable SQL Server services.  Continue Reading

• View SQL Server transaction logs using DBCC

  View SQL Server transaction logs using this undocumented DBCC command.  Continue Reading

• Restore basics: How to restore using T-SQL commands

  SQL Server Enterprise Manager may be a quick and easy way to run backups and restores, but T-SQL commands offer much greater flexibility. Find out how to script your SQL Server backups or restores.  Continue Reading

• Setting up and maintaining standby servers

  Standby servers may be used for continuous log shipping in the event of a database failure or to verify SQL Server backups. Here you'll find tips for setting up and maintaining a standby server.  Continue Reading

• T-SQL performance problems and solutions

  Don't assume all SQL Server performance problems are database-related. Poorly written T-SQL code could be to blame. Learn about common problems and workarounds in this clinic.  Continue Reading

• SQL Server Integration Services to simplify database maintenance

  SQL Server Integration Services (SSIS) eases your database maintenance workload in SQL Server 2005. Edgewood Solutions' Jeremy Kadlec reviews SSIS benefits and commonly asked questions.  Continue Reading

• How to reuse datasets with Reporting Services

  Rather than continually create, destroy and recreate the same datasets to run a report repeatedly, get a trick to create the dataset once and retain it until no longer needed.  Continue Reading

• Tricks for using the SQL Server Index Tuning Wizard

  Learn how to improve overall SQL Server 2000 performance with these Indexing Tuning Wizard tricks from Edgewood Solutions' Jeremy Kadlec.  Continue Reading

• Checklist: Maximize SQL Server backup performance

  Improve the I/O throughput of your SQL Server backups with the steps in this checklist by Edgewood Solutions' Greg Robidoux. It includes optimal backup configuration and scheduling options.  Continue Reading

• Watermarking reports in Reporting Services

  Reporting Services will not watermark reports to designate a document's status. Get other options for watermarking your reports in this tip.  Continue Reading

• SQL Server Clinic: Stop locking and blocking

  Locking ensures SQL Server properly processes transactions, but too much locking can bring transactions to a grinding halt. This SQL Server Clinic walks you through a typical locking scenario and the steps needed to address locking and blocking ...  Continue Reading

• Restore basics: How to restore SQL Server using Enterprise Manager

  To restore a SQL Server database you have two basic options. Edgewood Solutions' Greg Robidoux explores the first of two in this SQL Server restore series.  Continue Reading

• SQL Server batch rules

  SQL Server batch rules are explained in this tip by contributor Baya Pavliashvili who warns of potential failures if certain guidelines aren't followed when grouping T-SQL commands into batches.  Continue Reading

• Stored procedure: Easily filter for SQL Server connections

  In monitoring usage patterns on SQL Server connections, you need a tool to filter for connections of interest. Enterprise Manager has a basic tool to list connections, but it's not particularly flexible or convenient. The sp_ListConnections routine ...  Continue Reading

• SQL Profiler: Features, functions and setup in SQL Server 2005

  SQL Profiler is often considered the best native SQL Server resource because, for one thing, it understands micro-level processing on any SQL Server. Edgewood Solutions' Jeremy Kadlec introduces you to SQL Server 2005 Profiler and explains how to ...  Continue Reading

• 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 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

• 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

• 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

• 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

• 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 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

• 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

• 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

• 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

• 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

• 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

• 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

• 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

• Guidelines for creating indexes

  Some guidelines for index usage to maximize database performance.  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

• Stored procedures vs. functions

  Differences and when to use which.  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

• 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

• 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

• Have you been ODBCPing with SQL Server?

  Learn the syntax and what the command does in Microsoft SQL Server.  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

• Sorting output with CASE expressions for custom sort orders

  Quick tip on using CASE statements with 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

• Performing incremental inserts/updates in SQL Server

  This article discusses performing incremental inserts/updates to reduce lock conditions in Microsoft SQL Server.  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

• Horizontal partitioning of large databases

  Learn about the details and considerations of performing horizontal partitioning of large databases.  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

• 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

• 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 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

• 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

• 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

• An introduction to coding SQL-DMO

  SQL-DMO basics help DBAs program administrative tasks for SQL Server.  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

• 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

• 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

• 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

• 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

• 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

• 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

• 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