Categories
Database Development

MS SQL Server 2019 – deadlock after row update by primary key, possibly caused by trigger

I’ve a table: SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[users]( [id] [int] IDENTITY(1,1) NOT NULL, [username] [nvarchar](65) NOT NULL, [nickname] [nvarchar](65) NOT NULL, [status] [tinyint] NOT NULL, [email_address] [nvarchar](255) NULL, [activation_date] [datetime] NOT NULL, [deactivation_date] [datetime] NULL, [language] [nvarchar](16) NULL, [last_modify_date] [datetime] NULL, [creation_date] [datetime] NOT NULL, [suspension_start_date] [datetime] NULL, [suspension_end_date] […]

Categories
Database Development

Violation of PRIMARY KEY constraint in SSMS import/export wizard

I have table MYTABLE with one PK constraint, generated by a third-party app: CREATE TABLE [dbo].[mytable]( [ID] [int] IDENTITY(1,1) NOT NULL, [FIELD_A] [nvarchar](255) NOT NULL, [FIELD_B] [tinyint] NOT NULL, CONSTRAINT [mytable_PK] PRIMARY KEY CLUSTERED ( [ID] ASC ) WITH ( PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON […]

Categories
Mastering Development

Why “ID” is NULL when I try to insert in database while the field is primary key?

I am trying to insert (create) a book in the BookInfo table in database but I get this error: An unhandled exception occurred while processing the request. SqlException: The INSERT statement conflicted with the FOREIGN KEY constraint "FK_Book_Categories_BookInfo_BookID". The conflict occurred in database "MyBookShopDB", table "dbo.BookInfo", column ‘BookID’. I have BookInfo and Category with a […]

Categories
Database Development

How to speed up SQL Server 2014 Spatial functions

I have some normalized tables with about 1,2M records that I collect in a view in a SQL Server 2014 database. Note that I have simplified the names of the tables and columns. To illustrate I have created this query on the source tables: SELECT geom, dateFrom, dateTo FROM requestsTable INNER JOIN geomTable ON requestsTable.requestId […]

Categories
Database Development

sp_executesql not accepting multiple types

I have the following table: CREATE TABLE [dbo].[Settings]( [Id] [int] IDENTITY(1,1) NOT NULL, [Key] [varchar](50) NOT NULL, [Value] [nvarchar](max) NULL, CONSTRAINT [PK_Settings] PRIMARY KEY CLUSTERED ( [Id] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY] ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] GO […]

Categories
Database Development

Table Partitioning – database file increased after partitioning

I have a table that is 100 GB in size. I forgot the exact blog which I read when partitioning tables but when I ran the partition query, my primary table file increased while being able to partition it into a new drive. The steps I took were: Create file group ALTER DATABASE MY_DB ADD […]

Categories
Mastering Development

SQL Server query to assign serial numbers for rows without changing the order of rows

I have a SQL table that consists of the Driver Truck trips which carries containers. Each time a trip is made, the records/rows are stored. If he carries 1 container in a trip, then I’ve stored "1 Unit" under Unitcount Field. If he carries 2 Containers, then 2 Rows are created with the Unitcount Field […]

Categories
Mastering Development

Decimal number in MSSQL table gets rounded although precision is set

My c# object has a decimal property: public decimal LastPrice { get; set; } While processing my object, the decimal value gets set. For example: LastPrice = 0.091354; I modified my DbContext to increase the decimal precision as explained in another stackoverflow post: protected override void OnModelCreating(ModelBuilder modelBuilder) { foreach (var property in modelBuilder.Model.GetEntityTypes() .SelectMany(t […]

Categories
Database Development

Optimizing my query

Here’s my query: DECLARE @monthStartDate date; DECLARE @monthEndDate date; SET @monthStartDate = DATEFROMPARTS(2020,6,1); SET @monthEndDate = EOMONTH(@monthStartDate); SELECT @monthStartDate, MIN([Date]), @monthEndDate, GroupId, TypeId, COUNT(UserID) as ‘CountTotal’, SUM(CASE WHEN [IsA] = 1 THEN 1 ELSE 0 END) as ‘CountA’, SUM(CASE WHEN [IsB] = 1 THEN 1 ELSE 0 END) as ‘CountB’, SUM(CASE WHEN [IsC] = 1 […]

Categories
Database Development

SQL query speedup/optimization

I am using SQL Server 2008 R2.I have tried to improve the following query in different methods. Method 1: DBCC DROPCLEANBUFFERS SET STATISTICS TIME ON SELECT count(*) FROM [prglog].dbo.[errorlog] WHERE (( [prglog].dbo.[errorlog].[Errordescription] LIKE ‘%General network error%’ ) AND [date]>=dateadd(d,-7,cast(getdate()as date))) Method 2: DBCC DROPCLEANBUFFERS SET STATISTICS TIME ON SELECT count(*) FROM [prglog].dbo.[errorlog] WHERE (( [prglog].dbo.[errorlog].[Errordescription] […]