SQL Server, T-SQL, Development, DBA and Career Resources

Human Created Content by Industry Experts for Developers, DBAs and Analytics Professionals

Index System Tables in SQL Server 2000 vs SQL Server 2005

The primary resource in SQL Server 2000 to capture index related information was master.dbo.sysindexes. I used this system table to capture information on row and page counts or total size information for a specific table. I know with SQL Server 2005,

SQL Server Deadlock Priority Configuration

In specific SQL Servers databases, I have deadlock problems that often result in significant performance degradation. When the deadlocks occur frequently in my environment, the deadlocks often become a performance killer for the overall application. If

Suspect SQL Server 2000 Database (Part 1 of 2)

In your recent tip (Suspect SQL Server 2000 Database) you outlined a suspect database scenario. I took a look at the script and tried to run it in my environment. Unfortunately, this did not resolve my issue. Are you aware of other causes of SQL Server

Different Options for Importing Data into SQL Server

Moving data into SQL Server is something that most DBAs or Developers are faced with probably on a daily basis. One simple way of doing this is by using the Import / Export wizard, but along with this option there are several other ways of loading data i

Understanding SQL Server Indexing

With so many aspects of SQL Server to cover and to write about, some of the basic principals are often overlooked. There have been several people that have asked questions about indexing along with a general overview of the differences of clustered and no

SQL Server 2005 Service Broker Infrastructure Objects

In a recent tip you outlined steps to troubleshoot Service Broker. I can see how that would be a good tip for someone who is actively working with Service Broker in SQL Server 2005. Unfortunately, I am new to SQL Server 2005 and Service Broker. I am no

Suspect SQL Server 2000 Database

I have a SQL Server 2000 database that has the wrong database status. For some reason it is in the 'Suspect Offline' mode. I just need to correct the problem quickly and get my database back online. How can I do so? Once the status is correct, do I n

Export data from SQL Server to Excel

Exporting data from SQL Server to Excel seems like a reasonably simple request. I just need to write out a few reports for users on a regular basis, nothing too fancy, the same basic report with a few different parameters. What native SQL Server options

SQL Server Agent Proxies

With so many security changes in SQL Server 2005, how did the security changes affect SQL Server Agent? I have heard about SQL Server Agent Proxies, but I am not too familiar with them. How do I set them up? What dependencies do they have? What is the

ASP.NET Security for SQL Server

In all of my ASP applications I need to have at least basic security, how can I do so in simple manner? The reality is that in the majority of my applications I need membership, profiles, role management and personalization, are any applications availabl

Service Broker Troubleshooting

When learning about Service Broker (or any new SQL Server 2005 technology) you are bound to have some issues with your first application. With the value that Service Broker offers applications directly via the T-SQL language, this is a valuable technolog

Remotely Accessing the SQL Server Desktop

How can I get direct access to my SQL Server's desktop, not a terminal services session? Do I need to purchase a third party product or is a tool available with Windows or SQL Server? I know I should not need access to my SQL Server like this on a regul

Gaps in SQL Server Identity Columns

I have a table that makes use of integer based sequential values (identity based or otherwise) and I need to determine what gaps exist within this table. What is the best way to determine these gaps using a set-based approach (i.e. no cursor operations)?

SQL Server Full Text Catalogs Stored Procedures

As mentioned in a previous tip, there are a number of locations where full-text search information can be found in SQL Server 2000 and SQL Server 2005. SQL Server 2005 includes some dynamic management views, but in SQL Server 2000 there are no such views,

Buffer Pool Space in SQL Server 2005

I need to determine what objects/structures are consuming the largest amount of space at a given time (or over time) within the SQL Server buffer pool. How can I achieve this in SQL Server 2005?

Service Pack 2 Uninstall Not Possible for SQL Server 2005

You probably already know that SQL Server 2005 SP2 has been released. This is a good thing if you are in need of one of the changes that are included in this service pack. In a previous tip, SQL Server 2005 - Service Pack 2, we took a look at what was to