R Barry Young's procedure fails if any databases have conflicting collations. Report Server databases have Latin1_General_CI_AS_KS_WS collations, whereas the default collation is often SQL_Latin1_General_CP1_CI_AS.

Since the SQL Server Surface Area configuration tool is deprecated for SQL 2008 and later, you need to enable OPENROWSET and OPENDATASOURCE as below:

sp_configure 'show advanced options',1

reconfigure

go

sp_configure 'Ad Hoc Distributed...

I'm consulting at a company with a developer-designed database which has a very large table (over 2 billion rows) that has a number of small char or varchar categorical columns....

Hi all,

I borrowed the guts of what Timothy Parker did and created a version that works on one database only, so there is no outer cursor loop. I also...

This should do it for you.

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[uspDropStatsOnColumn]') AND type in (N'P', N'PC'))

DROP PROCEDURE [dbo].[uspDropStatsOnColumn]

go

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

GO

create procedure [dbo].[uspDropStatsOnColumn](

...

I like this better:

declare @bntTotalRowsScanned bigint

select @bntTotalRowsScanned = sum(i.rowcnt * u.user_scans)

from sys.dm_db_index_usage_stats as u

inner joinsys.sysobjects as o on u.object_id = o.id and o.xtype = 'U'

inner joinsys.sysindexes as i on u.index_id...

Michael,

Very slick! Thank you!

Jeff

Gail,

What you wrote was my prior understanding. I opened a case with MS which went on for weeks. Our procedure cache was being filled with different plans for the same...

You can also get multiple copies if the sproc does conditional logic within. Depending on the branch taken, different plans result.

FWIW, 2005 Standard Edition (and also 2008, I believe) does permit up to three partitions. We have been using it in production for several years with no problems. However, Microsoft...

Excellent article. I have run into all of these things.

Another poor practice is using bloated datatypes. On a large database, using the smallest reasonable integer, date, and money types...

Has anyone implemented this in production? If so, could you share your experience? Any tips or tricks or gotcha's?

Thanks in advance.

I believe this will produce errors on tables with existing data.

From BOL on DBCC CHECKIDENT:

DBCC CHECKIDENT ( 'table_name', RESEED, new_reseed_value )

Current identity value is set to the new_reseed_value. If no...

Great article. I apologize for my rude English-speaking compatriots. You seem to speak French natively, and I very much appreciate your writing the article in English, because I sure don't...

I had been working as a management consultant traveling every week, and wanted to stay home with my young children. Although I have a Stanford Ph.D., I had taken only...