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
