Индексы SQL | www.nookery.ru

Приветствую всех, сегодня разберем тему касаемо индексов на примерах.

Индексы используются для логической сортировки данных с целью повышения скорости поиска и выполнения в последующем операций сортировки.

Все знаю что такое массивы, вот на примере их можно представить мы можем обращаться к определенным данным по индексу, а не перебирая все данные скопом в поиске нужного.

Индексы баз данных работают весьма похожим образом. Данные первичного ключа всегда отсортированы — СУБД делает это для вас. Выборка указанных строк производиться по первичному ключу, таким образом, всегда гарантирует быстроту и эффективность этой
операции.

Поиск значений в других столбцах, однако, выполняется уже не столь эффективно. Вы можете определить в качестве индекса один или несколько столбцов так, чтобы СУБД хранила отсортированный список содержимого удобным для вас образом.

Однако прежде чем создавать множество индексов, примите во внимание следующее.

Индексы повышают производительность операций выборки, но ухудшают производительность при выполнении таких операций, как добавление данных, их модификация и удаление. Вызвано это тем, что при выполнении подобных операций СУБД должна еще и динамически обновлять индекс.
Для хранения данных индекса требуется много места на жестком диске.
Не все данные подходят для индексации. Данные, которые не являются по своей сути уникальными (как, например, названия штатов в столбце cust_state), не дадут такого выигрыша от индексации, как данные, которые имеют больше возможных значений (как, например, имя и фамилия).
Индексы используются для фильтрации и сортировки данных. Если вы часто сортируете данные определенным образом, эти данные могут быть кандидатом на индексацию.
В качестве индекса можно определить несколько столбцов (например, с названием штата и названием города). Такой индекс можно использовать, только если данные будут отсортированы в порядке «штат плюс город». (Если вы захотите отсортировать данные по названию города, такой индекс использован не будет).

Индекс – это список (такой как библиографическая информация или перечень цитат, взятых
из литературы), который обычно располагают в алфавитном порядке перечисления
определенных данных.
Индексы в таблице позволяют получить данные из определенной записи без перебора всех
записей в таблице.
Типы индексов:
1) Кластеризованный индекс
2) Некластеризованный индекс:

заданный на неупорядоченной таблице
заданный на кластеризированной таблице

Кластеризованный индекс реализуется в виде сбалансированного дерева, которое поддерживает быстрое получение строк по значениям ключа кластеризованного индекса.

Кластеризированной называется таблица, данные в которой отсортированы по определенному столбцу или группе столбцов на которых задан кластеризованный индекс.

Некластеризованный индекс содержит некластеризованное ключевое значение и указатель на строку.

Индексы хранятся в виде B-деревьев (B – tree). “B” означает балансированное.

Представления это способ вывода ограниченного набора столбцов из реальной таблицы в виде виртуальной таблицы.

CREATE TABLE BTree
(
numbers int primary key, -- Первичный ключ, добавляет кластеризованый индекс
word varchar(25)
)
--SET STATISTICS IO ON
INSERT BTree
VALUES
(1,'one'),
(2,'two'),
(3,'three'),
(4,'four'),
(6,'six'),
(7,'seven'),
(8,'eight'),
(9,'nine');

SELECT * FROM BTree WHERE word = 'one';

SELECT * FROM sys.indexes
WHERE object_id = (SELECT object_id FROM sys.tables
				   WHERE name = 'BTree')

INSERT BTree
VALUES (5,'five');

SELECT * FROM BTree;

--------------------------------------------------

DROP TABLE BTree;
GO

CREATE TABLE BTree
(
numbers int ,
word varchar(25)primary key -- кластеризированный индекс
)

INSERT BTree
VALUES
(1,'one'),
(2,'two'),
(3,'three'),
(4,'four'),
(6,'six'),
(7,'seven'),
(8,'eight'),
(9,'nine');

SELECT * FROM BTree;

INSERT BTree
VALUES (5,'five');

SELECT * FROM BTree;

--------------------------------------------------

DROP TABLE BTree;
GO

CREATE TABLE BTree
(
numbers int unique, -- некластеризированный индекс на не упорядоченой таблице (данные хранятся не на листевом уровне дерева)
word varchar(25)
)

INSERT BTree
VALUES
(1,'one'),
(2,'two'),
(3,'three'),
(4,'four'),
(6,'six'),
(7,'seven'),
(8,'eight'),
(9,'nine');

SELECT * FROM BTree;

INSERT BTree
VALUES (5,'five');

SELECT * FROM BTree where word = 'one';

--------------------------------------------------

DROP TABLE BTree;
GO

CREATE TABLE BTree
(
numbers int primary key, -- кластеризированый индекс
word varchar(25)unique   -- некластеризированный индекс на клатеризованной таблице
)

INSERT BTree
VALUES
(1,'one'),
(2,'two'),
(3,'three'),
(4,'four'),
(6,'six'),
(7,'seven'),
(8,'eight'),
(9,'nine');

SELECT * FROM BTree;

INSERT BTree
VALUES (5,'five');

SELECT * FROM BTree where word in ('five', 'one'); -- поиск по некластеризованному индексу
SELECT * FROM BTree where numbers in (3,5);   -- поиск по кластеризованному индексу
--------------------------------------------------
DROP TABLE OrderDetails;
GO
DROP TABLE Orders;
GO
DROP TABLE Customers
GO

CREATE TABLE Customers                
(                                      
	CustumerNo int NOT NULL, 
	CustumerName varchar(25) NOT NULL,
	Address1 varchar(25) NOT NULL,
	Address2 varchar(25) NOT NULL,
	City      varchar(15) NOT NULL,
	State char(2) NOT NULL,
	Zip varchar(10) NOT NULL,
	Contact varchar(25) NOT NULL,
	Phone char(12),
	FedIDNo  varchar(10) NOT NULL,
	DateInSystem smalldatetime NOT NULL 
)
GO

INSERT INTO Customers
( CustumerNo, CustumerName, Address1, Address2, City, State, Zip, Contact, Phone, FedIDNo, DateInSystem)
VALUES
(1,'Alex', 'NewSTR', 'NewSTR2', 'City', 'NS', 'NewZip', 'dfgjs@mail.ru', '(093)1231212', 'qweq', GETDATE()),
(2,'Alex2', 'NewSTR2', 'NewSTR22', 'City2', 'SN', 'NewZip2', 'dfg2@mail.ru', '(093)2221212', 'qwq2', GETDATE());
GO

SELECT * FROM Customers;

-----------------------------------------------------------------------------------------------------------------

DROP TABLE Customers;
GO

CREATE TABLE Customers                
(                                      
	CustumerNo int NOT NULL PRIMARY KEY,    -- кластеризованный индекс
	CustumerName varchar(25) NOT NULL,
	Address1 varchar(25) NOT NULL,
	Address2 varchar(25) NOT NULL,
	City      varchar(15) NOT NULL,
	State char(2) NOT NULL,
	Zip varchar(10) NOT NULL,
	Contact varchar(25) NOT NULL,
	Phone char(12),
	FedIDNo  varchar(10) NOT NULL,
	DateInSystem smalldatetime NOT NULL 
)
GO

INSERT INTO Customers
( CustumerNo, CustumerName, Address1, Address2, City, State, Zip, Contact, Phone, FedIDNo, DateInSystem)
VALUES
(1,'Alex', 'NewSTR', 'NewSTR2', 'City', 'NS', 'NewZip', 'dfgjs@mail.ru', '(093)1231212', 'qweq', GETDATE()),
(2,'Alex2', 'NewSTR2', 'NewSTR22', 'City2', 'SN', 'NewZip2', 'dfg2@mail.ru', '(093)2221212', 'qwq2', GETDATE());

SELECT * FROM Customers; -- для поиска используем кластеризованный индекс

-----------------------------------------------------------------------------------------------------------------

DROP TABLE Customers;
GO

CREATE TABLE Customers                
(                                      
	CustumerNo int NOT NULL, 
	CustumerName varchar(25) NOT NULL,
	Address1 varchar(25) NOT NULL,
	Address2 varchar(25) NOT NULL,
	City      varchar(15) NOT NULL,
	State char(2) NOT NULL,
	Zip varchar(10) NOT NULL,
	Contact varchar(25) NOT NULL,
	Phone char(12) UNIQUE, -- некластеризованный индекс
	FedIDNo  varchar(10) NOT NULL,
	DateInSystem smalldatetime NOT NULL 
)
GO

INSERT INTO Customers
( CustumerNo, CustumerName, Address1, Address2, City, State, Zip, Contact, Phone, FedIDNo, DateInSystem)
VALUES
(1,'Alex', 'NewSTR', 'NewSTR2', 'City', 'NS', 'NewZip', 'dfgjs@mail.ru', '(093)1231212', 'qweq', GETDATE()),
(2,'Alex2', 'NewSTR2', 'NewSTR22', 'City2', 'SN', 'NewZip2', 'dfg2@mail.ru', '(093)2221212', 'qwq2', GETDATE());
GO

SELECT * FROM Customers;

SELECT Phone FROM Customers			  
	WHERE Phone = '(093)2221212'; -- Для поиска используем некластеризованный
	
-----------------------------------------------------------------------------------------------------------------

DROP TABLE Customers;
GO

CREATE TABLE Customers                
(                                      
	CustumerNo int IDENTITY NOT NULL
		PRIMARY KEY,    -- кластеризованный индекс
	CustumerName varchar(25) NOT NULL,
	Address1 varchar(25) NOT NULL,
	Address2 varchar(25) NOT NULL,
	City      varchar(15) NOT NULL,
	State char(2) NOT NULL,
	Zip varchar(10) NOT NULL,
	Contact varchar(25) NOT NULL,
	Phone char(12) UNIQUE, -- некластеризованный индекс
	FedIDNo  varchar(10) NOT NULL,
	DateInSystem smalldatetime NOT NULL 
)
GO

INSERT INTO Customers
( CustumerName, Address1, Address2, City, State, Zip, Contact, Phone, FedIDNo, DateInSystem)
VALUES
('Alex', 'NewSTR', 'NewSTR2', 'City', 'NS', 'NewZip', 'dfgjs@mail.ru', '(093)1231212', 'qweq', GETDATE()),
('Alex2', 'NewSTR2', 'NewSTR22', 'City2', 'SN', 'NewZip2', 'dfg2@mail.ru', '(093)2221212', 'qwq2', GETDATE());

SELECT * FROM Customers; -- для поиска используем только кластеризованный индекс

SELECT * FROM Customers			  -- Для поиска используем кластеризованный
	WHERE Phone = '(093)2221212'; -- и не кластеризированный индексы !!!
	
------------------------- Кластеризованный индекс -------------------------------------------------------

DROP TABLE Customers;
GO

CREATE TABLE Customers                
(                                      
	CustumerNo int NOT NULL, 
	CustumerName varchar(25) NOT NULL,
	Address1 varchar(25) NOT NULL,
	Address2 varchar(25) NOT NULL,
	City      varchar(15) NOT NULL,
	State char(2) NOT NULL,
	Zip varchar(10) NOT NULL,
	Contact varchar(25) NOT NULL,
	Phone char(12),
	FedIDNo  varchar(10) NOT NULL,
	DateInSystem smalldatetime NOT NULL 
)
GO

CREATE CLUSTERED INDEX PK_IND1 -- создание собственного кластеризованного индекса
ON Customers(CustumerNo);
GO

INSERT INTO Customers
( CustumerNo, CustumerName, Address1, Address2, City, State, Zip, Contact, Phone, FedIDNo, DateInSystem)
VALUES
(1,'Alex', 'NewSTR', 'NewSTR2', 'City', 'NS', 'NewZip', 'dfgjs@mail.ru', '(093)1231212', 'qweq', GETDATE()),
(1,'Alex2', 'NewSTR2', 'NewSTR22', 'City2', 'SN', 'NewZip2', 'dfg2@mail.ru', '(093)2221212', 'qwq2', GETDATE()); -- CustumerNo = 1 как и в первой строке
GO

SELECT * FROM Customers;-- для поиска используем кластеризованный индекс

-----------------------Кластеризованный индекс -----------------------------------------------------------

DROP TABLE Customers;
GO

CREATE TABLE Customers               
(                                      
	CustumerNo int NOT NULL, 
	CustumerName varchar(25) NOT NULL,
	Address1 varchar(25) NOT NULL,
	Address2 varchar(25) NOT NULL,
	City      varchar(15) NOT NULL,
	State char(2) NOT NULL,
	Zip varchar(10) NOT NULL,
	Contact varchar(25) NOT NULL,
	Phone char(12) Primary key,
	FedIDNo  varchar(10) NOT NULL,
	DateInSystem smalldatetime NOT NULL 
)
GO

CREATE UNIQUE CLUSTERED INDEX PK_IND1 -- создание собственного кластеризованного индекса 
ON Customers(CustumerNo);
GO

INSERT INTO Customers
( CustumerNo, CustumerName, Address1, Address2, City, State, Zip, Contact, Phone, FedIDNo, DateInSystem)
VALUES
(1,'Alex', 'NewSTR', 'NewSTR2', 'City', 'NS', 'NewZip', 'dfgjs@mail.ru', '(093)1231212', 'qweq', GETDATE()),
(1,'Alex2', 'NewSTR2', 'NewSTR22', 'City2', 'SN', 'NewZip2', 'dfg2@mail.ru', '(093)2221212', 'qwq2', GETDATE()); -- CustumerNo = 1 будет ошибка
GO

INSERT INTO Customers
( CustumerNo, CustumerName, Address1, Address2, City, State, Zip, Contact, Phone, FedIDNo, DateInSystem)
VALUES
(1,'Alex', 'NewSTR', 'NewSTR2', 'City', 'NS', 'NewZip', 'dfgjs@mail.ru', '(093)1231212', 'qweq', GETDATE()),
(2,'Alex2', 'NewSTR2', 'NewSTR22', 'City2', 'SN', 'NewZip2', 'dfg2@mail.ru', '(093)2221212', 'qwq2', GETDATE()); --CustumerNo должен быть уникальным для каждой строки если CREATE UNIQUE CLUSTERED INDEX
GO

SELECT * FROM Customers; --для поиска используем кластеризованный индекс

------------------ Создание некластеризованного индекса на неупорядоченой таблице -------------------------------------------------

DROP TABLE Customers;
GO

CREATE TABLE Customers                
(                                      
	CustumerNo int NOT NULL, 
	CustumerName varchar(25) NOT NULL,
	Address1 varchar(25) NOT NULL,
	Address2 varchar(25) NOT NULL,
	City      varchar(15) NOT NULL,
	State char(2) NOT NULL,
	Zip varchar(10) NOT NULL,
	Contact varchar(25) NOT NULL,
	Phone char(12),
	FedIDNo  varchar(10) NOT NULL,
	DateInSystem smalldatetime NOT NULL 
)
GO

CREATE UNIQUE NONCLUSTERED INDEX NC_IND1 -- создание собственного некластеризованного  индекса на неупорядоченной таблице
ON Customers(Phone);
GO

INSERT INTO Customers
( CustumerNo, CustumerName, Address1, Address2, City, State, Zip, Contact, Phone, FedIDNo, DateInSystem)
VALUES
(1,'Alex', 'NewSTR', 'NewSTR2', 'City', 'NS', 'NewZip', 'dfgjs@mail.ru', '(093)1111111', 'qweq', GETDATE()),
(2,'Alex2', 'NewSTR2', 'NewSTR22', 'City2', 'SN', 'NewZip2', 'dfg2@mail.ru', '(093)2222222', 'qwq2', GETDATE()); --CustumerNo должен быть уникальным для каждой строки если CREATE UNIQUE CLUSTERED INDEX
GO

SELECT * FROM Customers;

SELECT * FROM Customers			  
	WHERE Phone = '(093)2222222'; 
	

----------------Создание некластеризованного индекса на кластеризованной таблице--------------------------------------

DROP TABLE Customers;
GO

CREATE TABLE Customers                
(                                      
	CustumerNo int NOT NULL, 
	CustumerName varchar(25) NOT NULL,
	Address1 varchar(25) NOT NULL,
	Address2 varchar(25) NOT NULL,
	City      varchar(15) NOT NULL,
	State char(2) NOT NULL,
	Zip varchar(10) NOT NULL,
	Contact varchar(25) NOT NULL,
	Phone char(12),
	FedIDNo  varchar(10) NOT NULL,
	DateInSystem smalldatetime NOT NULL 
)
GO

CREATE UNIQUE CLUSTERED INDEX PK_IND1 -- создание собственного кластеризованного индекса
ON Customers(CustumerNo);
GO

CREATE UNIQUE NONCLUSTERED INDEX NC_IND1 -- создание собственного некластеризованного  индекса на кластеризованной таблице
ON Customers(Phone);
GO

INSERT INTO Customers
( CustumerNo, CustumerName, Address1, Address2, City, State, Zip, Contact, Phone, FedIDNo, DateInSystem)
VALUES
(1,'Alex', 'NewSTR', 'NewSTR2', 'City', 'NS', 'NewZip', 'dfgjs@mail.ru', '(093)1231212', 'qweq', GETDATE()),
(2,'Alex2', 'NewSTR2', 'NewSTR22', 'City2', 'SN', 'NewZip2', 'dfg2@mail.ru', '(093)2221212', 'qwq2', GETDATE()); --CustumerNo должен быть уникальным для каждой строки если CREATE UNIQUE CLUSTERED INDEX
GO

SELECT * FROM Customers; -- Для поиска используем кластеризованный индекс

SELECT * FROM Customers			  -- Для поиска используем кластеризованный
	WHERE Phone = '(093)2221212'; --  и не кластеризированный индексы !!!
	


-----------------------------------------------------------
        /* Изменения индекса ALTER INDEX */
-----------------------------------------------------------

DROP TABLE Customers;
GO

CREATE TABLE Customers                
(                                      
	CustumerNo int NOT NULL, 
	CustumerName varchar(25) NOT NULL,
	Address1 varchar(25) NOT NULL,
	Address2 varchar(25) NOT NULL,
	City      varchar(15) NOT NULL,
	State char(2) NOT NULL,
	Zip varchar(10) NOT NULL,
	Contact varchar(25) NOT NULL,
	Phone char(12),
	FedIDNo  varchar(10) NOT NULL,
	DateInSystem smalldatetime NOT NULL 
)
GO

CREATE UNIQUE INDEX PK_IND1 --если включена опция IGNORE_DUP_KEY и нету ограничения UNIQUE будет предупреждение (возможна вставка одинаковых индексов Alex и alex)
ON Customers(CustumerName)
WITH 
(
IGNORE_DUP_KEY = ON -- включаем игнорирование различия между строчными и заглавными
);
GO

INSERT INTO Customers
( CustumerNo, CustumerName, Address1, Address2, City, State, Zip, Contact, Phone, FedIDNo, DateInSystem)
VALUES
(1,'Alex', 'NewSTR', 'NewSTR2', 'City', 'NS', 'NewZip', 'dfgjs@mail.ru', '(093)1231212', 'qweq', GETDATE()),
(2,'alex', 'NewSTR2', 'NewSTR22', 'City2', 'SN', 'NewZip2', 'dfg2@mail.ru', '(093)2221212', 'qwq2', GETDATE()); -- Не будет ошибки
GO

SELECT * FROM Customers


ALTER INDEX PK_IND1 -- изменение индекса PK_IND1 
ON Customers		-- заданого на таблице Customers
REBUILD WITH
	(
		IGNORE_DUP_KEY = OFF
	);

INSERT INTO Customers
( CustumerNo, CustumerName, Address1, Address2, City, State, Zip, Contact, Phone, FedIDNo, DateInSystem)
VALUES
(1,'Alex', 'NewSTR', 'NewSTR2', 'City', 'NS', 'NewZip', 'dfgjs@mail.ru', '(093)1231212', 'qweq', GETDATE()),
(2,'alex', 'NewSTR2', 'NewSTR22', 'City2', 'SN', 'NewZip2', 'dfg2@mail.ru', '(093)2221212', 'qwq2', GETDATE()); -- Не будет ошибки
GO

SELECT * FROM Customers;

----------------------------УДАЛЕНИЕ ИНДЕКСОВ---------------------------------------------------------------------
DROP TABLE Customers;
GO

CREATE TABLE Customers                
(                                      
	CustumerNo int NOT NULL, 
	CustumerName varchar(25) NOT NULL,
	Address1 varchar(25) NOT NULL,
	Address2 varchar(25) NOT NULL,
	City      varchar(15) NOT NULL,
	State char(2) NOT NULL,
	Zip varchar(10) NOT NULL,
	Contact varchar(25) NOT NULL,
	Phone char(12),
	FedIDNo  varchar(10) NOT NULL,
	DateInSystem smalldatetime NOT NULL 
)
GO

CREATE UNIQUE INDEX PK_IND1 -- если включена опция IGNORE_DUP_KEY и нет ограничения UNIQUE будет предупреждения (возможна вставка одинаковых индексов Alex и alex)
ON Customers(CustumerName)
WITH 
(
IGNORE_DUP_KEY = ON -- включаем игнорирование различия между строчными и заглавными символами
);
GO

INSERT INTO Customers
( CustumerNo, CustumerName, Address1, Address2, City, State, Zip, Contact, Phone, FedIDNo, DateInSystem)
VALUES
(1,'Alex', 'NewSTR', 'NewSTR2', 'City', 'NS', 'NewZip', 'dfgjs@mail.ru', '(093)1231212', 'qweq', GETDATE()),
(2,'alex', 'NewSTR2', 'NewSTR22', 'City2', 'SN', 'NewZip2', 'dfg2@mail.ru', '(093)2221212', 'qwq2', GETDATE()); -- если включена опция IGNORE_DUP_KEY и есть ограничения UNIQUE данная строка не запишется
GO

SELECT * FROM Customers WHERE CustumerName = 'alex'; -- для поиска используем не кластеризованный индекс

DROP INDEX Customers.PK_IND1;

SELECT * FROM Customers WHERE CustumerName = 'alex'; -- для поиска не используем индекс

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

157

158

159

160

161

162

163

164

165

166

167

168

169

170

171

172

173

174

175

176

177

178

179

180

181

182

183

184

185

186

187

188

189

190

191

192

193

194

195

196

197

198

199

200

201

202

203

204

205

206

207

208

209

210

211

212

213

214

215

216

217

218

219

220

221

222

223

224

225

226

227

228

229

230

231

232

233

234

235

236

237

238

239

240

241

242

243

244

245

246

247

248

249

250

251

252

253

254

255

256

257

258

259

260

261

262

263

264

265

266

267

268

269

270

271

272

273

274

275

276

277

278

279

280

281

282

283

284

285

286

287

288

289

290

291

292

293

294

295

296

297

298

299

300

301

302

303

304

305

306

307

308

309

310

311

312

313

314

315

316

317

318

319

320

321

322

323

324

325

326

327

328

329

330

331

332

333

334

335

336

337

338

339

340

341

342

343

344

345

346

347

348

349

350

351

352

353

354

355

356

357

358

359

360

361

362

363

364

365

366

367

368

369

370

371

372

373

374

375

376

377

378

379

380

381

382

383

384

385

386

387

388

389

390

391

392

393

394

395

396

397

398

399

400

401

402

403

404

405

406

407

408

409

410

411

412

413

414

415

416

417

418

419

420

421

422

423

424

425

426

427

428

429

430

431

432

433

434

435

436

437

438

439

440

441

442

443

444

445

446

447

448

449

450

451

452

453

454

455

456

457

458

459

460

461

462

463

464

465

466

467

468

469

470

471

472

473

474

475

476

477

478

479

480

481

482

483

484

485

486

487

488

489

490

491

492

493

494

495

496

497

498

CREATE TABLE BTree

(

numbers int primary key, -- Первичный ключ, добавляет кластеризованый индекс

word varchar(25)

)

--SET STATISTICS IO ON

INSERT BTree

VALUES

(1,'one'),

(2,'two'),

(3,'three'),

(4,'four'),

(6,'six'),

(7,'seven'),

(8,'eight'),

(9,'nine');

SELECT * FROM BTree WHERE word = 'one';

SELECT * FROM sys.indexes

WHERE object_id = (SELECT object_id FROM sys.tables

WHERE name = 'BTree')

INSERT BTree

VALUES (5,'five');

SELECT * FROM BTree;

--------------------------------------------------

DROP TABLE BTree;

CREATE TABLE BTree

(

numbers int ,

word varchar(25)primary key -- кластеризированный индекс

)

INSERT BTree

VALUES

(1,'one'),

(2,'two'),

(3,'three'),

(4,'four'),

(6,'six'),

(7,'seven'),

(8,'eight'),

(9,'nine');

SELECT * FROM BTree;

INSERT BTree

VALUES (5,'five');

SELECT * FROM BTree;

--------------------------------------------------

DROP TABLE BTree;

CREATE TABLE BTree

(

numbers int unique, -- некластеризированный индекс на не упорядоченой таблице (данные хранятся не на листевом уровне дерева)

word varchar(25)

)

INSERT BTree

VALUES

(1,'one'),

(2,'two'),

(3,'three'),

(4,'four'),

(6,'six'),

(7,'seven'),

(8,'eight'),

(9,'nine');

SELECT * FROM BTree;

INSERT BTree

VALUES (5,'five');

SELECT * FROM BTree where word = 'one';

--------------------------------------------------

DROP TABLE BTree;

CREATE TABLE BTree

(

numbers int primary key, -- кластеризированый индекс

word varchar(25)unique -- некластеризированный индекс на клатеризованной таблице

)

INSERT BTree

VALUES

(1,'one'),

(2,'two'),

(3,'three'),

(4,'four'),

(6,'six'),

(7,'seven'),

(8,'eight'),

(9,'nine');

SELECT * FROM BTree;

INSERT BTree

VALUES (5,'five');

SELECT * FROM BTree where word in ('five', 'one'); -- поиск по некластеризованному индексу

SELECT * FROM BTree where numbers in (3,5); -- поиск по кластеризованному индексу

--------------------------------------------------

DROP TABLE OrderDetails;

DROP TABLE Orders;

DROP TABLE Customers

CREATE TABLE Customers

(

CustumerNo int NOT NULL,

CustumerName varchar(25) NOT NULL,

Address1 varchar(25) NOT NULL,

Address2 varchar(25) NOT NULL,

City varchar(15) NOT NULL,

State char(2) NOT NULL,

Zip varchar(10) NOT NULL,

Contact varchar(25) NOT NULL,

Phone char(12),

FedIDNo varchar(10) NOT NULL,

DateInSystem smalldatetime NOT NULL

)

INSERT INTO Customers

( CustumerNo, CustumerName, Address1, Address2, City, State, Zip, Contact, Phone, FedIDNo, DateInSystem)

VALUES

(1,'Alex', 'NewSTR', 'NewSTR2', 'City', 'NS', 'NewZip', 'dfgjs@mail.ru', '(093)1231212', 'qweq', GETDATE()),

(2,'Alex2', 'NewSTR2', 'NewSTR22', 'City2', 'SN', 'NewZip2', 'dfg2@mail.ru', '(093)2221212', 'qwq2', GETDATE());

SELECT * FROM Customers;

-----------------------------------------------------------------------------------------------------------------

DROP TABLE Customers;

CREATE TABLE Customers

(

CustumerNo int NOT NULL PRIMARY KEY, -- кластеризованный индекс

CustumerName varchar(25) NOT NULL,

Address1 varchar(25) NOT NULL,

Address2 varchar(25) NOT NULL,

City varchar(15) NOT NULL,

State char(2) NOT NULL,

Zip varchar(10) NOT NULL,

Contact varchar(25) NOT NULL,

Phone char(12),

FedIDNo varchar(10) NOT NULL,

DateInSystem smalldatetime NOT NULL

)

INSERT INTO Customers

( CustumerNo, CustumerName, Address1, Address2, City, State, Zip, Contact, Phone, FedIDNo, DateInSystem)

VALUES

(1,'Alex', 'NewSTR', 'NewSTR2', 'City', 'NS', 'NewZip', 'dfgjs@mail.ru', '(093)1231212', 'qweq', GETDATE()),

(2,'Alex2', 'NewSTR2', 'NewSTR22', 'City2', 'SN', 'NewZip2', 'dfg2@mail.ru', '(093)2221212', 'qwq2', GETDATE());

SELECT * FROM Customers; -- для поиска используем кластеризованный индекс

-----------------------------------------------------------------------------------------------------------------

DROP TABLE Customers;

CREATE TABLE Customers

(

CustumerNo int NOT NULL,

CustumerName varchar(25) NOT NULL,

Address1 varchar(25) NOT NULL,

Address2 varchar(25) NOT NULL,

City varchar(15) NOT NULL,

State char(2) NOT NULL,

Zip varchar(10) NOT NULL,

Contact varchar(25) NOT NULL,

Phone char(12) UNIQUE, -- некластеризованный индекс

FedIDNo varchar(10) NOT NULL,

DateInSystem smalldatetime NOT NULL

)

INSERT INTO Customers

( CustumerNo, CustumerName, Address1, Address2, City, State, Zip, Contact, Phone, FedIDNo, DateInSystem)

VALUES

(1,'Alex', 'NewSTR', 'NewSTR2', 'City', 'NS', 'NewZip', 'dfgjs@mail.ru', '(093)1231212', 'qweq', GETDATE()),

(2,'Alex2', 'NewSTR2', 'NewSTR22', 'City2', 'SN', 'NewZip2', 'dfg2@mail.ru', '(093)2221212', 'qwq2', GETDATE());

SELECT * FROM Customers;

SELECT Phone FROM Customers

WHERE Phone = '(093)2221212'; -- Для поиска используем некластеризованный

-----------------------------------------------------------------------------------------------------------------

DROP TABLE Customers;

CREATE TABLE Customers

(

CustumerNo int IDENTITY NOT NULL

PRIMARY KEY, -- кластеризованный индекс

CustumerName varchar(25) NOT NULL,

Address1 varchar(25) NOT NULL,

Address2 varchar(25) NOT NULL,

City varchar(15) NOT NULL,

State char(2) NOT NULL,

Zip varchar(10) NOT NULL,

Contact varchar(25) NOT NULL,

Phone char(12) UNIQUE, -- некластеризованный индекс

FedIDNo varchar(10) NOT NULL,

DateInSystem smalldatetime NOT NULL

)

INSERT INTO Customers

( CustumerName, Address1, Address2, City, State, Zip, Contact, Phone, FedIDNo, DateInSystem)

VALUES

('Alex', 'NewSTR', 'NewSTR2', 'City', 'NS', 'NewZip', 'dfgjs@mail.ru', '(093)1231212', 'qweq', GETDATE()),

('Alex2', 'NewSTR2', 'NewSTR22', 'City2', 'SN', 'NewZip2', 'dfg2@mail.ru', '(093)2221212', 'qwq2', GETDATE());

SELECT * FROM Customers; -- для поиска используем только кластеризованный индекс

SELECT * FROM Customers -- Для поиска используем кластеризованный

WHERE Phone = '(093)2221212'; -- и не кластеризированный индексы !!!

------------------------- Кластеризованный индекс -------------------------------------------------------

DROP TABLE Customers;

CREATE TABLE Customers

(

CustumerNo int NOT NULL,

CustumerName varchar(25) NOT NULL,

Address1 varchar(25) NOT NULL,

Address2 varchar(25) NOT NULL,

City varchar(15) NOT NULL,

State char(2) NOT NULL,

Zip varchar(10) NOT NULL,

Contact varchar(25) NOT NULL,

Phone char(12),

FedIDNo varchar(10) NOT NULL,

DateInSystem smalldatetime NOT NULL

)

CREATE CLUSTERED INDEX PK_IND1 -- создание собственного кластеризованного индекса

ON Customers(CustumerNo);

INSERT INTO Customers

( CustumerNo, CustumerName, Address1, Address2, City, State, Zip, Contact, Phone, FedIDNo, DateInSystem)

VALUES

(1,'Alex', 'NewSTR', 'NewSTR2', 'City', 'NS', 'NewZip', 'dfgjs@mail.ru', '(093)1231212', 'qweq', GETDATE()),

(1,'Alex2', 'NewSTR2', 'NewSTR22', 'City2', 'SN', 'NewZip2', 'dfg2@mail.ru', '(093)2221212', 'qwq2', GETDATE()); -- CustumerNo = 1 как и в первой строке

SELECT * FROM Customers;-- для поиска используем кластеризованный индекс

-----------------------Кластеризованный индекс -----------------------------------------------------------

DROP TABLE Customers;

CREATE TABLE Customers

(

CustumerNo int NOT NULL,

CustumerName varchar(25) NOT NULL,

Address1 varchar(25) NOT NULL,

Address2 varchar(25) NOT NULL,

City varchar(15) NOT NULL,

State char(2) NOT NULL,

Zip varchar(10) NOT NULL,

Contact varchar(25) NOT NULL,

Phone char(12) Primary key,

FedIDNo varchar(10) NOT NULL,

DateInSystem smalldatetime NOT NULL

)

CREATE UNIQUE CLUSTERED INDEX PK_IND1 -- создание собственного кластеризованного индекса

ON Customers(CustumerNo);

INSERT INTO Customers

( CustumerNo, CustumerName, Address1, Address2, City, State, Zip, Contact, Phone, FedIDNo, DateInSystem)

VALUES

(1,'Alex', 'NewSTR', 'NewSTR2', 'City', 'NS', 'NewZip', 'dfgjs@mail.ru', '(093)1231212', 'qweq', GETDATE()),

(1,'Alex2', 'NewSTR2', 'NewSTR22', 'City2', 'SN', 'NewZip2', 'dfg2@mail.ru', '(093)2221212', 'qwq2', GETDATE()); -- CustumerNo = 1 будет ошибка

INSERT INTO Customers

( CustumerNo, CustumerName, Address1, Address2, City, State, Zip, Contact, Phone, FedIDNo, DateInSystem)

VALUES

(1,'Alex', 'NewSTR', 'NewSTR2', 'City', 'NS', 'NewZip', 'dfgjs@mail.ru', '(093)1231212', 'qweq', GETDATE()),

(2,'Alex2', 'NewSTR2', 'NewSTR22', 'City2', 'SN', 'NewZip2', 'dfg2@mail.ru', '(093)2221212', 'qwq2', GETDATE()); --CustumerNo должен быть уникальным для каждой строки если CREATE UNIQUE CLUSTERED INDEX

SELECT * FROM Customers; --для поиска используем кластеризованный индекс

------------------ Создание некластеризованного индекса на неупорядоченой таблице -------------------------------------------------

DROP TABLE Customers;

CREATE TABLE Customers

(

CustumerNo int NOT NULL,

CustumerName varchar(25) NOT NULL,

Address1 varchar(25) NOT NULL,

Address2 varchar(25) NOT NULL,

City varchar(15) NOT NULL,

State char(2) NOT NULL,

Zip varchar(10) NOT NULL,

Contact varchar(25) NOT NULL,

Phone char(12),

FedIDNo varchar(10) NOT NULL,

DateInSystem smalldatetime NOT NULL

)

CREATE UNIQUE NONCLUSTERED INDEX NC_IND1 -- создание собственного некластеризованного индекса на неупорядоченной таблице

ON Customers(Phone);

INSERT INTO Customers

( CustumerNo, CustumerName, Address1, Address2, City, State, Zip, Contact, Phone, FedIDNo, DateInSystem)

VALUES

(1,'Alex', 'NewSTR', 'NewSTR2', 'City', 'NS', 'NewZip', 'dfgjs@mail.ru', '(093)1111111', 'qweq', GETDATE()),

(2,'Alex2', 'NewSTR2', 'NewSTR22', 'City2', 'SN', 'NewZip2', 'dfg2@mail.ru', '(093)2222222', 'qwq2', GETDATE()); --CustumerNo должен быть уникальным для каждой строки если CREATE UNIQUE CLUSTERED INDEX

SELECT * FROM Customers;

SELECT * FROM Customers

WHERE Phone = '(093)2222222';

----------------Создание некластеризованного индекса на кластеризованной таблице--------------------------------------

DROP TABLE Customers;

CREATE TABLE Customers

(

CustumerNo int NOT NULL,

CustumerName varchar(25) NOT NULL,

Address1 varchar(25) NOT NULL,

Address2 varchar(25) NOT NULL,

City varchar(15) NOT NULL,

State char(2) NOT NULL,

Zip varchar(10) NOT NULL,

Contact varchar(25) NOT NULL,

Phone char(12),

FedIDNo varchar(10) NOT NULL,

DateInSystem smalldatetime NOT NULL

)

CREATE UNIQUE CLUSTERED INDEX PK_IND1 -- создание собственного кластеризованного индекса

ON Customers(CustumerNo);

CREATE UNIQUE NONCLUSTERED INDEX NC_IND1 -- создание собственного некластеризованного индекса на кластеризованной таблице

ON Customers(Phone);

INSERT INTO Customers

( CustumerNo, CustumerName, Address1, Address2, City, State, Zip, Contact, Phone, FedIDNo, DateInSystem)

VALUES

(1,'Alex', 'NewSTR', 'NewSTR2', 'City', 'NS', 'NewZip', 'dfgjs@mail.ru', '(093)1231212', 'qweq', GETDATE()),

SELECT * FROM Customers; -- Для поиска используем кластеризованный индекс

SELECT * FROM Customers -- Для поиска используем кластеризованный

WHERE Phone = '(093)2221212'; -- и не кластеризированный индексы !!!

-----------------------------------------------------------

/* Изменения индекса ALTER INDEX */

-----------------------------------------------------------

DROP TABLE Customers;

CREATE TABLE Customers

(

CustumerNo int NOT NULL,

CustumerName varchar(25) NOT NULL,

Address1 varchar(25) NOT NULL,

Address2 varchar(25) NOT NULL,

City varchar(15) NOT NULL,

State char(2) NOT NULL,

Zip varchar(10) NOT NULL,

Contact varchar(25) NOT NULL,

Phone char(12),

FedIDNo varchar(10) NOT NULL,

DateInSystem smalldatetime NOT NULL

)

CREATE UNIQUE INDEX PK_IND1 --если включена опция IGNORE_DUP_KEY и нету ограничения UNIQUE будет предупреждение (возможна вставка одинаковых индексов Alex и alex)

ON Customers(CustumerName)

WITH

(

IGNORE_DUP_KEY = ON -- включаем игнорирование различия между строчными и заглавными

);

INSERT INTO Customers

( CustumerNo, CustumerName, Address1, Address2, City, State, Zip, Contact, Phone, FedIDNo, DateInSystem)

VALUES

(1,'Alex', 'NewSTR', 'NewSTR2', 'City', 'NS', 'NewZip', 'dfgjs@mail.ru', '(093)1231212', 'qweq', GETDATE()),

(2,'alex', 'NewSTR2', 'NewSTR22', 'City2', 'SN', 'NewZip2', 'dfg2@mail.ru', '(093)2221212', 'qwq2', GETDATE()); -- Не будет ошибки

SELECT * FROM Customers

ALTER INDEX PK_IND1 -- изменение индекса PK_IND1

ON Customers -- заданого на таблице Customers

REBUILD WITH

(

IGNORE_DUP_KEY = OFF

);

INSERT INTO Customers

( CustumerNo, CustumerName, Address1, Address2, City, State, Zip, Contact, Phone, FedIDNo, DateInSystem)

VALUES

(1,'Alex', 'NewSTR', 'NewSTR2', 'City', 'NS', 'NewZip', 'dfgjs@mail.ru', '(093)1231212', 'qweq', GETDATE()),

(2,'alex', 'NewSTR2', 'NewSTR22', 'City2', 'SN', 'NewZip2', 'dfg2@mail.ru', '(093)2221212', 'qwq2', GETDATE()); -- Не будет ошибки

SELECT * FROM Customers;

----------------------------УДАЛЕНИЕ ИНДЕКСОВ---------------------------------------------------------------------

DROP TABLE Customers;

CREATE TABLE Customers

(

CustumerNo int NOT NULL,

CustumerName varchar(25) NOT NULL,

Address1 varchar(25) NOT NULL,

Address2 varchar(25) NOT NULL,

City varchar(15) NOT NULL,

State char(2) NOT NULL,

Zip varchar(10) NOT NULL,

Contact varchar(25) NOT NULL,

Phone char(12),

FedIDNo varchar(10) NOT NULL,

DateInSystem smalldatetime NOT NULL

)

CREATE UNIQUE INDEX PK_IND1 -- если включена опция IGNORE_DUP_KEY и нет ограничения UNIQUE будет предупреждения (возможна вставка одинаковых индексов Alex и alex)

ON Customers(CustumerName)

WITH

(

IGNORE_DUP_KEY = ON -- включаем игнорирование различия между строчными и заглавными символами

);

INSERT INTO Customers

( CustumerNo, CustumerName, Address1, Address2, City, State, Zip, Contact, Phone, FedIDNo, DateInSystem)

VALUES

(1,'Alex', 'NewSTR', 'NewSTR2', 'City', 'NS', 'NewZip', 'dfgjs@mail.ru', '(093)1231212', 'qweq', GETDATE()),

(2,'alex', 'NewSTR2', 'NewSTR22', 'City2', 'SN', 'NewZip2', 'dfg2@mail.ru', '(093)2221212', 'qwq2', GETDATE()); -- если включена опция IGNORE_DUP_KEY и есть ограничения UNIQUE данная строка не запишется

SELECT * FROM Customers WHERE CustumerName = 'alex'; -- для поиска используем не кластеризованный индекс

DROP INDEX Customers.PK_IND1;

SELECT * FROM Customers WHERE CustumerName = 'alex'; -- для поиска не используем индекс

Добавить комментарий Отменить ответ