forked from vincent/notebook
83 lines
2.1 KiB
Markdown
83 lines
2.1 KiB
Markdown
|
# commande SQL formation
|
||
|
|
|
||
|
|
voir version:
|
||
|
|
|
||
|
|
`select @@version`
|
||
|
|
|
||
|
|
passer une base en mode single user
|
||
|
|
` ALTER DATABASE [toto] SET SINGLE_USER WITH ROLLBACK IMMEDIATE`
|
||
|
|
|
||
|
|
passer en mode multi user
|
||
|
|
|
||
|
|
`alter database [toto] set MULTI_USER`
|
||
|
|
|
||
|
|
|
||
|
|
voir la configuration
|
||
|
|
`exec sp_configure`
|
||
|
|
|
||
|
|
activé affichage des option avancé
|
||
|
|
```SQL
|
||
|
|
exec sp_configure 'show advanced options', 1;
|
||
|
|
REconfigure;
|
||
|
|
```
|
||
|
|
|
||
|
|
autorisé la mise en autonomie partielle des bases
|
||
|
|
```SQL
|
||
|
|
exec sp_configure 'contained database authentication',1;
|
||
|
|
reconfigure
|
||
|
|
```
|
||
|
|
|
||
|
|
```SQL
|
||
|
|
exec sp_configure 'cost threshold for parallelism',12
|
||
|
|
exec sp_configure 'max degree of parallelism',2
|
||
|
|
exec sp_configure 'max server memory (MB)', 4096
|
||
|
|
exec sp_configure'optimize for ad hoc workloads',1
|
||
|
|
exec sp_configure'backup compression default',1
|
||
|
|
exec sp_configure'backup checksum default',1
|
||
|
|
exec sp_configure
|
||
|
|
reconfigure
|
||
|
|
```
|
||
|
|
|
||
|
|
Backup
|
||
|
|
```sql
|
||
|
|
BACKUP DATABASE [toto] TO DISK = 'c:\toto.bak' with Compression
|
||
|
|
```
|
||
|
|
|
||
|
|
ajouter filegroup
|
||
|
|
```SQL
|
||
|
|
USE [master]
|
||
|
|
GO
|
||
|
|
ALTER DATABASE [DB_GRAND_HOTEL] ADD FILEGROUP [FG_DATA]
|
||
|
|
GO
|
||
|
|
ALTER DATABASE [DB_GRAND_HOTEL] ADD FILE ( NAME = N'FG_DATA_1', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL15.SQL2019FBIN2\MSSQL\DATA\FD_DATA_1.mdf' , SIZE = 102400KB , FILEGROWTH = 65536KB ) TO FILEGROUP [FG_DATA]
|
||
|
|
GO
|
||
|
|
ALTER DATABASE [DB_GRAND_HOTEL] ADD FILE ( NAME = N'FG_DATA_2', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL15.SQL2019FBIN2\MSSQL\DATA\FD_DATA_2.mdf' , SIZE = 102400KB , FILEGROWTH = 65536KB ) TO FILEGROUP [FG_DATA]
|
||
|
|
GO
|
||
|
|
```
|
||
|
|
|
||
|
|
setter filegroup par default
|
||
|
|
```SQL
|
||
|
|
USE [DB_GRAND_HOTEL]
|
||
|
|
GO
|
||
|
|
IF NOT EXISTS (SELECT name FROM sys.filegroups WHERE is_default=1 AND name = N'FG_DATA') ALTER DATABASE [DB_GRAND_HOTEL] MODIFY FILEGROUP [FG_DATA] DEFAULT
|
||
|
|
GO
|
||
|
|
```
|
||
|
|
|
||
|
|
modifier taille de fichier
|
||
|
|
```SQL
|
||
|
|
USE [DB_GRAND_HOTEL]
|
||
|
|
GO
|
||
|
|
DBCC SHRINKFILE (N'DB_GRAND_HOTEL' , 10)
|
||
|
|
GO
|
||
|
|
USE [master]
|
||
|
|
GO
|
||
|
|
ALTER DATABASE [DB_GRAND_HOTEL] MODIFY FILE ( NAME = N'DB_GRAND_HOTEL_log', SIZE = 102400KB )
|
||
|
|
GO
|
||
|
|
```
|
||
|
|
|
||
|
|
voir nombre de page utilisé et temps
|
||
|
|
|
||
|
|
```SQL
|
||
|
|
SET STATISTCS IO ON
|
||
|
|
SET STATISTCS TIME ON
|
||
|
|
```
|