SQL Server Keywords (Reserved Words)

The keywords (Reserved Words) used by SQL Server 2008 Continue reading ...

WaitFor Delay & Time

Shows how to make SQL Server "Sleep" using the WaitFor command.

Join Types

Describes the different kinds of joins used in SQL Server. Continue reading ...

Rebuild and Reorganize Fragmented Indexes

This script will automatically determine whether a rebuild or a reorganize should be used according to the fragmentation of the index. It will then execute the appropriate command. Note that performing index rebuilds online during production hours will cause contention. [cc lang=”sql”] SET NOCOUNT ON; DECLARE @objectid int; DECLARE @indexid int; DECLARE @partitioncount bigint; DECLARE @schemaname nvarchar(258); DECLARE @objectname nvarchar(258); DECLARE @indexname nvarchar(258); DECLARE @partitionnum bigint; DECLARE @partitions bigint; DECLARE @frag float; DECLARE @command varchar(8000); — ensure the temporary table does not exist IF EXISTS (SELECT name FROM sys.objects WHERE name = ‘work’) DROP TABLE work; — conditionally select from the function, converting object and index IDs — to names. SELECT object_id AS objectid, index_id AS indexid, partition_number AS partitionnum, avg_fragmentation_in_percent AS frag INTO work FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, ‘LIMITED’) WHERE avg_fragmentation_in_percent > 10.0 AND index_id > 0; — Declare the cursor for the list of partitions to be processed. DECLARE partitions CURSOR FOR SELECT * FROM work; — Open the cursor. OPEN partitions; — Loop through the partitions. FETCH NEXT FROM partitions INTO @objectid, @indexid, @partitionnum, @frag; WHILE @@FETCH_STATUS = 0 BEGIN; SELECT @objectname = QUOTENAME(o.name), @schemaname = QUOTENAME(s.name) FROM sys.objects AS o JOIN sys.schemas as s ON s.schema_id = o.schema_id WHERE o.object_id = @objectid; SELECT @indexname = QUOTENAME(name) FROM sys.indexes WHERE object_id = @objectid AND index_id = @indexid; SELECT @partitioncount = count (*) FROM sys.partitions WHERE object_id = @objectid AND index_id = @indexid; — 30 is the decision point at which to switch — between […]

How to Update Statistics

There are two ways to update statistics. The first way is the easy way. It is one line of code that will update all the statistics in the database using the default sample size of 20,000 rows per table. [cc lang=”sql”] EXEC sp_updatestats [/cc] The other way, is to use the UPDATE STATISTICS command. This command gives much better granularity of control: [cc lang=”sql”] — Update all statistics on a table UPDATE STATISTICS Sales.SalesOrderDetail — Update a specific index on a table UPDATE STATISTICS Sales.SalesOrderDetail IX_SalesOrderDetail — Update one column on a table specifying sample size UPDATE STATISTICS Production.Product(Products) WITH SAMPLE 50 PERCENT [/cc] Using update statistics can give you the granularity of control to only update the out of date statistics, thus having less impact on your production system. The following script updates all out of date statistics. Set the @MaxDaysOld variable to the number of days you will allow the statistics to be out of date by. Setting the @SamplePercent variable to null will use the SQL Server default value of 20,000 rows. You can also change the sample type to specify rows or percent. [cc lang=”sql”] DECLARE @MaxDaysOld int DECLARE @SamplePercent int DECLARE @SampleType nvarchar(50) SET @MaxDaysOld = 0 SET @SamplePercent = NULL –25 SET @SampleType = ‘PERCENT’ –‘ROWS’ BEGIN TRY DROP TABLE #OldStats END TRY BEGIN CATCH SELECT 1 END CATCH SELECT RowNum = ROW_NUMBER() OVER (ORDER BY ISNULL(STATS_DATE(object_id, st.stats_id),1)) ,TableName = OBJECT_SCHEMA_NAME(st.object_id) + ‘.’ + OBJECT_NAME(st.object_id) ,StatName = st.name ,StatDate = ISNULL(STATS_DATE(object_id, st.stats_id),1) INTO #OldStats […] Continue reading ...

Featured Articles

 Site Author

  • Thanks for visiting!
css.php