SQL Server DEMO Scripts
USE [master];
GO
--Create new database
CREATE DATABASE [NewDatabase]
-- Database configuration
ALTER DATABASE [NewDatabase] SET COMPATIBILITY_LEVEL = 110;
GO
ALTER DATABASE [NewDatabase] SET AUTO_CLOSE OFF;
GO
ALTER DATABASE [NewDatabase] SET AUTO_SHRINK OFF;
GO
ALTER DATABASE [NewDatabase] SET AUTO_CREATE_STATISTICS ON;
GO
ALTER DATABASE [NewDatabase] SET AUTO_UPDATE_STATISTICS ON;
GO
ALTER DATABASE [NewDatabase] SET AUTO_UPDATE_STATISTICS_ASYNC ON;
GO
ALTER DATABASE [NewDatabase] SET QUOTED_IDENTIFIER OFF;
GO
-- Make sure page verify is set to CHECKSUM
ALTER DATABASE [NewDatabase] SET PAGE_VERIFY CHECKSUM;
GO
---RTO( recovery time objectives) in database level in sec over written is instance level
ALTER DATABASE [NewDatabase] SET TARGET_RECOVERY_TIME = 30 SECONDS;
GO
--- Change state mode
ALTER DATABASE [NewDatabase]SET OFFLINE
ALTER DATABASE [NewDatabase]SET ONLINE
SELECT [name] ,[state] ,[state_desc]FROM sys.[databases]
-- change read write mode
ALTER DATABASE [NewDatabase] SET READ_ONLY;
GO
ALTER DATABASE [NewDatabase] SET READ_WRITE;
GO
-- Make sure you consider what recovery model you will be using
ALTER DATABASE [NewDatabase] SET SINGLE_USER
GO
ALTER DATABASE [NewDatabase] SET RESTRICTED_USER
GO
ALTER DATABASE [NewDatabase] SET MULTI_USER;
GO
---SET COLLATE
--FIRST FIND WICH COLLATION
SELECT [name] ,[database_id] ,[collation_name]FROM sys.[databases];
GO
---SET TO NEW COLLATION
ALTER DATABASE [NewDatabase]COLLATE French_CS_AS;
GO
--SET TO DEFULT COLLATION
ALTER DATABASE [NewDatabase]COLLATE SQL_Latin1_General_CP1_CI_AS;
-- Cleanup
USE [master];
GO
DROP DATABASE [NewDatabase];
GO
---------------------
CREATE DATABASE
-------------------------------
--Lab Example # 1
USE [master];
-- Minimum file requirements = 1 primary file and
-- 1 transaction log file
CREATE DATABASE [DNTDemo]
ON PRIMARY
-- First file in primary filegroup is the primary file
( NAME = N'DNTDemo',-- logical_file_name
FILENAME = N'C:\Project\DNTDemo.mdf' ,
-- 'os_file_name'
SIZE = 1024MB ,
-- size [MB ]
MAXSIZE = UNLIMITED,
-- max_size [ MB| UNLIMITED
FILEGROWTH = 1024MB )
-- growth_increment [ KB | MB | GB | TB | % ]
LOG ON -- specifies log file details
( NAME = N'DNTDemo_log',
-- logical_file_name
FILENAME = N'C:\Project\DNTDemo_log.ldf' ,
-- 'os_file_name'
SIZE = 1024MB ,
-- size [ | MB | GB | TB ] ]
MAXSIZE = 2048GB ,
-- max_size [ | MB | GB | TB ] | UNLIMITED
FILEGROWTH = 1024MB);
-- Avoid growth by percentage!
EXEC sp_helpdb 'DNTDemo';
-- Cleanup
USE [master];
GO
DROP DATABASE [DNTDemo];
GO
--Lab Example # 2
USE [master];
-- Multiple data files
-- A maximum of 32,767 files and 32,767 filegroups-- (not a goal, )
CREATE DATABASE [DNTDemo]
ON PRIMARY
( NAME = N'DNTDemo',
FILENAME = N'C:\Project\DNTDemo.mdf' ,
SIZE = 1024MB ,
FILEGROWTH = 1024MB ),
( NAME = N'DNTDemo_2',
FILENAME = N'C:\Project\DNTDemo_2.ndf' ,
SIZE = 1024MB ,
FILEGROWTH = 1024MB )
LOG ON
( NAME = N'DNTDemo_log',
FILENAME = N'C:\Project\DNTDemo_log.ldf' ,
SIZE = 1024MB ,
FILEGROWTH = 1024MB);
GO
-- multiple transaction log files
---Go online and serarch how to write this
EXEC sp_helpdb 'DNTDemo';
-- Cleanup
USE [master];
GO
DROP DATABASE [DNTDemo];
GO
Create table bigtable
(id int identity not null,
Name char(8000))
select * from bigtable
declare @count int=0
while @count <1000
begin insert bigtable values('a')
set @count+=1
end
select * from bigtable
-- Individual File Sizes and space available for current database
SELECT f.name AS [File Name] , f.physical_name AS [Physical Name],
CAST((f.size/128.0) AS DECIMAL(15,2)) AS [Total Size in MB],
CAST(f.size/128.0 - CAST(FILEPROPERTY(f.name, 'SpaceUsed') AS int)/128.0 AS DECIMAL(15,2))
AS [Available Space In MB], [file_id], fg.name AS [Filegroup Name]
FROM sys.database_files AS f WITH (NOLOCK)
LEFT OUTER JOIN sys.data_spaces AS fg WITH (NOLOCK)
ON f.data_space_id = fg.data_space_id OPTION (RECOMPILE);
-- Look at some relevant database properties for this database
SELECT db.[name] AS [Database Name], db.recovery_model_desc AS [Recovery Model], db.state_desc,
db.log_reuse_wait_desc AS [Log Reuse Wait Description],
CONVERT(DECIMAL(18,2), ls.cntr_value/1024.0) AS [Log Size (MB)], CONVERT(DECIMAL(18,2), lu.cntr_value/1024.0) AS [Log Used (MB)],
CAST(CAST(lu.cntr_value AS FLOAT) / CAST(ls.cntr_value AS FLOAT)AS DECIMAL(18,2)) * 100 AS [Log Used %],
db.[compatibility_level] AS [DB Compatibility Level],
db.page_verify_option_desc AS [Page Verify Option], db.is_auto_create_stats_on, db.is_auto_update_stats_on,
db.is_auto_update_stats_async_on, db.is_parameterization_forced,
db.snapshot_isolation_state_desc, db.is_read_committed_snapshot_on,
db.is_auto_close_on, db.is_auto_shrink_on, db.is_cdc_enabled
FROM sys.databases AS db WITH (NOLOCK)
INNER JOIN sys.dm_os_performance_counters AS lu WITH (NOLOCK)
ON db.name = lu.instance_name
INNER JOIN sys.dm_os_performance_counters AS ls WITH (NOLOCK)
ON db.name = ls.instance_name
WHERE lu.counter_name LIKE N'Log File(s) Used Size (KB)%'
AND ls.counter_name LIKE N'Log File(s) Size (KB)%'
AND ls.cntr_value > 0
AND db.[name] = N'NewDatabase'
OPTION (RECOMPILE);
-----------
USE [master];
GO
CREATE DATABASE [dnt_demo]
ON PRIMARY
( NAME = N'dnt_demo',
FILENAME = N'C:\pROJECT\MDF\dnt_demo.mdf' ,
SIZE = 4096MB ,
FILEGROWTH = 1024MB ),
( NAME = N'dnt_demo_2',
FILENAME = N'C:\pROJECT\MDF\dnt_demo_2.ndf' ,
SIZE = 4096MB ,
FILEGROWTH = 1024MB )
LOG ON
( NAME = N'dnt_demo_log',
FILENAME = N'C:\pROJECT\LDF\dnt_demo_log.ldf' ,
SIZE = 1024MB ,
FILEGROWTH = 1024MB);
GO
-- Add another file
ALTER DATABASE [dnt_demo]
ADD FILE
( NAME = N'dnt_demo_3',
FILENAME = N'C:\pROJECT\MDF\dnt_demo_3.ndf' ,
SIZE = 4096MB ,
FILEGROWTH = 1024MB )
TO FILEGROUP [PRIMARY];
GO
-- Removing a file
ALTER DATABASE [dnt_demo]
REMOVE FILE [dnt_demo_3];
GO
-- What if we have data in this file?
ALTER DATABASE [dnt_demo]
ADD FILE
( NAME = N'dnt_demo_3',
FILENAME = N'C:\pROJECT\MDF\dnt_demo_3.ndf' ,
SIZE = 4096MB ,
FILEGROWTH = 1024MB )
TO FILEGROUP [PRIMARY];
GO
USE [dnt_demo];
GO
CREATE TABLE dbo.[UseSpace]
([col01] INT NOT NULL PRIMARY KEY CLUSTERED IDENTITY (1,1),
[col02] NVARCHAR (4000) NOT NULL);
GO
SET NOCOUNT ON;
GO
INSERT dbo.[UseSpace]
([col02])
VALUES (REPLICATE (N'z', 4000));
GO 1000
-- Does this work?
USE [master];
ALTER DATABASE [dnt_demo]
REMOVE FILE [dnt_demo_3];
GO
-- Empty file
USE [dnt_demo];
-- Migrates all data to other files in the same filegroup
DBCC SHRINKFILE ('dnt_demo_3', EMPTYFILE);
-- Does this work?
USE [master];
GO
ALTER DATABASE [dnt_demo]
REMOVE FILE [dnt_demo_3];
GO
-- Cleanup
USE [master];
GO
DROP DATABASE [dnt_demo];
GO