SQL


Group and sort by month and year

This will take a datetime2 and group the rows together by month and year and then sort i by their count.

SELECT YEAR(datetime), MONTH(datetime), count(*)
FROM [Database].[dbo].[Table]
GROUP BY YEAR(datetime), MONTH(datetime)
ORDER BY YEAR(datetime) desc, MONTH(datetime) desc

 

Shrink log-file

ALTER DATABASE faktabank SET RECOVERY Simple

DBCC SHRINKFILE (database_name_log, 1)

ALTER DATABASE database_name SET RECOVERY Full

 

See degragmentation of all tables for database

SELECT dbschemas.[name] as 'Schema', 
dbtables.[name] as 'Table', 
dbindexes.[name] as 'Index',
indexstats.alloc_unit_type_desc,
indexstats.avg_fragmentation_in_percent,
indexstats.page_count
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS indexstats
INNER JOIN sys.tables dbtables on dbtables.[object_id] = indexstats.[object_id]
INNER JOIN sys.schemas dbschemas on dbtables.[schema_id] = dbschemas.[schema_id]
INNER JOIN sys.indexes AS dbindexes ON dbindexes.[object_id] = indexstats.[object_id]
AND indexstats.index_id = dbindexes.index_id
WHERE indexstats.database_id = DB_ID()
ORDER BY indexstats.avg_fragmentation_in_percent desc

 

Rebuild all table indexes for database

USE 'database_name' --Enter the name of the database you want to reindex 
 
DECLARE @TableName varchar(255) 
DECLARE TableCursor CURSOR FOR 
SELECT table_name FROM information_schema.tables 
WHERE table_type = 'base table' 

OPEN TableCursor 

FETCH NEXT FROM TableCursor INTO @TableName 
WHILE @@FETCH_STATUS = 0 
BEGIN 
DBCC DBREINDEX('TableName',' ',90) 
FETCH NEXT FROM TableCursor INTO @TableName 
END 
 
CLOSE TableCursor 
DEALLOCATE TableCursor 

 

Check if table exists before adding or removing it

IF OBJECT_ID('dbo. MyTable_Name', 'U') IS NOT NULL
DROP TABLE MyTable_Name

IF OBJECT_ID('dbo. MyTable_Name', 'U') IS NOT NULL
CREATE TABLE MyTable_Name(
TheDateTime datetime NOT NULL,
Name nvarchar(64) NOT NULL,
Data varbinary(MAX) NOT NULL)

 

 


Published: 2016-08-25