clear markdown feedback
clear markdown feedback
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 3798815 fiddles created (41847 in the last week).

create table game ( game_id int not null primary key, game_name varchar(100) not null ) ; create table area ( area_id int not null primary key, area_name varchar(100) not null ) ; create table player ( player_id int not null primary key, player_name varchar(100) not null ) ;
 hidden batch(es)


insert into game values (1, 'chess'), (2, 'go'), (3, 'reversi'), (4, 'backgammon'), (5, 'hex'), (6, 'havannah'), (7, 'pacman') ; insert into area values (11, 'usa'), (12, 'russia'), (13, 'greece'), (14, 'uk'), (15, 'france'), (16, 'hungary'), (17, 'ukraine'), (18, 'belgium'), (19, 'canada'), (20, 'new zealand') ; insert into player values (7, 'John'), (8, 'Mary'), (9, 'Alex'), (10, 'Anna'), (11, 'Fred'), (12, 'Fay') ;
23 rows affected
 hidden batch(es)


create table playground ( playground_id int not null identity primary key, game_id int not null references game, area_id int not null references area, player_id int not null references player, various_stuff varchar(100) null default 'abcdefghijklmnopqrstuvwxyz-abcdefghijklmnopqrstuvwxyz-abcdefghijklmnopqrstuvwxyz', constraint playground_uq unique (game_id, area_id, player_id) ) ;
 hidden batch(es)


create view dbo.play (game_id, area_id, player_id, name) WITH SCHEMABINDING as select pg.game_id, pg.area_id, pg.player_id, name = g.game_name + '-' + a.area_name + '-' + p.player_name from dbo.playground as pg join dbo.game as g on g.game_id = pg.game_id join dbo.area as a on a.area_id = pg.area_id join dbo.player as p on p.player_id = pg.player_id ;
 hidden batch(es)


-- create an index on the view create unique clustered index play_cix on play (game_id, area_id, player_id) ;
 hidden batch(es)


insert into playground (game_id, area_id, player_id) select game_id, area_id, player_id from game, area, player ;
420 rows affected
 hidden batch(es)


-------------------------------------------------------------------------------- -- Or use XML to see the visual representation, thanks to Justin Pealing and -- his library: https://github.com/JustinPealing/html-query-plan -------------------------------------------------------------------------------- set statistics xml on; select -- top (10) game_id, area_id, player_id, name from play WITH (NOEXPAND) -- Hint used because we are in Express edition ; set statistics xml off;
game_id area_id player_id name
1 11 7 chess-usa-John
1 11 8 chess-usa-Mary
1 11 9 chess-usa-Alex
1 11 10 chess-usa-Anna
1 11 11 chess-usa-Fred
1 11 12 chess-usa-Fay
1 12 7 chess-russia-John
1 12 8 chess-russia-Mary
1 12 9 chess-russia-Alex
1 12 10 chess-russia-Anna
1 12 11 chess-russia-Fred
1 12 12 chess-russia-Fay
1 13 7 chess-greece-John
1 13 8 chess-greece-Mary
1 13 9 chess-greece-Alex
1 13 10 chess-greece-Anna
1 13 11 chess-greece-Fred
1 13 12 chess-greece-Fay
1 14 7 chess-uk-John
1 14 8 chess-uk-Mary
1 14 9 chess-uk-Alex
1 14 10 chess-uk-Anna
1 14 11 chess-uk-Fred
1 14 12 chess-uk-Fay
1 15 7 chess-france-John
1 15 8 chess-france-Mary
1 15 9 chess-france-Alex
1 15 10 chess-france-Anna
1 15 11 chess-france-Fred
1 15 12 chess-france-Fay
1 16 7 chess-hungary-John
1 16 8 chess-hungary-Mary
1 16 9 chess-hungary-Alex
1 16 10 chess-hungary-Anna
1 16 11 chess-hungary-Fred
1 16 12 chess-hungary-Fay
1 17 7 chess-ukraine-John
1 17 8 chess-ukraine-Mary
1 17 9 chess-ukraine-Alex
1 17 10 chess-ukraine-Anna
1 17 11 chess-ukraine-Fred
1 17 12 chess-ukraine-Fay
1 18 7 chess-belgium-John
1 18 8 chess-belgium-Mary
1 18 9 chess-belgium-Alex
1 18 10 chess-belgium-Anna
1 18 11 chess-belgium-Fred
1 18 12 chess-belgium-Fay
1 19 7 chess-canada-John
1 19 8 chess-canada-Mary
1 19 9 chess-canada-Alex
1 19 10 chess-canada-Anna
1 19 11 chess-canada-Fred
1 19 12 chess-canada-Fay
1 20 7 chess-new zealand-John
1 20 8 chess-new zealand-Mary
1 20 9 chess-new zealand-Alex
1 20 10 chess-new zealand-Anna
1 20 11 chess-new zealand-Fred
1 20 12 chess-new zealand-Fay
2 11 7 go-usa-John
2 11 8 go-usa-Mary
2 11 9 go-usa-Alex
2 11 10 go-usa-Anna
2 11 11 go-usa-Fred
2 11 12 go-usa-Fay
2 12 7 go-russia-John
2 12 8 go-russia-Mary
2 12 9 go-russia-Alex
2 12 10 go-russia-Anna
2 12 11 go-russia-Fred
2 12 12 go-russia-Fay
2 13 7 go-greece-John
2 13 8 go-greece-Mary
2 13 9 go-greece-Alex
2 13 10 go-greece-Anna
2 13 11 go-greece-Fred
2 13 12 go-greece-Fay
2 14 7 go-uk-John
2 14 8 go-uk-Mary
2 14 9 go-uk-Alex
2 14 10 go-uk-Anna
2 14 11 go-uk-Fred
2 14 12 go-uk-Fay
2 15 7 go-france-John
2 15 8 go-france-Mary
2 15 9 go-france-Alex
2 15 10 go-france-Anna
2 15 11 go-france-Fred
2 15 12 go-france-Fay
2 16 7 go-hungary-John
2 16 8 go-hungary-Mary
2 16 9 go-hungary-Alex
2 16 10 go-hungary-Anna
2 16 11 go-hungary-Fred
2 16 12 go-hungary-Fay
2 17 7 go-ukraine-John
2 17 8 go-ukraine-Mary
2 17 9 go-ukraine-Alex
2 17 10 go-ukraine-Anna
2 17 11 go-ukraine-Fred
2 17 12 go-ukraine-Fay
2 18 7 go-belgium-John
2 18 8 go-belgium-Mary
2 18 9 go-belgium-Alex
2 18 10 go-belgium-Anna
2 18 11 go-belgium-Fred
2 18 12 go-belgium-Fay
2 19 7 go-canada-John
2 19 8 go-canada-Mary
2 19 9 go-canada-Alex
2 19 10 go-canada-Anna
2 19 11 go-canada-Fred
2 19 12 go-canada-Fay
2 20 7 go-new zealand-John
2 20 8 go-new zealand-Mary
2 20 9 go-new zealand-Alex
2 20 10 go-new zealand-Anna
2 20 11 go-new zealand-Fred
2 20 12 go-new zealand-Fay
3 11 7 reversi-usa-John
3 11 8 reversi-usa-Mary
3 11 9 reversi-usa-Alex
3 11 10 reversi-usa-Anna
3 11 11 reversi-usa-Fred
3 11 12 reversi-usa-Fay
3 12 7 reversi-russia-John
3 12 8 reversi-russia-Mary
3 12 9 reversi-russia-Alex
3 12 10 reversi-russia-Anna
3 12 11 reversi-russia-Fred
3 12 12 reversi-russia-Fay
3 13 7 reversi-greece-John
3 13 8 reversi-greece-Mary
3 13 9 reversi-greece-Alex
3 13 10 reversi-greece-Anna
3 13 11 reversi-greece-Fred
3 13 12 reversi-greece-Fay
3 14 7 reversi-uk-John
3 14 8 reversi-uk-Mary
3 14 9 reversi-uk-Alex
3 14 10 reversi-uk-Anna
3 14 11 reversi-uk-Fred
3 14 12 reversi-uk-Fay
3 15 7 reversi-france-John
3 15 8 reversi-france-Mary
3 15 9 reversi-france-Alex
3 15 10 reversi-france-Anna
3 15 11 reversi-france-Fred
3 15 12 reversi-france-Fay
3 16 7 reversi-hungary-John
3 16 8 reversi-hungary-Mary
3 16 9 reversi-hungary-Alex
3 16 10 reversi-hungary-Anna
3 16 11 reversi-hungary-Fred
3 16 12 reversi-hungary-Fay
3 17 7 reversi-ukraine-John
3 17 8 reversi-ukraine-Mary
3 17 9 reversi-ukraine-Alex
3 17 10 reversi-ukraine-Anna
3 17 11 reversi-ukraine-Fred
3 17 12 reversi-ukraine-Fay
3 18 7 reversi-belgium-John
3 18 8 reversi-belgium-Mary
3 18 9 reversi-belgium-Alex
3 18 10 reversi-belgium-Anna
3 18 11 reversi-belgium-Fred
3 18 12 reversi-belgium-Fay
3 19 7 reversi-canada-John
3 19 8 reversi-canada-Mary
3 19 9 reversi-canada-Alex
3 19 10 reversi-canada-Anna
3 19 11 reversi-canada-Fred
3 19 12 reversi-canada-Fay
3 20 7 reversi-new zealand-John
3 20 8 reversi-new zealand-Mary
3 20 9 reversi-new zealand-Alex
3 20 10 reversi-new zealand-Anna
3 20 11 reversi-new zealand-Fred
3 20 12 reversi-new zealand-Fay
4 11 7 backgammon-usa-John
4 11 8 backgammon-usa-Mary
4 11 9 backgammon-usa-Alex
4 11 10 backgammon-usa-Anna
4 11 11 backgammon-usa-Fred
4 11 12 backgammon-usa-Fay
4 12 7 backgammon-russia-John
4 12 8 backgammon-russia-Mary
4 12 9 backgammon-russia-Alex
4 12 10 backgammon-russia-Anna
4 12 11 backgammon-russia-Fred
4 12 12 backgammon-russia-Fay
4 13 7 backgammon-greece-John
4 13 8 backgammon-greece-Mary
4 13 9 backgammon-greece-Alex
4 13 10 backgammon-greece-Anna
4 13 11 backgammon-greece-Fred
4 13 12 backgammon-greece-Fay
4 14 7 backgammon-uk-John
4 14 8 backgammon-uk-Mary
4 14 9 backgammon-uk-Alex
4 14 10 backgammon-uk-Anna
4 14 11 backgammon-uk-Fred
4 14 12 backgammon-uk-Fay
4 15 7 backgammon-france-John
4 15 8 backgammon-france-Mary
4 15 9 backgammon-france-Alex
4 15 10 backgammon-france-Anna
4 15 11 backgammon-france-Fred
4 15 12 backgammon-france-Fay
4 16 7 backgammon-hungary-John
4 16 8 backgammon-hungary-Mary
4 16 9 backgammon-hungary-Alex
4 16 10 backgammon-hungary-Anna
4 16 11 backgammon-hungary-Fred
4 16 12 backgammon-hungary-Fay
4 17 7 backgammon-ukraine-John
4 17 8 backgammon-ukraine-Mary
4 17 9 backgammon-ukraine-Alex
4 17 10 backgammon-ukraine-Anna
4 17 11 backgammon-ukraine-Fred
4 17 12 backgammon-ukraine-Fay
4 18 7 backgammon-belgium-John
4 18 8 backgammon-belgium-Mary
4 18 9 backgammon-belgium-Alex
4 18 10 backgammon-belgium-Anna
4 18 11 backgammon-belgium-Fred
4 18 12 backgammon-belgium-Fay
4 19 7 backgammon-canada-John
4 19 8 backgammon-canada-Mary
4 19 9 backgammon-canada-Alex
4 19 10 backgammon-canada-Anna
4 19 11 backgammon-canada-Fred
4 19 12 backgammon-canada-Fay
4 20 7 backgammon-new zealand-John
4 20 8 backgammon-new zealand-Mary
4 20 9 backgammon-new zealand-Alex
4 20 10 backgammon-new zealand-Anna
4 20 11 backgammon-new zealand-Fred
4 20 12 backgammon-new zealand-Fay
5 11 7 hex-usa-John
5 11 8 hex-usa-Mary
5 11 9 hex-usa-Alex
5 11 10 hex-usa-Anna
5 11 11 hex-usa-Fred
5 11 12 hex-usa-Fay
5 12 7 hex-russia-John
5 12 8 hex-russia-Mary
5 12 9 hex-russia-Alex
5 12 10 hex-russia-Anna
5 12 11 hex-russia-Fred
5 12 12 hex-russia-Fay
5 13 7 hex-greece-John
5 13 8 hex-greece-Mary
5 13 9 hex-greece-Alex
5 13 10 hex-greece-Anna
5 13 11 hex-greece-Fred
5 13 12 hex-greece-Fay
5 14 7 hex-uk-John
5 14 8 hex-uk-Mary
5 14 9 hex-uk-Alex
5 14 10 hex-uk-Anna
5 14 11 hex-uk-Fred
5 14 12 hex-uk-Fay
5 15 7 hex-france-John
5 15 8 hex-france-Mary
5 15 9 hex-france-Alex
5 15 10 hex-france-Anna
5 15 11 hex-france-Fred
5 15 12 hex-france-Fay
5 16 7 hex-hungary-John
5 16 8 hex-hungary-Mary
5 16 9 hex-hungary-Alex
5 16 10 hex-hungary-Anna
5 16 11 hex-hungary-Fred
5 16 12 hex-hungary-Fay
5 17 7 hex-ukraine-John
5 17 8 hex-ukraine-Mary
5 17 9 hex-ukraine-Alex
5 17 10 hex-ukraine-Anna
5 17 11 hex-ukraine-Fred
5 17 12 hex-ukraine-Fay
5 18 7 hex-belgium-John
5 18 8 hex-belgium-Mary
5 18 9 hex-belgium-Alex
5 18 10 hex-belgium-Anna
5 18 11 hex-belgium-Fred
5 18 12 hex-belgium-Fay
5 19 7 hex-canada-John
5 19 8 hex-canada-Mary
5 19 9 hex-canada-Alex
5 19 10 hex-canada-Anna
5 19 11 hex-canada-Fred
5 19 12 hex-canada-Fay
5 20 7 hex-new zealand-John
5 20 8 hex-new zealand-Mary
5 20 9 hex-new zealand-Alex
5 20 10 hex-new zealand-Anna
5 20 11 hex-new zealand-Fred
5 20 12 hex-new zealand-Fay
6 11 7 havannah-usa-John
6 11 8 havannah-usa-Mary
6 11 9 havannah-usa-Alex
6 11 10 havannah-usa-Anna
6 11 11 havannah-usa-Fred
6 11 12 havannah-usa-Fay
6 12 7 havannah-russia-John
6 12 8 havannah-russia-Mary
6 12 9 havannah-russia-Alex
6 12 10 havannah-russia-Anna
6 12 11 havannah-russia-Fred
6 12 12 havannah-russia-Fay
6 13 7 havannah-greece-John
6 13 8 havannah-greece-Mary
6 13 9 havannah-greece-Alex
6 13 10 havannah-greece-Anna
6 13 11 havannah-greece-Fred
6 13 12 havannah-greece-Fay
6 14 7 havannah-uk-John
6 14 8 havannah-uk-Mary
6 14 9 havannah-uk-Alex
6 14 10 havannah-uk-Anna
6 14 11 havannah-uk-Fred
6 14 12 havannah-uk-Fay
6 15 7 havannah-france-John
6 15 8 havannah-france-Mary
6 15 9 havannah-france-Alex
6 15 10 havannah-france-Anna
6 15 11 havannah-france-Fred
6 15 12 havannah-france-Fay
6 16 7 havannah-hungary-John
6 16 8 havannah-hungary-Mary
6 16 9 havannah-hungary-Alex
6 16 10 havannah-hungary-Anna
6 16 11 havannah-hungary-Fred
6 16 12 havannah-hungary-Fay
6 17 7 havannah-ukraine-John
6 17 8 havannah-ukraine-Mary
6 17 9 havannah-ukraine-Alex
6 17 10 havannah-ukraine-Anna
6 17 11 havannah-ukraine-Fred
6 17 12 havannah-ukraine-Fay
6 18 7 havannah-belgium-John
6 18 8 havannah-belgium-Mary
6 18 9 havannah-belgium-Alex
6 18 10 havannah-belgium-Anna
6 18 11 havannah-belgium-Fred
6 18 12 havannah-belgium-Fay
6 19 7 havannah-canada-John
6 19 8 havannah-canada-Mary
6 19 9 havannah-canada-Alex
6 19 10 havannah-canada-Anna
6 19 11 havannah-canada-Fred
6 19 12 havannah-canada-Fay
6 20 7 havannah-new zealand-John
6 20 8 havannah-new zealand-Mary
6 20 9 havannah-new zealand-Alex
6 20 10 havannah-new zealand-Anna
6 20 11 havannah-new zealand-Fred
6 20 12 havannah-new zealand-Fay
7 11 7 pacman-usa-John
7 11 8 pacman-usa-Mary
7 11 9 pacman-usa-Alex
7 11 10 pacman-usa-Anna
7 11 11 pacman-usa-Fred
7 11 12 pacman-usa-Fay
7 12 7 pacman-russia-John
7 12 8 pacman-russia-Mary
7 12 9 pacman-russia-Alex
7 12 10 pacman-russia-Anna
7 12 11 pacman-russia-Fred
7 12 12 pacman-russia-Fay
7 13 7 pacman-greece-John
7 13 8 pacman-greece-Mary
7 13 9 pacman-greece-Alex
7 13 10 pacman-greece-Anna
7 13 11 pacman-greece-Fred
7 13 12 pacman-greece-Fay
7 14 7 pacman-uk-John
7 14 8 pacman-uk-Mary
7 14 9 pacman-uk-Alex
7 14 10 pacman-uk-Anna
7 14 11 pacman-uk-Fred
7 14 12 pacman-uk-Fay
7 15 7 pacman-france-John
7 15 8 pacman-france-Mary
7 15 9 pacman-france-Alex
7 15 10 pacman-france-Anna
7 15 11 pacman-france-Fred
7 15 12 pacman-france-Fay
7 16 7 pacman-hungary-John
7 16 8 pacman-hungary-Mary
7 16 9 pacman-hungary-Alex
7 16 10 pacman-hungary-Anna
7 16 11 pacman-hungary-Fred
7 16 12 pacman-hungary-Fay
7 17 7 pacman-ukraine-John
7 17 8 pacman-ukraine-Mary
7 17 9 pacman-ukraine-Alex
7 17 10 pacman-ukraine-Anna
7 17 11 pacman-ukraine-Fred
7 17 12 pacman-ukraine-Fay
7 18 7 pacman-belgium-John
7 18 8 pacman-belgium-Mary
7 18 9 pacman-belgium-Alex
7 18 10 pacman-belgium-Anna
7 18 11 pacman-belgium-Fred
7 18 12 pacman-belgium-Fay
7 19 7 pacman-canada-John
7 19 8 pacman-canada-Mary
7 19 9 pacman-canada-Alex
7 19 10 pacman-canada-Anna
7 19 11 pacman-canada-Fred
7 19 12 pacman-canada-Fay
7 20 7 pacman-new zealand-John
7 20 8 pacman-new zealand-Mary
7 20 9 pacman-new zealand-Alex
7 20 10 pacman-new zealand-Anna
7 20 11 pacman-new zealand-Fred
7 20 12 pacman-new zealand-Fay
Microsoft SQL Server 2005 XML Showplan
<ShowPlanXML xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan" Version="1.481" Build="14.0.3335.7"><BatchSequence><Batch><Statements><StmtSimple StatementText="select -- top (10) &#xa; game_id, area_id, player_id, name &#xa;from play &#xa; WITH (NOEXPAND)" StatementId="1" StatementCompId="2" StatementType="SELECT" RetrievedFromCache="true" StatementSubTreeCost="0.00522548" StatementEstRows="420" SecurityPolicyApplied="false" StatementOptmLevel="TRIVIAL" QueryHash="0xBA9BAED6D700FD27" QueryPlanHash="0xE0086846AA2CCE7F" CardinalityEstimationModelVersion="140"><StatementSetOptions QUOTED_IDENTIFIER="true" ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="true" ANSI_NULLS="true" ANSI_PADDING="true" ANSI_WARNINGS="true" NUMERIC_ROUNDABORT="false"></StatementSetOptions><QueryPlan DegreeOfParallelism="0" NonParallelPlanReason="NoParallelPlansInDesktopOrExpressEdition" CachedPlanSize="16" CompileTime="0" CompileCPU="0" CompileMemory="88"><MemoryGrantInfo SerialRequiredMemory="0" SerialDesiredMemory="0"></MemoryGrantInfo><OptimizerHardwareDependentProperties EstimatedAvailableMemoryGrant="838808" EstimatedPagesCached="52425" EstimatedAvailableDegreeOfParallelism="1" MaxCompileMemory="4358088"></OptimizerHardwareDependentProperties><TraceFlags IsCompileTime="1"><TraceFlag Value="460" Scope="Global"></TraceFlag><TraceFlag Value="8017" Scope="Global"></TraceFlag></TraceFlags><TraceFlags IsCompileTime="0"><TraceFlag Value="460" Scope="Global"></TraceFlag><TraceFlag Value="8017" Scope="Global"></TraceFlag></TraceFlags><QueryTimeStats ElapsedTime="0" CpuTime="0"></QueryTimeStats><RelOp NodeId="0" PhysicalOp="Clustered Index Scan" LogicalOp="Clustered Index Scan" EstimateRows="420" EstimatedRowsRead="420" EstimateIO="0.00460648" EstimateCPU="0.000619" AvgRowSize="174" EstimatedTotalSubtreeCost="0.00522548" TableCardinality="420" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_502a3a71151d49b99b6226a1fbc47c43]" Schema="[dbo]" Table="[play]" Column="game_id"></ColumnReference><ColumnReference Database="[fiddle_502a3a71151d49b99b6226a1fbc47c43]" Schema="[dbo]" Table="[play]" Column="area_id"></ColumnReference><ColumnReference Database="[fiddle_502a3a71151d49b99b6226a1fbc47c43]" Schema="[dbo]" Table="[play]" Column="player_id"></ColumnReference><ColumnReference Database="[fiddle_502a3a71151d49b99b6226a1fbc47c43]" Schema="[dbo]" Table="[play]" Column="name"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="420" Batches="0" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualScans="1" ActualLogicalReads="5" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" ActualRowsRead="420" ActualEndOfScans="1" ActualExecutions="1"></RunTimeCountersPerThread></RunTimeInformation><IndexScan Ordered="0" ForcedIndex="0" ForceScan="0" NoExpandHint="1" Storage="RowStore"><DefinedValues><DefinedValue><ColumnReference Database="[fiddle_502a3a71151d49b99b6226a1fbc47c43]" Schema="[dbo]" Table="[play]" Column="game_id"></ColumnReference></DefinedValue><DefinedValue><ColumnReference Database="[fiddle_502a3a71151d49b99b6226a1fbc47c43]" Schema="[dbo]" Table="[play]" Column="area_id"></ColumnReference></DefinedValue><DefinedValue><ColumnReference Database="[fiddle_502a3a71151d49b99b6226a1fbc47c43]" Schema="[dbo]" Table="[play]" Column="player_id"></ColumnReference></DefinedValue><DefinedValue><ColumnReference Database="[fiddle_502a3a71151d49b99b6226a1fbc47c43]" Schema="[dbo]" Table="[play]" Column="name"></ColumnReference></DefinedValue></DefinedValues><Object Database="[fiddle_502a3a71151d49b99b6226a1fbc47c43]" Schema="[dbo]" Table="[play]" Index="[play_cix]" IndexKind="ViewClustered" Storage="RowStore"></Object></IndexScan></RelOp></QueryPlan></StmtSimple></Statements></Batch></BatchSequence></ShowPlanXML>
 hidden batch(es)