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


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]

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] )


--- 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]

Comments

Popular posts from this blog

Shared / Static Class in vb.net/C#

Xamarin Forms : PopUp Page with RG.Plugins.Popup

Text was truncated or one or more characters had no match in the target code page.". (SQL Server Import and Export Wizard)