quote:

---

Originally posted by byrmol

Clusterd Indexes and Keys are NOT the same thing.
A key (in SQL Server) requires an index, but an index does not require a key.

---

quote:

---

Originally posted by Merkin

So, if you will be searching on EITHER First name OR Last Name, I'd probably go for a separate Non Clustered index on each of those columns, because a combined index won't help you find only one of those criteria.

---

quote:

---

Originally posted by byrmol

>>Can you explain how an index (clustered or non-clustered) works if you don't have a key?

You've been spending too much time at the disk level... A good thing for the lead on the storage engine... Love your work by the way!

I was speaking about the difference between a KEY (Relational model) vs SQL Server's implmentation of them.. I am pretty sure I can create an index on a non-key column in sql server...

Paul, are you aware of the TransRelationalModel (TM)? Seems right up your alley. [url]http://www.dbdebunk.com/page/page/1548800.htm[/url]

EDIT: Spelling (what a shock!) and question

DavidM

A front-end is something that tries to violate a back-end.

---

create table TestIndex(NonKey INT NOT NULL)
GO
CREATE NONCLUSTERED INDEX IDX_NONKEY ON TestIndex (NonKey)
--CREATE CLUSTERED INDEX IDX_NONKEY ON TestIndex (NonKey)
GO
DECLARE @Count INT
SET @Count = 0
WHILE @Count < 1000
BEGIN
	INSERT TestIndex(NonKey)
	VALUES (@Count)
	SET @Count = @Count + 1
END
--Duplicate value 
INSERT TestIndex(NonKey)
	VALUES (1)
GO
Select * 
from TestIndex
WHERE NonKey = 1

Select * 
from TestIndex
WHERE NonKey BETWEEN 5 and 8

--DROP table TestIndex

quote:

---

Originally posted by byrmol

>>You can't create an index on a non-key column - the columns that are indexed form its key, by >>definition

You are speaking internally again arn't you? So SQL Server creates an internal "row index key" if there is none defined declaratively?

Given this example of a bag...

create table TestIndex(NonKey INT NOT NULL)
GO
CREATE NONCLUSTERED INDEX IDX_NONKEY ON TestIndex (NonKey)
--CREATE CLUSTERED INDEX IDX_NONKEY ON TestIndex (NonKey)
GO
DECLARE @Count INT
SET @Count = 0
WHILE @Count < 1000
BEGIN
	INSERT TestIndex(NonKey)
	VALUES (@Count)
	SET @Count = @Count + 1
END
--Duplicate value 
INSERT TestIndex(NonKey)
	VALUES (1)
GO
Select * 
from TestIndex
WHERE NonKey = 1

Select * 
from TestIndex
WHERE NonKey BETWEEN 5 and 8

--DROP table TestIndex

So what is happening internally here?

DavidM

A front-end is something that tries to violate a back-end.

---

create function keycount(@Key INT)
returns int
as
begin
declare @return int
select @return = COUNT(*) from test where derivedkey = @key
RETURN @return
end
go
create table test(derivedkey int not null CHECK (dbo.keycount(derivedkey) = 1))
GO
insert test (derivedkey)
	SELECT 1
	UNION ALL 
	SELECT 2
go
insert test (derivedkey)
	SELECT 3
	UNION ALL 
	SELECT 2

quote:

---

Originally posted by byrmol


Do CHECK constraints get an index key? Because I can create a logical key without an index by using UDF's and CHECK constraints...

---

quote:

---

And I think I speak for everyone here in saying that we appreciate the interaction with the SQL Server dev team members and would love to see more of it!

---

CREATE TABLE t(c INT NOT NULL)

ALTER TABLE t ADD CONSTRAINT UC_x_c UNIQUE(c) WITH FILLFACTOR = 80 	-- UNIQUE CONSTRAINT
CREATE UNIQUE INDEX UIX_x_c ON t(c) WITH FILLFACTOR = 80 		-- UNIQUE INDEX

EXEC sp_helpindex t
EXEC sp_helpconstraint t
SELECT CONSTRAINT_NAME FROM INFORMATION_SCHEMA.TABLe_CONSTRAINTS WHERE TABLE_NAME = 't'
--SELECT * FROM sysindexes WHERE id = OBJECT_ID('t')

DROP TABLE t

/*
index_name                                          index_description                                   index_keys                                          
--------------------------------------------------- --------------------------------------------------- ----------
UC_x_c                                              nonclustered, unique, unique key located on FG_LAB1 c
UIX_x_c                                             nonclustered, unique located on FG_LAB1             c

constraint_type                                     constraint_name
--------------------------------------------------- ---------------
UNIQUE (non-clustered)                              UC_x_c

CONSTRAINT_NAME
---------------
UC_x_c
*/

quote:

---

Originally posted by rockmoose

Is there a physical difference in creating a UNIQUE CONTRAINT or a UNIQUE INDEX ?
Are they basically the same thing, or are they treated differently by SQL Server?

---

CREATE TABLE t(c INT NOT NULL)

--ALTER TABLE t ADD CONSTRAINT PK_t_c PRIMARY KEY(c)			-- PRIMARY KEY
/*
Server: Msg 2627, Level 14, State 1, Line 1
Violation of PRIMARY KEY constraint 'PK_t_c'. Cannot insert duplicate key in object 't'.
The statement has been terminated.*/

--ALTER TABLE t ADD CONSTRAINT UC_t_c UNIQUE(c) WITH FILLFACTOR = 80 	-- UNIQUE CONSTRAINT
/*
Server: Msg 2627, Level 14, State 2, Line 1
Violation of UNIQUE KEY constraint 'UC_t_c'. Cannot insert duplicate key in object 't'.
The statement has been terminated.*/

--CREATE UNIQUE INDEX UIX_t_c ON t(c)			 		-- UNIQUE INDEX
/*
Server: Msg 2601, Level 14, State 3, Line 1
Cannot insert duplicate key row in object 't' with unique index 'UIX_t_c'.
The statement has been terminated.*/

CREATE UNIQUE INDEX UIX_t_c ON t(c) WITH IGNORE_DUP_KEY 		-- UNIQUE INDEX
/*
Server: Msg 3604, Level 16, State 1, Line 27
Duplicate key was ignored.*/
--BOL on 3604 : Severity Level 10 !!!???
--Funny this get's caught before a PK violation


INSERT T SELECT 1
INSERT T SELECT 1
GO

DROP TABLE T
GO

quote:

---

Originally posted by rockmoose

Thanks, nice to know that they are the same under the covers.

Found a quirk in BOL / SQL server, a 3604 (IGNORE_DUP_KEY) gets higher severity than PK violation,
contrary to what BOL says !?

Different errors:

CREATE TABLE t(c INT NOT NULL)

--ALTER TABLE t ADD CONSTRAINT PK_t_c PRIMARY KEY(c)			-- PRIMARY KEY
/*
Server: Msg 2627, Level 14, State 1, Line 1
Violation of PRIMARY KEY constraint 'PK_t_c'. Cannot insert duplicate key in object 't'.
The statement has been terminated.*/

--ALTER TABLE t ADD CONSTRAINT UC_t_c UNIQUE(c) WITH FILLFACTOR = 80 	-- UNIQUE CONSTRAINT
/*
Server: Msg 2627, Level 14, State 2, Line 1
Violation of UNIQUE KEY constraint 'UC_t_c'. Cannot insert duplicate key in object 't'.
The statement has been terminated.*/

--CREATE UNIQUE INDEX UIX_t_c ON t(c)			 		-- UNIQUE INDEX
/*
Server: Msg 2601, Level 14, State 3, Line 1
Cannot insert duplicate key row in object 't' with unique index 'UIX_t_c'.
The statement has been terminated.*/

CREATE UNIQUE INDEX UIX_t_c ON t(c) WITH IGNORE_DUP_KEY 		-- UNIQUE INDEX
/*
Server: Msg 3604, Level 16, State 1, Line 27
Duplicate key was ignored.*/
--BOL on 3604 : Severity Level 10 !!!???
--Funny this get's caught before a PK violation


INSERT T SELECT 1
INSERT T SELECT 1
GO

DROP TABLE T
GO

rockmoose

---

quote:

---

BOL is correct - its an informational message.

There's a problem with that message in the translation table that OLEDB uses and so it comes out as sev 16. This is fixed in SQL Server 2005.

---