Podstawowe polecenia w SQL Server

Podczas pracy z SQL Server niezbędne wg mnie są następujące polecenia:

1. Wyszukiwanie treści w bazie:

select o.name
from sys.sql_modules c
join sysobjects o on c.object_id = o.id
where [definition] like '%%'

2. Procent przebudowania indeksów w bazie:

;WITH cte AS
(
SELECT
object_id,
index_id,
partition_number,
rows,
ROW_NUMBER() OVER(PARTITION BY object_id, index_id, partition_number ORDER BY partition_id) as rn
FROM sys.partitions
)
SELECT
   object_name(cur.object_id) as TableName,
   cur.index_id,
   cur.partition_number,
   PrecentDone =
      CASE
         WHEN pre.rows = 0 THEN 0
      ELSE
         ((cur.rows * 100.0) / pre.rows)
      END,
   pre.rows - cur.rows as MissingRows
FROM cte as cur
INNER JOIN cte as pre on (cur.object_id = pre.object_id) AND (cur.index_id = pre.index_id) AND (cur.partition_number = pre.partition_number) AND (cur.rn = pre.rn +1)
ORDER BY 4

3. Aktualnie wykonujący się blok kodu w danej sesji:

select QueryText = SUBSTRING(st.text, (re.statement_start_offset/2)+1,         
       ((CASE re.statement_end_offset
          WHEN -1 THEN DATALENGTH(st.text)
         ELSE re.statement_end_offset
         END - re.statement_start_offset)/2) + 1)
from sys.dm_exec_requests re with (nolock)
CROSS APPLY sys.dm_exec_sql_text(re.sql_handle) AS st
where re.session_id = 1234

4. Wypisywanie aktualnie wykonywanej operacji bez oczekiwania na zakończenie się całej procedury:

declare @Date date ='2015-01-16';
declare @DateStr varchar(100);
while(@Date < cast(getdate() as date))
begin
 set @DateStr = cast(@Date as varchar);
 Raiserror(@DateStr,0,1) with nowait
 --tutaj operacje z jakimiś datami
 set @Date = dateadd(day,1,@date);
end

5. Przykład wykorzystania ROW_NUMBER() do wyeliminowania duplikatów danych:

create table #fox_table  (ID bigint)

insert into #fox_table(ID) values (1);
insert into #fox_table(ID) values (1);
insert into #fox_table(ID) values (1);
insert into #fox_table(ID) values (1);
insert into #fox_table(ID) values (5);
insert into #fox_table(ID) values (5);
insert into #fox_table(ID) values (5);

;with duplicates as
(
 select ID, ROW_NUMBER() over (partition by ID order by ID) as RowNum
 from #fox_table with (nolock)
)
delete from duplicates where RowNum > 1;

select * from #fox_table

drop table #fox_table