To find some text in some Db Object, use this script:
select * from sysobjects s inner join syscomments c on c.id = s.id
where text like '%blablabla%'
To convert datetime into integer number
select CONVERT(CHAR(8), getdate(), 112)
To find rows that their date field, is in range of speciphic date and keep using index on the relevant field:
declare @date SMALLDATETIM
SELECT *
FROM dbo.SomeLogTable
WHERE DateColumn >= @date AND DateColumn < DATEADD(DAY, 1, @date)
To get the Counts of rows for each day in the month use this code:
declare @Month datetime
set @Month='20110601'
--If you have Already numbers table: tblNumbers with one column: [Num]
SELECT [day] = DATEADD(DAY, [Num]-1, @Month)
FROM [tblNumbers]
WITH [days] AS
(
WHERE [Num] <= DATEDIFF(DAY, @Month, DATEADD(MONTH, 1, @Month))
SELECT [day] = DATEADD(DAY, [Num]-1, @Month)
FROM dbo.[tblNumbers]
WHERE [Num] <= DATEDIFF(DAY, @Month, DATEADD(MONTH, 1, @Month))
)
SELECT
d.[day],COUNT(t.DateColumn)
FROM
[days] AS d INNER JOIN dbo.SomeLogTable AS t ON t.DateColumn >= d.[day] AND t.DateColumn < DATEADD(DAY, 1, d.[day])
ORDER BY d.[day]
Execute X time transaction and measure the time it takes:
GROUP BY d.[day]
SELECT CURRENT_TIMESTAMP
GO
EXEC SomeProcedure
GO 1000
SELECT CURRENT_TIMESTAMP
GO
Create Numbers table base on Input parameter
declare @totalNumbers int
select @totalNumbers=2000
;with cte_N as(
select 1 Num
union all
select Num+1 Num
from cte_N
where (Num+1)<@totalNumbers
)
select Num
from cte_N
OPTION (MAXRECURSION 0)
get all objects related to a filegroup
select distinct(object_name(id)) from sysindexes where groupid=filegroup_id('<filegroup-name>')
Get all unused indexes since the lasr restart of rhe instance\Database
DECLARE @dbid INT
SELECT @dbid = DB_ID(DB_NAME()SELECT OBJECTNAME = OBJECT_NAME(I.OBJECT_ID),INDEXNAME= I.NAME,
I.INDEX_ID
FROM SYS.INDEXES I JOIN SYS.OBJECTS O
ON I.OBJECT_ID = O.OBJECT_ID
OBJECTPROPERTY(O.OBJECT_ID,'IsUserTable') = 1 and I.INDEX_ID>0
WHERE
AND I.INDEX_ID NOT IN (
SELECT S.INDEX_ID
FROM SYS.DM_DB_INDEX_USAGE_STATS S
WHERE S.OBJECT_ID = I.OBJECT_ID
AND
I.INDEX_ID = S.INDEX_ID
AND
DATABASE_ID = @dbid)
ORDER
BY OBJECTNAME,
I.INDEX_ID, INDEXNAME ASC
Paging Query
You can make paging select in Tsql, by combining Ranking Window Functions and CTE:
DECLARE @RowsInPage TINYINT , @PageId INT
--create sample table
CREATE TABLE #tmp(id int IDENTITY (1,1) PRIMARY KEY , name varchar(50))
DECLARE @i int, @str varchar(50)
SElecT @i=0,@str=newId()
--fill the table
;with cte_i as
(
SELECT 1 AS sNum
UNION all
SELECT sNum+1 sNum
FROM cte_i
WHERE sNum<1001
)
INSERT INTO #tmp(name)
SELECT left(@str,len(@str)-4)+ltrim(sNum)
FROM cte_i OPTION (MAXRECURSION 0);
--select the required page rows
SELECT @RowsInPage= 10,@PageId = 3
;WITH Cte_T AS
(
SELECT TOP (@PageId * @RowsInPage) RId = ROW_NUMBER() OVER (ORDER BY name),*
FROM #tmp
)
SELECT *
FROM Cte_T
WHERE RId BETWEEN ((@PageId - 1) * @RowsInPage + 1) AND
(((@PageId - 1) * @RowsInPage) + @RowsInPage)
ORDER BY RId
DROP TABLE #tmp