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