As you perform inserts updates and deletes, your indexes will become fragmented both internally and externally.
Internal fragmentation is you have a high percentage of free space on your index pages, meaning that SQL Server needs to read more pages when scanning the index.
External fragmentation is when the pages of the index are not in order any more, so SQL Server has to do more work, especially in IO terms to read the index.
If your indexes become too fragmented, at best, your queries will be less efficient but at worst, SQL Server will just stop using the indexes all together, meaning virtually all queries would have to perform a table scan or clustered index scan. This will hurt your performance a lot!
When you reorganise an index, then SQL Server uses the existing index pages and just shuffles data around on those ages. This will alleviate internal fragmentation and can also remove a small amount of external fragmentation. It is a lighter weight operation than rebuild and is always online.
When you rebuild an index, SQL Server actually resorts the data of the index and uses a new set of index pages. This will obviously alleviate both internal and external fragmentation but is a more heavy weight operation and by default causes the index to go offline, although it can be performed as an online operation, depending on your SQL Server version and settings.
Please do not expect to have 0 fragmentation after a Rebuild however. Unless you use a MAXDOP query hint, SQL Server will parallelise the rebuild operation and the more processors involved, the more fragmentation there is likely to be, because each processor or core, will rebuild their section or fragment of the index individually, without regard for each other. This is a trade off between best fragmentation levels and time taken to rebuild the index. For near 0 fragmentation, use MAXDOP 1 and sort the results in TempDB.
The clustered index "is" the table. Rebuilding the index will require enough space to duplicate each existing non-deleted row, page-by-page. Since you don't have Enterprise Edition, you do not have the luxury of rebuilding the index online. This means the table will not be accessible during the rebuild operation.
You'd likely be better off rebuilding by creating a duplicate empty table on a different drive that has plenty of free space with room for foreseeable growth, moving data row-by-row in key order, then renaming the old and new tables. For instance if the original table is Table1 and the new copy is Table2, rename Table1 as Table1-old then rename Table2 as Table1.
Best Answer
Even though it's a bit late, I'm going to field a response with hope that it helps or at least spurns some additional ideas/commentary on this issue because I think it's a good question.
First, and I don't know if you're doing this or not, but please don't assume that high fragmentation levels on the index are always going to cause poor performance. Stale statistics (e.g. sys.dm_db_stats_properties) and high amounts of white space per page (i.e. avg_page_space_used_in_percent column in sys.dm_db_index_physical_stats dmv) hold more relevance regarding performance issues than fragmentation alone. Yes, highly fragmented indexes will generate more read-aheads and you typically do see stale statistics and higher levels of white space per page coupled with fragmentation, but fragmentation isn't directly tied to query plan optimizations nor how much memory loading the index from disk will actually consume. Query plans are affected by statistics and your memory footprint bloats with more white space. For instance, an index that is 99% fragmented but has less than 5% avg. white space and up-to-date statistics is likely not causing you drastic performance issues as compared to either a bad execution plan as a result of stale statistics or constant paging of an index that's too big to fully fit in memory because there's a significant amount of white space present per page.
If fragmentation is truly an issue, you can reduce it, ONLINE, by issuing an
ALTER INDEX ... REORGANIZEstatement as identified by Dan Guzman in the comments. This won't create as streamlined an index as aREBUILDoperation will, but it will reduce your fragmentation. The key here is to identify windows of lower usage on your database and run it then. This could be 15 minutes or multiple hours, obviously the longer the better, but the key here is this operation doesn't rollback and retains any progress made even if you kill it mid-execution.If, in a perfect world where your fragmentation was eliminated, would it make more sense to utilize partitioning on this table? Azure SQL Database does allow for table partitioning and Microsoft has a great article outlining some Partitioning strategies for Azure SQL Database. If your data is non-volitile, partitioning it may help reduce maintenance needs, and if coupled with Table Compression, you may even be able to reduce your overall storage footprint as well. Alberto Murillo's earlier answer alludes to utilizing Horizontal Partitioning based on a data region, and this approach may help create some maintenance windows for you as your data would be more regionally specific instead of global.
Transitioning to a partitioned table won't be easy with your current absence of maintenance windows, but you may be able to utilize an approach outlined by Maria Zakourdaev which uses Partitioned Views over the top of your current table and a new partitioned table to start partitioning future data. As time goes on (and hopefully your old data is purged), you can eventually transition fully over to the partitioned table. Again, I don't know your data or application, but maybe this approach is something you can employ.