Bu SQL scriptini yazarken asıl amacım🎯sonucu uzun sürebilecek 🤹 sorguların performansını ölçmekti. Milyonlarca ya da milyarlarca🚀satırlık büyük veri setleriyle, özellikle yanlış indexlemeler, yanlış join seçimleri gibi maliyetli execution plan senaryoları deniyordum. Ancak tam olarak istediğim gibi bir yapıyı SQL için bulamadığımdan kendim yazmaya karar verdim. Resmi posta kodları dosyasından sanal adres verileri oluştururken Ömer ÇOLAKOĞLU'nun şu videosuna rastladım. Kendisinin paylaştığı excel dosyasından da ad-soyad, telefon gibi veriler eklediğimde gerçeğe yakın, mantıklı verilere sahip bir veri tabanı ortaya çıktı. 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.
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. Yalnız 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.
Maxdeğerini scriptin sonlarına doğru bulabilirsiniz.Addresstablosundaki 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
Vereceğiniz satır sayısına ve bilgisayarınızın performansına göre sorgu süresi uzayacak ancak en az 10 saniye civarı sürecektir. Scripti mümkün olduğunca minimum MSSQL yetkilerine göre yazmaya çalıştım eğer sorguyu çalıştırmanıza rağmen veri tabanı oluşmuyorsa izinlerinizde bir problem vardır. Aşağıdaki gibi PersonInfo ismini verdiğim view bütün verilerinizi döndürecektir.
select top 10 * from [FakeData].[dbo].[PersonInfo]/*view*/ order by 1--burada top kullandığımız için order by zorunlu| PersonId | Gender | Person | 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 |
ömer
2 yıl öncehocam ben bu dosyayı ama sadece bir tane dosya var başka dosya neden yok? bunu ne yapmam gerekiyor?