CODEWITHSUNDEEP
sql-serverprimary-key
Alexander Galkin
Alexander Galkin

Reputation: 12554

Inserting data into tables without primary key?

I was surprised to know that SQL Server (I tested the 2008 R2 edition, but this seems to work in earlier versions too) allows inserting data into tables which do not have a primary key (and hence a clustered index) defined.

How is it possible and how the data are stored physically for these tables?

Upvotes: 1

Views: 2412

Answers (1)

davek
davek

Reputation: 22925

Helpful link here:

http://www.sqlmag.com/blog/sql-server-questions-answered-28/sql-server/what-happens-if-i-drop-a-clustered-index-137109

Q: I’ve heard that the clustered index is “the data,” but I don’t fully understand what that means. If I drop a clustered index, will I lose the data?

A: I get asked this question a lot, and I find that index structures tend to confuse people; indexes seem mysterious and, as a result, are unintentionally thought of as very complicated. A table can be stored internally with or without a clustered index. If a table doesn’t have a clustered index, it’s called a heap. If the table has a clustered index, it’s often referred to as a clustered table. When a clustered index is created, SQL Server will temporarily duplicate and sort the data from the heap into the clustered index key order (because the key defines the ordering of the data) and remove the original pages associated with the heap. From this point forward, SQL Server will maintain order logically through a doubly-linked list and a B+ tree that’s used to navigate to specific points within the data.

Upvotes: 3

Related Questions