8

I am using GUID Id as my partition key and I am facing problem when I am trying to run a stored procedure. To run a store procedure I need to provide partition key ans I am not sure what value should I provide in this case? Please assist.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Dadwals
  • 1,171
  • 2
  • 8
  • 15

2 Answers2

4

If the collection the stored procedure is registered against is a single-partition collection, then the transaction is scoped to all the documents within the collection. If the collection is partitioned, then stored procedures are executed in the transaction scope of a single partition key. Each stored procedure execution must then include a partition key value corresponding to the scope the transaction must run under.

You could refer to the description above which mentioned here.

As @Rafat Sarosh said, GUID Id is not an appropriate partitioning key. Based on your situation , city may be more appropriate.You may need to adjust your database partitioning scheme because the partitioning key can not be deleted or modified after you have defined it.

I suggest you exporting your data to json file then import to a new collection which is partitioned by city via Azure Cosmos DB Data migration tool.

Hope it helps you.

Just for summary:

Issue:

Unable to provide specific partition key value when executing sql to query documents.

Solution:

1.Set EnableCrossPartitionQuery to true when executing query sql.(has performance bottleneck)

2.Consider setting a frequently queried field as a partitioning key.

Jay Gong
  • 23,163
  • 2
  • 27
  • 32
  • I do understand the concept of partition key, but still my first scenario is still a puzzle, are you saying now I have to pass all combinations of cities while executing the store procedure? Which to me doesn't make any sense. – Dadwals Feb 16 '18 at 07:20
  • It will be great if you let me know how should I execute a select query based on tracking id when city is my partition key. That would be great help – Dadwals Feb 16 '18 at 07:22
  • @Dadwals Let's talk in this chat room: https://chat.stackoverflow.com/rooms/165266/jaydadwals – Jay Gong Feb 16 '18 at 07:33
  • I just joined the chat room – Dadwals Feb 16 '18 at 07:42
  • @JayGong I have a scenario like I have to delete a document with different partiotion key (Suppose my partition key is vehicle type), If i have to delete 10 types of vehicle, can i do it in a single go or do I need to run the sp 10 times ? – Antony Aug 07 '19 at 04:07
0

Example your partition key is /id

and your cosmos document is

{
  "id" : abcde
}

When store procedure run, you need to paste: abcde value

So if you want your store procedure running cross partition, it can't Answer from cosmos team https://feedback.azure.com/forums/263030-azure-cosmos-db/suggestions/33550159-support-stored-procedure-execution-over-all-partit

Wolf
  • 6,361
  • 2
  • 28
  • 25