SQL Server Performance Forum – Threads Archive
Seed and Increment property
I have a table titled ‘Models of cars#%92 with three columns; ‘Model_id#%92, ‘Model_name#%92 and ‘Manufacturer#%92. Now the table has more than 100 models and I have designed the ‘Model_id#%92 column with seed (1) and increment (1), and now I need to insert new models between the models so that the order by ‘Manufacturer#%92 does not change, but I#%92 m afraid that is not possible. Isn#%92t it?What are the consequences of to add the new ‘models#%92 at the end of the list? Therefore the ‘Manufacturer#%92 column will not be ordered. Thanks,
Cesar
Since there are only 100 models (and hence 100 rows) you can easily create a new table with the same structure and insert old models and new models into this table. And while doing this you may want to use an increment of 2 or 3 for the identity column in the new table. This will allow you for such future insertions.
Why no retrieve the table order by manufacturer?.
Is some reazon I can´t see?
May in future 2 o 3 must be 5 or 10. Luis Martin …Thus mathematics may be defined as the subject in which we never know what we are talking about, nor whether what we are saying is true.
Bertrand Russell
Cesar, I found this to be the best approch to this kind of Problem. I keep the index field the way it is, add another column called "sequence" make it an integer. insert into the sequence column the order you what your list to display, then when you add new cars to your list the ID field does not matter. And you can change the order by changing the value in the sequence field. Of course when you query the table you will need to order by sequence asc. Hope this works for you. Steven.
This would be a good idea when you want to force a certain order that does not depend on any of the columns in table. But in this case Luis is right. A simple order by ‘Manufacturer#%92 will do. Bambola.
Good! I’m going to do it this way, but I have some beginner doubts. Can I order by this column the ‘physical#%92 table or only the views (queries)?
After order one column, Can I order too another column at he same time? obviously after the first one.
Thank you,
Cesar
If you want to physically order a table by a certain column, you should create a clustered index. And you can order by column1, column2. Bambola.
Ok, thank you. Cesar
]]>