Insert into table with Clustered index

Question

What is the best practice to insert data into table with the clustered index ?? - sql azure required it. I mean programically by scharp ?? I´m friend with GUID but they aren´t Int :-(. I Usually create some GUID, check that exist in table - if no: insert - if yes: create new and check again...

Answer 1

For INSERT statements there is no difference if an (clustered) index exists or not, it's always the same.

A good candidate for a primary key + clustered index is an integer column with IDENTITY property, it generates auto-incremented values.

---

Olaf Helper
* cogito ergo sum * errare humanum est * quote erat demonstrandum *
Wenn ich denke, ist das ein Fehler und das beweise ich täglich
Blog Xing

Answer 2

I agree with Olaf on the primary key + clustered index with integer with Identity.  Use an ever increasing value.  If you use a guid use the NEWSEQUENTIALID function.

a few additional thoughts:

• Try to create a clustered index on the most frequently used column to retrieve data.
• created a (clustered) index on as few columns as possible
  
• Clustered index columns shoudn't have duplicate values
• Put clustered indexes on columns which are not updated

There is a very good write up on best practices on SQL Indexing (not azure specific) here: http://blogs.technet.com/b/josebda/archive/2009/03/17/indexing-best-practices-for-sql-server-2008.aspx

EDIT: Concerning an INSERT: i don't think it's always the same whether you have an (clustered) index or not. A Clustered index creates an index in which the logical order of the key values determines the physical order of the corresponding rows in a table. Depending on your choice of clustered index this may affect insert performance.

Hope it helps.

---

Dampee (blog | twitter)

Answer 3

Hi Marchellito69,

Like Olaf said, it doesn´t really matter if a clustered index exists or not. If your primary key is a GUID (uniqueidentifier) you can use default newid():

CREATE TABLE Customers
(
 CustomerID uniqueidentifier NOT NULL
   DEFAULT newid(),
 Company nvarchar(30)
)
ALTER TABLE Customers
ADD CONSTRAINT Ct_ID PRIMARY KEY (CustomerID)

Therefore, everytime you perform an INSERT in that table it will automatically generate a unique (random) GUID for each record. You no longer need to perform that check you mentioned. And in your csharp code, you no longer need to provide the ID parameter anymore, as well.

Hope this helps!

---

Cheers, Carlos Sardo

Answer 4

Hi, I assume you are talking about clusetered index created on primary key column.

If so you can use GUID, and you need not check for atomocity explicitely, you can define default constraint on that as NEWID() For example

create table test(col1 uniqueidentifier primary key default NEWID(),col2 varchar(10))

GUID is not INT, but still it is not a bad practice to use.

If you are keen on using INT, use define identity for the column and set auto increment

If you are using SQL 2012, you use sequences also which is very famous on Oracle.

Hope it helps

---

Please mark the replies as Answered if they help and Vote if you found them helpful.

Answer 5

A clustered indexed is actually a constrain imposed on writes and forces all rows (at database page level) to be in physical sorted order.

Where a clustered index really shines, is on reads which retrieve a set of rows. For example, getting all orders in a given time frame. Assume the clustered index is on date (or on Date + Order Id). Once the query locates the first order via the index, subsequent orders can be cheaply retrieved, because they are physically near on the same database page and on subsequent database pages and can be easily traversed to, without an index look-up.

That being said, I would create clustered indexes for read efficiency, if that is possible; and worry about write efficiency only when it is more important than read efficiency.

Answer 6

Thank you guys, problem solved