Create / Alter / Drop Database

This page gives you for learning on version of MS SQL server, creating and altering database using SQL commands.

MS SQL SERVER VERSION

USE [master]
GO

-- SQL SERVER VERSION
PRINT @@VERSION -- Print command is used to write the output on console window, @@VERSION that shows the version of MS SQL server

CREATE DATABASE


CREATE DATABASE [TutorialDB] ON  PRIMARY (
NAME = N'TutorialDB', -- Database Name
                                FILENAME = N'C:\MSSQL\DATA\TutorialDB.mdf' , -- Primary database file which contains the schema and data.
                                SIZE = 3072KB ,     -- Is the initial size of the file, size is not supplied for the primary file, the Database Engine uses the size of the primary file in the model database.
                                MAXSIZE = UNLIMITED -- Is the maximum file size, UNLIMITED specifies that the file grows until the disk is full.
                                FILEGROWTH = 1024KB -- Is the amount of space added to the file every time new space is required.
                 )
 LOG ON
                ( NAME = N'TutorialDB_log',
                  FILENAME = N'C:\MSSQL\DATA\TutorialDB_log.ldf' , -- Database log file which contains transaction logs.
                  SIZE = 1024KB ,    -- Is the initial size of the file, size is not supplied, the Database Engine uses makes the file 1 MB. 
                  MAXSIZE = 2048GB , -- Is the maximum file size.
                  FILEGROWTH = 10% -- Is the amount of space added to the file every time new space is required.
)

GO

DATABASE SERVER VERSION


-- using database property
SELECT DATABASEPROPERTY('TestDB','version')

-- using DBCC PAGE
DBCC TRACEON (3604)
DBCC PAGE('TestDB',1,9,3)
DBCC TRACEOFF (3604)

-- you can see at the TestDB database, the database version is currently set at <version number> and it was created with <version number>

-- using DBCC DBINFO
DBCC TRACEON (3604)
DBCC DBINFO
DBCC TRACEOFF (3604)

               
SQL Server Version
Internal Database Version
Database Compatibility Level
SQL Server 2016
852
130
SQL Server 2014
782
120
SQL Server 2012
706
110
SQL Server 2012 CTP1 (SQL Server 2011 Denali)
684
110
SQL Server 2008 R2
660 / 661
100
SQL Server 2008
655
100
SQL Server 2005 SP2+ with VarDecimal enabled
612
90
SQL Server 2005
611
90
SQL Server 2000
539
80
SQL Server 7.0
515
70
SQL Server 6.5
408
65
SQL Server 6.0

60


ENABLE FULLTEXTSERVICEPROPERTY



IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled')) -- returns full-text service installed on the related Microsoft SQL Server instance, - 1 - installed, 0 - not installed 
BEGIN
                EXEC [TutorialDB].[dbo].[sp_fulltext_database] @action = 'enable'
                -- If the component Enable the Full-Text Search. It is used for CONTAINS or FREETEXT predicate on table or indexed view. 
                -- SELECT * FROM Employee WHERE CONTAINS(EmployeeName,'kri')
                -- SELECT * FROM Employee WHERE FREETEXT(EmployeeName,'krishna kumar')                                                                                                                                                                                                                                                                                                                    
                -- why one should use INFLECTIONAL in CONTAINS predicate as against FREETEXT?? So, the answer to this is: Full-text queries using FREETEXT are less precise than those full-text queries using CONTAINS. The SQL Server full-text search engine identifies important words and phrases. No special meaning is given to any of the reserved keywords or wildcard characters that typically have meaning when specified in the <contains_search_condition> parameter of the CONTAINS predicate
END
GO

https://blogs.msdn.microsoft.com/sqlserverfaq/2009/09/28/stemming-functionality-in-full-text-search-using-contains/

ALTER DATABASE

-- COMPATIBILITY LEVEL

ALTER DATABASE [TutorialDB] SET COMPATIBILITY_LEVEL = 100  -- The compatibility level of a database specifies how certain language elements of the database function as it relates to an earlier version "backward compatibility" of SQL Server.
GO


These are the following compatibility levels:

SQL Server Version
Current Level
Oldest Available    Level
 SQL Server 6.0 
60

 SQL Server 6.5 
65

 SQL Server 7.0 
70

 SQL Server 2000
80
70
 SQL Server 2005
90
70
 SQL Server 2008
100
80
 SQL Server 2012
110
90
 SQL Server 2014
120
100
 SQL Server 2016
130
110



ALTER DATABASE [TutorialDB] SET ANSI_NULL_DEFAULT OFF
--https://msdn.microsoft.com/en-us/library/ms188340.aspx
GO


/*
SET ANSI_NULL_DEFAULT OFF;  It is database level option/property; NULL INSERT should fail when it is OFF.
CREATE TABLE [dbo].[Degree](
                [DegreeID] [int] NOT NULL IDENTITY(1,1),
                [DegreeName] [varchar](50) NOT NULL,
                [IsActive] [bit] NOT NULL CONSTRAINT DF_Degree_IsActive DEFAULT (1)
 CONSTRAINT [PK_Degree_DegreeID] PRIMARY KEY CLUSTERED
(
                [DegreeID] ASC
)
INSERT [dbo].[Degree] ([DegreeID], [DegreeName], [IsActive]) VALUES (1, N'Master', NULL) // NULL INSERT should fail


SET ANSI_NULL_DEFAULT OFF; It is database level property; NULL INSERT should success.  

SET ANSI_NULL_DFLT_ON ON
CREATE TABLE [dbo].[Degree](
                [DegreeID] [int] NOT NULL IDENTITY(1,1),
                [DegreeName] [varchar](50) NOT NULL,
                [IsActive] [bit] NOT NULL CONSTRAINT DF_Degree_IsActive DEFAULT (1)
 CONSTRAINT [PK_Degree_DegreeID] PRIMARY KEY CLUSTERED
(
                [DegreeID] ASC
) ON [PRIMARY]
) ON [PRIMARY]
INSERT [dbo].[Degree] ([DegreeID], [DegreeName], [IsActive]) VALUES (1, N'Master', NULL)
GO 
*/


ALTER DATABASE [TutorialDB] SET ANSI_NULL_DEFAULT OFF
--https://msdn.microsoft.com/en-us/library/ms188340.aspx
GO
ALTER DATABASE [TutorialDB] SET ANSI_NULLS OFF
--https://msdn.microsoft.com/en-us/library/ms188048.aspx
GO

ALTER DATABASE [TutorialDB] SET ANSI_PADDING OFF
--https://msdn.microsoft.com/en-us/library/ms187403.aspx
GO

ALTER DATABASE [TutorialDB] SET ANSI_WARNINGS OFF
--https://msdn.microsoft.com/en-us/library/ms190368.aspx
GO

ALTER DATABASE [TutorialDB] SET ARITHABORT OFF
--https://msdn.microsoft.com/en-us/library/ms190306.aspx
GO

ALTER DATABASE [TutorialDB] SET AUTO_CLOSE OFF
--https://msdn.microsoft.com/en-us/library/bb402929.aspx
GO

ALTER DATABASE [TutorialDB] SET AUTO_CREATE_STATISTICS ON
--https://msdn.microsoft.com/en-us/library/ms188038.aspx
GO

ALTER DATABASE [TutorialDB] SET AUTO_SHRINK OFF
--https://msdn.microsoft.com/en-us/library/ms189035.aspx
GO

ALTER DATABASE [TutorialDB] SET AUTO_UPDATE_STATISTICS ON
--https://msdn.microsoft.com/en-us/library/ms188038.aspx
GO

ALTER DATABASE [TutorialDB] SET CURSOR_CLOSE_ON_COMMIT OFF
--https://msdn.microsoft.com/en-us/library/ms184746.aspx
GO

ALTER DATABASE [TutorialDB] SET CURSOR_DEFAULT  GLOBAL --/ LOCAL
--https://social.msdn.microsoft.com/Forums/sqlserver/en-US/1ab0ef86-aa2a-4d80-ac9c-7f5a2739bd46/side-effects-switching-cursor-default-from-global-to-local?forum=transactsql
GO

ALTER DATABASE [TutorialDB] SET CONCAT_NULL_YIELDS_NULL OFF
--https://msdn.microsoft.com/en-us/library/ms176056.aspx
GO

ALTER DATABASE [TutorialDB] SET NUMERIC_ROUNDABORT OFF
--https://msdn.microsoft.com/en-us/library/ms188791.aspx
GO

ALTER DATABASE [TutorialDB] SET QUOTED_IDENTIFIER OFF
--https://msdn.microsoft.com/en-us/library/ms174393.aspx
GO

ALTER DATABASE [TutorialDB] SET RECURSIVE_TRIGGERS OFF
--https://msdn.microsoft.com/en-us/library/ms190946.aspx
GO

ALTER DATABASE [TutorialDB] SET DISABLE_BROKER
--http://rusanu.com/2006/01/30/how-long-should-i-expect-alter-databse-set-enable_broker-to-run/
GO

ALTER DATABASE [TutorialDB] SET AUTO_UPDATE_STATISTICS_ASYNC OFF
--https://social.msdn.microsoft.com/Forums/sqlserver/en-US/e808f58c-e968-4390-b5c9-e23e9469a103/autoupdatestatisticsasync?forum=sqldatabaseengine
GO

ALTER DATABASE [TutorialDB] SET DATE_CORRELATION_OPTIMIZATION OFF
--https://technet.microsoft.com/en-us/library/ms177416(v=sql.105).aspx
GO

ALTER DATABASE [TutorialDB] SET TRUSTWORTHY OFF
--https://msdn.microsoft.com/en-us/library/ms187861.aspx
--https://support.microsoft.com/en-in/kb/2183687
GO

ALTER DATABASE [TutorialDB] SET ALLOW_SNAPSHOT_ISOLATION OFF
--https://msdn.microsoft.com/en-us/library/tcbchxcb(v=vs.110).aspx
GO

ALTER DATABASE [TutorialDB] SET PARAMETERIZATION SIMPLE
--https://technet.microsoft.com/en-us/library/ms186219(v=sql.105).aspx
--https://blogs.msdn.microsoft.com/psssql/2013/12/04/how-simple-parameterization-works/
GO

ALTER DATABASE [TutorialDB] SET READ_COMMITTED_SNAPSHOT OFF
--https://msdn.microsoft.com/en-us/library/tcbchxcb(v=vs.110).aspx
GO

ALTER DATABASE [TutorialDB] SET HONOR_BROKER_PRIORITY OFF
--https://technet.microsoft.com/en-us/library/bb934456(v=sql.105).aspx
GO

ALTER DATABASE [TutorialDB] SET  READ_WRITE
--https://msdn.microsoft.com/en-us/library/bb522682.aspx
GO

ALTER DATABASE [TutorialDB] SET RECOVERY SIMPLE
--https://msdn.microsoft.com/en-IN/library/ms189272.aspx
GO

ALTER DATABASE [TutorialDB] SET  MULTI_USER
--https://msdn.microsoft.com/en-IN/library/ms345598.aspx
GO

ALTER DATABASE [TutorialDB] SET PAGE_VERIFY CHECKSUM 
--https://msdn.microsoft.com/en-us/library/bb402873.aspx
GO

ALTER DATABASE [TutorialDB] SET DB_CHAINING OFF
--https://msdn.microsoft.com/en-us/library/bb669059(v=vs.110).aspx
GO

Refer the following links for Alerting database
--https://msdn.microsoft.com/en-us/library/bb522682.aspx
--https://technet.microsoft.com/en-us/library/bb522682(v=sql.110).aspx


DROP DATABASE


DROP DATABASE [TutorialDB] 

No comments:

Post a Comment