The intention was to know the reason do we need to bother about fragmentation for tables with 10's or few 100's of pages. I see dev team...

I agree with Phil.  Posting inserts to populate the test table with data will help a whole lot.

Also, the code you posted isn't from SQL Server but, since...

And, sorry... I was reading and answering from top down.  You and the OP got all this together.

If you just want an id you can just add ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) id, as a column to the query to get a sequential id column....

But, not the ID column the OP is looking for.

Simple question.

Who is the Brent Ozar of cloud computing ie all you need to know about SQL management and options in the cloud the does and don't of DBA...

Step 1 would be to get into the habit of using the 2 part naming convention.  That doesn't have so much to do with performance in this case (although there...

I agree with Phil.  Posting inserts to populate the test table with data will help a whole lot.

Also, the code you posted isn't from SQL Server but, since you posted...

Sorry... second look at the code I posted said it was wrong and took it down.

The intention was to know the reason do we need to bother about fragmentation for tables with 10's or few 100's of pages. I see dev team asking question...

If you are only moving the application databases (not the system databases). You can just detach the databases, move the database files to a different location then...

Just remember... your favorite bloggers may not actually be correct. 😉

My recommendation is this... go to all of the BBFAATT (a "Moden-ism for Books, Blogs, Forums, Articles, AI, Talks, and 'Tubes and is pronounced as "bee-bee-fat" and can be bleated...

As a bit of a sidebar, renaming backup files destroys the ability to use the backup information stored in MSDB for research.  If you want to name something, it should...

My...