MFF Notebook > Databázové systémy

Databázové systémy

Vše co souvisí s Microsoft SQL Server a T-SQL.

Velikost tabulek

DECLARE @tables TABLE (
    [TABLE_QUALIFIER] nvarchar(max) NULL,
    [TABLE_OWNER] nvarchar(max) NULL,
    [TABLE_NAME] nvarchar(max) NULL,
    [TABLE_TYPE] nvarchar(max) NULL,
    [REMARKS] nvarchar(max) NULL
)

DECLARE @spaceused TABLE (
    [name] nvarchar(max),
    [rows] int,
    [reserved] nvarchar(max),
    [data] nvarchar(max),
    [index_size] nvarchar(max),
    [unused] nvarchar(max)
)

INSERT INTO @tables EXEC sp_tables

DELETE FROM @tables WHERE [TABLE_TYPE] != 'TABLE' OR [TABLE_NAME] = 'sysdiagrams'

WHILE ((SELECT COUNT(*) from @tables) > 0) BEGIN
    
    DECLARE @table nvarchar(max)
    SET @table = (SELECT TOP 1 [TABLE_NAME] FROM @tables)
        
    INSERT INTO @spaceused EXEC sp_spaceused @table
        
    DELETE TOP (1) FROM @tables
END

SELECT * FROM @spaceused

Nebo také

EXEC sp_spaceused

Počet připojení k databázi

SELECT db_name(dbid) as DatabaseName, count(dbid) as NoOfConnections, 
loginame as LoginName 
FROM sys.sysprocesses 
WHERE dbid > 0 
GROUP BY dbid, loginame

Fragmentace

SELECT ps.database_id, ps.OBJECT_ID,
ps.index_id, b.name,
ps.avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS ps
INNER JOIN sys.indexes AS b ON ps.OBJECT_ID = b.OBJECT_ID
AND ps.index_id = b.index_id
WHERE ps.database_id = DB_ID()
ORDER BY ps.OBJECT_ID

Pro 10-40% reorganize. Nad 40% rebuild.

ALTER INDEX ALL ON table_name REBUILD

Unique index

USE MyDatabase
GO
CREATE TABLE dbo.MyTable (
    column nvarchar(449) NOT NULL
)
GO
CREATE UNIQUE NONCLUSTERED INDEX IX_MyTable ON dbo.MyTable (MyColumn)
GO

Unique index je na textové pole možný, avšak nejdelší délka klíče je 900 bajtů. Každý znak v typu nvarchar zabírá dva bajty a samotný typ si zabere dva bajty navrh. Takže jeho délka nesmí být větší než 449 znaků, aby se mohl na vším vytvořit index.

Záloha struktury databáze

SQL Server Management Studio: Databáze > Tasks > Generate Scripts…

JOIN: CROSS, INNER, LEFT, RIGHT, FULL

SQL Server nepodporuje NATURAL JOIN ani JOIN USING, ale mohl by podporovat databázové dělení.

[one].[c] [two].[c]
NULL NULL
A NULL
NULL B
C C
SELECT * FROM one CROSS JOIN two
SELECT * FROM one, two
SELECT * FROM one INNER JOIN two ON 1 = 1
NULL NULL
A NULL
NULL NULL
C NULL
NULL NULL
A NULL
NULL NULL
C NULL
NULL B
A B
NULL B
C B
NULL C
A C
NULL C
C C
[one].[c] [two].[c]
A B
C C
SELECT * FROM one INNER JOIN two ON one.c = two.c
SELECT * FROM one JOIN two ON one.c = two.c
SELECT * FROM one CROSS JOIN two WHERE one.c = two.c
SELECT * FROM one, two WHERE one.c = two.c
C C
SELECT * FROM one LEFT JOIN two ON one.c = two.c
SELECT * FROM one LEFT OUTER JOIN two ON one.c = two.c
A NULL
C C
SELECT * FROM one RIGHT JOIN two ON one.c = two.c
SELECT * FROM one RIGHT OUTER JOIN two ON one.c = two.c
NULL B
C C
SELECT * FROM one FULL JOIN two ON one.c = two.c
SELECT * FROM one FULL OUTER JOIN two ON one.c = two.c
A NULL
C C
NULL B

Creative Commons License