From: 011netservice@gmail.com Date: 2022-04-24 Subject: FAQ-KeyWord-SqlServer.txt ---------- 20200117 basic syntax: INSERT [dbo].[TConfig] ([FID], [FParentID], [FSeqNo], [FKey], [FValue], [FValueB], [FReadonly], [FNote], [FCreateTime], [FUpdateTime]) VALUES (1001, 1000, 10, N'M', N'男', N'Male', NULL, NULL, CAST(N'2019-11-22 10:43:00.870' AS DateTime), CAST(N'2019-12-08 17:20:05.107' AS DateTime)) INSERT [dbo].[TConfig] ([FID], [FParentID], [FSeqNo], [FKey], [FValue], [FValueB], [FReadonly], [FNote], [FCreateTime], [FUpdateTime]) VALUES (0, NULL, NULL, N'KeyList', N'代碼清單', NULL, 1, N'FKey for 程式控制.'+CHAR(13)+CHAR(10)+'FValue for 主要語言.'+CHAR(13)+CHAR(10)+'FValueB for 次要語言或匯入語言.', GETDATE(), GETDATE()) ---------- 20200115 MS SQL 自動編號(identity)歸零(reset) 語法格式 DBCC CHECKIDENT(dbo.table_name, RESEED, 0) 注意是要設定為(目前table中最大的數字), 下一次新增時會自動加1. 範例 DBCC CHECKIDENT(TConfig, RESEED, 1005) 上例執行後, 下一次新增時為1006 新建 Table 時, IDENTITY 語法: 若 truncate table 後也會復原. CREATE TABLE [dbo].[TConfig]( [FID] [int] IDENTITY(1000,1) NOT NULL, [FParentID] [int] NULL, [FSeqNo] [int] NULL, [FKey] [nvarchar](256) NOT NULL, [FValue] [nvarchar](max) NULL, [FValueB] [nvarchar](max) NULL, [FReadonly] [int] NULL, [FNote] [nvarchar](max) NULL, [FCreateTime] [datetime] NOT NULL CONSTRAINT [DF_TConfig_FCreateTime] DEFAULT (getdate()), [FUpdateTime] [datetime] NOT NULL CONSTRAINT [DF_TConfig_FUpdateTime] DEFAULT (getdate()), CONSTRAINT [PK_TConfig] PRIMARY KEY CLUSTERED ( [FID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] ---------- 20191208 MultiLine: ---------- 使用 CHAR(13)+CHAR(10) N'Male 中文為男,'+CHAR(13)+CHAR(10)+'Female 中文是女.' delete from TConfig where 1=1 SET IDENTITY_INSERT [dbo].[TConfig] ON INSERT [dbo].[TConfig] ([FID], [FParentID], [FSeqNo], [FKey], [FValue], [FValueB], [FReadonly], [FNote], [FCreateTime], [FUpdateTime]) VALUES (0, NULL, NULL, N'KeyList', N'代碼清單', NULL, 1, NULL, CAST(N'2019-11-22 10:42:31.660' AS DateTime), CAST(N'2019-11-22 10:42:31.660' AS DateTime)) INSERT [dbo].[TConfig] ([FID], [FParentID], [FSeqNo], [FKey], [FValue], [FValueB], [FReadonly], [FNote], [FCreateTime], [FUpdateTime]) VALUES (1000, 0, NULL, N'Gender', N'性別', NULL, NULL, N'Male 中文為男,'+CHAR(13)+CHAR(10)+'Female 中文是女.', CAST(N'2019-11-22 10:42:31.660' AS DateTime), CAST(N'2019-12-08 17:17:27.553' AS DateTime)) INSERT [dbo].[TConfig] ([FID], [FParentID], [FSeqNo], [FKey], [FValue], [FValueB], [FReadonly], [FNote], [FCreateTime], [FUpdateTime]) VALUES (1001, 1000, 10, N'M', N'男', N'Male', NULL, NULL, CAST(N'2019-11-22 10:43:00.870' AS DateTime), CAST(N'2019-12-08 17:20:05.107' AS DateTime)) INSERT [dbo].[TConfig] ([FID], [FParentID], [FSeqNo], [FKey], [FValue], [FValueB], [FReadonly], [FNote], [FCreateTime], [FUpdateTime]) VALUES (1002, 1000, 20, N'F', N'女', N'Female', NULL, NULL, CAST(N'2019-11-22 10:43:10.233' AS DateTime), CAST(N'2019-11-22 10:43:10.233' AS DateTime)) INSERT [dbo].[TConfig] ([FID], [FParentID], [FSeqNo], [FKey], [FValue], [FValueB], [FReadonly], [FNote], [FCreateTime], [FUpdateTime]) VALUES (1003, 0, NULL, N'Marriage', N'婚姻', NULL, NULL, N'婚姻狀態除了單身和已婚以外,'+CHAR(13)+CHAR(10)+'還可以細分為單身未婚、單身已婚.', CAST(N'2019-11-22 10:44:25.577' AS DateTime), CAST(N'2019-12-08 17:19:50.053' AS DateTime)) INSERT [dbo].[TConfig] ([FID], [FParentID], [FSeqNo], [FKey], [FValue], [FValueB], [FReadonly], [FNote], [FCreateTime], [FUpdateTime]) VALUES (1004, 1003, 10, N'Single', N'單身', NULL, NULL, NULL, CAST(N'2019-11-22 10:44:55.327' AS DateTime), CAST(N'2019-11-22 10:44:55.327' AS DateTime)) INSERT [dbo].[TConfig] ([FID], [FParentID], [FSeqNo], [FKey], [FValue], [FValueB], [FReadonly], [FNote], [FCreateTime], [FUpdateTime]) VALUES (1005, 1003, 20, N'Married', N'已婚', NULL, NULL, NULL, CAST(N'2019-11-22 10:45:22.400' AS DateTime), CAST(N'2019-11-22 10:45:22.400' AS DateTime)) SET IDENTITY_INSERT [dbo].[TConfig] OFF SET ANSI_PADDING ON ---------- 使用連接符號 \ SET IDENTITY_INSERT [dbo].[TConfig] ON INSERT [dbo].[TConfig] ([FID], [FParentID], [FSeqNo], [FKey], [FValue], [FValueB], [FReadonly], [FNote], [FCreateTime], [FUpdateTime]) VALUES (0, NULL, NULL, N'KeyList', N'代碼清單', NULL, 1, NULL, CAST(N'2019-11-22 10:42:31.660' AS DateTime), CAST(N'2019-11-22 10:42:31.660' AS DateTime)) INSERT [dbo].[TConfig] ([FID], [FParentID], [FSeqNo], [FKey], [FValue], [FValueB], [FReadonly], [FNote], [FCreateTime], [FUpdateTime]) VALUES (1000, 0, NULL, N'Gender', N'性別', NULL, NULL, N'Male 中文為男,\ Female 中文是女.', CAST(N'2019-11-22 10:42:31.660' AS DateTime), CAST(N'2019-12-08 17:17:27.553' AS DateTime)) INSERT [dbo].[TConfig] ([FID], [FParentID], [FSeqNo], [FKey], [FValue], [FValueB], [FReadonly], [FNote], [FCreateTime], [FUpdateTime]) VALUES (1001, 1000, 10, N'M', N'男', N'Male', NULL, NULL, CAST(N'2019-11-22 10:43:00.870' AS DateTime), CAST(N'2019-12-08 17:20:05.107' AS DateTime)) INSERT [dbo].[TConfig] ([FID], [FParentID], [FSeqNo], [FKey], [FValue], [FValueB], [FReadonly], [FNote], [FCreateTime], [FUpdateTime]) VALUES (1002, 1000, 20, N'F', N'女', N'Female', NULL, NULL, CAST(N'2019-11-22 10:43:10.233' AS DateTime), CAST(N'2019-11-22 10:43:10.233' AS DateTime)) INSERT [dbo].[TConfig] ([FID], [FParentID], [FSeqNo], [FKey], [FValue], [FValueB], [FReadonly], [FNote], [FCreateTime], [FUpdateTime]) VALUES (1003, 0, NULL, N'Marriage', N'婚姻', NULL, NULL, N'婚姻狀態除了單身和已婚以外,\ 還可以細分為單身未婚、單身已婚.', CAST(N'2019-11-22 10:44:25.577' AS DateTime), CAST(N'2019-12-08 17:19:50.053' AS DateTime)) INSERT [dbo].[TConfig] ([FID], [FParentID], [FSeqNo], [FKey], [FValue], [FValueB], [FReadonly], [FNote], [FCreateTime], [FUpdateTime]) VALUES (1004, 1003, 10, N'Single', N'單身', NULL, NULL, NULL, CAST(N'2019-11-22 10:44:55.327' AS DateTime), CAST(N'2019-11-22 10:44:55.327' AS DateTime)) INSERT [dbo].[TConfig] ([FID], [FParentID], [FSeqNo], [FKey], [FValue], [FValueB], [FReadonly], [FNote], [FCreateTime], [FUpdateTime]) VALUES (1005, 1003, 20, N'Married', N'已婚', NULL, NULL, NULL, CAST(N'2019-11-22 10:45:22.400' AS DateTime), CAST(N'2019-11-22 10:45:22.400' AS DateTime)) SET IDENTITY_INSERT [dbo].[TConfig] OFF SET ANSI_PADDING ON GO ---------- 20191127 Inner Join Left Join Right Join 原資料: select FID, FParentID from TConfig order by FID FID FParentID 0 NULL 1000 0 1001 1000 1002 1000 1003 0 1004 1003 1005 1003 以原資料 Find parent exists: (一定有 parent 的資料) select t1.FID, t2.FID from TConfig t1 Inner join TConfig t2 on t1.FParentID = t2.FID order by t1.FID FID FID 1000 0 1001 1000 1002 1000 1003 0 1004 1003 1005 1003 以原資料 Find Parent: (Null 代表沒有 Parent) select t1.FID, t2.FID from TConfig t1 left join TConfig t2 on t1.FParentID = t2.FID order by t1.FID FID FID 0 NULL 1000 0 1001 1000 1002 1000 1003 0 1004 1003 1005 1003 以原資料 Find Children: (Null 代表沒有 Children) select t1.FID, t2.FID from TConfig t1 right join TConfig t2 on t1.FParentID = t2.FID order by t1.FID FID FID NULL 1002 NULL 1001 NULL 1005 NULL 1004 1000 0 1001 1000 1002 1000 1003 0 1004 1003 1005 1003 ---------- 20191127 Reset identity seed after deleting records in SQL Server https://stackoverflow.com/questions/21824478/reset-identity-seed-after-deleting-records-in-sql-server The DBCC CHECKIDENT management command is used to reset identity counter. The command syntax is: DBCC CHECKIDENT (table_name [, { NORESEED | { RESEED [, new_reseed_value ]}}]) [ WITH NO_INFOMSGS ] Example: DBCC CHECKIDENT ('[TestTable]', RESEED, 0); GO It was not supported in a previous versions of Azure SQL Database, but is supported now. Please note that new_reseed_value argument is varied across SQL Server versions according to documentation: If rows are present in the table, the next row is inserted with the new_reseed_value value. In version SQL Server 2008 R2 and earlier, the next row inserted uses new_reseed_value + the current increment value. However, I find this information misleading (just plain wrong actually) because observed behaviour indicates that at least SQL Server 2012 is still uses new_reseed_value + the current increment value logic. Microsoft even contradicts with its own Example C found on same page: C. Forcing the current identity value to a new value The following example forces the current identity value in the AddressTypeID column in the AddressType table to a value of 10. Because the table has existing rows, the next row inserted will use 11 as the value, that is, the new current increment value defined for the column value plus 1. USE AdventureWorks2012; GO DBCC CHECKIDENT ('Person.AddressType', RESEED, 10); GO Still, this all leaves an option for different behaviour on newer SQL Server versions. I guess the only way to be sure, until Microsoft clear up things in its own documentation, is to do actual tests before usage. MS SQL 自動編號(identity)歸零(reset) 方法一 語法格式 DBCC CHECKIDENT(dbo.table_name, RESEED, 0) 範例 DBCC CHECKIDENT(MyTable, RESEED, 0) 方法二 語法格式 truncate table dbo.table_name 範例 truncate table MyTable ---------- 20190311 /* CreateDBAspNet.sql 20190217, Honda, Create for Asp.net identity usage. Based on https://github.com/github-honda/VS2015TemplateOriginal/blob/master/WebAuth1/doc/CreateIdentitySQLServer.sql Test run on SQL Server 2012: Microsoft SQL Server Management Studio 11.0.6251.0 Microsoft .NET Framework 4.0.30319.42000 Operating System 6.3.17763 */ use [master]; GO CREATE DATABASE [DBAspNet1] CONTAINMENT = NONE ON PRIMARY ( NAME = N'DBAspNet1', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\DBAspNet1.mdf' , SIZE = 5120KB , FILEGROWTH = 1024KB ) LOG ON ( NAME = N'DBAspNet1_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\DBAspNet1_log.ldf' , SIZE = 2048KB , FILEGROWTH = 10%) GO ALTER DATABASE [DBAspNet1] SET COMPATIBILITY_LEVEL = 110 GO ALTER DATABASE [DBAspNet1] SET ANSI_NULL_DEFAULT OFF GO ALTER DATABASE [DBAspNet1] SET ANSI_NULLS OFF GO ALTER DATABASE [DBAspNet1] SET ANSI_PADDING OFF GO ALTER DATABASE [DBAspNet1] SET ANSI_WARNINGS OFF GO ALTER DATABASE [DBAspNet1] SET ARITHABORT OFF GO ALTER DATABASE [DBAspNet1] SET AUTO_CLOSE OFF GO ALTER DATABASE [DBAspNet1] SET AUTO_CREATE_STATISTICS ON GO ALTER DATABASE [DBAspNet1] SET AUTO_SHRINK OFF GO ALTER DATABASE [DBAspNet1] SET AUTO_UPDATE_STATISTICS ON GO ALTER DATABASE [DBAspNet1] SET CURSOR_CLOSE_ON_COMMIT OFF GO ALTER DATABASE [DBAspNet1] SET CURSOR_DEFAULT GLOBAL GO ALTER DATABASE [DBAspNet1] SET CONCAT_NULL_YIELDS_NULL OFF GO ALTER DATABASE [DBAspNet1] SET NUMERIC_ROUNDABORT OFF GO ALTER DATABASE [DBAspNet1] SET QUOTED_IDENTIFIER OFF GO ALTER DATABASE [DBAspNet1] SET RECURSIVE_TRIGGERS OFF GO ALTER DATABASE [DBAspNet1] SET DISABLE_BROKER GO ALTER DATABASE [DBAspNet1] SET AUTO_UPDATE_STATISTICS_ASYNC OFF GO ALTER DATABASE [DBAspNet1] SET DATE_CORRELATION_OPTIMIZATION OFF GO ALTER DATABASE [DBAspNet1] SET PARAMETERIZATION SIMPLE GO ALTER DATABASE [DBAspNet1] SET READ_COMMITTED_SNAPSHOT OFF GO ALTER DATABASE [DBAspNet1] SET READ_WRITE GO ALTER DATABASE [DBAspNet1] SET RECOVERY FULL GO ALTER DATABASE [DBAspNet1] SET MULTI_USER GO ALTER DATABASE [DBAspNet1] SET PAGE_VERIFY CHECKSUM GO ALTER DATABASE [DBAspNet1] SET TARGET_RECOVERY_TIME = 0 SECONDS GO use [DBAspNet1]; GO /* __MigrationHistory CREATE TABLE [dbo].[__MigrationHistory] ( [MigrationId] NVARCHAR (150) NOT NULL, [ContextKey] NVARCHAR (300) NOT NULL, [Model] VARBINARY (MAX) NOT NULL, [ProductVersion] NVARCHAR (32) NOT NULL, CONSTRAINT [PK_dbo.__MigrationHistory] PRIMARY KEY CLUSTERED ([MigrationId] ASC, [ContextKey] ASC) ); GO */ /* AspNetUsers */ CREATE TABLE [dbo].[AspNetUsers] ( [Id] NVARCHAR (128) NOT NULL, [Email] NVARCHAR (256) NULL, [EmailConfirmed] BIT NOT NULL, [PasswordHash] NVARCHAR (MAX) NULL, [SecurityStamp] NVARCHAR (MAX) NULL, [PhoneNumber] NVARCHAR (MAX) NULL, [PhoneNumberConfirmed] BIT NOT NULL, [TwoFactorEnabled] BIT NOT NULL, [LockoutEndDateUtc] DATETIME NULL, [LockoutEnabled] BIT NOT NULL, [AccessFailedCount] INT NOT NULL, [UserName] NVARCHAR (256) NOT NULL, CONSTRAINT [PK_dbo.AspNetUsers] PRIMARY KEY CLUSTERED ([Id] ASC) ); GO CREATE UNIQUE NONCLUSTERED INDEX [UserNameIndex] ON [dbo].[AspNetUsers]([UserName] ASC); GO /* AspNetRoles */ CREATE TABLE [dbo].[AspNetRoles] ( [Id] NVARCHAR (128) NOT NULL, [Name] NVARCHAR (256) NOT NULL, CONSTRAINT [PK_dbo.AspNetRoles] PRIMARY KEY CLUSTERED ([Id] ASC) ); GO CREATE UNIQUE NONCLUSTERED INDEX [RoleNameIndex] ON [dbo].[AspNetRoles]([Name] ASC); GO /* AspNetUserClaims */ CREATE TABLE [dbo].[AspNetUserClaims] ( [Id] INT IDENTITY (1, 1) NOT NULL, [UserId] NVARCHAR (128) NOT NULL, [ClaimType] NVARCHAR (MAX) NULL, [ClaimValue] NVARCHAR (MAX) NULL, CONSTRAINT [PK_dbo.AspNetUserClaims] PRIMARY KEY CLUSTERED ([Id] ASC), CONSTRAINT [FK_dbo.AspNetUserClaims_dbo.AspNetUsers_UserId] FOREIGN KEY ([UserId]) REFERENCES [dbo].[AspNetUsers] ([Id]) ON DELETE CASCADE ); GO CREATE NONCLUSTERED INDEX [IX_UserId] ON [dbo].[AspNetUserClaims]([UserId] ASC); /* USE AspNetUserLogins */ CREATE TABLE [dbo].[AspNetUserLogins] ( [LoginProvider] NVARCHAR (128) NOT NULL, [ProviderKey] NVARCHAR (128) NOT NULL, [UserId] NVARCHAR (128) NOT NULL, CONSTRAINT [PK_dbo.AspNetUserLogins] PRIMARY KEY CLUSTERED ([LoginProvider] ASC, [ProviderKey] ASC, [UserId] ASC), CONSTRAINT [FK_dbo.AspNetUserLogins_dbo.AspNetUsers_UserId] FOREIGN KEY ([UserId]) REFERENCES [dbo].[AspNetUsers] ([Id]) ON DELETE CASCADE ); GO CREATE NONCLUSTERED INDEX [IX_UserId] ON [dbo].[AspNetUserLogins]([UserId] ASC); GO /* AspNetUserRoles */ CREATE TABLE [dbo].[AspNetUserRoles] ( [UserId] NVARCHAR (128) NOT NULL, [RoleId] NVARCHAR (128) NOT NULL, CONSTRAINT [PK_dbo.AspNetUserRoles] PRIMARY KEY CLUSTERED ([UserId] ASC, [RoleId] ASC), CONSTRAINT [FK_dbo.AspNetUserRoles_dbo.AspNetRoles_RoleId] FOREIGN KEY ([RoleId]) REFERENCES [dbo].[AspNetRoles] ([Id]) ON DELETE CASCADE, CONSTRAINT [FK_dbo.AspNetUserRoles_dbo.AspNetUsers_UserId] FOREIGN KEY ([UserId]) REFERENCES [dbo].[AspNetUsers] ([Id]) ON DELETE CASCADE ); GO CREATE NONCLUSTERED INDEX [IX_UserId] ON [dbo].[AspNetUserRoles]([UserId] ASC); GO CREATE NONCLUSTERED INDEX [IX_RoleId] ON [dbo].[AspNetUserRoles]([RoleId] ASC); GO /* Test data INSERT INTO [dbo].[__MigrationHistory] ([MigrationId], [ContextKey], [Model], [ProductVersion]) VALUES (N'201902150317221_InitialCreate', N'WebAuth1.Models.ApplicationDbContext', , N'6.1.3-40302') GO */ INSERT INTO [dbo].[AspNetUsers] ([Id], [Email], [EmailConfirmed], [PasswordHash], [SecurityStamp], [PhoneNumber], [PhoneNumberConfirmed], [TwoFactorEnabled], [LockoutEndDateUtc], [LockoutEnabled], [AccessFailedCount], [UserName]) VALUES (N'b709c15b-f805-44af-8237-1be89b4222a3', N'test1@some.com', 0, N'AJXCcIMNdksqvJMyLPleatRpak91jG4CW7sWYp46n7nUmAYIq053HblHTsuKWf+dgQ==', N'b190cd2c-54f9-49f1-9e05-5dcd6d3e5f0a', NULL, 0, 0, NULL, 1, 0, N'test1@some.com') GO