gcer-hidenori / antsqlparser Goto Github PK

ALTER ENDPOINT [aodns-hadr] 
    AS TCP (LISTENER_IP = ALL)
GO

-- Business continuity/Always On availability groups/How-to/Configure availability group/Configure distributed availability groups
-- https://docs.microsoft.com/en-us/sql/database-engine/availability-groups/windows/configure-distributed-availability-groups?view=sql-server-ver15

USE [master]
RESTORE DATABASE [SQLTestDB] 
FROM DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\Backup\SQLTestDB.bak' WITH  FILE = 1,  NOUNLOAD,  STATS = 5
GO

-- Business continuity/Backup & restore/Quickstarts/Backup & restore
-- https://docs.microsoft.com/en-us/sql/relational-databases/backup-restore/quickstart-backup-restore-database?view=sql-server-ver15

BACKUP DATABASE AdventureWorks2016  
TO URL = 'https://<mystorageaccountname>.blob.core.windows.net/<mycontainername>/AdventureWorks2016.bak'   
      WITH CREDENTIAL = '<mycredentialname>'   
     ,COMPRESSION  
     ,STATS = 5;  
GO   

-- Business continuity/Backup & restore/How-to/Backup to Azure/Backup To URL
-- https://docs.microsoft.com/en-us/sql/relational-databases/backup-restore/sql-server-backup-to-url?view=sql-server-ver15

CREATE TABLE [dbo].[ASPStateTempSessions]
(
	[SessionId] [nvarchar](88) COLLATE Latin1_General_100_BIN2 NOT NULL,
	[Created] [datetime] NOT NULL DEFAULT (getutcdate()),
	[Expires] [datetime] NOT NULL,
	[LockDate] [datetime] NOT NULL,
	[LockDateLocal] [datetime] NOT NULL,
	[LockCookie] [int] NOT NULL,
	[Timeout] [int] NOT NULL,
	[Locked] [bit] NOT NULL,
	[SessionItemShort] [varbinary](7000) NULL,
	[SessionItemLong] [varbinary](max) NULL,
	[Flags] [int] NOT NULL DEFAULT ((0)),

INDEX [Index_Expires] NONCLUSTERED 
(
	[Expires] ASC
),
PRIMARY KEY NONCLUSTERED HASH 
(
	[SessionId]
)WITH ( BUCKET_COUNT = 33554432)
)WITH ( MEMORY_OPTIMIZED = ON , DURABILITY = SCHEMA_ONLY )
GO

-- Create a SQL Server login for low-priority operations  
USE master;  
CREATE LOGIN [domain_name\MAX_CPU] FROM WINDOWS;  
GRANT VIEW SERVER STATE TO [domain_name\MAX_CPU];  
GO  
-- Create a SQL Server user in AdventureWorks2012 for this login  
USE AdventureWorks2012;  
CREATE USER [domain_name\MAX_CPU] FOR LOGIN [domain_name\MAX_CPU];  
EXEC sp_addrolemember 'db_backupoperator', 'domain_name\MAX_CPU';  
GO  

-- Business continuity/Backup & restore/How-to/Configuration/Limit CPU usage by backup compression (T-SQL)
-- https://docs.microsoft.com/en-us/sql/relational-databases/backup-restore/use-resource-governor-to-limit-cpu-usage-by-backup-compression-transact-sql?view=sql-server-ver15

USE msdb;  
GO  
EXEC msdb.managed_backup.sp_backup_config_basic   
 @container_url = 'https://managedbackupstorage.blob.core.windows.net/backupcontainer'
GO  

-- Business continuity/Backup & restore/How-to/Backup to Azure/Managed backups/Enable
-- https://docs.microsoft.com/en-us/sql/relational-databases/backup-restore/enable-sql-server-managed-backup-to-microsoft-azure?view=sql-server-ver15

@container_url = 'aaa'

SELECT * FROM a WHERE cluster = 0

USE master;
GO
ALTER DATABASE AdventureWorks2012 
SET RECOVERY FULL, PAGE_VERIFY CHECKSUM;
GO

-- Database design/Databases/How-to guides/Manage/Change configuration settings
-- https://docs.microsoft.com/en-us/sql/relational-databases/databases/change-the-configuration-settings-for-a-database?view=sql-server-ver15

ALTER DATABASE CURRENT SET MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT=ON  
GO  

CREATE PROCEDURE [dbo].[TempGetStateItemExclusive3]
			@id         nvarchar(88),
            @itemShort  varbinary(7000) OUTPUT,
            @locked     bit OUTPUT,
            @lockAge    int OUTPUT,
            @lockCookie int OUTPUT,
            @actionFlags int OUTPUT
WITH NATIVE_COMPILATION, SCHEMABINDING, EXECUTE AS OWNER
AS BEGIN ATOMIC WITH ( TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = N'us_english')

-- User-defined Data Type Syntax    
CREATE TYPE [ schema_name. ] type_name  
{   
    [
      FROM base_type   
      [ ( precision [ , scale ] ) ]  
      [ NULL | NOT NULL ]
    ]
    | EXTERNAL NAME assembly_name [ .class_name ]   
    | AS TABLE ( { <column_definition> | <computed_column_definition> [ ,... n ] }
      [ <table_constraint> ] [ ,... n ]    
      [ <table_index> ] [ ,... n ] } )
 
} [ ; ]  

create_type
    : CREATE TYPE name = simple_name
      (FROM data_type default_value)?
      (AS TABLE LR_BRACKET column_def_table_constraints RR_BRACKET)?
    ;

create_type
    : CREATE TYPE name = simple_name
      (FROM data_type null_notnull)?
      (AS TABLE LR_BRACKET column_def_table_constraints RR_BRACKET)?
    ;

CREATE TABLE Org_T3  
(  
   EmployeeId hierarchyid PRIMARY KEY,  
   ParentId AS EmployeeId.GetAncestor(1) PERSISTED    
      REFERENCES Org_T3(EmployeeId),  
   LastChild hierarchyid,   
   EmployeeName nvarchar(50)  
)  
GO  

-- Database design/Hierarchical Data
-- https://docs.microsoft.com/en-us/sql/relational-databases/hierarchical-data-sql-server?view=sql-server-ver15

EXEC sp_rename 'dbo.MyTableNew', 'dbo.MyTable’;

274

INSERT Org_T2 (EmployeeId, EmployeeName)   
    VALUES(hierarchyid::GetRoot(), 'David') ;  
GO  
AddEmp 0x , 'Sariya'  
GO  
AddEmp 0x58 , 'Mary'  
GO  
SELECT * FROM Org_T2  

-- Database design/Hierarchical Data
-- https://docs.microsoft.com/en-us/sql/relational-databases/hierarchical-data-sql-server?view=sql-server-ver15

USE AdventureWorks ;  
GO  
  
CREATE TABLE Org_T1  
   (  
    EmployeeId hierarchyid PRIMARY KEY,  
    OrgLevel AS EmployeeId.GetLevel(),  
    EmployeeName nvarchar(50)   
   ) ;  
GO  
  
CREATE INDEX Org_BreadthFirst ON Org_T1(OrgLevel, EmployeeId);
GO  
  
CREATE PROCEDURE AddEmp(@mgrid hierarchyid, @EmpName nvarchar(50) )   
AS  
BEGIN  
    DECLARE @last_child hierarchyid;
INS_EMP:   
    SELECT @last_child = MAX(EmployeeId) FROM Org_T1   
        WHERE EmployeeId.GetAncestor(1) = @mgrid;
    INSERT INTO Org_T1 (EmployeeId, EmployeeName)  
        SELECT @mgrid.GetDescendant(@last_child, NULL), @EmpName;
-- On error, return to INS_EMP to recompute @last_child  
IF @@error <> 0 GOTO INS_EMP   
END ;  
GO  

-- Database design/Hierarchical Data
-- https://docs.microsoft.com/en-us/sql/relational-databases/hierarchical-data-sql-server?view=sql-server-ver15

271

BACKUP DATABASE [SQLTestDB] 
TO DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\Backup\SQLTestDB.bak' 
WITH NOFORMAT, NOINIT,  
NAME = N'SQLTestDB-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO

-- Business continuity/Backup & restore/Quickstarts/Backup & restore
-- https://docs.microsoft.com/en-us/sql/relational-databases/backup-restore/quickstart-backup-restore-database?view=sql-server-ver15

ID:                  ( [A-Za-z_#] | FullWidthLetter) ( [A-Za-z_#$@0-9] | FullWidthLetter )*;

CREATE PROCEDURE [dbo].[TempGetStateItemExclusive3]
			@id         nvarchar(88),
            @itemShort  varbinary(7000) OUTPUT,
            @locked     bit OUTPUT,
            @lockAge    int OUTPUT,
            @lockCookie int OUTPUT,
            @actionFlags int OUTPUT
WITH NATIVE_COMPILATION, SCHEMABINDING, EXECUTE AS OWNER
AS BEGIN ATOMIC WITH ( TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = N'us_english')

USE master
GO
ALTER DATABASE AdventureWorks2012
ADD FILEGROUP Test1FG1;
GO
ALTER DATABASE AdventureWorks2012 
ADD FILE 
(
    NAME = test1dat3,
    FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\t1dat3.ndf',
    SIZE = 5MB,
    MAXSIZE = 100MB,
    FILEGROWTH = 5MB
),
(
    NAME = test1dat4,
    FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\t1dat4.ndf',
    SIZE = 5MB,
    MAXSIZE = 100MB,
    FILEGROWTH = 5MB
)
TO FILEGROUP Test1FG1;
GO

-- Database design/Databases/How-to guides/Manage/Add data files
-- https://docs.microsoft.com/en-us/sql/relational-databases/databases/add-data-or-log-files-to-a-database?view=sql-server-ver15

CREATE EXTERNAL TABLE [inventory_ora]
    ([inv_date] DECIMAL(10,0) NOT NULL, [inv_item] DECIMAL(10,0) NOT NULL,
    [inv_warehouse] DECIMAL(10,0) NOT NULL, [inv_quantity_on_hand] DECIMAL(10,0))
WITH (DATA_SOURCE=[OracleSalesSrvr],
        --LOCATION='<oracle_service_name,nvarchar(30),xe>.<oracle_schema,nvarchar(128),HR>.<oracle_table,nvarchar(128),INVENTORY>');
        LOCATION='xe.HR.INVENTORY');

-- Big Data Clusters/How-to guides/Data virtualization/Relational data/Query Oracle data
-- https://docs.microsoft.com/en-us/sql/big-data-cluster/tutorial-query-oracle?view=sql-server-ver15

ハンドルされていない例外: System.Reflection.TargetInvocationException: 呼び出しのターゲットが例外をスローしました。 ---> LibTSQL.ParserError: no viable alternative at input 'CREATEEXTERNALTABLE' ---> Antlr4.Runtime.NoViableAltException: 種類 'Antlr4.Runtime.NoViableAltException' の例外がスローされました。
   場所 Antlr4.Runtime.Atn.ParserATNSimulator.HandleNoViableAlt(ITokenStream input, Int32 startIndex, SimulatorState previous)
   場所 Antlr4.Runtime.Atn.ParserATNSimulator.ExecATN(DFA dfa, ITokenStream input, Int32 startIndex, SimulatorState initialState)
   場所 Antlr4.Runtime.Atn.ParserATNSimulator.ExecDFA(DFA dfa, ITokenStream input, Int32 startIndex, SimulatorState state)
   場所 Antlr4.Runtime.Atn.ParserATNSimulator.AdaptivePredict(ITokenStream input, Int32 decision, ParserRuleContext outerContext, Boolean useContext)
   場所 Antlr4.Runtime.Atn.ParserATNSimulator.AdaptivePredict(ITokenStream input, Int32 decision, ParserRuleContext outerContext)
   場所 LibTSQL.TSqlParser.ddl_clause() 場所 C:\Users\Hidenori\source\repos\SQLParser\LibTSQL\obj\Debug\TSqlParser.cs:行 1744
   --- 内部例外スタック トレースの終わり ---
   場所 LibTSQL.ParserErrorListener.SyntaxError(IRecognizer recognizer, IToken offendingSymbol, Int32 line, Int32 charPositionInLine, String msg, RecognitionException e) 場所 C:\Users\Hidenori\source\repos\SQLParser\LibTSQL\ParserErrorListener.cs:行 16
   場所 Antlr4.Runtime.ProxyErrorListener`1.SyntaxError(IRecognizer recognizer, Symbol offendingSymbol, Int32 line, Int32 charPositionInLine, String msg, RecognitionException e)
   場所 Antlr4.Runtime.Parser.NotifyErrorListeners(IToken offendingToken, String msg, RecognitionException e)
   場所 Antlr4.Runtime.DefaultErrorStrategy.NotifyErrorListeners(Parser recognizer, String message, RecognitionException e)
   場所 Antlr4.Runtime.DefaultErrorStrategy.ReportNoViableAlternative(Parser recognizer, NoViableAltException e)
   場所 Antlr4.Runtime.DefaultErrorStrategy.ReportError(Parser recognizer, RecognitionException e)
   場所 LibTSQL.TSqlParser.ddl_clause() 場所 C:\Users\Hidenori\source\repos\SQLParser\LibTSQL\obj\Debug\TSqlParser.cs:行 2875
   場所 LibTSQL.TSqlParser.sql_clause() 場所 C:\Users\Hidenori\source\repos\SQLParser\LibTSQL\obj\Debug\TSqlParser.cs:行 1091
   場所 LibTSQL.TSqlParser.sql_clauses() 場所 C:\Users\Hidenori\source\repos\SQLParser\LibTSQL\obj\Debug\TSqlParser.cs:行 998
   場所 LibTSQL.TSqlParser.batch() 場所 C:\Users\Hidenori\source\repos\SQLParser\LibTSQL\obj\Debug\TSqlParser.cs:行 920
   場所 LibTSQL.TSqlParser.tsql_file() 場所 C:\Users\Hidenori\source\repos\SQLParser\LibTSQL\obj\Debug\TSqlParser.cs:行 823
   --- 内部例外スタック トレースの終わり ---
   場所 System.RuntimeMethodHandle.InvokeMethod(Object target, Object[] arguments, Signature sig, Boolean constructor)
   場所 System.Reflection.RuntimeMethodInfo.UnsafeInvokeInternal(Object obj, Object[] parameters, Object[] arguments)
   場所 System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture)
   場所 System.Reflection.MethodBase.Invoke(Object obj, Object[] parameters)
   場所 LibTSQL.LibTSQL.load_string(String str) 場所 C:\Users\Hidenori\source\repos\SQLParser\LibTSQL\LibTSQL.cs:行 40
   場所 LibTSQL.LibTSQL.load_file(String filepath, String encoding_name) 場所 C:\Users\Hidenori\source\repos\SQLParser\LibTSQL\LibTSQL.cs:行 56
   場所 SQLParser.Program.Main(String[] args) 場所 C:\Users\Hidenori\source\repos\SQLParser\SQLParser\Program.cs:行 45

select * from event

CREATE PROCEDURE [dbo].[TempGetStateItemExclusive3]
			@id         nvarchar(88),
            @itemShort  varbinary(7000) OUTPUT,
            @locked     bit OUTPUT,
            @lockAge    int OUTPUT,
            @lockCookie int OUTPUT,
            @actionFlags int OUTPUT
WITH NATIVE_COMPILATION, SCHEMABINDING, EXECUTE AS OWNER
AS BEGIN ATOMIC WITH ( TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = N'us_english')

    DECLARE @textptr AS varbinary(max)
    DECLARE @length AS int
    DECLARE @now AS datetime
    DECLARE @nowLocal AS datetime

    SET @now = GETUTCDATE()
    SET @nowLocal = GETDATE()
	
	DECLARE @LockedCheck bit
	DECLARE @Flags int

	SELECT @LockedCheck=Locked, @Flags=Flags FROM dbo.ASPStateTempSessions WHERE SessionID=@id
		
	IF @Flags&1 <> 0
	BEGIN
		SET @actionFlags=1
		UPDATE dbo.ASPStateTempSessions SET Flags=Flags& ~1 WHERE SessionID=@id
	END
	ELSE
		SET @actionFlags=0

	IF @LockedCheck=1
	BEGIN
		UPDATE dbo.ASPStateTempSessions
        SET Expires = DATEADD(n, Timeout, @now), 
            @lockAge = DATEDIFF(second, LockDate, @now),
            @lockCookie = LockCookie,
            @itemShort = NULL,
            --@textptr = NULL,
            @length = NULL,
            @locked = 1
        WHERE SessionId = @id
	END
	ELSE
	BEGIN
		UPDATE dbo.ASPStateTempSessions
        SET Expires = DATEADD(n, Timeout, @now), 
            LockDate = @now,
            LockDateLocal = @nowlocal,
            @lockAge = 0,
            @lockCookie = LockCookie = LockCookie + 1,
            @itemShort = SessionItemShort,
            @textptr = SessionItemLong,
            @length = 1,
            @locked = 0,
            Locked = 1
        WHERE SessionId = @id
        
		IF @TextPtr IS NOT NULL
			SELECT @TextPtr
		
	END
END
GO

-- Cancel automatic seeding.  Connect to global primary but specify DAG AG2
ALTER AVAILABILITY GROUP [distributedag]   
   MODIFY  
   AVAILABILITY GROUP ON  
   'ag2' WITH  
   ( SEEDING_MODE = MANUAL );  

-- Business continuity/Always On availability groups/How-to/Configure availability group/Configure distributed availability groups
-- https://docs.microsoft.com/en-us/sql/database-engine/availability-groups/windows/configure-distributed-availability-groups?view=sql-server-ver15

IF NOT EXISTS(SELECT * FROM sys.external_data_sources WHERE name = 'SqlStoragePool')
    CREATE EXTERNAL DATA SOURCE SqlStoragePool
    WITH (LOCATION = 'sqlhdfs://controller-svc/default');

-- Big Data Clusters/How-to guides/Data virtualization/HDFS data/Virtualize CSV data
-- https://docs.microsoft.com/en-us/sql/big-data-cluster/data-virtualization-csv?view=sql-server-ver15

mismatched input ''sqlhdfs://controller-svc/default'' expecting {QUOTED_URL, QUOTED_HOST_AND_PORT}

QUOTED_HOST_AND_PORT:'\''(([A-Za-z]+[.]|[A-Za-z]+)|IPV4_ADDR) ([:] DECIMAL) '\'';

SELECT medallion, hack_license, pickup_datetime FROM nyctaxi_sample TABLESAMPLE (70 percent) REPEATABLE (98052) 

SELECT max_size  
FROM a

USE AdventureWorks2012;  
GO  
SELECT file_id, name, type_desc, physical_name, size, max_size  
FROM sys.database_files ;  
GO  
  

-- Database design/Databases/How-to guides/Manage/Display Data & Log Space Information for a Database
-- https://docs.microsoft.com/en-us/sql/relational-databases/databases/display-data-and-log-space-information-for-a-database?view=sql-server-ver15

CREATE AVAILABILITY GROUP [ag1]   
FOR DATABASE db1   
REPLICA ON N'server1' WITH (ENDPOINT_URL = N'TCP://server1.contoso.com:5022',  
    FAILOVER_MODE = AUTOMATIC,  
    AVAILABILITY_MODE = SYNCHRONOUS_COMMIT,   
    BACKUP_PRIORITY = 50,   
    SECONDARY_ROLE(ALLOW_CONNECTIONS = NO),   
    SEEDING_MODE = AUTOMATIC),   
N'server2' WITH (ENDPOINT_URL = N'TCP://server2.contoso.com:5022',   
    FAILOVER_MODE = AUTOMATIC,   
    AVAILABILITY_MODE = SYNCHRONOUS_COMMIT,   
    BACKUP_PRIORITY = 50,   
    SECONDARY_ROLE(ALLOW_CONNECTIONS = NO),   
    SEEDING_MODE = AUTOMATIC);   
GO  
  

-- Business continuity/Always On availability groups/How-to/Configure availability group/Configure distributed availability groups
-- https://docs.microsoft.com/en-us/sql/database-engine/availability-groups/windows/configure-distributed-availability-groups?view=sql-server-ver15

begin
	begin
		begin
			begin
				begin
					select 1
				end
			end
		end
	end
end

ハンドルされていない例外: System.Text.Json.JsonException: A possible object cycle was detected which is not supported. This can either be due to a cycle or if the object depth is larger than the maximum allowed depth of 0.
   場所 System.Text.Json.ThrowHelper.ThrowInvalidOperationException_SerializerCycleDetected(Int32 maxDepth)
   場所 System.Text.Json.JsonSerializer.Write(Utf8JsonWriter writer, Int32 originalWriterDepth, Int32 flushThreshold, JsonSerializerOptions options, WriteStack& state)
   場所 System.Text.Json.JsonSerializer.WriteCore(Utf8JsonWriter writer, Object value, Type type, JsonSerializerOptions options)
   場所 System.Text.Json.JsonSerializer.WriteCore(PooledByteBufferWriter output, Object value, Type type, JsonSerializerOptions options)
   場所 System.Text.Json.JsonSerializer.WriteCoreString(Object value, Type type, JsonSerializerOptions options)
   場所 System.Text.Json.JsonSerializer.Serialize[TValue](TValue value, JsonSerializerOptions options)
   場所 LibTSQL.LibTSQL.to_json() 場所 C:\Users\Hidenori\source\repos\SQLParser\LibTSQL\LibTSQL.cs:行 50
   場所 SQLParser.Program.Main(String[] args) 場所 C:\Users\Hidenori\source\repos\SQLParser\SQLParser\Program.cs:行 49

IF (NOT sys.fn_hadr_backup_is_preferred_replica(@DBNAME))  
BEGIN  
      Select 'This is not the preferred replica, exiting with success';  
      RETURN 0 -- This is a normal, expected condition, so the script returns success  
END  
BACKUP DATABASE @DBNAME TO DISK=<disk>  
   WITH COPY_ONLY;  

-- Business continuity/Always On availability groups/How-to/Configure availability group/Configure backup on replicas
-- https://docs.microsoft.com/en-us/sql/database-engine/availability-groups/windows/configure-backup-on-availability-replicas-sql-server?view=sql-server-ver15

USE master;
GO
ALTER DATABASE AdventureWorks2012
REMOVE FILE test1dat4;
GO

-- Database design/Databases/How-to guides/Manage/Delete Data or Log Files from a Database
-- https://docs.microsoft.com/en-us/sql/relational-databases/databases/delete-data-or-log-files-from-a-database?view=sql-server-ver15

EXECUTE sp_execute_external_script @language = N'Python'
    , @script = N'OutputDataSet = InputDataSet'
    , @input_data_1 = N'SELECT 1 AS hello'
WITH RESULT SETS(([Hello World] INT));
GO

-- Big Data Clusters/How-to guides/Big Data/Machine Learning/Machine learning quickstarts/Python/Run Python scripts
-- https://docs.microsoft.com/en-us/sql/machine-learning/tutorials/quickstart-python-create-script?toc=/sql/toc.json&view=sql-server-ver15

EXECUTE( ' Use Sales; CREATE LOGIN sample_user  WITH PASSWORD = ''password123!#'' ;') AT  DATA_SOURCE SqlDataPool;

EXECUTE('Use Sales; CREATE USER sample_user; ALTER ROLE [db_datareader] ADD MEMBER sample_user;  ALTER ROLE [db_datawriter] ADD MEMBER sample_user;') AT DATA_SOURCE SqlDataPool;

-- Big Data Clusters/How-to guides/Data ingestion/Load data with Spark
-- https://docs.microsoft.com/en-us/sql/big-data-cluster/tutorial-data-pool-ingest-spark?view=sql-server-ver15

ALTER AVAILABILITY GROUP [ag1]    
    ADD LISTENER 'ag1-listener' ( 
        WITH IP ( ('2001:db88:f0:f00f::cf3c'),('2001:4898:e0:f213::4ce2') ) , 
        PORT = 60173);    
GO  

-- Business continuity/Always On availability groups/How-to/Configure availability group/Configure distributed availability groups
-- https://docs.microsoft.com/en-us/sql/database-engine/availability-groups/windows/configure-distributed-availability-groups?view=sql-server-ver15

CREATE FUNCTION ListAncestors (@node hierarchyid)  
RETURNS TABLE (node hierarchyid)  
AS  
EXTERNAL NAME HierarchyId_Operations.HierarchyId_Operations.ListAncestors  
GO  

-- Database design/Hierarchical Data
-- https://docs.microsoft.com/en-us/sql/relational-databases/hierarchical-data-sql-server?view=sql-server-ver15

CREATE DATABASE SCOPED CREDENTIAL OracleCredential
WITH IDENTITY = 'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa', SECRET = 'bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb';

no viable alternative at input 'WITHIDENTITY'
LibTSQL.ParserError: no viable alternative at input 'WITHIDENTITY' ---> Antlr4.Runtime.NoViableAltException: 種類 'Antlr4.Runtime.NoViableAltException' の例外がスローされました。
   場所 Antlr4.Runtime.Atn.ParserATNSimulator.HandleNoViableAlt(ITokenStream input, Int32 startIndex, SimulatorState previous)
   場所 Antlr4.Runtime.Atn.ParserATNSimulator.ExecATN(DFA dfa, ITokenStream input, Int32 startIndex, SimulatorState initialState)
   場所 Antlr4.Runtime.Atn.ParserATNSimulator.ExecDFA(DFA dfa, ITokenStream input, Int32 startIndex, SimulatorState state)
   場所 Antlr4.Runtime.Atn.ParserATNSimulator.AdaptivePredict(ITokenStream input, Int32 decision, ParserRuleContext outerContext, Boolean useContext)
   場所 Antlr4.Runtime.Atn.ParserATNSimulator.AdaptivePredict(ITokenStream input, Int32 decision, ParserRuleContext outerContext)
   場所 LibTSQL.TSqlParser.sql_clause() 場所 C:\Users\Hidenori\source\repos\SQLParser\LibTSQL\obj\Debug\TSqlParser.cs:行 1077
   --- 内部例外スタック トレースの終わり ---
   場所 LibTSQL.ParserErrorListener.SyntaxError(IRecognizer recognizer, IToken offendingSymbol, Int32 line, Int32 charPositionInLine, String msg, RecognitionException e) 場所 C:\Users\Hidenori\source\repos\SQLParser\LibTSQL\ParserErrorListener.cs:行 16
   場所 Antlr4.Runtime.ProxyErrorListener`1.SyntaxError(IRecognizer recognizer, Symbol offendingSymbol, Int32 line, Int32 charPositionInLine, String msg, RecognitionException e)
   場所 Antlr4.Runtime.Parser.NotifyErrorListeners(IToken offendingToken, String msg, RecognitionException e)
   場所 Antlr4.Runtime.DefaultErrorStrategy.NotifyErrorListeners(Parser recognizer, String message, RecognitionException e)
   場所 Antlr4.Runtime.DefaultErrorStrategy.ReportNoViableAlternative(Parser recognizer, NoViableAltException e)
   場所 Antlr4.Runtime.DefaultErrorStrategy.ReportError(Parser recognizer, RecognitionException e)
   場所 LibTSQL.TSqlParser.sql_clause() 場所 C:\Users\Hidenori\source\repos\SQLParser\LibTSQL\obj\Debug\TSqlParser.cs:行 1130
   場所 LibTSQL.TSqlParser.sql_clauses() 場所 C:\Users\Hidenori\source\repos\SQLParser\LibTSQL\obj\Debug\TSqlParser.cs: 行 995
   場所 LibTSQL.TSqlParser.batch() 場所 C:\Users\Hidenori\source\repos\SQLParser\LibTSQL\obj\Debug\TSqlParser.cs:行 917
   場所 LibTSQL.TSqlParser.tsql_file() 場所 C:\Users\Hidenori\source\repos\SQLParser\LibTSQL\obj\Debug\TSqlParser.cs:行 820
   場所 LibTSQL.LibTSQL.load_string(String str) 場所 C:\Users\Hidenori\source\repos\SQLParser\LibTSQL\LibTSQL.cs:行 33
   場所 LibTSQL.LibTSQL.load_file(String filepath, String encoding_name) 場所 C:\Users\Hidenori\source\repos\SQLParser\LibTSQL\LibTSQL.cs:行 47
   場所 SQLParser.Program.Main(String[] args) 場所 C:\Users\Hidenori\source\repos\SQLParser\SQLParser\Program.cs:行 40

   if object_id(N'proc_calculate_RTO', 'p') is not null
       drop procedure proc_calculate_RTO
   go
   
   raiserror('creating procedure proc_calculate_RTO', 0,1) with nowait
   go
   --
   -- name: proc_calculate_RTO
   --
   -- description: Calculate RTO of a secondary database.
   -- 
   -- parameters:	@secondary_database_name nvarchar(max): name of the secondary database.
   --
   -- security: this is a public interface object.
   --
   create procedure proc_calculate_RTO
   (
   @secondary_database_name nvarchar(max)
   )
   as
   begin
 	  declare @db sysname
 	  declare @is_primary_replica bit 
 	  declare @is_failover_ready bit 
 	  declare @redo_queue_size bigint 
 	  declare @redo_rate bigint
 	  declare @replica_id uniqueidentifier
 	  declare @group_database_id uniqueidentifier
 	  declare @group_id uniqueidentifier
 	  declare @RTO float 

 	  select 
 	  @is_primary_replica = dbr.is_primary_replica, 
 	  @is_failover_ready = dbcs.is_failover_ready, 
 	  @redo_queue_size = dbr.redo_queue_size, 
 	  @redo_rate = dbr.redo_rate, 
 	  @replica_id = dbr.replica_id,
 	  @group_database_id = dbr.group_database_id,
 	  @group_id = dbr.group_id 
 	  from sys.dm_hadr_database_replica_states dbr join sys.dm_hadr_database_replica_cluster_states dbcs 	on dbr.replica_id = dbcs.replica_id and 
 	  dbr.group_database_id = dbcs.group_database_id  where dbcs.database_name = @secondary_database_name

 	  if  @is_primary_replica is null or @is_failover_ready is null or @redo_queue_size is null or @replica_id is null or @group_database_id is null or @group_id is null
 	  begin
 	  	print 'RTO of Database '+ @secondary_database_name +' is not available'
 	  	return
 	  end
 	  else if @is_primary_replica = 1
 	  begin
 	  	print 'You are visiting wrong replica';
 	  	return
 	  end

 	  if @redo_queue_size = 0 
 	  	set @RTO = 0 
 	  else if @redo_rate is null or @redo_rate = 0 
 	  begin
 	  	print 'RTO of Database '+ @secondary_database_name +' is not available'
 	  	return
 	  end
 	  else 
 	  	set @RTO = CAST(@redo_queue_size AS float) / @redo_rate
   
 	  print 'RTO of Database '+ @secondary_database_name +' is ' + convert(varchar, ceiling(@RTO))
 	  print 'group_id of Database '+ @secondary_database_name +' is ' + convert(nvarchar(50), @group_id)
 	  print 'replica_id of Database '+ @secondary_database_name +' is ' + convert(nvarchar(50), @replica_id)
 	  print 'group_database_id of Database '+ @secondary_database_name +' is ' + convert(nvarchar(50), @group_database_id)
   end

-- Business continuity/Always On availability groups/Reference/Troubleshooting & monitoring guide/Monitor performance for availability groups
-- https://docs.microsoft.com/en-us/sql/database-engine/availability-groups/windows/monitor-performance-for-always-on-availability-groups?view=sql-server-ver15

277

CREATE EXTERNAL FILE FORMAT csv_file
WITH (
    FORMAT_TYPE = DELIMITEDTEXT,
    FORMAT_OPTIONS(
        FIELD_TERMINATOR = ',',
        STRING_DELIMITER = '"',
        FIRST_ROW = 2,
        USE_TYPE_DEFAULT = TRUE)
);

-- Big Data Clusters/How-to guides/Data virtualization/HDFS data/Query HDFS data
-- https://docs.microsoft.com/en-us/sql/big-data-cluster/tutorial-query-hdfs-storage-pool?view=sql-server-ver15

no viable alternative at input 'CREATEEXTERNALFILE'

ハンドルされていない例外: System.Reflection.TargetInvocationException: 呼び出しのターゲットが例外をスローしました。 ---> LibTSQL.ParserError: no viable alternative at input 'CREATEEXTERNALFILE' ---> Antlr4.Runtime.NoViableAltException: 種類 'Antlr4.Runtime.NoViableAltException' の例外がスローされました。
   場所 Antlr4.Runtime.Atn.ParserATNSimulator.HandleNoViableAlt(ITokenStream input, Int32 startIndex, SimulatorState previous)
   場所 Antlr4.Runtime.Atn.ParserATNSimulator.ExecATN(DFA dfa, ITokenStream input, Int32 startIndex, SimulatorState initialState)
   場所 Antlr4.Runtime.Atn.ParserATNSimulator.ExecDFA(DFA dfa, ITokenStream input, Int32 startIndex, SimulatorState state)
   場所 Antlr4.Runtime.Atn.ParserATNSimulator.AdaptivePredict(ITokenStream input, Int32 decision, ParserRuleContext outerContext, Boolean useContext)
   場所 Antlr4.Runtime.Atn.ParserATNSimulator.AdaptivePredict(ITokenStream input, Int32 decision, ParserRuleContext outerContext)
   場所 LibTSQL.TSqlParser.ddl_clause() 場所 C:\Users\Hidenori\source\repos\SQLParser\LibTSQL\obj\Debug\TSqlParser.cs:行 1744

GRANT CONNECT ON ENDPOINT::DIAG_EP TO [InstanceX_User];
GO

-- Business continuity/Always On availability groups/Concepts/Configuration/Domain-independent availability groups
-- https://docs.microsoft.com/en-us/sql/database-engine/availability-groups/windows/domain-independent-availability-groups?view=sql-server-ver15

RESTORE FILELISTONLY FROM DISK='/tmp/<db file name>.bak'

-- Big Data Clusters/How-to guides/Data ingestion/Restore a database
-- https://docs.microsoft.com/en-us/sql/big-data-cluster/data-ingestion-restore-database?view=sql-server-ver15

CREATE EVENT SESSION [alwayson_health] ON SERVER   
ADD EVENT availability_replica_state_change  
ADD TARGET package0.event_file(SET filename=N'alwayson_health.xel',max_file_size=(5),max_rollover_files=(4),metadatafile=N'alwayson_health.xem')  
WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF,STARTUP_STATE=ON)  
GO  

-- Business continuity/Always On availability groups/Reference/Troubleshooting & monitoring guide/Useful tools for troubleshooting/Extended events
-- https://docs.microsoft.com/en-us/sql/database-engine/availability-groups/windows/always-on-extended-events?view=sql-server-ver15

CREATE TABLE [dbo].[ASPStateTempSessions]
(
	[SessionId] [nvarchar](88) COLLATE Latin1_General_100_BIN2 NOT NULL,
	[Created] [datetime] NOT NULL DEFAULT (getutcdate()),
	[Expires] [datetime] NOT NULL,
	[LockDate] [datetime] NOT NULL,
	[LockDateLocal] [datetime] NOT NULL,
	[LockCookie] [int] NOT NULL,
	[Timeout] [int] NOT NULL,
	[Locked] [bit] NOT NULL,
	[SessionItemShort] [varbinary](7000) NULL,
	[SessionItemLong] [varbinary](max) NULL,
	[Flags] [int] NOT NULL DEFAULT ((0)),

INDEX [Index_Expires] NONCLUSTERED 
(
	[Expires] ASC
),
PRIMARY KEY NONCLUSTERED HASH 
(
	[SessionId]
)WITH ( BUCKET_COUNT = 33554432)
)WITH ( MEMORY_OPTIMIZED = ON , DURABILITY = SCHEMA_ONLY )
GO

BACKUP DATABASE [MYTestDB]  
TO DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Backup\MyTestDB.bak'  
WITH  
  COMPRESSION,  
  ENCRYPTION   
   (  
   ALGORITHM = AES_256,  
   SERVER CERTIFICATE = BackupEncryptCert  
   ),  
  STATS = 10  
GO  

-- Business continuity/Backup & restore/Concepts/Configuration/Encryption
-- https://docs.microsoft.com/en-us/sql/relational-databases/backup-restore/backup-encryption?view=sql-server-ver15

WHILE 1=1
BEGIN
	RAISERROR (@msg, 0, 1) WITH NOWAIT
END

ALTER DATABASE SCOPED CONFIGURATION FOR SECONDARY SET MAXDOP = PRIMARY   

-- Database design/Databases/How-to guides/Manage/Manage properties
-- https://docs.microsoft.com/en-us/sql/relational-databases/databases/view-or-change-the-properties-of-a-database?view=sql-server-ver15