-- Inspect tempdb - Tables - T1 - Keys & Indexes:
-- https://msdn.microsoft.com/en-us/library/ms188783.aspx
-- Nase NAZVY indexov:
--		- pki - primary key index - can not be dropped
--		- ci  - clustered index
--		- nci - non clustered index
-- a) no index
-- b1) primary key => clustered index
--		Neda sa DROP-nut pki, iba ci a nci:
--		TYPY objektov a indexov: sys.objects, sys.indexes
--		DATUM VYTVORENIA:
-- b2) CLUSTERED: sys.indexes <=> sysindexes
-- c1) NONCLUSTERED:
-- c2) Default - NONCLUSTERED:

USE tempdb
GO

IF OBJECT_ID('TI','U') IS NOT NULL DROP TABLE TI;
GO

----- a) no index
CREATE TABLE TI(id int, s CHAR(10))

-- b1) primary key => clustered index
--CREATE TABLE TI(id int NOT NULL primary key, s CHAR(10)) -- bez nazvu
--CREATE TABLE TI(id int NOT NULL, s CHAR(10), CONSTRAINT pki_TI primary key (id) ) -- s nazvom

-- Neda sa DROP-nut:
--IF EXISTS (SELECT name FROM sys.indexes WHERE OBJECT_ID=OBJECT_ID('TI') AND
--											  name = 'pki_TI' ) DROP INDEX TI.pki_TI;

-- TYPY objektov a indexov:
--SELECT * FROM sys.objects
SELECT type_desc, count(*) freq FROM sys.objects group by type_desc
--SELECT * FROM sys.indexes
SELECT type_desc, count(*) freq FROM sys.indexes group by type_desc

             SELECT * FROM sys.indexes WHERE OBJECT_ID=OBJECT_ID('TI') -- OBJECT_ID('TI','U')
SELECT name, index_id FROM sys.indexes WHERE OBJECT_ID=OBJECT_ID('TI')

-- DATUM VYTVORENIA:
-- a) Hladaj na zaklade nazvu inexu:
select crdate, i.name ind, object_name(o.id) tab from sysindexes i join sysobjects o ON o.id = i.id where i.name='pki_TI'
-- b) Hladaj na zaklade nazvu tabulky:
select crdate, i.name ind, object_name(o.id) tab from sysindexes i join sysobjects o ON o.id = i.id where object_name(o.id)='TI'

-- b2) CLUSTERED: sys.indexes <=> sysindexes
--IF NOT EXISTS (SELECT name FROM sys.indexes WHERE name = 'ci_TI')
--	CREATE CLUSTERED INDEX [ci_TI] ON TI(id) -- Simpler, however NEBEZPECNE
-- BETTER:
IF EXISTS (SELECT name FROM sys.indexes WHERE OBJECT_ID=OBJECT_ID('TI') AND
											  name = 'ci_TI' ) DROP INDEX TI.ci_TI;
	CREATE CLUSTERED INDEX [ci_TI] ON TI(id)

-- c1) NONCLUSTERED:
--IF NOT EXISTS (SELECT name FROM sys.indexes WHERE name = 'nci_x')
--	CREATE NONCLUSTERED INDEX [nci_x] ON TI(s)

-- c2) Default - NONCLUSTERED:
--IF NOT EXISTS (SELECT name FROM sys.indexes WHERE name = 'nci_x')
--	CREATE INDEX [nci_x] ON TI(s)