SQL SERVER - Sequence Type in SQL With table
Sequence Type in SQL With table
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Table_1](
[ID] [int] IDENTITY(1,1) NOT NULL,
[ContinousNumber] [bigint] NULL,
[name] [varchar](50) NULL,
CONSTRAINT [PK_Table_1] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Table_1](
[ID] [int] IDENTITY(1,1) NOT NULL,
[ContinousNumber] [bigint] NULL,
[name] [varchar](50) NULL,
CONSTRAINT [PK_Table_1] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE SEQUENCE [dbo].[Sequence-20191206-124448]
AS [bigint]
START WITH 1
INCREMENT BY 1
MINVALUE -9223372036854775808
MAXVALUE 9223372036854775807
NO CACHE
GO
Delete from [Table_1]
--- Check working of Sequence
INSERT INTO [dbo].[Table_1] ([ContinousNumber],[name]) VALUES ( NEXT VALUE FOR [Sequence-20191206-124448],'AABC')
SELECT TOP (1000) [ID]
,[ContinousNumber]
,[name]
FROM [ACFAFEC02122019].[dbo].[Table_1]
--- Reset the Sequence
ALTER SEQUENCE [Sequence-20191206-124448]
RESTART WITH 1
INCREMENT BY 1
MINVALUE -9223372036854775808
MAXVALUE 9223372036854775807
NO CYCLE
NO CACHE
;
-- You Assign using Alter Table or Create Table Default Value as Sequence
ALTER TABLE [dbo].[Table_1] ADD CONSTRAINT [DF_Table_1_ContinousNumber] DEFAULT (NEXT VALUE FOR [dbo].[Sequence-20191206-124448]) FOR [ContinousNumber]
AS [bigint]
START WITH 1
INCREMENT BY 1
MINVALUE -9223372036854775808
MAXVALUE 9223372036854775807
NO CACHE
GO
Delete from [Table_1]
--- Check working of Sequence
INSERT INTO [dbo].[Table_1] ([ContinousNumber],[name]) VALUES ( NEXT VALUE FOR [Sequence-20191206-124448],'AABC')
SELECT TOP (1000) [ID]
,[ContinousNumber]
,[name]
FROM [ACFAFEC02122019].[dbo].[Table_1]
--- Reset the Sequence
ALTER SEQUENCE [Sequence-20191206-124448]
RESTART WITH 1
INCREMENT BY 1
MINVALUE -9223372036854775808
MAXVALUE 9223372036854775807
NO CYCLE
NO CACHE
;
-- You Assign using Alter Table or Create Table Default Value as Sequence
ALTER TABLE [dbo].[Table_1] ADD CONSTRAINT [DF_Table_1_ContinousNumber] DEFAULT (NEXT VALUE FOR [dbo].[Sequence-20191206-124448]) FOR [ContinousNumber]
GO
-- If Same need to be Done using Designer
----in Column Properties - > Select - > Default Value or Binding (Property) and Write below
Code
--- (NEXT VALUE FOR [dbo].[Sequence-20191206-124448] )
--- (NEXT VALUE FOR [dbo].[Sequence-20191206-124448] )
--- test default Value working with Sequence. IF You dont Include Field in Insert it will Insert Default Value
INSERT INTO [dbo].[Table_1] ([name]) VALUES ('64654646464')
GO
Select * from [Table_1]
INSERT INTO [dbo].[Table_1] ([name]) VALUES ('64654646464')
GO
Select * from [Table_1]
Comments
Post a Comment