Indexed view

To speed up the aggregate functions, you may consider using indexed view. The following script shows you how to create an indexed view.

Indexed view basic requirements:

1. COUNT_BIG() must be included in the field list.
2. An unique clustered index must be created.
   

create view vw_test1
with schemabinding
as
select cust_id, total_amt = sum(amt), count = count_big(*)
from invoice
group by cust_id

go

create unique clustered index ix_vw_test1
on vw_test1 (cust_id)

go

select *
from vw_test1 with (noexpand)

Notes:

• "WITH (NOEXPAND)" option must be used when you are querying the data through the indexed view. Otherwise, the query optimizer will take use the view definition to query the data and the indexes created for the view will not be used.
  

References:

http://technet.microsoft.com/en-us/library/ms191432.aspx
http://technet.microsoft.com/en-us/library/cc917715.aspx

Converting comma delimeter string into a table

It would be nice to split the incoming comma delimeter string into a table and returns a table back to the caller.

For example, you would like to turn 'A,B' into a table.

select *
from dbo.fn_to_table ('''A'',''B''')

select count(*)
from dbo.fn_to_table ('''A'',''B''')

create function dbo.fn_to_table (@str nvarchar(max))
returns  @tb table (code nvarchar(50)) 
as
begin

declare 
  @pos int,
  @prev_pos int,
  @s nvarchar(50)

select @pos = charindex(',', @str)
select @prev_pos = 0

if ((len(@str) > 0) and (@pos = 0))
 insert into @tb (code)  
 select replace(@str, '''','');

while @pos > 0
begin
 select @s = replace( substring(@str, @prev_pos, 
                 @pos - @prev_pos), '''','');
 -- select @prev_pos, @pos, @s
 insert into @tb (code)
 select @s

 select @prev_pos = @pos + 1;
 select @pos = charindex(',', @str, @pos + 1);
end

if ((@prev_pos > 0) and (@pos = 0))
begin
 insert into @tb (code)
 select  replace( substring(@str, @prev_pos, 
             len(@str) - @prev_pos), '''','');
end

return 

end

Statistics

Query optimizer is using statistics to come out with a better query plan. By default, the SQL Server will automatically create statistics in the database.

In Microsoft SQL Server Management Studio, right click on the database name and choose Properties. Then, goto "Options" page and look for "Auto Create Statistics" and "Auto Update Statistics" options.

In case you would like to create the statistics manually you may execute the following stored procedure:

  sp_createstats

OR manually update the statistics:

  sp_updatestats

OR

  update statistics {table name}

To view the statistic details, you have to call DBCC command:

  dbcc show_statistics ('{table name}', '{index or statistics}')

References:
http://technet.microsoft.com/en-us/library/ms190397.aspx
http://msdn.microsoft.com/en-us/library/ms174384.aspx
http://msdn.microsoft.com/en-us/library/cc966419.aspx

Check database consistency

if exists( select * from sys.objects where name ='mgt_check_db')
 drop proc mgt_check_db
go

create proc mgt_check_db (
 @db_name sysname
)
as

DBCC CHECKDB(@db_name);

go

Create backup stored procedure

This stored procedure helps you to backup the database in 7 day cycle and each cycle has one file.

exec sp_addumpdevice 'disk', 'db_mon_bak', 
        'c:\mssql\backup\db_mon_bak.bak'
go
exec sp_addumpdevice 'disk', 'db_tue_bak', 
        'c:\mssql\backup\db_tue_bak.bak'
go
exec sp_addumpdevice 'disk', 'db_wed_bak', 
        'c:\mssql\backup\db_wed_bak.bak'
go
exec sp_addumpdevice 'disk', 'db_thu_bak', 
        'c:\mssql\backup\db_thu_bak.bak'
go
exec sp_addumpdevice 'disk', 'db_fri_bak', 
        'c:\mssql\backup\db_fri_bak.bak'
go
exec sp_addumpdevice 'disk', 'db_sat_bak', 
        'c:\mssql\backup\db_sat_bak.bak'
go
exec sp_addumpdevice 'disk', 'db_sun_bak', 
        'c:\mssql\backup\db_sun_bak.bak'
go

if exists( select * from sysobjects 
                where name = 'mgt_backup_db')
 drop proc mgt_backup_db
go

CREATE proc mgt_backup_db (
 @db_name sysname
)
as
begin

declare @d int

select @d = datepart( weekday, getdate())

if (@d = 2)
 backup database @db_name
 to db_mon_bak
 with init

else if (@d = 3)
 backup database @db_name
 to db_tue_bak
 with init

else if (@d = 4)
 backup database @db_name
 to db_wed_bak
 with init

else if (@d = 5)
 backup database @db_name
 to db_thu_bak
 with init

else if (@d = 6)
 backup database @db_name
 to db_fri_bak
 with init

else if (@d = 7)
 backup database @db_name
 to db_sat_bak
 with init



end


GO

Offline rebuilding indexes

"DBCC DBREINDEX" or "ALTER INDEX..REBUILD" is an offline reindexing. You may consider using "DBCC INDEXDEFRAG" or "ALTER INDEX..REORGANIZE" to do the online reindexing in a mission critical database.

if exists( select * from sys.objects where name ='mgt_reindex')
   drop proc mgt_reindex
go

create proc mgt_reindex
as
begin

declare @tb_name sysname,
@idx_name sysname,
@sql nvarchar(500)

declare cr cursor
for

   select
       o.name as tb_name, i.name as idx_name
   from sys.indexes i
   inner join sys.objects o on o.object_id = i.object_id
   where o.type='u'
       and i.name is not null
   order by o.name, i.name

open cr

fetch next from cr
into @tb_name, @idx_name

while (@@fetch_status = 0)
begin
   select @sql = 'dbcc dbreindex(' 
      + @tb_name +','
      + @idx_name + ')'
   exec sp_executesql @sql  

   fetch next from cr
   into @tb_name, @idx_name

end

close cr
deallocate cr


end
go