Üzerinde çeşitli denemeler ⚙️ yapılabilecek veriler üreten bir SQL scriptini aşağıda paylaşıyorum. Aslında bunu yazarken asıl amacım 🎯 sonucu uzun sürebilecek 🤹 sorguların performansını ölçmekti. Milyonlarca -ya da isteğinize bağlı olarak milyar-🚀 satırlık büyük veri setleriyle, özellikle yanlış indexlemeler, yanlış join seçimleri gibi maliyetli execution plan senaryoları deniyordum. Posta kodu excel dosyasından sanal adres verileri oluştururken Ömer ÇOLAKOĞLU'nun şu videosuna rastladım. Kendisinin paylaştığı excel dosyasından ad-soyad, telefon gibi veriler de ekleyip gerçeğe yakın, mantıklı veriler üreten bir veri tabanı oluşturarak paylaşıyorum. Sonuç olarak yine mevcut tablolardan farklı tablolar, farklı veriler; hayal gücüne bağlı artık ne istenirse türetilebilir. Sayfanın en altında üretilen verilerden basit örneklere ulaşabilirsiniz.

Monitör sayısı kadar ekran resmi yakalama-screenshot{{lightbox=veritabani}}

Normalizasyonu yapıp temel indeksler dışında herhangi bir şey eklemedim. Veri tutarlılığı için adres tablosunu besleyen diğer tabloların primary key kolonlarını tek bir kompozit tabloda birleştirdim. Bu durum özellikle insert sorgularını kısmen yavaşlatacaktır. İsteğinize göre bunları kaldırabilirsiniz. Veri tabanının ER diagramını yukarıdaki resme tıklayıp büyüterek görebilirsiniz.

Yaklaşık yüzbin satır olduğu için aşağıdaki kod bloklarında sadece tablo şemalarını paylaşıyorum. Tüm scripte github.com/g-u-r-k-a-n/FakeData linkinden ulaşabilirsiniz.

  • İstediğiniz max değeri kadar rastgele veriler üretir.
  • Max değerini scriptin sonlarına doğru bulabilirsiniz.
  • Address tablosundaki foreign key ilişkileri veri tutarlılığı için ekledim. İsteğe göre kaldırabilirsiniz.
    USE [master]
    GO
    CREATE DATABASE [FakeData]
    CONTAINMENT = NONE
    GO
    ALTER DATABASE [FakeData] SET COMPATIBILITY_LEVEL = 150
    GO
    IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))
    begin
    EXEC [FakeData].[dbo].[sp_fulltext_database] @action = 'enable'
    end
    GO
    ALTER DATABASE [FakeData] SET ANSI_NULL_DEFAULT OFF 
    GO
    ALTER DATABASE [FakeData] SET ANSI_NULLS OFF 
    GO
    ALTER DATABASE [FakeData] SET ANSI_PADDING OFF 
    GO
    ALTER DATABASE [FakeData] SET ANSI_WARNINGS OFF 
    GO
    ALTER DATABASE [FakeData] SET ARITHABORT OFF 
    GO
    ALTER DATABASE [FakeData] SET AUTO_CLOSE OFF 
    GO
    ALTER DATABASE [FakeData] SET AUTO_SHRINK OFF 
    GO
    ALTER DATABASE [FakeData] SET AUTO_UPDATE_STATISTICS ON 
    GO
    ALTER DATABASE [FakeData] SET CURSOR_CLOSE_ON_COMMIT OFF 
    GO
    ALTER DATABASE [FakeData] SET CURSOR_DEFAULT  GLOBAL 
    GO
    ALTER DATABASE [FakeData] SET CONCAT_NULL_YIELDS_NULL OFF 
    GO
    ALTER DATABASE [FakeData] SET NUMERIC_ROUNDABORT OFF 
    GO
    ALTER DATABASE [FakeData] SET QUOTED_IDENTIFIER OFF 
    GO
    ALTER DATABASE [FakeData] SET RECURSIVE_TRIGGERS OFF 
    GO
    ALTER DATABASE [FakeData] SET  DISABLE_BROKER 
    GO
    ALTER DATABASE [FakeData] SET AUTO_UPDATE_STATISTICS_ASYNC OFF 
    GO
    ALTER DATABASE [FakeData] SET DATE_CORRELATION_OPTIMIZATION OFF 
    GO
    ALTER DATABASE [FakeData] SET TRUSTWORTHY OFF 
    GO
    ALTER DATABASE [FakeData] SET ALLOW_SNAPSHOT_ISOLATION OFF 
    GO
    ALTER DATABASE [FakeData] SET PARAMETERIZATION SIMPLE 
    GO
    ALTER DATABASE [FakeData] SET READ_COMMITTED_SNAPSHOT OFF 
    GO
    ALTER DATABASE [FakeData] SET HONOR_BROKER_PRIORITY OFF 
    GO
    ALTER DATABASE [FakeData] SET RECOVERY FULL 
    GO
    ALTER DATABASE [FakeData] SET  MULTI_USER 
    GO
    ALTER DATABASE [FakeData] SET PAGE_VERIFY CHECKSUM  
    GO
    ALTER DATABASE [FakeData] SET DB_CHAINING OFF 
    GO
    ALTER DATABASE [FakeData] SET FILESTREAM( NON_TRANSACTED_ACCESS = OFF ) 
    GO
    ALTER DATABASE [FakeData] SET TARGET_RECOVERY_TIME = 60 SECONDS 
    GO
    ALTER DATABASE [FakeData] SET DELAYED_DURABILITY = DISABLED 
    GO
    ALTER DATABASE [FakeData] SET ACCELERATED_DATABASE_RECOVERY = OFF  
    GO
    EXEC sys.sp_db_vardecimal_storage_format N'FakeData', N'ON'
    GO
    ALTER DATABASE [FakeData] SET QUERY_STORE = OFF
    GO
    USE [FakeData]
    GO
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    CREATE function [dbo].[CreateEmail](@name as nvarchar(50),@lastName as nvarchar(50))
    returns nvarchar(250)
    as
    begin
        set @name=replace((select dbo.FixTurkishChar(@name)),' ','')    
        set @lastName=trim((select dbo.FixTurkishChar(@lastName)))    
        return concat(@name,'.',@lastName,'@',(select [Name] from Domain where Id=round((select * from GetRandValue)*2,0)+1))
    end
    GO
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    CREATE function [dbo].[FixTurkishChar](@content nvarchar(max))
    returns nvarchar(max)
    as
    begin
        set @content=lower(@content)
        if(CHARINDEX('ğ',@content) > 0) set @content=replace(@content,'ğ','g')        
        if(CHARINDEX('ç',@content) > 0) set @content=replace(@content,'ç','c')        
        if(CHARINDEX('ş',@content) > 0) set @content=replace(@content,'ş','s')        
        if(CHARINDEX('ü',@content) > 0) set @content=replace(@content,'ü','u')        
        if(CHARINDEX('ö',@content) > 0) set @content=replace(@content,'ö','o')        
        if(CHARINDEX('ı',@content) > 0) set @content=replace(@content,'ı','i')        
        return @content
    end
    GO
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    CREATE TABLE [dbo].[County](
        [Id] [int] IDENTITY(1,1) NOT NULL,
        [Name] [nvarchar](250) NOT NULL,
        [CityId] [int] NOT NULL,
    CONSTRAINT [PK_County] PRIMARY KEY CLUSTERED 
    (
        [Id] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    ) ON [PRIMARY]
    GO
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    CREATE TABLE [dbo].[Street](
        [Id] [int] IDENTITY(1,1) NOT NULL,
        [Name] [nvarchar](250) NOT NULL,
    CONSTRAINT [PK_Street] PRIMARY KEY CLUSTERED 
    (
        [Id] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    ) ON [PRIMARY]
    GO
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    CREATE TABLE [dbo].[Address](
        [Id] [int] IDENTITY(1,1) NOT NULL,
        [NeighboorHoodId] [int] NULL,
        [StreetId] [int] NULL,
        [DistrictId] [int] NOT NULL,
        [CountyId] [int] NOT NULL,
        [CityId] [int] NOT NULL,
    CONSTRAINT [PK_Address] PRIMARY KEY CLUSTERED 
    (
        [Id] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    ) ON [PRIMARY]
    GO
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    CREATE TABLE [dbo].[City](
        [Id] [int] IDENTITY(1,1) NOT NULL,
        [Name] [nvarchar](50) NOT NULL,
        [PlateCode] [char](2) NOT NULL,
        [PhoneCode] [char](3) NOT NULL,
    CONSTRAINT [PK_City] PRIMARY KEY CLUSTERED 
    (
        [Id] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    ) ON [PRIMARY]
    GO
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    CREATE TABLE [dbo].[Neighborhood](
        [Id] [int] IDENTITY(1,1) NOT NULL,
        [Name] [nvarchar](250) NOT NULL,
        [DistrictId] [int] NOT NULL,
    CONSTRAINT [PK_Neighborhood_1] PRIMARY KEY CLUSTERED 
    (
        [Id] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    ) ON [PRIMARY]
    GO
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    CREATE TABLE [dbo].[PhoneNumber](
        [Id] [int] IDENTITY(1,1) NOT NULL,
        [Number] [char](7) NOT NULL,
    CONSTRAINT [PK_PhoneNumber] PRIMARY KEY CLUSTERED 
    (
        [Id] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    ) ON [PRIMARY]
    GO
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    CREATE TABLE [dbo].[District](
        [Id] [int] IDENTITY(1,1) NOT NULL,
        [Name] [nvarchar](250) NOT NULL,
        [CountyId] [int] NOT NULL,
    CONSTRAINT [PK_District] PRIMARY KEY CLUSTERED 
    (
        [Id] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    ) ON [PRIMARY]
    GO
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    CREATE VIEW [dbo].[GetRandValue]
    AS
    SELECT RAND() AS Value
    GO
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    CREATE TABLE [dbo].[Domain](
        [Id] [tinyint] IDENTITY(1,1) NOT NULL,
        [Name] [nvarchar](50) NOT NULL,
    CONSTRAINT [PK_Domain] PRIMARY KEY CLUSTERED 
    (
        [Id] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    ) ON [PRIMARY]
    GO
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    CREATE TABLE [dbo].[FirstName](
        [Id] [int] IDENTITY(1,1) NOT NULL,
        [FirstName] [nvarchar](50) NOT NULL,
        [Gender] [bit] NOT NULL,
    CONSTRAINT [PK_FirstName] PRIMARY KEY CLUSTERED 
    (
        [Id] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    ) ON [PRIMARY]
    GO
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    CREATE TABLE [dbo].[LastName](
        [Id] [int] IDENTITY(1,1) NOT NULL,
        [LastName] [nvarchar](50) NOT NULL,
    CONSTRAINT [PK_LastName] PRIMARY KEY CLUSTERED 
    (
        [Id] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    ) ON [PRIMARY]
    GO
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    CREATE TABLE [dbo].[Person](
        [Id] [bigint] IDENTITY(1,1) NOT NULL,
        [FirstName] [nvarchar](50) NOT NULL,
        [LastName] [nvarchar](50) NOT NULL,
        [AddressId] [int] NULL,
        [BirthDate] [datetime] NULL,
        [Gender] [bit] NULL,
        [Email] [nvarchar](250) NULL,
        [PhoneNumberId] [int] NULL,
    CONSTRAINT [PK_Customer] PRIMARY KEY CLUSTERED 
    (
        [Id] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    ) ON [PRIMARY]
    GO
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO

CREATE view [dbo].[PersonInfo] as
select 
    pers.Id                            as PersonId    
    ,case 
        when Gender=0 then 'Bayan'
        when Gender=1 then 'Bay'
    end                                as Gender
    ,pers.FirstName+' '+pers.LastName   as Person
    ,pers.Email
    ,city.PhoneCode+'-'+ph.Number        as Phone    
    ,concat(
    nei.[Name]      
    ,' '
    ,strt.[Name]
    ,' '
    ,dist.[Name]                        
    ,'-'
    ,cou.[Name]
    ,'/'
    ,city.[Name]
    )                    as [Address]    
from Person                as pers
    join [Address]        as addr    on addr.Id = pers.AddressId
    join Neighborhood    as nei    on nei.Id  = addr.NeighboorHoodId
    join Street            as strt    on strt.Id = addr.StreetId
    join District        as dist on dist.Id = addr.DistrictId
    join County            as cou  on cou.Id  = addr.CountyId
    join City            as city on city.Id = addr.CityId
    join PhoneNumber    as ph    on ph.Id   = pers.PhoneNumberId
GO
ALTER TABLE [dbo].[Address]  WITH CHECK ADD  CONSTRAINT [FK_Address_City] FOREIGN KEY([CityId])
REFERENCES [dbo].[City] ([Id])
GO
ALTER TABLE [dbo].[Address] CHECK CONSTRAINT [FK_Address_City]
GO
ALTER TABLE [dbo].[Address]  WITH CHECK ADD  CONSTRAINT [FK_Address_County] FOREIGN KEY([CountyId])
REFERENCES [dbo].[County] ([Id])
GO
ALTER TABLE [dbo].[Address] CHECK CONSTRAINT [FK_Address_County]
GO
ALTER TABLE [dbo].[Address]  WITH CHECK ADD  CONSTRAINT [FK_Address_District] FOREIGN KEY([DistrictId])
REFERENCES [dbo].[District] ([Id])
GO
ALTER TABLE [dbo].[Address] CHECK CONSTRAINT [FK_Address_District]
GO
ALTER TABLE [dbo].[Address]  WITH CHECK ADD  CONSTRAINT [FK_Address_Neighborhood1] FOREIGN KEY([NeighboorHoodId])
REFERENCES [dbo].[Neighborhood] ([Id])
GO
ALTER TABLE [dbo].[Address] CHECK CONSTRAINT [FK_Address_Neighborhood1]
GO
ALTER TABLE [dbo].[Address]  WITH CHECK ADD  CONSTRAINT [FK_Address_Street] FOREIGN KEY([StreetId])
REFERENCES [dbo].[Street] ([Id])
GO
ALTER TABLE [dbo].[Address] CHECK CONSTRAINT [FK_Address_Street]
GO
ALTER TABLE [dbo].[County]  WITH CHECK ADD  CONSTRAINT [FK_County_City] FOREIGN KEY([CityId])
REFERENCES [dbo].[City] ([Id])
GO
ALTER TABLE [dbo].[County] CHECK CONSTRAINT [FK_County_City]
GO
ALTER TABLE [dbo].[District]  WITH CHECK ADD  CONSTRAINT [FK_District_County] FOREIGN KEY([CountyId])
REFERENCES [dbo].[County] ([Id])
GO
ALTER TABLE [dbo].[District] CHECK CONSTRAINT [FK_District_County]
GO
ALTER TABLE [dbo].[Neighborhood]  WITH CHECK ADD  CONSTRAINT [FK_Neighborhood_District1] FOREIGN KEY([DistrictId])
REFERENCES [dbo].[District] ([Id])
GO
ALTER TABLE [dbo].[Neighborhood] CHECK CONSTRAINT [FK_Neighborhood_District1]
GO
ALTER TABLE [dbo].[Person]  WITH CHECK ADD  CONSTRAINT [FK_Person_Address] FOREIGN KEY([AddressId])
REFERENCES [dbo].[Address] ([Id])
GO
ALTER TABLE [dbo].[Person] CHECK CONSTRAINT [FK_Person_Address]
GO
ALTER TABLE [dbo].[Person]  WITH CHECK ADD  CONSTRAINT [FK_Person_PhoneNumber] FOREIGN KEY([PhoneNumberId])
REFERENCES [dbo].[PhoneNumber] ([Id])
GO
ALTER TABLE [dbo].[Person] CHECK CONSTRAINT [FK_Person_PhoneNumber]
GO
USE [master]
GO
ALTER DATABASE [FakeData] SET  READ_WRITE 
GO

use [FakeData]

declare @i               int = 1
    ,@max                int = 100000
    ,@neighboorhoodCount int = (select count(*) from Neighborhood)-1
    ,@streetCount        int = (select count(*) from Street)-1
    ,@districtCount      int = (select count(*) from District)-1
    ,@countyCount        int = (select count(*) from County)-1
    ,@cityCount          int = (select count(*) from City)-1;

if (not exists (select * from [Address]))
begin    
    while(@i<=10000)
    begin    
        declare @neighborboorId    int = (select round(rand()*@neighboorhoodCount,0)+1);
        declare @distictId        int = (select DistrictId  from Neighborhood where Id=@neighborboorId);
        declare @countyId        int = (select CountyId      from District        where Id=@distictId);
        declare @cityId            int = (select CityId      from County        where Id=@countyId);

        insert into [Address] values(
            @neighborboorId
            ,(select round(rand()*@streetCount,0)+1)
            ,@distictId
            ,@countyId
            ,@cityId
        );
        set @i=@i+1;
    end
end

set @i=1;

declare @nameCount   int = (select count(*) from FirstName)-1
    ,@lastNameCount  int = (select count(*) from LastName)-1
    ,@phoneCount     int = (select count(*) from PhoneNumber)-1
    ,@domainCount    int = (select count(*) from Domain)-1
    ,@addressCount   int = (select count(*) from [Address])-1;

while(@i<=@max)
begin
    declare @r          smallint    = round(rand()*@nameCount,0)+1;
    declare @name      varchar(50) = (select FirstName from FirstName where Id = @r);
    declare @lastname varchar(50) = (select LastName from LastName where Id = round(rand()*@lastNameCount,0)+1);

    insert into Person values(
        @name
        ,@lastname        
        ,(select round((select * from GetRandValue)*@addressCount,0)+1)
        ,(select dateadd(day,round(rand()*18250,0)+1 ,'1950-01-01'))
        ,(select Gender from FirstName where Id = @r)
        ,(select dbo.CreateEmail(@name,@lastname))
        ,(select round(rand()*@phoneCount,0)+1)
    );

    set @i=@i+1;
end

--commit

use [FakeData]
go
--alter database [FakeData]  remove file [FakeData_log]
--dbcc shrinkfile (N'FakeData_log' , 100)
ALTER DATABASE [FakeData] 
MODIFY FILE(NAME = N'FakeData_log', SIZE = 100MB, FILEGROWTH = 50MB);
go
select top 10 * from [FakeData].[dbo].[PersonInfo]/*view*/ order by 1    
Örnek Veriler
PersonId Gender Person Email Phone Address
1 Bay Vedat ISLAKCAN vedat.islakcan@yandex.com 212-3217885 Gürler Mah. Cerrah Saliha Sok. Tepebaşı-Taksim/İstanbul
2 Bay Bedirhan GÜÇER bedirhan.gucer@gmail.com 212-4422231 Zeytinlik Mah. Ezgi Sok. Zeytinlik-Bakırköy/İstanbul
3 Bayan Şenay KİNNA senay.kinna@gmail.com 286-5517987 Gazi Mustafa Kemal Mah. 18 Mart Sok. Merkez/Çanakkale
4 Bayan İpek ÖZKARAKULAK ipek.ozkarakulak@gmail.com 216-1341683 Atatürk Mah. Gaziler Cad. Cumhuriyet-Üsküdar/İstanbul
5 Bay Evren YURTKULU evren.yurtkulu@yandex.com 366-8634023 İnalı Mah. Emin Ongan Sok. Bingöl-Merkez/Kastamonu
6 Bayan Bahar ÇARLIK bahar.carlik@yandex.com 242-7247251 Yeşilyurt Mah. Saygılı Sok. Yeşilköy-Kemer/Antalya
7 Bayan Berivan ARSLANTUNÇ berivan.arslantunc@gmail.com 282-1333247 Esenler Mah. Sıran Söğüt Sok. Marmaracık-Ergene/Tekirdağ
8 Bay Baran KEREY baran.kerey@hotmail.com 388-1820943 Bahçelievler Mah. Yunus Emre Cad. Bilecik-Merkez/Niğde
9 Bay Alparslan BALALAN alparslan.balalan@hotmail.com 438-2241223 Dize Mah. Bulvar Cad. Yüksekova-Yüksekova/Hakkari
10 Bayan Selda BATTALLAR selda.battallar@hotmail.com 222-8547251 Adahisar Mah Yunus Emre Cad. Dinek-Odunpazarı/Eskişehir