Database design – Igor Micev

What does an Index mean to a Query

Posted on November 11, 2016 by Igor Micev — No Comments ↓

This post is about the importance of a right index to a query. The following simple query was considered.


SELECT
    [GroupBy1].[A1] AS [C1]
    FROM ( SELECT
        MAX([Extent1].[CreatedDate]) AS [A1]
        FROM [dbo].[GeoIP2Location] AS [Extent1] WITH (NOLOCK)
    )  AS [GroupBy1]

SELECT

[GroupBy1].[A1] AS [C1]

FROM ( SELECT

MAX([Extent1].[CreatedDate]) AS [A1]

FROM [dbo].[GeoIP2Location] AS [Extent1] WITH (NOLOCK)

) AS [GroupBy1]

Even simple, this query was causing troubles on our production systems. The query is generated from .NET Entity Framework. A simple Continue reading What does an Index mean to a Query→

The CHECK constraints unlike behaviours

Posted on June 13, 2016 by Igor Micev — No Comments ↓

The CHECK constraints enforce integrity by limiting the values that are accepted by one or more columns. You can create a CHECK constraint with any logical expression that returns TRUE or FALSE based on the logical operators. CHECK constraints are Continue reading The CHECK constraints unlike behaviours→

ISJSON() restriction in SQL Server 2016 and not in SQL Server 2017

Posted on June 6, 2016 by Igor Micev — No Comments ↓

You should have a database on SQL Server 2016 instance with an In-Memory file group so that you can create tables with the MEMORY_OPTIMIZED option set to ON. Like in the example below


--table
CREATE TABLE dbo.People (
 Id int primary key nonclustered,
 InfoJson nvarchar(max) -- nvarchar(100), nvarchar(1000)
) WITH (MEMORY_OPTIMIZED=ON)

--table

CREATE TABLE dbo.People (

Id int primary key nonclustered,

InfoJson nvarchar(max) -- nvarchar(100), nvarchar(1000)

) WITH (MEMORY_OPTIMIZED=ON)

try to create the next constraint Continue reading ISJSON() restriction in SQL Server 2016 and not in SQL Server 2017→

Too many Foreign Keys

Posted on March 10, 2016 by Igor Micev — No Comments ↓

I’ve seen tables that are referenced by many other tables in the databases and that number was usually around some couple of foreign keys and up to 20, 30 and similar. Working on a bug, I met an odd table with too many Continue reading Too many Foreign Keys→

Why you should not update Primary Key values

Posted on November 11, 2015 by Igor Micev — No Comments ↓

Recently I had the chance to take a look at a problematic query in an application. I caught the query in profiler and it’s the following:


SET TRANSACTION ISOLATION LEVEL READ COMMITTED
-- Modify the updatable columns
UPDATE [dbo].[StationaryMaster] 
SET 
	[Id] = @Id, --comment this
    [HistoryId] = @HistoryId,
    [ServiceId] = @ServiceId,
    [ServiceName] = @ServiceName,
    [Status] = @Status,
    [OrderId] = @OrderId,
    [DoctorId] = @DoctorId,
    [TransferedFromDoctorId] = @TransferedFromDoctorId,
    [StationaryNumber] = @StationaryNumber,
    [StationaryYear] = @StationaryYear,
    [Protokol] = @Protokol,
    [Date] = @Date,
    [CreatedBy] = @CreatedBy,
    [ModifiedBy] = @ModifiedBy,
    [CreatedDate] = @CreatedDate,
    [ModifiedDate] = @ModifiedDate
WHERE 
      [Id] = @OriginalId

SET TRANSACTION ISOLATION LEVEL READ COMMITTED

-- Modify the updatable columns

UPDATE [dbo].[StationaryMaster]

SET

[Id] = @Id, --comment this

[HistoryId] = @HistoryId,

[ServiceId] = @ServiceId,

[ServiceName] = @ServiceName,

[Status] = @Status,

[OrderId] = @OrderId,

[DoctorId] = @DoctorId,

[TransferedFromDoctorId] = @TransferedFromDoctorId,

[StationaryNumber] = @StationaryNumber,

[StationaryYear] = @StationaryYear,

[Protokol] = @Protokol,

[Date] = @Date,

[CreatedBy] = @CreatedBy,

[ModifiedBy] = @ModifiedBy,

[CreatedDate] = @CreatedDate,

[ModifiedDate] = @ModifiedDate

WHERE

[Id] = @OriginalId

The query was throwing the following error: The query processor ran out of Continue reading Why you should not update Primary Key values→