ON DELETE CASCADE ve ON UPDATE CASCADE Nedir ve Nasıl Kullanılır

Bu ifadeleri Foreing Key constaint’i ile beraber kullanabiliriz. Öncelikle Foreign Key’in ne olduğunu detaylı bir şekilde anlamak gerekir. “Primary Key ve Foreign Key” isimli makaleden faydalanabilirsiniz.

“Primary Key ve Foreign Key” isimli makalede anlattığım gibi Foreing key tanımladıysanız, normal şartlar altında foreing key’in olduğu tablodaki kaydı silmeden, primary tablodaki kaydı silemezsiniz. Silmek istediğiniz aşağıdaki gibi hata alırsınız.

Msg 547, Level 16, State 0, Line 4

The DELETE statement conflicted with the REFERENCE constraint “FK_sehirozellikleri_Sehir”. The conflict occurred in database “test”, table “dbo.sehirozellikleri”, column ‘SehirID’.

The statement has been terminated.

Ama foreing key’i oluşturuken aşağıdaki gibi sonuna ON DELETE CASCADE koymuş olsaydınız, primary tablodaki kaydı sildiğinizde foreign key’in tanımlı olduğu alt tablodaki kayıtları’da otomatik olarak silerdi. Yukardaki hatayı almazdınız. Tabiki bu her zaman istenen bir davranış değildir. Bu yüzden default olarak bu şekilde bir davranış sergilenmez.

ON DELETE CASCADE ile bir foreign key’i aşağıdaki script’ten faydalanalarak oluşturabilirsiniz.

ALTER TABLE [dbo].[sehirozellikleri]  WITH CHECK ADD  CONSTRAINT [FK_sehirozellikleri_Sehir] FOREIGN KEY([SehirID])
REFERENCES [dbo].[sehir] ([ID]) ON DELETE CASCADE
GO
ALTER TABLE [dbo].[sehirozellikleri] CHECK CONSTRAINT [FK_sehirozellikleri_Sehir]
GO

ON UPDATE CASCADE‘da primary tablodaki foreing key’in reference olduğu kolon’da bir update olduğu zaman bu update’in foreing key’in olduğu tabloya da yansımasını sağlar. Ya da primary tablodaki kolonun tipi değiştirilmek istenirse bu değişiklik foreign key’in olduğu tabloda da yapılır.

Eğer Foreign Key’i ON UPDATE CASCADE eklentisiyle oluşturmadıysanız primary tabloda, foreing key tarafından reference olunan kolonda bir update yapmak istediğinizde aşağıdakin gibi hata alırsınız.

Msg 547, Level 16, State 0, Line 4

The UPDATE statement conflicted with the REFERENCE constraint “FK_sehirozellikleri_Sehir”. The conflict occurred in database “test”, table “dbo.sehirozellikleri”, column ‘SehirID’.

The statement has been terminated.

Foreing Key’i aşağıdaki şekilde ON UPDATE CASCADE eklentisiyle oluşturabilirsiniz.

ALTER TABLE [dbo].[sehirozellikleri]  WITH CHECK ADD  CONSTRAINT [FK_sehirozellikleri_Sehir] FOREIGN KEY([SehirID])
REFERENCES [dbo].[sehir] ([ID]) ON UPDATE CASCADE
GO
ALTER TABLE [dbo].[sehirozellikleri] CHECK CONSTRAINT [FK_sehirozellikleri_Sehir]
GO

ALTER DATABASE SCOPED CONFIGURATION ile Veritabanı Seviyesinde Konfigürasyon

SQL Server 2016 ile beraber, MAXDOP gibi instance seviyesinde ayarlanan bazı kritik konfigürasyonları veritabanı seviyesinde konfigüre edebilmemize olanak veren ALTER DATABASE SCOPED CONFIGURATION ifadesi hayatımıza girdi. Özellikle konsolide edilmiş sistemlerde veritabanı bazında farklı konfigürasyon yapmanız gerekebiliyor. Bu anlamda bence bu çok güzel bir gelişme.

Veritabanı seviyesinde aşağıdaki ayarları konfigüre edebiliyoruz. Veritabanı üzerine sağ tıklayarak properties dedikten sonra Options sekmesinden erişebilirsiniz.

Legacy Cardinality Estimation: SQL Server 2014’te, sorgular için, sorguların sonucunun kaç satır geleceğini tahmin ederek daha iyi query plan üretilmesini sağlayan Cardinality Estimator yeniden dizayn edildi. Sorguların yeni cardinality estimator’ı kullanması için Compatibility Level’in 120 ve üzeri olması gerekir.

Sorguların %98’i için bu cardinality estimator ile daha iyi query plan üretiliyor. Fakat %2’lik dilim için Legacy CE ters etki yapabiliyor. Böyle durumlarda compatibility level’i 110’a çekebilirsiniz. Ama yeni compatibility level’in getirdiği bir sürü yenilikten mahrum kalmış olursunuz.

Ya da compatibility level’i downgrade yapmak yerine aşağıdaki script yardımıyla sorgunun çalıştırıldığı veritabanı için eski cardinality estimator’ı kullan diyebilirsiniz.  Default değeri OFF’tur ve compatibility level 120 ve üzerisi yeni cardinality estimator’ı kullanır.

ALTER DATABASE SCOPED CONFIGURATION FOR SECONDARY SET LEGACY_CARDINALITY_ESTIMATION=ON ;

Sorgu bazlı eski cardinality estimator’ı kullanmasını isteyebilirsiniz. Bunu için SQL Server 2016 SP1 ile gelen query hint’i aşağıdaki şekilde kullanabilirsiniz. Sorgunun sonuna eklemelisiniz.

OPTION (USE HINT ('FORCE_LEGACY_CARDINALITY_ESTIMATION'));

Eğer Always ON kullanıyorsanız aşağıdaki sorgu yardımıyla secondary veritabanınız için primary veritabanında nasıl hareket ediyorsa o şekilde hareket et diyebilirsiniz.

ALTER DATABASE SCOPED CONFIGURATION FOR SECONDARY SET LEGACY_CARDINALITY_ESTIMATION=PRIMARY ;

Ya da Secondary veritabanı için primary veritabanından farklı bir ayar da set edebilirsiniz.

ALTER DATABASE SCOPED CONFIGURATION FOR SECONDARY SET LEGACY_CARDINALITY_ESTIMATION=OFF;

MAXDOP: Sorguların paralellik seviyesini, öncesinde instance ya da sorgu seviyesinde ayarlayabiliyorduk. Artık spesifik veritabanları için spesifik MAXDOP değerleri ayarlayabiliyoruz. MAXDOP için “Numa Nodes, MAX/MIN Server Memory, Lock Pages In Memory, MAXDOP” isimli makaleyi okuyabilirsiniz.

Veritabanı seviyesinde ayarladığımız konfigürasyon instance seviyesinde ayarladığımız konfigürasyonu override ediyor. Eğer session seviyesinde bir maxdop set edilirse o da veritabanı seviyesindekini override edecektir.

Aşağıdaki sorgu yardımıyla veritabanı seviyesinde maxdop set edilebilir.

ALTER DATABASE SCOPED CONFIGURATION SET MAXDOP =1 ;  

Eğer Always On kullanıyorsanız seconday veritabanınız için aşağıdaki şekilde farklı bir maxdop ayarı set edebilirsiniz.

ALTER DATABASE SCOPED CONFIGURATION FOR SECONDARY SET MAXDOP=4 ;

Ya da aşağıdaki gibi primary veritabanında hangi maxdop ayarı kullanılıyorsa o ayar kullanılsın diyebiliyorsunuz.

ALTER DATABASE SCOPED CONFIGURATION FOR SECONDARY SET MAXDOP=PRIMARY;

Parameter Sniffing: Veritabanı seviyesindeki bu ayarın ne olduğunu anlamak için öncelikle parameter sniffing’in ne olduğunu idrak etmemiz gerekir. “Parameter Sniffing” isimli makalede detaylı bir içerik bulabilirsiniz.

Veritabanı seviyesinde parameter sniffing sorununu çözmek için aşağıdaki script’i kullanabilirsiniz. Bu script ile veritabanına gelen bütün sorgular sonuna “OPTIMIZE FOR UNKNOWN” eklenmiş gibi hareket ediyor.

ALTER DATABASE SCOPED CONFIGURATION SET PARAMETER_SNIFFING =OFF;

Aşağıdaki script yardımıyla da always on kullanıyorsanız secondary veritabanında parameter sniffing olmasını engelleyebilirsiniz.

ALTER DATABASE SCOPED CONFIGURATION FOR SECONDARY SET PARAMETER_SNIFFING=OFF;

Ya da aşağıdaki script yardımıyla parameter sniffing konusunda secondary veritabanı, primary veritabanı nasıl davranıyorsa öyle davransın diyebilirsiniz.

ALTER DATABASE SCOPED CONFIGURATION FOR SECONDARY SET PARAMETER_SNIFFING=PRIMARY;

Query Optimizer Fixes: Bu konfigürasyonu aktif ederek veritabanının compatibility level’ine bakmadan Query Optimizer ile ilgili son hotfix’lerin avantajlarından faydalan demiş oluyoruz.

Aşağıdaki şekilde aktif edebiliriz.

ALTER DATABASE SCOPED CONFIGURATION SET QUERY_OPTIMIZER_HOTFIXES=ON;

Aşağıdaki script ile de, always on kullanıyorsanız secondary veritabanı, primary veritabanı nasıl davranıyorsa öyle davransın diyebilirsiniz.

ALTER DATABASE SCOPED CONFIGURATION FOR SECONDARY SET QUERY_OPTIMIZER_HOTFIXES =PRIMARY;

Clear Procedure Cache: Veritabanındaki procedure cache’i temizlemeye yarıyor. Aşağıdaki script ile bu işlemi gerçekleştirebilirsiniz.

ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE;

Identitiy Cache: SQL Server 2017 ile beraber gelen bir veritabanı konfigürasyonu. Eğer bir identity kolonunuz varsa ve bu kolonun olduğu tabloya bir insert yapılırken commit etmeden failover gerçekleşirse yada servis beklenmedik bir şekilde kapanırsa otomatik olarak artan identitiy değerlerinizde boşluklar oluşur. Örneğin 5 kayıt insert ettiniz. ID değerleri 1,2,3,4,5 diye gitti. Sonraki kayıtların 6,7,8 şeklinde devam etmesi gerekirken servisin beklenmedik şekilde kapanmasının ya da failover olmasından sonra 1003,1004 şeklinde devam eder. Identity cache’i aşağıdaki script yardımıyla disable ederek bu sorunu çözebiliyoruz.

ALTER DATABASE SCOPED CONFIGURATION SET IDENTITY_CACHE=OFF ;

File Table Nedir ve File Stream’den Farkları Neler

File Table, File Stream’in teknolojisini kullanır. File sistem verilerini tablo olarak tutar. Yani diskteki verilere veritabanı içersinden erişebilirsiniz.

File Stream’den farklı olarak dizin hiyerarşisini veritabanında tutar.

File table’da file stream’deki gibi bir şema yapısı tanımlamazsınız. Şema yapısı kendisi direk oluşur ve değiştirilemez.

File Table’da da veriler windows üzerinden bir share gibi görüntülenebilir. Bunun için Configuration Manager üzerinden Enable FILESTREAM for file I/O access seçeneğini aktif hale getirmeniz gerekir. “File Stream Nedir” isimli makalede configuration manager üzeridnen bu ayarı nasıl yapacağınızı bulabilirsiniz. Bu özellik veritabanı seviyesinde aktif edilebilir.

File Table’da dosya özelliklerine(create date, modified date) veritabanı üzerinden erişebilirsiniz.

File Stream gibi dosya dizinlerinin ve dosyaların SQL içersinden tsql ile sorgulanmasına olanak verir.

File Stream’i ve File Table’ı bir arada kullanabilirsiniz.

File Table memory-mapped dosyaları desteklemez. Örneğin notepad ve paint memory mapped dosyaları kullanır. Bu uygulamaları file table ile birlikte aynı sunucuda kullanamazsınız. Fakat farklı bir sunucudan file table’ın içindeki dosyaları bu uygulamalaru kullanarak uzaktan açabilirsiniz.

Bir örnekle File Table’ı daha net anlayabileceğimizi düşünüyorum. Öncelikle FileTableDB isminde bir veritabanı oluşturalım. Veritabanı oluşturmak için “Veritabanı Oluşturmak Deyip Geçmeyin!” isimli makalemi okuyabilirsiniz.

Daha sonra File Stream’i aktif hale getirelim. “File Stream Nedir” isimli makaledeki adımların aynısını uygulamamız gerekiyor. Tablo oluşturma kısmına kadar geldikten sonra adımlarımız değişiyor.

Oluşturduğumuz veritabanı üzerinde sağ tıklayıp aşağıdaki gibi New File Table… dediğimizde bize File Table oluşturma script’ini veriyor.

Bu script’i kullanmak yerine aşağıdaki gibi daha hazır hale getirilmiş script’i kullanarak file table’ımızı oluşturuyoruz.

CREATE TABLE FileTableOrnek   AS FileTable
WITH
(
      FileTable_Directory =   'FileTableOrnek',
      FileTable_Collate_Filename = database_default
);
GO

Eğer veritabanını oluştururken File Table için NON_TRANSACTED_ACCESS yönetmini ve DIRECTORY_NAME’i belirtmediyseniz aşağıdaki gibi hata alırsınız.

Msg 33414, Level 16, State 1, Line 2

FileTable objects require the FILESTREAM database option DIRECTORY_NAME to be non-NULL. To create a FileTable in the database ”, set the DIRECTORY_NAME option to a non-NULL value using ALTER DATABASE. Or, to set the DIRECTORY_NAME option to NULL, in the database ” disable or drop the existing FileTables.

Bu hatayı çözmek için aşağıdaki script’i çalıştırmalısınız.

--NON_TRANSACTED_ACCESS için READ_ONLY veya OFF seçenekleri de mevcut
ALTER DATABASE FileTableDB SET FILESTREAM (NON_TRANSACTED_ACCESS   = FULL, DIRECTORY_NAME = N'FileTableOrnek')

Script’i çalıştırdığınızda hata alıyorsanız aşağıdaki hale getirip veritabanını önce single user’a en son da multi user’a çekebilirsiniz.

ALTER DATABASE FileTableDB SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
--NON_TRANSACTED_ACCESS için READ_ONLY veya OFF seçenekleri de mevcut
ALTER DATABASE FileTableDB SET FILESTREAM (NON_TRANSACTED_ACCESS   = FULL, DIRECTORY_NAME = N'FileTableOrnek')
GO
ALTER DATABASE FileTableDB SET MULTI_USER WITH ROLLBACK IMMEDIATE

Yukarıdaki file table oluşturma script’imizi tekrar çalıştırdığınızda bu sefer hata almadan tablonuzu oluşturabilirsiniz.

Daha sonra oluşturduğumuz File Table’a aşağıdaki gibi select çekebilirsiniz.

İlk etapta sonuç gelmeyecektir.

Aşağıdaki gibi file table’ın üzerine sağ tıklayarak Explore File Table Directory dediğinizde File Table için oluşturulan directory’e gidersiniz.

Bu directory’de bir şeyler oluşturup tabloya tekrar select çektiğinizde bu sefer her klasör ve dosya için tabloda bir satır oluşturduğunu göreceksiniz.

Partition Yapılmış Bir Tabloda Unique Index Oluşturmak

“Partition Switch Yapılamıyor” isimli makalemizde switch partition yapabilmek için partition yapılmış tablodaki tüm index’lerin partition şema’ya göre align edilmesi gerektiğinden bahsetmiştik. Bahsi geçen makalede align işlemi sırasında ortaya aşağıdaki gibi bir script çıkmıştı.

USE [AdventureWorks2014_new]
GO
CREATE UNIQUE NONCLUSTERED INDEX [AK_SalesOrderDetail_rowguid] ON [Sales].[SalesOrderDetail]
(
[rowguid] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF,
DROP_EXISTING = ON, ONLINE = on, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PartSchema]([CarrierTrackingNumber])
GO

Bu script’i çalıştırdığımda aşağıdaki gibi hata aldım.

Bu hatada;

Eğer partition tabloda unique index oluşturmak istiyorsak index’in yanında partition yapılan kolonun’da olması gerektiği yazıyor.

Msg 1908, Level 16, State 1, Line 5

Column ‘CarrierTrackingNumber’ is partitioning column of the index ‘AK_SalesOrderDetail_rowguid’. Partition columns for a unique index must be a subset of the index key.

Script’i aşağıdaki gibi düzenleyerek yeniden çalıştırıyoruz. Burada CarrierTrackingNumber kolonu bizim partition yaptığımız kolon ismi.

USE [AdventureWorks2014_new]
GO
CREATE UNIQUE NONCLUSTERED INDEX [AK_SalesOrderDetail_rowguid] ON [Sales].[SalesOrderDetail]
(
[rowguid] ASC,CarrierTrackingNumber
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF,
DROP_EXISTING = ON, ONLINE = on, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PartSchema]([CarrierTrackingNumber])
GO

Partition oluşturmak ile ilgili detayları “Partition Oluşturmak” isimli makalede bulabilirsiniz.

Partition yapılmış tabloları ve detaylarını görmek için aşağıdaki makalelerden de faydalanabilirsiniz.

“Veritabanında Partition Yapılmış Tabloları Bulmak“,

“Partition Yapılmış Tablodaki Partition Detaylarını Görmek“

Partition Switch Yapılamıyor

Eğer partition yapılmış tablonuzdaki tüm index’ler parittion’a göre align edilmemişse aşağıdaki gibi hata alırsınız.

Msg 7733, Level 16, State 4, Line 44

‘ALTER TABLE SWITCH’ statement failed. The table ‘AdventureWorks2014_new.Sales.SalesOrderDetail’ is partitioned while index ‘AK_SalesOrderDetail_rowguid’ is not partitioned.

Partition Switch ile ilgli detayları “Sliding Window-Switch Partition-Split Range-Merge Range” isimli makalede bulabilirsiniz.

Partition’a dahil edilmemiş index’leri partition’a dahil etmek için(align işlemi) aşağıdaki gibi partition yapılmış tabloda hatada belirtilen index’e sağ tıklayarak properties diyoruz.

Storage kısmından Partition scheme’yı tıklıyoruz ve alt satırda aşağıdaki gibi karşımıza çıkan seçenekleri seçerek Script’e tıklayarak yaptığımız değişikliklerin script’ini alıp cancel diyoruz.

Karşımıza aşağıdaki gibi bir script çıkarıyor.

USE [AdventureWorks2014_new]
GO
CREATE UNIQUE NONCLUSTERED INDEX [AK_SalesOrderDetail_rowguid] ON [Sales].[SalesOrderDetail]
(
[rowguid] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF,
DROP_EXISTING = ON, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PartFunc]([CarrierTrackingNumber])
GO

Script’te gördüğünüz gibi index’i yeniden ilgili partition şema üzerinde oluşturuyor. Sadece ONLINE=OFF kısmında ON yaparak script’in sisteminizde kesinti olmadan çalışmasını sağlayabilirsiniz. Script’in index’i yeniden oluşturma işlemi bittiğinde script’i tekrar çalıştırdığınız Switch işlemi başarılı bir şekilde tamamlanacaktır. Tabi tablodaki diğer index’lerin de aynı şekilde partition şema’ya göre align edilmiş olması gerekiyor.

Primary Key’i aynı şekilde yapamazsınız. Bunun için Primary Key’in DROP CREATE Script’ini alıp aşağıdaki gibi  create ederken key’lerin sonuna partition yapılmış kolon’u ekleyip daha sonra da script’in en sonuna ON partition schema ismi parantez içinde partition kolonu şeklinde eklemeniz gerekiyor.

ALTER TABLE [Sales].[SalesOrderDetail]
ADD  CONSTRAINT [PK_SalesOrderDetail_SalesOrderID_SalesOrderDetailID] PRIMARY KEY NONCLUSTERED
(
       [SalesOrderID] ASC,
       [SalesOrderDetailID] ASC,
       CarrierTrackingNumber
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = ON,
ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
ON [PartSchema]([CarrierTrackingNumber])

Partition oluşturmak ile ilgili detayları “Partition Oluşturmak” isimli makalede bulabilirsiniz.

Partition yapılmış tabloları ve detaylarını görmek için aşağıdaki makalelerden de faydalanabilirsiniz.

“Veritabanında Partition Yapılmış Tabloları Bulmak“,

“Partition Yapılmış Tablodaki Partition Detaylarını Görmek“