CODEWITHSUNDEEP
sql-serversql-server-2008
Craig
Craig

Reputation: 18684

Using a GUID in a WHERE clause - Performance?

In our application, we create a lot of rows in a single table, based on some calculations. Due to the volume of rows, we use the BULK INSERT from within our .Net application, to write the rows quickly.

But, we need to know which IDs were written in that BULK INSERT call. So, the idea is to generate a GUID, and add it to each row being written in the Bulk Update. So the GUID gets persisted in the table.

If we need to see what rows were written, we can SELECT .. FROM TABLE ... WHERE SessionID = the guid we generated.

I'd have a column on the table called SessionID (for example), VARCHAR(50) NOT NULL, Indexed.

Is this acceptable design?

Upvotes: 1

Views: 702

Answers (1)

Phil Wright
Phil Wright

Reputation: 22906

You should create the column with type 'uniqueidentifier', which is intended for storing GUID values. Internally it will store it as a 16 byte (128 bit) integer instead of the much slower character string that you want to use.

Performance should be very good because you are just comparing 16 byte values for building the index, which is a pretty quick operation.

Upvotes: 3

Related Questions