sp_msforeachdb Stored Procedure Kullanarak Tüm Databaselerin CheckDB Yapılması

sp_msforeachdb stored prosedürü master veritabanı altında bulunan oldukça yararlı bir stored prosedürdür. Bu stored prosedür ile tüm DBlerin içinde dönebilir ve istediğiniz işlemi tüm dbler üzerinde yapabiliriz.

Aşağıdaki scriptte tüm databaselerin üzerindeki tüm nesneler  üzerinde dolaşarak  mantıksal ve fiziksel veri bütünlüğü  kontrolü yapabiliriz.

exec sp_MSforeachDB 'DBCC CHECKDB (?) WITH ALL_ERRORMSGS, EXTENDED_LOGICAL_CHECKS, DATA_PURITY'

OFFSET ve FETCH NEXT Özelliğinin Kullanımı

Bugünkü yazımda sizlere SQL Server 2012 ile yeni gelen özelliklerden birisi olan  OFFSET  ve FETCH NEXT özelliğinden bahsetmek istiyorum. Bu özellikler verileri sayfalamak (paging) için kullanılır.

OFFSET-FETCH kullanımındaki kısıtlamalar

• OFFSET-FETCH yalnızca ORDER BY yan tümcesi ile kullanılabilir.
• FETCH ile OFFSET maddesi zorunludur. ORDER BY ... FETCH OFFSET olmadan kullanılamaz.
• TOP, aynı sorgu ifadesinde OFFSET ve FETCH ile birleştirilemez.

ÖRNEK1  Sıralama edilen sonuç kümesinden ilk 10 satırı atlanır ve kalan satırları döndür.

SELECT FirstName + ' ' + LastName FROM  [Person].[Person] ORDER BY FirstName  OFFSET 10 ROWS;

ÖRNEK2  Sıralama edilen sonuç kümesinden ilk 10 satırı atlanır ve kalan 5 satırı döndür.

SELECT FirstName + ' ' + LastName FROM  [Person].[Person] ORDER BY FirstName  OFFSET 10 ROWS FETCH NEXT 5 ROWS ONLY;

OFFSET  ve FETCH NEXT Özelliğinin Kullanımı 

ISNULL, NULLIF ve COALESCE Fonksiyonları

SQL Server'ın ISNULL () fonksiyonu, NULL değerlerine nasıl davranılmasını istediğinizi belirtmek için kullanılır. Aşağıdaki örnekte MaxQty NULL ise 0 olarak değer döndürmesi sağlanmıştır.

USE AdventureWorks2014; 

GO 

SELECT Description, DiscountPct, MinQty, ISNULL(MaxQty, 0.00) AS 'Max Quantity' 

FROM Sales.SpecialOffer; 

GO 

NULLIF fonksiyonunda ise iki değer birbirine eşit ise  NULL değer döndürecektir.

USE AdventureWorks2014; 

GO 

SELECT ProductID, MakeFlag, FinishedGoodsFlag,  

   NULLIF(MakeFlag,FinishedGoodsFlag)AS 'Null if Equal' 

FROM Production.Product 

WHERE ProductID < 10; 

GO 

COALESCE  ise birden fazla parametreyle çalışır ve eğer kolon null ise null olmayan ilk parametreyi getirir.

USE AdventureWorks2014; 

GO 

SELECT Name, Class, Color, ProductNumber, 

COALESCE(Class, Color, ProductNumber) AS FirstNotNull 

FROM Production.Product; 

TRY_CONVERT, TRY_PARSE ve PARSE Fonksiyonları

Bugünkü yazımda sizlere SQL Server 2012 ile gelen TRY_CONVERT, TRY_PARSE ve PARSE fonksiyonlardan bahsetmek istiyorum

TRY_CONVERT fonksiyonu bir veri tipinden diğer veri tipine dönüşüm için kullanılır. CAST ve CONVERT fonksiyonlarından farkı ise belirtilen veri tipine dönüştürme gerçekleşmez ise fonksiyon hata vermez. Null değer döndürür.

SELECT

    CASE WHEN TRY_CONVERT(float, 'test') IS NULL

    THEN 'Cast failed'

    ELSE 'Cast succeeded'

END AS Result;

GO

TRY_PARSE fonksiyonu TRY_CONVERT gibi çalışır. TRY_PARSE fonksiyonu string fonksiyonundan diğer fonksiyonlara çevrim yapar.

SELECT

    CASE WHEN TRY_PARSE('Aragorn' AS decimal USING 'sr-Latn-CS') IS NULL

        THEN 'True'

        ELSE 'False'

END

AS Result

PARSE fonksiyonu, CAST ve CONVERT fonksiyonlarından farklı olarak sadece string dataları girdi olarak kabul eder. Bir string değeri  tam sayı, date ve time  veri tiplerine dönüştürmek için kullanılır.  Bu dönüşümde kültür kodu parametre olarak verilebilir.

SELECT PARSE('Monday, 06 august 2012' AS Datetime2  USING 'tr-TR') AS [PARSE Function Result]

GO

CAST ve CONVERT Fonksiyonları

Cast ve Convert Fonksiyonları SQL Serverda Tür dönüşümü için kullanılan fonksiyonlardır.  Her ikisininde yaptığı iş aynıdır. Sadece Convertte stilini değiştirebiliriz.

CAST için Syntax aşağıdaki gibidir.

CAST ( expression AS data_type [ ( length ) ] )

CONVERT için Syntax ise aşağıdaki gibidir.

CONVERT ( data_type [ ( length ) ] , expression [ , style ] )

-- CAST Kullanımı

USE AdventureWorks2014;

GO

SELECT SUBSTRING(Name, 1, 30) AS ProductName, ListPrice

FROM Production.Product

WHERE CAST(ListPrice AS int) LIKE '3%';

GO

-- CONVERT Kullanımı

USE AdventureWorks2014;

GO

SELECT SUBSTRING(Name, 1, 30) AS ProductName, ListPrice

FROM Production.Product

WHERE CONVERT(int, ListPrice) LIKE '3%';

GO

Sql Server Test Datası Nasıl Oluştururum?

Bir coğumuz SQL Server üzerinde sorgu testleri yapmak ve büyük boyutlu databaselerde sorgularımızın analizini yapmak için test datası oluşturma ihtiyacımız olmuştur. Bu işlemleri yapmak için 3.party yazılımlar kullanabileceğimiz gibi kendimizde T-SQL komutları kullanarak bu işlemi yapabiliriz. Bu makalemde  sizlere T-SQL kullanarak test datası oluşturma işlemimizi anlatmak istiyorum.

Bu işlemi oluşturma esnasında ilk prosesim bir table oluşturmak olacak. Bu tableım 1 adet nvrchar primary key kolon ve 4 adet ise  date kolonum içerecek. T-SQL döngüleri ve random sayı üreticileri ile bu tabloyu doldurmak istiyorum.

CREATE TABLE dbo.TestTableSize ( MyKeyField VARCHAR(10) NOT NULL, MyDate1 DATETIME NOT NULL, MyDate2 DATETIME NOT NULL, MyDate3 DATETIME NOT NULL, MyDate4 DATETIME NOT NULL, MyDate5 DATETIME NOT NULL )

Tableımı oluşturduktan  sonra Aşağıdaki script kullanarak değişkenleri oluşturuyoruz ve döngümüzün ne kadar çalışacağını belirliyoruz. Döngümüzü çalıştırırak tablomuzu dolduruyoruz. Sizde tablomuz üzerinde ve T-SQL scriptimizde değişiklik yaparak  sorguyu özelleştirebilir ve kendi test datamızı doldurabiliriz.

DECLARE @RowCount INT DECLARE @RowString VARCHAR(10) DECLARE @Random INT DECLARE @Upper INT DECLARE @Lower INT DECLARE @InsertDate DATETIME SET @Lower = -730 SET @Upper = -1 SET @RowCount = 0 WHILE @RowCount < 3000000 BEGIN SET @RowString = CAST(@RowCount AS VARCHAR(10)) SELECT @Random = ROUND(((@Upper - @Lower -1) * RAND() + @Lower), 0) SET @InsertDate = DATEADD(dd, @Random, GETDATE()) INSERT INTO TestTableSize (MyKeyField ,MyDate1 ,MyDate2 ,MyDate3 ,MyDate4 ,MyDate5) VALUES (REPLICATE('0', 10 - DATALENGTH(@RowString)) + @RowString , @InsertDate ,DATEADD(dd, 1, @InsertDate) ,DATEADD(dd, 2, @InsertDate) ,DATEADD(dd, 3, @InsertDate) ,DATEADD(dd, 4, @InsertDate)) SET @RowCount = @RowCount + 1 END

SQL Server Ayarları İçin T-SQL Komutları

SET NO COUNT : set nocount on olarak ayarlandığında, sayım döndürülmez. set nocount off olduğunda, sayısı döner.   Yapılması gereken sorgudan önce bu komutu çalıştırmaktır.

USE [AdventureWorks2016CTP3]

GO

SET NOCOUNT OFF;

GO

-- Display the count message.

SELECT TOP(5)LastName

FROM Person.Person

WHERE LastName LIKE 'A%';

GO

-- SET NOCOUNT to ON to no longer display the count message.

SET NOCOUNT ON;

GO

SELECT TOP(5) LastName

FROM Person.Person

WHERE LastName LIKE 'A%';

GO

-- Reset SET NOCOUNT to OFF

SET NOCOUNT OFF;

GO 

SET ANSI_NULLS : SQL Server Null değerlerde where ifadesi kullanırken  nasıl bir ifadenin yazılacağı ANSI_NULLS özelliğine bağlıdır.  ANSI_NULLS özelliği ON olarak düzenlendiğinde  NULL ifadesi ile yapılan karşılaştırmalar false sonucunu döndürür yani arama listesine dahil edilmez.  Sorgu sonucunu dahil etmek için IS NULL  ve IS NOT NULL kullanılır. 

SQL Server varsayılan olarak ON modundadır.

ANSI_NULLS özelliğini OFF olarak düzenlendiğinde null kayıtlar diğer kayıtlarla karşılaştırılabilir duruma getirilir.

-- Create table t1 and insert values.

CREATE TABLE t1 (a INT NULL)

INSERT INTO t1 values (NULL)

INSERT INTO t1 values (0)

INSERT INTO t1 values (1)

GO

-- Print message and perform SELECT statements.

PRINT 'Testing default setting'

DECLARE @varname int

SELECT @varname = NULL

SELECT *

FROM t1

WHERE a = @varname

SELECT *

FROM t1

WHERE a <> @varname

SELECT *

FROM t1

WHERE a IS NULL

GO

-- SET ANSI_NULLS to ON and test.

PRINT 'Testing ANSI_NULLS ON'

SET ANSI_NULLS ON

GO

DECLARE @varname int

SELECT @varname = NULL

SELECT *

FROM t1

WHERE a = @varname

SELECT *

FROM t1

WHERE a <> @varname

SELECT *

FROM t1

WHERE a IS NULL

GO

-- SET ANSI_NULLS to OFF and test.

PRINT 'Testing SET ANSI_NULLS OFF'

SET ANSI_NULLS OFF

GO

DECLARE @varname int

SELECT @varname = NULL

SELECT *

FROM t1

WHERE a = @varname

SELECT *

FROM t1

WHERE a <> @varname

SELECT *

FROM t1

WHERE a IS NULL

GO

-- Drop table t1.

DROP TABLE t1

SET ANSI_PADDING :ANSI_PADDING açık olduğunda Varchar değerleri ile boşluklar doldurulur ve Varbinary değerler null ile doldurulur .Gelecek sürüm Microsoft SQL ServerANSI_PADDING on her zaman olacaktır ve açıkça seçeneği off için ayarlanmış tüm uygulamaları bir hata üretecektir.

PRINT 'Testing with ANSI_PADDING ON'
SET ANSI_PADDING ON;
GO

CREATE TABLE t1 (
   charcol CHAR(16) NULL,
   varcharcol VARCHAR(16) NULL,
   varbinarycol VARBINARY(8)
);
GO
INSERT INTO t1 VALUES ('No blanks', 'No blanks', 0x00ee);
INSERT INTO t1 VALUES ('Trailing blank ', 'Trailing blank ', 0x00ee00);

SELECT 'CHAR' = '>' + charcol + '<', 'VARCHAR'='>' + varcharcol + '<',
   varbinarycol
FROM t1;
GO

PRINT 'Testing with ANSI_PADDING OFF';
SET ANSI_PADDING OFF;
GO

CREATE TABLE t2 (
   charcol CHAR(16) NULL,
   varcharcol VARCHAR(16) NULL,
   varbinarycol VARBINARY(8)
);
GO
INSERT INTO t2 VALUES ('No blanks', 'No blanks', 0x00ee);
INSERT INTO t2 VALUES ('Trailing blank ', 'Trailing blank ', 0x00ee00);

SELECT 'CHAR' = '>' + charcol + '<', 'VARCHAR'='>' + varcharcol + '<',
   varbinarycol
FROM t2;
GO

DROP TABLE t1
DROP TABLE t2

SET QUOTED_IDENTIFIER :SQL Server’da QUOTED_IDENTIFIER özelliği ile SQL Server için ayrılmış özel kelimeleri kullanarak nesne oluşturmamıza imkan sağlar. “Table,Group,Alter” vb. SQL Server için rezerve edilmiş kelimeleri kullanmamıza imkan sağlayan bir özelliktir.

SQL Server QUOTED_IDENTIFIER özelliği varsayılan olarak açık (ON) durumdadır.

SET QUOTED_IDENTIFIER OFF
GO
-- An attempt to create a table with a reserved keyword as a name
-- should fail.
CREATE TABLE "select" ("identity" INT IDENTITY NOT NULL, "order" INT NOT NULL);
GO

SET QUOTED_IDENTIFIER ON;
GO

-- Will succeed.
CREATE TABLE "select" ("identity" INT IDENTITY NOT NULL, "order" INT NOT NULL);
GO

SELECT "identity","order"
FROM "select"
ORDER BY "order";
GO

DROP TABLE "SELECT";
GO

SET QUOTED_IDENTIFIER OFF;
GO

SET CONCAT_NULL_YIELDS_NULL : SQL Serverda bu ayar ON olduğunda birleştirilen değerlerde NULL değer varsa birleştirme sonucu NULL değer döndürür.  OFF ise NULL değer boş değer olarak kabul edilecektir.

Gelecek sürüm SQL ServerCONCAT_NULL_YIELDS_NULL on her zaman olacaktır ve açıkça seçeneği off için ayarlanmış tüm uygulamaları bir hata üretecektir.

PRINT 'Setting CONCAT_NULL_YIELDS_NULL ON';

GO

-- SET CONCAT_NULL_YIELDS_NULL ON and testing.

SET CONCAT_NULL_YIELDS_NULL ON;

GO

SELECT 'abc' + NULL ;

GO

-- SET CONCAT_NULL_YIELDS_NULL OFF and testing.

SET CONCAT_NULL_YIELDS_NULL OFF;

GO

SELECT 'abc' + NULL;

GO

  

SET ANSI_WARNINGS : ON olarak ayarlandığında SUM, AVG, MAX, MIN, STDSAPMA, STDSAPMAS, VAR, VARP, ya da COUNT toplama işlemleri boş değer verdiğinde bir uyarı mesajı oluşur eğer OFF olarak ayarlanmışsa bir hata verilmez.

USE AdventureWorks2012;

GO

CREATE TABLE T1 (

   a INT,

   b INT NULL,

   c VARCHAR(20)

);

GO

SET NOCOUNT ON

INSERT INTO T1

VALUES (1, NULL, '');

INSERT INTO T1

VALUES (1, 0, '');

INSERT INTO T1

VALUES (2, 1, '');

INSERT INTO T1

VALUES (2, 2, '');

SET NOCOUNT OFF;

GO

 

PRINT '**** Setting ANSI_WARNINGS ON';

GO

 

SET ANSI_WARNINGS ON;

GO

 

PRINT 'Testing NULL in aggregate';

GO

SELECT a, SUM(b)

FROM T1

GROUP BY a;

GO

 

PRINT 'Testing String Overflow in INSERT';

GO

INSERT INTO T1

VALUES (3, 3, 'Text string longer than 20 characters');

GO

 

PRINT 'Testing Divide by zero';

GO

SELECT a / b AS ab

FROM T1;

GO

 

PRINT '**** Setting ANSI_WARNINGS OFF';

GO

SET ANSI_WARNINGS OFF;

GO

 

PRINT 'Testing NULL in aggregate';

GO

SELECT a, SUM(b)

FROM T1

GROUP BY a;

GO

 

PRINT 'Testing String Overflow in INSERT';

GO

INSERT INTO T1

VALUES (4, 4, 'Text string longer than 20 characters');

GO

SELECT a, b, c

FROM T1

WHERE a = 4;

GO

PRINT 'Testing Divide by zero';

GO

SELECT a / b AS ab

FROM T1;

GO

DROP TABLE T1

SET ARITHABORT:ON olarak ayarlandığında Sorgu yürütme sırasında taşma veya tarafından sıfıra bölme hatası oluştuğunda, bir sorgu sona erer. Eğer hata işlem sırasında meydana gelirse, işlem geri alınır.

off olarak ayarlandığında yukarda bahsedilen hatalardan biri meydana geldiğinde bir uyarı mesajı görüntülenir ama sorgu ya da işlem hiç bir hata olmamış gibi sürece devam eder.

set arithabort hesaplanmış klonlardaki indeksleri ve index viewları oluştururken ya da işlerken, on olarak ayarlanmalıdır.

-- SET ARITHABORT

-------------------------------------------------------------------------------

-- Create tables t1 and t2 and insert data values.

CREATE TABLE t1 (

   a TINYINT,

   b TINYINT

);

CREATE TABLE t2 (

   a TINYINT

);

GO

INSERT INTO t1

VALUES (1, 0);

INSERT INTO t1

VALUES (255, 1);

GO

PRINT '*** SET ARITHABORT ON';

GO

-- SET ARITHABORT ON and testing.

SET ARITHABORT ON;

GO

PRINT '*** Testing divide by zero during SELECT';

GO

SELECT a / b AS ab

FROM t1;

GO

PRINT '*** Testing divide by zero during INSERT';

GO

INSERT INTO t2

SELECT a / b AS ab 

FROM t1;

GO

PRINT '*** Testing tinyint overflow';

GO

INSERT INTO t2

SELECT a + b AS ab

FROM t1;

GO

PRINT '*** Resulting data - should be no data';

GO

SELECT *

FROM t2;

GO

-- Truncate table t2.

TRUNCATE TABLE t2;

GO

-- SET ARITHABORT OFF and testing.

PRINT '*** SET ARITHABORT OFF';

GO

SET ARITHABORT OFF;

GO

-- This works properly.

PRINT '*** Testing divide by zero during SELECT';

GO

SELECT a / b AS ab 

FROM t1;

GO

-- This works as if SET ARITHABORT was ON.

PRINT '*** Testing divide by zero during INSERT';

GO

INSERT INTO t2

SELECT a / b AS ab 

FROM t1;

GO

PRINT '*** Testing tinyint overflow';

GO

INSERT INTO t2

SELECT a + b AS ab

FROM t1;

GO

PRINT '*** Resulting data - should be 0 rows';

GO

SELECT *

FROM t2;

GO

-- Drop tables t1 and t2.

DROP TABLE t1;

DROP TABLE t2;

GO

Databasede Yapılan Değişiklikleri İzlemek

DBAlerin genel problemlerinden bir tanesi databasede yapılan değişiklikleri izlemektir. Bunun için kullanılan 3  yöntem olsada ben bu makalemde sizlere DDL kullanarak değişiklikleri izlemeyi anlatacağım. Diğer iki yöntem olan Extent Events ve Service Broker yönetimini ise daha sonra anlatmayı planlıyorum

Database Tablomu kim drop etti?
Databasede bulunan Viewde kim değişiklik yaptı?
Databasede bulunan Function ve Stored Procedurde kim değişiklik yaptı?

-------CREATE table to store changes--------

CREATE TABLE [dbo].[ChangeLog]
( [LogId] [int] IDENTITY(1,1) NOT NULL,
[DatabaseName] [varchar] (256) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[EventType] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[ObjectName] [varchar](256) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[ObjectType] [varchar](25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[SqlCommand] [varchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[EventDate] [datetime] NOT NULL CONSTRAINT [DF_EventsLog_EventDate] DEFAULT (getdate()),
[LoginName] [varchar](256) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]

go

---CREATE DATABASE TRIGGER TO INSERT CHANGES INTO dbo.changelog TABLE--

CREATE trigger backup_objects
on database
for create_procedure, alter_procedure, drop_procedure,
create_table, alter_table, drop_table,
create_function, alter_function, drop_function
as
set nocount on
declare @data xml
DECLARE @client_ip VARCHAR(15)
set @data = EVENTDATA()
SELECT @client_ip = client_net_address
FROM sys.dm_exec_connections
WHERE session_id =@data.value('(/EVENT_INSTANCE/SPID)[1]', 'varchar(256)')
insert into YOURDATABASE.dbo.changelog(databasename, eventtype,
objectname, objecttype, sqlcommand, loginname)
values(
@data.value('(/EVENT_INSTANCE/DatabaseName)[1]', 'varchar(256)'),
@data.value('(/EVENT_INSTANCE/EventType)[1]', 'varchar(50)'),
@data.value('(/EVENT_INSTANCE/SchemaName)[1]', 'varchar(256)') +'.'+
@data.value('(/EVENT_INSTANCE/ObjectName)[1]', 'varchar(256)'),
@data.value('(/EVENT_INSTANCE/ObjectType)[1]', 'varchar(25)'),
@data.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'varchar(max)'),
@data.value('(/EVENT_INSTANCE/LoginName)[1]', 'varchar(256)')+'('+@client_ip+')' )

Primary Key Olmayan Tabloları Bulmak

Bu yazımda sizlere bir databasede Primary Keyi olmayan tabloları bulmanın kolay yolunu anlatmaya çalışacağım. Primary Key olmayan tabloları bulmanın bir çok yolu var. Ben üç tanesini yazmak istiyorum.

1.Yöntem
Bu yöntemde OBJECTPROPERTY  fonksiyonun TableHasPrimaryKey kullanarak tüm tabloları kontrol edilir ve Primary Key olmayan tespit edilir.

use [AdventureWorks2016CTP3]
go
SELECT
SCHEMA_NAME(schema_id) AS [Schema name] ,
name AS [Table name] FROM sys.tables
WHERE
OBJECTPROPERTY(object_id,'TableHasPrimaryKey') = 0

2.Yöntem
En kolay yöntem sys.object adlı viewin kontrol edilmesidir. En kolay yöntem budur.

use [AdventureWorks2016CTP3]
GO
SELECT
  SCHEMA_NAME(schema_id) AS [Schema name]
, name AS [Table name] FROM sys.objects
WHERE
[type]='U' AND object_id
NOT IN (
SELECT parent_object_id FROM sys.objects
WHERE [type]='PK' )




3.Yöntem
Bu yöntem ise sys.tables & sys.key_constraints adlı system viewlerini kullanmaktadır.

use [AdventureWorks2016CTP3]
GO
SELECT
SCHEMA_NAME(schema_id) AS [Schema name]
, name AS [Table name]
FROM sys.tables
WHERE object_id NOT IN
(
SELECT parent_object_id
FROM sys.key_constraints
WHERE type = 'PK'
);

Bütün Databaselerin AUTO_SHRINK Özelliğinin Açılması veya Kapatılması

Aşağıdaki T-SQL kodunu kullanarak AUTOSHRINK açabilir veya kapatabiliriz.

Kapatılması

DECLARE @name varchar(500)
DECLARE @sql varchar(8000)
SET @sql = ''
DECLARE Database_Cursor CURSOR READ_ONLY FOR
SELECT Name
FROM sysdatabases
WHERE DBID > 4
OPEN Database_Cursor
FETCH NEXT FROM Database_Cursor INTO @name
WHILE @@FETCH_STATUS = 0
BEGINSET @sql = @sql + 'ALTER DATABASE [' + @name + '] SET AUTO_SHRINK OFF' + CHAR(10)
FETCH NEXT FROM Database_Cursor INTO @name
ENDCLOSE Database_Cursor
DEALLOCATE Database_Cursor
print @sql
EXEC(@sql)