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+')' )

SQL Server Linux Üzerinde Çalışabilecek.

SQL Server’ın bundan sonra Linux platformunda da kullanılabileceğini duyurdu Linux için ön inceleme(preview) versiyonu yayınlanan SQL Server’ın tam versiyonun 2017 yılı ortalarında yayınlanması planlanıyor.

SQL Server ile birlikte Linux kullanıcıları açık kaynaklı ücretsiz yazılımların yanında daha çok kurumsal kullanıma hitap eden bir veritabanı yönetim aracına daha sahip olmuş olacaklar. Microsoft‘un Linux platformunda özellikle kurumsal müşterilere karşı Oracle ile rekabette ne kadar başarılı olacağını zamanla göreceğiz ancak Microsoft için artık işletim sistemi değil geliştirdiği servislerin bir numaralı öncelik olduğunu rahatlıkla söyleyebiliriz.

 

IDC‘de kurumsal altyapılar bölümünden sorumlu başkan yardımcısı olan Al Gillen’da Microsoft’un yaptığı bu hamle ile SQL Server’ın adaptasyon oranında yükselmeye geçebileceğini altını çizmiş. Satya Nadella‘da Microsoft’un ”servis, ürün” öncelikli bir şirket olduğunu şu anda ”Veri”nin şirketin temel varlıklarından biri olduğunu ancak Microsoft SQL Server’ın şirketin en fazla öneme sahip stratejik varlığı olmadığının altını çizmiş.

 

 

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'
);

SQL Server 2016 Instant File Initialization

Instant File Initialization (Anında Dosya Oluşturulması) 2005 versiyonu ile karşımıza gelen bir özelliktir. Çok hızlı büyüyen veritabanlarında bu özelliğin aktif edilmesi önerilmektedir. Bu özellik sayesinde allocate edilen data dosyaları sıfır ile doldurulmadan anında allocate edilmesidir.

Eğer bu ayar aktif edilmezse allocate işlemi sırasında datafile sıfır ile doldurulmaktadır.

Bu sayede aşağıdaki işlemler çok hızlı bir şekilde yapılabilmektedir.
1-Database Oluşturulması
2-Mevcut veritabanına data file ekleme
3-Mevcut veritabanında datafile boyutunu manuel olarak büyütülmesi
4-Restore İşlemleri

2016 versiyonuna kadar bu işlemleri kurulum sonrasında yaptığımız bir çok ayar gibi kurulum sonrasında yapıyorduk. 2016 versiyonu ile birlikte“Grant Perform Volume Maintenance Task privilege to SQL Server Database engine Service” kutucuğunu işaretleyerek hızlıca yapılabilmektedir.

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)