Labels

admin (1) aix (1) alert (1) always-on (2) Architecture (1) aws (3) Azure (1) backup (3) BI-DWH (10) Binary (3) Boolean (1) C# (1) cache (1) casting (3) cdc (1) certificate (1) checks (1) cloud (3) cluster (1) cmd (7) collation (1) columns (1) compilation (1) configurations (7) Connection-String (2) connections (6) constraint (6) copypaste (2) cpu (2) csv (3) CTE (1) data-types (1) datetime (23) db (547) DB2 (1) deadlock (2) Denali (7) device (6) dotNet (5) dynamicSQL (11) email (5) encoding (1) encryption (4) errors (124) excel (1) ExecutionPlan (10) extended events (1) files (7) FIPS (1) foreign key (1) fragmentation (1) functions (1) GCP (2) gMSA (2) google (2) HADR (1) hashing (3) in-memory (1) index (3) indexedViews (2) insert (3) install (10) IO (1) isql (6) javascript (1) jobs (11) join (2) LDAP (2) LinkedServers (8) Linux (15) log (6) login (1) maintenance (3) mariadb (1) memory (4) merge (3) monitoring (4) MSA (2) mssql (444) mssql2005 (5) mssql2008R2 (20) mssql2012 (2) mysql (36) MySQL Shell (5) network (1) NoSQL (1) null (2) numeric (9) object-oriented (1) offline (1) openssl (1) Operating System (4) oracle (7) ORDBMS (1) ordering (2) Outer Apply (1) Outlook (1) page (1) parameters (2) partition (1) password (1) Performance (103) permissions (10) pivot (3) PLE (1) port (4) PostgreSQL (14) profiler (1) RDS (3) read (1) Replication (12) restore (4) root (1) RPO (1) RTO (1) SAP ASE (48) SAP RS (20) SCC (4) scema (1) script (8) security (10) segment (1) server (1) service broker (2) services (4) settings (75) SQL (74) SSAS (1) SSIS (19) SSL (8) SSMS (4) SSRS (6) storage (1) String (35) sybase (57) telnet (2) tempdb (1) Theory (2) tips (120) tools (3) training (1) transaction (6) trigger (2) Tuple (2) TVP (1) unix (8) users (3) vb.net (4) versioning (1) windows (14) xml (10) XSD (1) zip (1)

SQL table "Inside Order" - Identity VS CLUSTERED INDEX

Q: What will be the result order of a select (without ORDER BY) from a table - with/without Identity and CLUSTERED INDEX?
And when we will add Identity and/or CLUSTERED INDEX - how will this affect?

A: Follow the next selects:

create table dbo.NoID
(      someSTR nvarchar(50),
       someDate datetime
) ON [PRIMARY];

insert into NoID (someSTR, someDate) values ('aaa', GETDATE())
insert into NoID (someSTR, someDate) values ('bbb', GETDATE())
insert into NoID (someSTR, someDate) values ('ccc', GETDATE())
insert into NoID (someSTR, someDate) values ('zzz', GETDATE())
insert into NoID (someSTR, someDate) values ('ddd', GETDATE())
insert into NoID (someSTR, someDate) values ('eee', '2017-01-01 00:00:00')

select * from NoID

alter table dbo.NoID add ID int identity(1,1) not null

select * from NoID



create table dbo.NoID_PKClusteredNotOrderedByTime
(
       someSTR nvarchar(50),
       someDate datetime,
       CONSTRAINT [PK_NoID_PKClusteredNotOrderedByTime] PRIMARY KEY CLUSTERED
       (
              [someSTR] ASC
       ) ON [PRIMARY]
) ON [PRIMARY];

insert into NoID_PKClusteredNotOrderedByTime (someSTR, someDate) values ('aaa', GETDATE())
insert into NoID_PKClusteredNotOrderedByTime (someSTR, someDate) values ('bbb', GETDATE())
insert into NoID_PKClusteredNotOrderedByTime (someSTR, someDate) values ('ccc', GETDATE())
insert into NoID_PKClusteredNotOrderedByTime (someSTR, someDate) values ('zzz', GETDATE())
insert into NoID_PKClusteredNotOrderedByTime (someSTR, someDate) values ('ddd', GETDATE())
insert into NoID_PKClusteredNotOrderedByTime (someSTR, someDate) values ('eee', '2017-01-01 00:00:00')

select * from NoID_PKClusteredNotOrderedByTime

alter table dbo.NoID_PKClusteredNotOrderedByTime add ID int identity(1,1) not null

select * from NoID_PKClusteredNotOrderedByTime




create table dbo.NoID_ClusteredNotOrderedByTime
(
       someSTR nvarchar(50),
       someDate datetime
) ON [PRIMARY];

insert into NoID_ClusteredNotOrderedByTime (someSTR, someDate) values ('aaa', GETDATE())
insert into NoID_ClusteredNotOrderedByTime (someSTR, someDate) values ('bbb', GETDATE())
insert into NoID_ClusteredNotOrderedByTime (someSTR, someDate) values ('ccc', GETDATE())
insert into NoID_ClusteredNotOrderedByTime (someSTR, someDate) values ('zzz', GETDATE())
insert into NoID_ClusteredNotOrderedByTime (someSTR, someDate) values ('ddd', GETDATE())
insert into NoID_ClusteredNotOrderedByTime (someSTR, someDate) values ('eee', '2017-01-01 00:00:00')

select * from NoID_ClusteredNotOrderedByTime

CREATE CLUSTERED INDEX [ix_NoID_ClusteredNotOrderedByTime] ON dbo.NoID_ClusteredNotOrderedByTime
(
       [someSTR] asc
) ON [PRIMARY]
GO

select * from NoID_ClusteredNotOrderedByTime

alter table NoID_ClusteredNotOrderedByTime add ID int identity(1,1) not null

select * from NoID_ClusteredNotOrderedByTime

drop INDEX [ix_NoID_ClusteredNotOrderedByTime]  ON dbo.NoID_ClusteredNotOrderedByTime
alter table NoID_ClusteredNotOrderedByTime drop column ID

alter table NoID_ClusteredNotOrderedByTime add ID int identity(1,1) not null
select * from NoID_ClusteredNotOrderedByTime



create table dbo.NoID_ClusteredNotOrderedByTimeIdentityBeforeCI
(
       someSTR nvarchar(50),
       someDate datetime
) ON [PRIMARY];

insert into NoID_ClusteredNotOrderedByTimeIdentityBeforeCI (someSTR, someDate) values ('aaa', GETDATE())
insert into NoID_ClusteredNotOrderedByTimeIdentityBeforeCI (someSTR, someDate) values ('bbb', GETDATE())
insert into NoID_ClusteredNotOrderedByTimeIdentityBeforeCI (someSTR, someDate) values ('ccc', GETDATE())
insert into NoID_ClusteredNotOrderedByTimeIdentityBeforeCI (someSTR, someDate) values ('zzz', GETDATE())
insert into NoID_ClusteredNotOrderedByTimeIdentityBeforeCI (someSTR, someDate) values ('ddd', GETDATE())
insert into NoID_ClusteredNotOrderedByTimeIdentityBeforeCI (someSTR, someDate) values ('eee', '2017-01-01 00:00:00')

select * from NoID_ClusteredNotOrderedByTimeIdentityBeforeCI

alter table NoID_ClusteredNotOrderedByTimeIdentityBeforeCI add ID int identity(1,1) not null

select * from NoID_ClusteredNotOrderedByTimeIdentityBeforeCI

CREATE CLUSTERED INDEX [ix_NoID_ClusteredNotOrderedByTimeIdentityBeforeCI] ON dbo.NoID_ClusteredNotOrderedByTimeIdentityBeforeCI
(
       [someSTR] asc
) ON [PRIMARY]
GO

select * from NoID_ClusteredNotOrderedByTimeIdentityBeforeCI

/*
drop table dbo.NoID
drop table dbo.NoID_ClusteredNotOrderedByTime
drop table dbo.NoID_PKClusteredNotOrderedByTime
drop table NoID_ClusteredNotOrderedByTimeIdentityBeforeCI
*/

No comments:

Post a Comment