🗄️

SQL Database

SQL queries, database optimization och maintenance

Vad du hittar här

Denna sida innehåller praktiska SQL-exempel, optimeringsstrategier och maintenance-skript för SQL Server-databaser. Allt från grundläggande frågor till avancerad databasadministration.

Innehåll

Query Examples

Maintenance Scripts

Best Practices

Common Issues

SQL Query Examples

📅

Group and Sort by Month and Year

This will take a datetime2 and group the rows together by month and year and then sort 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

💡 Förklaring: Groups records by year and month, then sorts by count in descending order

🗜️

Shrink Log File

Reduce database log file size by switching to simple recovery mode temporarily.

ALTER DATABASE faktabank SET RECOVERY Simple
DBCC SHRINKFILE (database_name_log, 1)
ALTER DATABASE database_name SET RECOVERY Full

💡 Förklaring: Changes recovery mode, shrinks log file, then restores full recovery mode

🔍

Check Table Fragmentation

See fragmentation level of all tables and indexes in the 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

💡 Förklaring: Returns fragmentation statistics for all indexes, sorted by worst fragmentation first

Database Maintenance

Rebuild All Table Indexes

Rebuild all indexes for all tables in the database to improve performance.

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

⚠️ Varning: This can take a long time on large databases and may block other operations

Check if Table Exists

Safe way to drop and recreate tables without errors if they don't exist.

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

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

⚠️ Varning: Always backup your data before dropping tables

Best Practices

✅ Use Parameterized Queries

Always use parameters to prevent SQL injection attacks and improve performance through plan reuse.

💡 Exempel: Use @param instead of concatenating strings into SQL queries

✅ Index Strategy

Create indexes on columns used in WHERE, JOIN, and ORDER BY clauses, but avoid over-indexing.

💡 Exempel: Monitor query execution plans to identify missing indexes

✅ Regular Maintenance

Schedule regular index rebuilds, statistics updates, and database integrity checks.

💡 Exempel: Set up maintenance plans for automated optimization

✅ Monitor Performance

Use DMVs (Dynamic Management Views) to identify slow queries and resource bottlenecks.

💡 Exempel: Query sys.dm_exec_query_stats to find expensive queries

Vanliga problem och lösningar

⚠️ Blocking and Deadlocks

Long-running transactions can block other queries and cause timeouts

🔧 Lösning: Use shorter transactions, proper indexing, and READ COMMITTED SNAPSHOT isolation

⚠️ Index Fragmentation

Fragmented indexes slow down query performance over time

🔧 Lösning: Regular index maintenance and monitoring fragmentation levels

⚠️ Poor Query Performance

Queries running slower than expected due to missing indexes or poor plans

🔧 Lösning: Analyze execution plans, add missing indexes, and update statistics

⚠️ Growing Log Files

Transaction log files growing unexpectedly large

🔧 Lösning: Configure appropriate recovery model and log backup schedule

💡SQL Development Tips

Test på utvecklingsmiljö först

Testa alltid komplicerade queries och maintenance-skript på en dev-miljö innan produktion.

Använd execution plans

Aktivera Show Actual Execution Plan för att förstå hur SQL Server exekverar dina queries.

Dokumentera komplexa queries

Lägg till kommentarer i SQL-kod som förklarar business logic och komplexa joins.