T-SQL – Page 2 – Igor Micev

T-SQL for UDFs

Posted on January 15, 2016 by Igor Micev — No Comments ↓

SQL Server has plenty of built-in functions, but sometimes we just have to combine some of them to create functions for our purposes. Such one, for example, is the function that returns the number of the days in a month. Before Continue reading T-SQL for UDFs→

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→

Script for database snapshot

Posted on May 9, 2014 by Igor Micev — No Comments ↓

The idea for this script came at a time when I was having a project that needed to use snapshots of databases frequently. For that reason I needed to create a T-SQL code that would make snapshots for me instantly. I Continue reading Script for database snapshot→