If I change a column to not nullable or shorten a column, I get the error:
The altered column cannot be:
A column with a text, image, ntext, or timestamp data type.
The ROWGUIDCOL for the table.
A computed column or used in a computed column.
A replicated column.
Used in an index, unless the column is a varchar, nvarchar, [...]
In most cases it is wise to let SQL Server automatically update statistics.
To turn on automatic updates for all statistics in a database, if not already on, execute the following statement:
USE master
EXEC sp_dboption ‘MyDatabase’, ‘ auto update statistics’, ‘true’
To turn on automatic updates for all statistics on a specific table, such as Clients, execute the [...]
select
schemas.name as table_schema,
tbls.name as table_name,
i.name as index_name,
i.id as table_id,
i.indid as index_id,
i.groupid,
i.rowmodctr as modifiedRows,
(select max(rowcnt) from sysindexes i2 where i.id = i2.id and i2.indid < 2) as rowcnt,
convert(DECIMAL(18,8), convert(DECIMAL(18,8),i.rowmodctr) / convert(DECIMAL(18,8),(select max(rowcnt) from sysindexes i2 where i.id = i2.id and i2.indid < 2))) as ModifiedPct,
stats_date( i.id, i.indid ) as lastStatsUpdate,
‘False’ as Processed
into ##updateStatsQueue
from sysindexes i
inner join [...]
Query to get a list Statistics in the database (from sys.sysindexes table)
select name, rowcnt, rowmodctr,* from sys.sysindexes where rowcnt = 0 and name like ‘%_WA%’
All Statistics objects in the sys.sysindexes have rowcnt = 0 and its name starts from ‘_WA’
SP_UPDATESTATS refers to work against all user-defined tables in the current database.
For the stored procedures performance you need to recompile them in order to take the new plan in to the memory, so run SP_RECOMPILE for all or required stored procedures to compare the sequence of performance.
If you want to find out if an index has had its indexes updated or not, you can use the command, like this:
DBCC SHOW_STATISTICS (table_name , index_name)
This command will not only tell you when statistics were last updated, but also provide you information on the kind of statistics that has been collected for the index [...]
SQL Server follows a very specific set of rules on when it should update the statistics of an index. Here they are:
- If the number of rows in a table are greater than 6, but less than or equal to 500, then statistics are automatically updated when there have been 500 modifications made.
- If the [...]
SQL Server keeps statistics about the distribution of the key values in each index and uses these statistics to determine which index(es) to use in query processing. Users can create statistics on nonindexed columns by using the CREATE STATISTICS statement.
Query optimization depends on the accuracy of the distribution steps:
- If there is significant change in the [...]
FULLSCAN is the same as SAMPLE RATE 100 with the difference that with SAMPLE RATE, SQL Server 2005 might adjust the rate up (and use a sample rate that is sufficiently high to create useful statistics) and if the requested rate creates more values than is considered needed, it “tries to match the requested sample [...]
SELECT ‘update statistics’ + ‘ ‘ + RTRIM(object_name(I.id)) + ‘ ‘ + RTRIM(name)
/* DATALENGTH (statblob) size,
STATS_DATE (I.id, I.indid) last_updated */
FROM
sysindexes as I
WHERE
OBJECTPROPERTY(I.id, N’IsUserTable’) = 1 AND
INDEXPROPERTY (I.id , name , ‘IsAutoStatistics’ ) = 1 AND
DATALENGTH (statblob) is null