DropTable does not drop the Sequence SQL Server 2016

#1

Can we drop the Sequence from SQL SERVER when we drop a table ?

In my Nighly build process I recreate my database with DropTable and CreateTable method then I seed my data.

It seem that the DropTable does not drop the Sequence so the CreateTable failed with no error that I can see.
If I look in my DB with SSMS I see my table disappeared but the CreateTable do not bring back my table.

If we can’t drop the Sequence can we make the CreateTable work and check if the sequence exist ?
I use SqlServer2016Dialect.Provider.

   db.DropTable<DietResult>();
   db.CreateTable<DietResult>();

[Schema("REIBeef")] //Specifique to REIBeef
public class DietResult
{
	[Sequence("DietId"), ReturnOnInsert]
	public int DietId { get; set; }
	public int AccessFiber { get; set; }
	public int MainFiber { get; set; }
	public int FiberQuality { get; set; }
	public int FiberRegularity { get; set; }
	public bool ValidCategory { get; set; }
	public ulong RowVersion { get; set; }

	[Default(OrmLiteVariables.SystemUtc)]
	public DateTime? CreatedDateUtc { get; set; }

	[Default(OrmLiteVariables.SystemUtc)]
	public DateTime? UpdatedDateUtc { get; set; }

	[PrimaryKey, AutoId, ReturnOnInsert]
	public Guid? RowId { get; set; }

}
#2

OrmLite docs on sequences explains the behavior:

When creating tables, OrmLite will also create any missing Sequences automatically so you can continue to have reproducible tests and consistent Startups states that’s unreliant on external state. But it doesn’t drop sequences when OrmLite drops the table as they could have other external dependents.

i.e. OrmLite doesn’t drop sequences but it does check if they exist when tables are created so it uses existing sequences if they exist.

#3

Make sens for the external dependency.

In my case CreateTable do not recreate the table when the sequence exist. I will make a repro and send you the link.

#4

Here is my repro. solution

static void Main()
{
   var connectionstring = "Data Source=<Server>;Initial Catalog=<Database>;MultipleActiveResultSets=true;User Id=<User>;Password=<Password>;";
   dbFactory = new OrmLiteConnectionFactory(connectionstring, SqlServer2016Dialect.Provider);
   db = dbFactory.Open();
   ResetSchema();
    		   
   //DropTable DietResult Exist? False
   //CreateTable DietResult Exist? True
   //DropTable DietResult Exist? False
   //CreateTable DietResult Exist? False <----- Should be True
}
    
private static void ResetSchema()
{
   db.DropTable<DietResult>();
   Console.WriteLine("DropTable DietResult Exist? {0}", db.TableExists<DietResult>());
   db.CreateTable<DietResult>();
   Console.WriteLine("CreateTable DietResult Exist? {0}", db.TableExists<DietResult>());
   db.DropTable<DietResult>();
   Console.WriteLine("DropTable DietResult Exist? {0}", db.TableExists<DietResult>());
   db.CreateTable<DietResult>();
   Console.WriteLine("CreateTable DietResult Exist? {0}", db.TableExists<DietResult>());
   }
}
#5

This example is working as expected:

image.png723×308 18.4 KB

I’ve just changed the connection string and to use a Schema I already have. Maybe there’s an issue with your REIBeef Schema, does it work without a Schema?

#6

Very Interesting…

With no schema it always work.
With my schema it work sometimes it’s not constant
When it work once with my schema, I add the attribute:

 [PreCreateTable("DROP SEQUENCE [ReiBeef].[DietId];")]

Now it always work…

I will try to look in the sql profiler if I can see some message.

#7

OK I think I got it:

From the Profiler when I erase manualy My sequence I got:

SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'DietResult' AND TABLE_SCHEMA = 'REIBeef'
go
SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'DietResult' AND TABLE_SCHEMA = 'REIBeef'
go
SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'DietResult' AND TABLE_SCHEMA = 'REIBeef'
go
SELECT 1 FROM SYS.SEQUENCES WHERE object_id=object_id('DietId')
go
CREATE SEQUENCE "REIBeef"."DietId" AS BIGINT START WITH 1 INCREMENT BY 1 NO CACHE;
go
CREATE TABLE "REIBeef"."DietResult" 
(
  "DietId" INTEGER NOT NULL, 
  "AccessFiber" INTEGER NOT NULL, 
  "MainFiber" INTEGER NOT NULL, 
  "FiberQuality" INTEGER NOT NULL, 
  "FiberRegularity" INTEGER NOT NULL, 
  "ValidCategory" BIT NOT NULL, 
  RowVersion rowversion NOT NULL, 
  "CreatedDateUtc" DATETIME NULL DEFAULT (SYSUTCDATETIME()), 
  "UpdatedDateUtc" DATETIME NULL DEFAULT (SYSUTCDATETIME()), 
  "RowId" UniqueIdentifier PRIMARY KEY DEFAULT (newid()) 
); 

go
SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'DietResult' AND TABLE_SCHEMA = 'REIBeef'
go

When we check if the sequence existe:
SELECT 1 FROM SYS.SEQUENCES WHERE object_id=object_id(‘DietId’)
should be:
SELECT 1 FROM SYS.SEQUENCES WHERE object_id=object_id(‘REIBeef.DietId’)

On the second attempt

CREATE SEQUENCE "REIBeef"."DietId" AS BIGINT START WITH 1 INCREMENT BY 1 NO CACHE;

give an error that we don’t catch…

When I rerun the profiler give me this:

SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'DietResult' AND TABLE_SCHEMA = 'REIBeef'
go
SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'DietResult' AND TABLE_SCHEMA = 'REIBeef'
go
SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'DietResult' AND TABLE_SCHEMA = 'REIBeef'
go
SELECT 1 FROM SYS.SEQUENCES WHERE object_id=object_id('DietId')
go
CREATE SEQUENCE "REIBeef"."DietId" AS BIGINT START WITH 1 INCREMENT BY 1 NO CACHE;
go
SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'DietResult' AND TABLE_SCHEMA = 'REIBeef'
go
#8

Without the Schema attribute the profiler return me:

SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'DietResult'
go
SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'DietResult'
go
SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'DietResult'
go
SELECT 1 FROM SYS.SEQUENCES WHERE object_id=object_id('DietId')
go
CREATE SEQUENCE "DietId" AS BIGINT START WITH 1 INCREMENT BY 1 NO CACHE;
go
CREATE TABLE "DietResult" 
(
  "DietId" INTEGER NOT NULL, 
  "AccessFiber" INTEGER NOT NULL, 
  "MainFiber" INTEGER NOT NULL, 
  "FiberQuality" INTEGER NOT NULL, 
  "FiberRegularity" INTEGER NOT NULL, 
  "ValidCategory" BIT NOT NULL, 
  RowVersion rowversion NOT NULL, 
  "CreatedDateUtc" DATETIME NULL DEFAULT (SYSUTCDATETIME()), 
  "UpdatedDateUtc" DATETIME NULL DEFAULT (SYSUTCDATETIME()), 
  "RowId" UniqueIdentifier PRIMARY KEY DEFAULT (newid()) 
); 

go
SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'DietResult'
go

Second Attempt:

SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'DietResult'
go
DROP TABLE "DietResult"
go
SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'DietResult'
go
SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'DietResult'
go
SELECT 1 FROM SYS.SEQUENCES WHERE object_id=object_id('DietId')
go
CREATE TABLE "DietResult" 
(
  "DietId" INTEGER NOT NULL, 
  "AccessFiber" INTEGER NOT NULL, 
  "MainFiber" INTEGER NOT NULL, 
  "FiberQuality" INTEGER NOT NULL, 
  "FiberRegularity" INTEGER NOT NULL, 
  "ValidCategory" BIT NOT NULL, 
  RowVersion rowversion NOT NULL, 
  "CreatedDateUtc" DATETIME NULL DEFAULT (SYSUTCDATETIME()), 
  "UpdatedDateUtc" DATETIME NULL DEFAULT (SYSUTCDATETIME()), 
  "RowId" UniqueIdentifier PRIMARY KEY DEFAULT (newid()) 
); 

go
SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'DietResult'
go
#9

From SqlServer2012OrmLiteDialectProvider.cs

I think we have our guilty code :wink:
But I’m not able to find where sequenceName is build from…

public override bool DoesSequenceExist(IDbCommand dbCmd, string sequenceName)
    {
        var sql = "SELECT 1 FROM SYS.SEQUENCES WHERE object_id=object_id({0})"
            .SqlFmt(this, sequenceName);

        dbCmd.CommandText = sql;
        var result = dbCmd.ExecuteScalar();

        return result != null;
    }
#11

ok the [Sequnce] attribute doesn’t support creating sequences in Schema’s so you would need to stick with your [PreCreateTable] solution.

#12

I am able to see the sequence created after the command, from the profiler I see:

CREATE SEQUENCE "REIBeef"."DietId" AS BIGINT START WITH 1 INCREMENT BY 1 NO CACHE;

I think the issues is with

SELECT 1 FROM SYS.SEQUENCES WHERE object_id=object_id('DietId')

When it check if it exist.

#13

Right it doesn’t support schemas so it’s only checking for the sequence name. The sequence name isn’t built it’s just using the name in the [Sequence] attribute.