I am using Microsoft SQL Server 2016.
For my products table;
I need to define a ClusteredIndex with 2 fields of Product Type and Product Code. ProductType(tinyint), ProductCode(varchar(32)).
I know that ProductId is the right option for this, but it affects my software development speed.
But in terms of speed, I want my Product table to run fast.
Estimately there will be 1,000,000 records in the table.
What I want to ask is if using varchar(16) instead of varchar(32) in ProductCode, how much will it affect performance in queries?
Is there a software that I can test as ProductCode varchar(32) and varchar(16) in 1,000,000 lines? Can I do this on sql Management studio?
16 characters is enough right now. However, the user may want the product code to be given by the system itself in this case I want to automatically set a product code with newid.
Product Id(int) is not the right solution for me. Because many transactions (orders, warehouses, sales) that have their own product codes are executed through this code.
Edit: I tested with SqQueryStress based on the comment.
I am sharing the results.
I created 2 tables as products and sales.
- productsID.id clustered index
- salesID.productId non clustered index
- products16.productCode clustered index
- sales16.productCode non clustered index
- products36.productCode clustered index
- sales36.productCode non clustered index
I entered 100,000 products, random results
productsId 06:03 [![enter image description here]]
products16 05:46 [![enter image description here]]
I entered a sales record from 10,000 random products tables.
I listed the sales reports according to the data I entered. (1000 times)
Does that mean reporting 1000 * 10,000 (sales item) 10,000,000 rows?
I used SqlQueryStress for the first time, I don't know much about interpretation.
When I pull 10,000,000 sales data. Reporting sales36 and sales16 as well 02:55 (175 seconds) 02:05 (125 seconds) Is there a 40% performance difference?