Last active
June 20, 2022 10:44
-
-
Save weeyin83/82d5fac0d421270c43888cef6f58edd2 to your computer and use it in GitHub Desktop.
Setup the SQL database for my Speaking Reports - https://techielass.com/speaking-reports
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
/****** Set database settings ******/ | |
ALTER DATABASE [SpeakingLogs] SET ANSI_NULL_DEFAULT OFF | |
GO | |
ALTER DATABASE [SpeakingLogs] SET ANSI_NULLS OFF | |
GO | |
ALTER DATABASE [SpeakingLogs] SET ANSI_PADDING OFF | |
GO | |
ALTER DATABASE [SpeakingLogs] SET ANSI_WARNINGS OFF | |
GO | |
ALTER DATABASE [SpeakingLogs] SET ARITHABORT OFF | |
GO | |
ALTER DATABASE [SpeakingLogs] SET AUTO_SHRINK OFF | |
GO | |
ALTER DATABASE [SpeakingLogs] SET AUTO_UPDATE_STATISTICS ON | |
GO | |
ALTER DATABASE [SpeakingLogs] SET CURSOR_CLOSE_ON_COMMIT OFF | |
GO | |
ALTER DATABASE [SpeakingLogs] SET CONCAT_NULL_YIELDS_NULL OFF | |
GO | |
ALTER DATABASE [SpeakingLogs] SET NUMERIC_ROUNDABORT OFF | |
GO | |
ALTER DATABASE [SpeakingLogs] SET QUOTED_IDENTIFIER OFF | |
GO | |
ALTER DATABASE [SpeakingLogs] SET RECURSIVE_TRIGGERS OFF | |
GO | |
ALTER DATABASE [SpeakingLogs] SET ALLOW_SNAPSHOT_ISOLATION ON | |
GO | |
ALTER DATABASE [SpeakingLogs] SET PARAMETERIZATION SIMPLE | |
GO | |
ALTER DATABASE [SpeakingLogs] SET READ_COMMITTED_SNAPSHOT ON | |
GO | |
ALTER DATABASE [SpeakingLogs] SET MULTI_USER | |
GO | |
ALTER DATABASE [SpeakingLogs] SET ENCRYPTION ON | |
GO | |
ALTER DATABASE [SpeakingLogs] SET QUERY_STORE = ON | |
GO | |
ALTER DATABASE [SpeakingLogs] SET QUERY_STORE (OPERATION_MODE = READ_WRITE, CLEANUP_POLICY = (STALE_QUERY_THRESHOLD_DAYS = 30), DATA_FLUSH_INTERVAL_SECONDS = 900, INTERVAL_LENGTH_MINUTES = 60, MAX_STORAGE_SIZE_MB = 100, QUERY_CAPTURE_MODE = AUTO, SIZE_BASED_CLEANUP_MODE = AUTO) | |
GO | |
USE [SpeakingLogs] | |
GO | |
ALTER DATABASE SCOPED CONFIGURATION SET BATCH_MODE_ADAPTIVE_JOINS = ON; | |
GO | |
ALTER DATABASE SCOPED CONFIGURATION SET BATCH_MODE_MEMORY_GRANT_FEEDBACK = ON; | |
GO | |
ALTER DATABASE SCOPED CONFIGURATION SET ELEVATE_ONLINE = OFF; | |
GO | |
ALTER DATABASE SCOPED CONFIGURATION SET ELEVATE_RESUMABLE = OFF; | |
GO | |
ALTER DATABASE SCOPED CONFIGURATION SET GLOBAL_TEMPORARY_TABLE_AUTO_DROP = ON; | |
GO | |
ALTER DATABASE SCOPED CONFIGURATION SET IDENTITY_CACHE = ON; | |
GO | |
ALTER DATABASE SCOPED CONFIGURATION SET INTERLEAVED_EXECUTION_TVF = ON; | |
GO | |
ALTER DATABASE SCOPED CONFIGURATION SET ISOLATE_SECURITY_POLICY_CARDINALITY = OFF; | |
GO | |
ALTER DATABASE SCOPED CONFIGURATION SET LEGACY_CARDINALITY_ESTIMATION = OFF; | |
GO | |
ALTER DATABASE SCOPED CONFIGURATION FOR SECONDARY SET LEGACY_CARDINALITY_ESTIMATION = PRIMARY; | |
GO | |
ALTER DATABASE SCOPED CONFIGURATION SET MAXDOP = 0; | |
GO | |
ALTER DATABASE SCOPED CONFIGURATION FOR SECONDARY SET MAXDOP = PRIMARY; | |
GO | |
ALTER DATABASE SCOPED CONFIGURATION SET OPTIMIZE_FOR_AD_HOC_WORKLOADS = OFF; | |
GO | |
ALTER DATABASE SCOPED CONFIGURATION SET PARAMETER_SNIFFING = ON; | |
GO | |
ALTER DATABASE SCOPED CONFIGURATION FOR SECONDARY SET PARAMETER_SNIFFING = PRIMARY; | |
GO | |
ALTER DATABASE SCOPED CONFIGURATION SET QUERY_OPTIMIZER_HOTFIXES = OFF; | |
GO | |
ALTER DATABASE SCOPED CONFIGURATION FOR SECONDARY SET QUERY_OPTIMIZER_HOTFIXES = PRIMARY; | |
GO | |
ALTER DATABASE SCOPED CONFIGURATION SET XTP_PROCEDURE_EXECUTION_STATISTICS = OFF; | |
GO | |
ALTER DATABASE SCOPED CONFIGURATION SET XTP_QUERY_EXECUTION_STATISTICS = OFF; | |
GO | |
/****** Create Speaking Log Table ******/ | |
SET ANSI_NULLS ON | |
GO | |
SET QUOTED_IDENTIFIER ON | |
GO | |
CREATE TABLE [dbo].[SpeakingLogs]( | |
[LogId] [int] IDENTITY(1,1) NOT NULL, | |
[TalkDate] [date] NOT NULL, | |
[EventName] [varchar](255) NOT NULL, | |
[City] [varchar](255) NULL, | |
[Country] [varchar](255) NULL, | |
[Lat] [float](20) NULL, | |
[Long] [float](20) NULL, | |
[Attendance] [int] NULL, | |
[Tags] [nvarchar](1024) NULL, | |
[TalkType] [varchar](100) NULL, | |
CONSTRAINT [PK_SpeakingLogs] PRIMARY KEY CLUSTERED | |
( | |
[LogId] ASC | |
)WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY] | |
) ON [PRIMARY] | |
GO | |
/****** Create Event Logo Table ******/ | |
SET ANSI_NULLS ON | |
GO | |
SET QUOTED_IDENTIFIER ON | |
GO | |
CREATE TABLE [dbo].[EventLogos]( | |
[EventName] [varchar](255) NOT NULL, | |
[ImageURL] [nvarchar](max) NULL | |
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] | |
GO |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment