Sponsored White Papers, Webcasts, and Downloads
TechRepublic Resources
- Audit data using SQL Server 2005's COLUMNS_UPDATED function
- Auditing data changes in your production environment is very important, especially if you are dealing with confidential information. Besides tracking the data that has been modified, it is also useful to track the individual field names that have been modified. This information is great for your auditing department, and it...
- Tags: Function, Audit, Microsoft SQL Server, Auditing, Trigger, Microsoft SQL Server 2005, COLUMNS_UPDATED, COLUMNS_UPDATED Function, System Function, Tim, Databases, Enterprise Software, Software, Data Management, Tim Chapman
- Blog posts 2008-05-13
- Convert numbers to fractions using SQL Server
- I recently worked on an interesting problem that involved converting a decimal number to a fraction value in SQL Server. At first glance, it seemed reasonably simple, but after digging into the actual code a bit, I realized it was rather challenging. Here's how you can write...
- Tags: Fraction, Microsoft SQL Server, Server, VARCHAR, Tim, Databases, Enterprise Software, Software, Data Management, Tim Chapman
- Blog posts 2008-04-30
- Generate synchronization scripts in SQL Server with TableDiff
- DBAs often have to find the differences between lookup tables in different database environments (i.e., development, quality-assurance, staging, and production). The data in these lookup tables are required to be the same across environments to ensure that your testing is accurate. There are a variety of great...
- Tags: Microsoft SQL Server, Environment, Server, TableDiff, Tim, Databases, Storage, Enterprise Software, Software, Data Management, Hardware, Tim Chapman
- Blog posts 2008-04-22
- Simplify SQL Server 2005 queries with a Dates table
- I find that it can be advantageous to use a Dates table, especially when doing a lot of date calculations. Learn what a Dates table is and how to create one, and then try it out for yourself. What is a Dates table? A Dates table stores a...
- Tags: Microsoft SQL Server, Microsoft SQL Server 2005, Table, DateLookup, DATEPART, SaleDate, Tim, Sales Strategy, Databases, Enterprise Software, Sales, Software, Data Management, Tim Chapman
- Blog posts 2008-04-14
- Document Sarbanes-Oxley compliance with SQL Server 2005
- As the DBA for revenue-generating applications, I am frequently invited by my CIO to meetings with Sarbanes-Oxley auditors to discuss the security and integrity of corporate data. In a nutshell, we're expected to answer any number of questions, via appropriate documentation, about who has access to our data, how access...
- Tags: Sarbanes-Oxley Compliance, Audit, Event, Sarbanes-Oxley Act, DDL, Microsoft SQL Server, Trigger, Microsoft SQL Server 2005, Data Definition Language Trigger, Data Definition Language Event, Tim, Databases, Enterprise Software, Software, Data Management, Tim Chapman
- Blog posts 2008-04-07
- Diagnose SQL Server performance issues using sp_lock
- A common misconception among IT pros is that "locks are bad," and you must do everything possible to ensure database locking doesn't prevent processes from running. In order to ensure a consistent database environment, the database engine must use a mechanism to acquire exclusive use of a resource when that...
- Tags: Procedure, Performance, Microsoft SQL Server, Lock, Locks, Spid, Dbid, Tim, Storage, Databases, Hardware, Enterprise Software, Software, Data Management, Tim Chapman
- Blog posts 2008-03-31
- Bulk importing data into SQL Server
- If you work with databases, at some point, you'll deal with inserting data into SQL Server from outside data files. This tutorial shows how to import data using the BULK INSERT command and explains how changing some of its options can make it easier and more efficient to insert data....
- Tags: Microsoft SQL Server, Data, Server, Trigger, BULK INSERT BULK INSERT, SalesHistory, FIELDTERMINATOR, Tim, Databases, Enterprise Software, Storage, Software, Data Management, Hardware, Tim Chapman
- Blog posts 2008-03-24
- Build directory structures using SQL Server 2005
- If you ever work with directory structures on the filing system, you know how challenging it can be to traverse through folders to find specific files. If you store this type of information structure in the database, you are even more aware of what it takes to retrieve the data....
- Tags: Microsoft SQL Server, Microsoft SQL Server 2005, Directory Structure, Tim, Databases, Storage, XML, Enterprise Software, Software, Data Management, Hardware, Software/Web Development, Web Development, Tim Chapman
- Blog posts 2008-03-17
- Generate dynamic SQL statements in SQL Server
- When you need to solve a tricky database problem, the ability to generate SQL statements is a powerful tool -- although you must be careful when using it. This article explores how you can use this functionality to generate SQL statements on the fly. Dynamic SQL statements A...
- Tags: Parameter, Procedure, Microsoft SQL Server, Statement, Server, SQL, SalesHistory, usp_GetSalesHistory, EXECUTE, Tim, Programming Languages, Databases, Software Development, Software/Web Development, Enterprise Software, Software, Data Management, Tim Chapman
- Blog posts 2008-03-10
- Sorting character strings using SQL Server
- You are probably familiar with the built-in sorting abilities of SQL Server for columnar data, but what if you need to sort character strings stored in SQL Server fields? Even though this may not be a common need for most users, this issue may come up from time to time. I'll demonstrate...
- Tags: Algorithm, Function, Microsoft SQL Server, Server, String, Character, VARCHAR, Tim, Engineering, Databases, Enterprise Software, Software, Data Management, Tim Chapman
- Blog posts 2008-03-03
- Monitor database file sizes with SQL Server Jobs
- The ability to automate administrative tasks is vital in almost any SQL Server database environment. In the SQL Server world, this scheduling of work is known as a "Job." It allows you to perform such tasks as database maintenance, invoke executables, ActiveX script, Integration Services packages, and business transactions. ...
- Tags: Job, Monitor, Step, Microsoft SQL Server, Tim, Recruitment & Selection, Databases, Enterprise Software, Human Resources, Workforce Management, Software, Data Management, Tim Chapman
- Blog posts 2008-02-25
- Capturing SQL Server 2005 database file size information
- It's very important to capture trends of the sizes of your SQL Server 2005 database because it allows you to plan for future space needs, notice types of problems, and plan for time periods of heavy volume. I'll show you the simple method that I use to capture this information....
- Tags: Microsoft SQL Server, Information, Microsoft SQL Server 2005, DatabaseFiles, Tim, Storage, Databases, Hardware, Enterprise Software, Software, Data Management, Tim Chapman
- Blog posts 2008-02-15
- Easily correct data casing with SQL Server
- Whether data is entered into your SQL Server system from a Web site, internal applications, or through files, the presentation of the data is always a concern. In this tutorial, I will create a function that will be useful in terms of updating data or just displaying data to end...
- Tags: Function, Microsoft SQL Server, Server, Data, Tim, Databases, Storage, Enterprise Software, Software, Data Management, Hardware, Tim Chapman
- Blog posts 2008-02-11
- Finding dependencies in SQL Server 2005
- Any time you need to modify objects in your SQL Server 2005 database, the objects that are dependent upon those objects are a concern. You don't want to remove columns from tables, procedures, views, or tables if there are objects dependent upon them that are being used. ...
- Tags: Procedure, Microsoft SQL Server, Microsoft SQL Server 2005, Object, SalesHistory, SalePrice, Tim, Databases, Storage, Enterprise Software, Software, Data Management, Hardware, Tim Chapman
- Blog posts 2008-02-04
- Find blocking processes using recursion in SQL Server 2005
- Blocking occurs in SQL Server 2005 when one process has one or more records locked, while another process tries to acquire locks on the same set or subset of records. This causes the second process to wait until the first process is done so it can acquire the records; this...
- Tags: Process, Microsoft SQL Server, Statement, Information, Microsoft SQL Server 2005, #Processes, BlockingSPID, SELECT, RowNo, LevelRow, usp_StoredProcedure3, Tim, Databases, Enterprise Software, Software, Data Management, Tim Chapman
- Blog posts 2008-01-28
- Monitor query performance with the SQL Server 2005 procedure cache
- In SQL Server 2005, you only need to recompile the statements inside stored procedures. This is much different than SQL Server 2000, where the entire procedure or batch may be recompiled. The benefit to the statement level recompile in SQL Server 2005 is that typically just a few statements inside...
- Tags: Monitor, Procedure, Performance, Microsoft SQL Server, Statement, Database Engine, Microsoft SQL Server 2005, Execution Plan, Tim, Databases, Storage, Enterprise Software, Software, Data Management, Hardware, Tim Chapman
- Blog posts 2008-01-17
- Capture execution plans with SQL Server 2005 Profiler
- Execution plans are one of the best tools for diagnosing problem queries and for performance tuning queries on your SQL Server. Prior to SQL Server 2005, the only options for query executions were text plans or graphical execution plans for queries run by hand. In SQL Server 2005, you can...
- Tags: Event, Microsoft SQL Server, Trace, Microsoft SQL Server 2005, Option, Execution Plan, SQL Server Management Studio, Tim, Databases, Storage, Enterprise Software, Software, Data Management, Hardware, Tim Chapman
- Blog posts 2008-01-14
- Write your own sp_lock system stored procedure in SQL Server 2005
- The sp_lock system stored procedure is a great tool for checking the amount of locking that occurs on your database system. It returns the number and types of locks that are being held by current active SQL Server sessions. One of my previous articles describes how you can use sp_lock...
- Tags: Procedure, Microsoft SQL Server, Statement, Information, Session, Microsoft SQL Server 2005, LEFT JOIN, Sql_handle, sp_LockDetail Example, Tim, Storage, Databases, Hardware, Enterprise Software, Software, Data Management, Tim Chapman
- Blog posts 2008-01-07
- Detecting index fragmentation in SQL Server 2005
- When data is inserted, updated, or deleted in a database table, if indexes exist that operate on the table, they must be maintained to reflect the table data changes. The maintenance of these indexes will eventually cause the indexes to become less efficient. This inefficiency is typically due to the...
- Tags: Function, Database, Fragmentation, Workaround, Microsoft SQL Server, Index, Microsoft SQL Server 2005, Tim, Databases, Storage, Enterprise Software, Software, Data Management, Hardware, Tim Chapman
- Blog posts 2007-12-17
- Rebuild indexes online with SQL Server 2005
- Indexes are specialized data structures that operate on tables and sometimes views in the database engine used to aid in the searching for and sorting of data. Indexes are vital to the database engine returning results quickly. As data is modified in the underlying tables that the indexes operate on,...
- Tags: Fragmentation, Microsoft SQL Server, Statement, Index, Microsoft SQL Server 2005, Table, ALTER INDEX Statement, ONLINE, ONLINE Option, Tim, Databases, Enterprise Software, Software, Data Management, Tim Chapman
- Blog posts 2007-12-10
