ORDER BY Clause Enhancement and Pagination in SQL Server 2011

Microsoft SQL Server Developers group Group News | LinkedIn

Find SQL Server data and log files that are almost out of space

Find SQL Server data and log files that are almost out of space

Creating I/O performance snapshots to find performance problems in SQL Server

Creating I/O performance snapshots to find performance problems in SQL Server

Five DMV Queries That Will Make You A Superhero! | Glenn Berry's SQL Server Performance

Five DMV Queries That Will Make You A Superhero! | Glenn Berry's SQL Server Performance

SQL SERVER – 2005 – Database Table Partitioning Tutorial – How to Horizontal Partition Database Table « Journey to SQLAuthority

SQL SERVER – 2005 – Database Table Partitioning Tutorial – How to Horizontal Partition Database Table « Journey to SQLAuthority

Index fragmentation and re-indexing them

SET NOCOUNT ON;

declare @tbl nvarchar(100) = ''
if(OBJECT_ID('tempdb..#tmp')>1)
drop table #tmp

create table #tmp (servername nvarchar(100),dbname nvarchar(100), tblname nvarchar(100), indexid int, indexname nvarchar(1000), avg_fragmentation_in_percent numeric(15,10), string nvarchar(1000))

declare @mid int , @comid int
declare tmp_cur cursor for
select name From sys.tables where type_desc = 'USER_TABLE' and name not like '[__]%'

open tmp_cur
Fetch next from tmp_cur into @tbl

while @@fetch_status = 0
begin
--print @tbl

--dbcc showcontig(@tbl)
insert into #tmp
SELECT
@@servername,db_name(), @tbl, a.index_id, name, avg_fragmentation_in_percent,
(case
when avg_fragmentation_in_percent < 30 and avg_fragmentation_in_percent > 5 then 'ALTER INDEX '+name+' ON '+@tbl+' REORGANIZE ;'
when avg_fragmentation_in_percent > 30 then 'ALTER INDEX '+name+' ON '+@tbl+' REBUILD WITH (ONLINE=ON) ;'
else 'go'
end)
FROM sys.dm_db_index_physical_stats (DB_ID(), OBJECT_ID(N'dbo.'+@tbl+''),
NULL, NULL, NULL) AS a
JOIN sys.indexes AS b ON a.object_id = b.object_id AND a.index_id = b.index_id;



Fetch next from tmp_cur into @tbl
--print '---'
end

close tmp_cur
deallocate tmp_cur

--select * from #tmp where string != 'go'
----------------------------------------------------

declare @string nvarchar(1000), @avgfrag numeric(20,15), @tblname nvarchar(100), @idx nvarchar(100)
declare tmp_cur cursor for
select string, avg_fragmentation_in_percent,tblname,indexname from #tmp where string != 'go'

open tmp_cur
Fetch next from tmp_cur into @string ,@avgfrag,@tblname,@idx

while @@fetch_status = 0
begin
print '--' +@tblname+' : '+@idx+' : '+convert(nvarchar(20),@avgfrag)

print @string
print 'go'
print 'dbcc dbreindex('''+@tblname+''','''',90)'
print 'go'

print '---'

Fetch next from tmp_cur into @string, @avgfrag,@tblname,@idx
end

close tmp_cur
deallocate tmp_cur

Microsoft SQL Server 2000 Index Defragmentation Best Practices

Microsoft SQL Server 2000 Index Defragmentation Best Practices

Reorganizing and Rebuilding Indexes

Reorganizing and Rebuilding Indexes

High Availability Options for SQL Server 2008

High Availability Options for SQL Server 2008

function to create a random string

set ANSI_NULLS ON set QUOTED_IDENTIFIER ON
go
create view RandomHelper as select rand( ) as r
GO
CREATE function [dbo].[CreateRandomString]
(
@passwordLength as smallint
)
RETURNS varchar(100)
AS
Begin
DECLARE @password varchar(100)
declare @characters varchar(100)
declare @count int set @characters = ''
-- load up numbers 0 - 9
set @count = 48

while @count <=57
begin
set @characters = @characters + Cast(CHAR(@count) as char(1))
set @count = @count + 1 end
-- load up uppercase letters A - Z
set @count = 65
while @count <=90
begin
set @characters = @characters + Cast(CHAR(@count) as char(1))
set @count = @count + 1 end
-- load up lowercase letters a - z
set @count = 97
while @count <=122
begin
set @characters = @characters + Cast(CHAR(@count) as char(1))
set @count = @count + 1
end
set @count = 0
set @password = ''
while @count < @passwordLength
begin
set @password = @password + SUBSTRING(@characters,CAST((SELECT r FROM RandomHelper)*LEN(@characters) as int)+1,1)
set @count = @count + 1
end
RETURN @password
end
GO

SELECT dbo.CreateRandomString(10)
go