MSSQLTips Author Profile


Today's Site Sponsor

	Cure SQL Server performance headaches with NEW SQL doctor. Try the BETA and enter to win an iPad!

Today's Tip Tips By Category Latest Tips Top 10 Tips Suggest a Tip Search By Tip ID

Tools Tutorials Webcasts Whitepapers Blogs Forums Forums - Active Forums - Unanswered RSS Feeds

Join MSSQLTips Authors Contribute Giveaways Feedback User Groups Follow us on Twitter Follow us on LinkedIn

About Us Advertise Copyright Disclaimer Feedback Privacy Statement

Search By Tip ID Search By Category

Author - Greg Robidoux
	Greg Robidoux is the President and founder of Edgewood Solutions, a technology services company delivering services and solutions for Microsoft SQL Server. He has been working with SQL Server for the past 11 years and has authored numerous articles and delivered several presentations online and at local and national SQL Server events.

Analysis Services

Getting Started with SQL Server Analysis Services (1/30/2007)

Auditing and Compliance

Auditing your SQL Server database and server permissions (9/27/2006)
Using passwords with SQL Server database backup files (11/8/2006)

Backup and Recovery

Auto generate SQL Server database restore scripts (5/14/2007)
Auto generate SQL Server restore script from backup files in a directory (9/16/2008)
Automating Transaction Log Backups for All SQL Server Databases (8/28/2007)
Backup to multiple files for faster and smaller SQL Server backup files (6/5/2006)
Changing the default SQL Server backup folder (9/15/2008)
COPY_ONLY Backups with SQL Server 2005 (8/31/2006)
Encrypt and safeguard your SQL Server database backups (5/22/2007)
Getting exclusive access to restore SQL Server databases (1/8/2008)
How to stop logging all successful backups in your SQL Server error logs (3/18/2008)
Issues with running backup log with no_log or truncate_only in SQL Server (3/27/2008)
Scheduling Backups for SQL Server 2005 Express (2/7/2007)
Selecting the SQL Server database recovery model to ensure proper backups (5/13/2008)
Simple script to backup all SQL Server databases (9/13/2006)
SQL Server 2000 Database Restores Mapping Users to Logins (9/15/2006)
SQL Server backup and restore to network drive (12/7/2006)
SQL Server Backup Plan (7/12/2007)
SQL Server point in time recovery (4/26/2007)
What is in your SQL Server backup files? (1/4/2007)

Common Language Runtime (CLR)

CLR function to delete older backup and log files in SQL Server (11/15/2007)
CLR String Sort Function in SQL Server 2005 (10/4/2007)
Debugging SQL Server CLR functions, triggers and stored procedures (11/11/2008)
Send Email from SQL Server Express Using a CLR Stored Procedure (7/16/2009)
SQL Server CLR and T-SQL functions to parse a delimited string (1/14/2009)

Data Management

Database Snapshots for Reporting, Auditing and Data Recovery in SQL Server 2005 (6/30/2006)
Minimally Logging Bulk Load Inserts into SQL Server (2/22/2007)
Simple way to find errors in SQL Server error log (8/13/2007)

Data Transformation Services

SQL Server DTS command line utility (6/26/2006)

Database Administration

Different ways to determine free space for SQL Server databases and database files (7/30/2009)
Simplified process to move all SQL Server system databases at one time (10/14/2008)

Database Configurations

How to determine SQL Server database transaction log usage (4/18/2007)
How to rename a SQL Server database (12/4/2006)
Retrieving SQL Server Database Properties with DATABASEPROPERTYEX (8/4/2006)

Database Consistency Checks (DBCCs)

DBCC SHOWCONTIG Blocking Issues in SQL Server (3/9/2007)
SQL Server 2005 DBCC CHECKDB with DATA_PURITY command (11/21/2006)
SQL Server Database Integrity Checks Checklist (7/27/2007)

Database Design

Modifying SQL Server database tables and keeping like columns together (7/13/2007)
Retrieving SQL Server Column Properties with COLUMNPROPERTY (6/28/2007)
Simple way to create table structures in SQL Server using an Excel spreadsheet (8/29/2006)
SQL Server 2005 tablediff command line utility (9/25/2006)

Database Mail

SQL Server 2005 Database Mail Install Hangs (6/8/2007)

Database Mirroring

Adjusting the automatic failover time for SQL Server Database Mirroring (10/13/2008)
Database Mirroring Automating Failover for Multiple SQL Server Databases (8/18/2008)

DBA Best Practices

SQL Server Comparison Tools (9/11/2006)
SQL Server DBA database management checklist (7/3/2007)

DDL Trigger

Auditing DDL (Create, Alter, Drop) Commands in SQL Server 2005 (6/22/2006)

Disaster Recovery

SQL Server Log Shipping (1/17/2007)

Dynamic Management Views\Functions

Finding a SQL Server process percentage complete with dynamic management views (9/26/2007)
Using SQL Server DMVs to Identify Missing Indexes (11/25/2008)

Dynamic SQL

Execute Dynamic SQL commands in SQL Server (1/19/2007)

Encryption

Decrypting SQL Server database objects (8/23/2006)

Error Handling

SQL Server 2005 Try and Catch Exception Handling (7/26/2006)

Fragmentation and Index Maintenance

Managing SQL Server Database Fragmentation (1/25/2007)
Selectively rebuilding indexes with SQL Server 2005 maintenance plans (12/8/2006)
SQL Server Index Maintenance Checklist (9/27/2007)
SQL Server script to rebuild all indexes for all tables and all databases (11/6/2007)

Functions

Dynamically controlling the number of rows affected by a SQL Server query (7/8/2008)
Retrieving random data from SQL Server with TABLESAMPLE (8/14/2007)
SQL Server 2000 Text Data Manipulation (7/25/2006)
Standardize Text with Multi Search and Replace Function in SQL Server (8/25/2010)

Identities

Adding or dropping identity property for an existing column in SQL Server (1/4/2008)
Auto create identity insert SQL Server command to sync tables (6/10/2010)
Managing and maintaining SQL Server identity values (12/5/2006)
Retrieving identity column properties for all tables in a SQL Server database (6/29/2007)
Simple approach to populate a SQL Server column with a sequential number not using an identity column (4/1/2008)
Using Identity Insert to keep SQL Server database table keys in sync (9/26/2006)

Indexing

Building SQL Server Indexes in Ascending vs Descending Order (9/25/2007)
Clustered Tables vs Heap Tables (5/30/2007)
Deeper insight into unused indexes for SQL Server (7/22/2008)
Finding a better candidate for your SQL Server clustered indexes (12/9/2008)
How to get index usage information in SQL Server (5/8/2007)
Retrieving SQL Server Index Properties with INDEXPROPERTY (8/11/2006)
SQL Server Index Analysis Script for All Indexes on All Tables (6/19/2006)
Understanding SQL Server Indexing (3/22/2007)

JOINing Tables

Comparing Multiple SQL Server Datasets with the INTERSECT and EXCEPT operators (9/11/2007)
How to join SQL Server tables where columns include NULL values (3/4/2008)
Joining data and differences of using UNION and UNION ALL in SQL Server (12/5/2007)

Locking / Blocking

Finding and troubleshooting SQL Server deadlocks (8/9/2006)

Maintenance

Finding and fixing SQL Server database constraint issues (5/11/2007)
Maintenance task to delete old SQL Server backup files (9/6/2007)
Performing maintenance tasks in SQL Server (7/7/2006)
Supporting tables for SQL Server 2005 style maintenance plans (12/18/2006)
Why do my SQL Server database maintenance plan transaction log backups always fail (4/10/2007)

Monitoring

Determining space used for each table in a SQL Server database (2/12/2007)
Getting operating system content into SQL Server (2/6/2007)
Listing SQL Server Object Dependencies (7/26/2007)
Methods to determine the status of a SQL Server database (4/15/2008)
Monitoring SQL Server database transaction log space (2/13/2007)
Problems running sp_helpdb in SQL Server (4/6/2007)
Reading the SQL Server log files using T-SQL (4/14/2008)
SQL Server Command Line Tools To Manage Your Server (7/28/2006)

Partitioning

Handling Large SQL Server Tables with Data Partitioning (3/15/2007)
Switching data in and out of a SQL Server 2005 data partition (1/7/2008)

Performance Tuning

Avoid functions in the SQL Server WHERE clause for Performance Improvements (5/3/2007)
Correlating Performance Monitor and SQL Server Profiler Data (3/30/2007)
Gathering I/O statistics down to the SQL Server database file level (1/21/2008)
Optimize Parameter Driven Queries with the OPTIMIZE FOR Hint in SQL Server (10/18/2007)
Remove some SQL Server blocking issues with the NOLOCK hint (4/27/2007)
SQL Server Database Specific Performance Counters (6/14/2007)
SQL Server statements currently running with fn_get_sql (6/27/2006)
Troubleshooting Performance Problems in SQL Server 2005 (1/31/2007)

Professional Development

Defining and Establishing SQL Server Policies and Procedures (8/9/2007)

Profiler / Trace

SQL Server Performance Statistics Using a Server Side Trace (8/8/2006)

Query Optimization

Crosstab queries using PIVOT in SQL Server 2005 (7/24/2006)
Randomly Retrieve SQL Server Records (6/29/2006)
Using Derived Tables to Simplify the SQL Server Query Process (8/16/2006)

Query Plans

Capturing Graphical Query Plans with SQL Server Profiler (6/13/2007)
Maximizing your view into SQL Query Plans (11/5/2007)

Referential Integrity

Finding primary keys and missing primary keys in SQL Server (5/4/2007)
Identify all of your foreign keys in a SQL Server database (1/5/2007)
Script to create commands to disable, enable, drop and recreate Foreign Key constraints in SQL Server (11/19/2007)

Replication

Changing Not For Replication Value for Identity Columns in SQL Server (6/27/2007)
Replication support only option with SQL Server 2005 replication (11/20/2006)
SQL Server 2005 Peer to Peer Replication (8/7/2006)
Transactional Replication Snapshot Issues in SQL Server (6/21/2007)

Reporting Services

Getting Started with Reporting Services (10/9/2007)

Scripts

Capture all statements for a SQL Server session (3/18/2009)
Cross tab queries with SQL Server 2000 (6/15/2006)
Executing a T-SQL batch multiple times using GO (4/5/2007)
Pros and Cons of Finding Text within the Text data type with the LIKE Operator in SQL Server (10/8/2007)
Recursive queries with SQL Server 2000 (8/30/2006)
Scripting SQL Server Database Objects Using DMO (Distributed Management Objects) (8/22/2006)
Searching and finding a string value in all columns in a SQL Server table (6/18/2008)
SQL Server Find and Replace Values in All Tables and All Text Columns (8/5/2008)
Standardize your SQL Server data with this text lookup and replace function (9/12/2006)

Security

Enabling xp_cmdshell in SQL Server 2005 (7/13/2006)
Giving and removing permissions in SQL Server (12/19/2006)
Granting permission with the EXECUTE AS command in SQL Server 2005 (4/20/2007)
Hiding instances of SQL Server 2005 (3/7/2007)

Service Packs / Patches

Applying SQL Server Service Packs (9/10/2007)
Issue with SQL Server 2005 SP2 (3/8/2007)
SQL Server 2005 - Service Pack 2 (1/22/2007)

SQL Server 2008

Access to SQL Server 2008 Virtual Environment (1/16/2008)
SQL Server 2008 has arrived (6/6/2007)

SQL Server Agent

Accessing SQL Server Agent Data (5/15/2007)
Automatic cleanup of SQL Server Agent scheduled jobs (12/20/2006)
Collecting Performance Monitor Statistics for SQL Server Agent Scheduled Jobs (3/28/2007)
Free Job Scheduling Tool for SQL Server Express and MSDE (10/10/2006)
How to store longer SQL Agent Job Step output messages (12/14/2007)
Missing SQL Server Agent History (6/20/2007)
Setting Up Alerts for All SQL Server Agent Jobs (10/26/2006)

SQL Server Configurations

How to tell what SQL Server version you are running (12/21/2006)
Setting up Database Mail for SQL 2005 (11/9/2006)
SQL Server 2000 vs SQL Server 2005 Startup Parameters (6/28/2006)

SQL Server Integration Services

Different Options for Importing Data into SQL Server (3/23/2007)
Dynamic SQL Server Integration Services SSIS workflow based on variables (4/29/2008)
How to strip out double quotes from an import file in SQL Server Integration Services SSIS (8/24/2007)
Importing Excel data with SQL Server Integration Services SSIS with unicode and non-unicode data issues (12/13/2007)
Making data imports into SQL Server as fast as possible (11/20/2007)
Merging multiple data sources with the MERGE JOIN task in SQL Server Integration Services SSIS (9/5/2007)
Passing dynamic parameter values to SQL Server Integration Services SSIS (12/17/2007)

SQL Server Management Studio

Assigning shortcuts to commonly run tasks in SQL Server 2005 Management Studio (7/17/2007)
Build code using the SQL Server object browser's drag and drop capabilities (5/16/2007)
Copying column headers with grid query results in SQL Server 2005 Management Studio (11/7/2006)
Drag and drop query result columns in SQL Server 2005 (1/18/2007)
Dynamic stored procedure execution form in SQL Server 2005 Management Studio (11/16/2007)
Scripting out T-SQL commands generated by SQL Server Management Studio (5/23/2008)
SQL Query Analyzer Shortcuts (7/5/2006)
Using Solutions and Projects to manage your SQL Server code (11/1/2007)

Stored Procedures

Getting started with SQL Server stored procedures (5/9/2008)
SET NOCOUNT ON Improves Stored Procedure Performance in SQL Server (4/19/2007)

T-SQL

Deleting duplicate rows when there is no primary key on a SQL Server database table (11/10/2006)
Determining SET Options for a Current Session in SQL Server (1/18/2008)
How to get length of data in Text, NText and Image columns in SQL Server (2/27/2007)
Limiting amount of data returned with the TEXTSIZE command in SQL Server (12/19/2007)
Problem using DISTINCT in case insensitive SQL Server databases (5/27/2008)
Renaming SQL Server database objects and changing object owners (12/21/2007)
SQL Server Four-part naming (11/6/2006)
Using SQL Server Bitwise operators to store multiple values in one column (4/9/2007)

Testing

Creating delays in SQL Server processes to mimic user input or delay process steps (1/30/2008)

Tools

Finding SQL Server Code Errors using Query Analyzer and a Double Click (10/27/2006)
Free Performance Profiler Tool for SQL Server 2005 Express (3/20/2008)
Free SQL Server tools from Microsoft (7/6/2006)
How does Enterprise Manager get its data (6/20/2006)
SQL Formatter Tool (6/7/2006)
SQL Server Documentation Tools (5/24/2007)

Upgrades

Upgrading SQL Server databases and changing database compatibility levels (2/18/2008)

Views

Using Views to Simplify Data Access in SQL Server (10/5/2007)

XML

BCP XML Format Files with SQL Server 2005 (9/28/2006)

Sponsor Information

Free SQL Server performance monitoring dashboard – Idera SQL check

Quickly and accurately deploy database changes with Red Gate's SQL Compare – the industry standard comparison and deployment tool.

You don't know, what you don't know about SQL Server... Customized Consulting and Training

Interested in SharePoint? Love the tips? Check this out...

Free Whitepaper - The Seven Steps to Successful SQL Server Auditing

Red Gate Software - SQL Backup

Need to create smaller, more reliable backups? Ensure your backups are optimized for robustness and speed with Red Gate SQL Backup Pro. Compress your backups by up to 95% and minimize disruptions to your backups caused by flaky networks with new network resilience. 'Network resilience puts SQL Backup Pro 6 at the top of the list of backup tools. It’s the cherry on top, and I definitely recommend using SQL Backup over SQL Server 2008 native backups.' William Durkin, Development DBA. Download now.

Download now!

More SQL Server Tools

SQL comparison toolset

SQL safe backup

SQL compliance manager

SQL Compare

SQL Data Generator